home / skills / andrelandgraf / fullstackrecipes / using-drizzle-queries

using-drizzle-queries skill

This skill helps you write type-safe Drizzle ORM queries in TypeScript for select, insert, update, delete, and relational data.

npx playbooks add skill andrelandgraf/fullstackrecipes --skill using-drizzle-queries

Review the files below or copy the command above to add this skill to your agents.

Files (1)

SKILL.md

3.2 KB

---
name: using-drizzle-queries
description: Write type-safe database queries with Drizzle ORM. Covers select, insert, update, delete, relational queries, and adding new tables.
---

# Working with Drizzle

Write type-safe database queries with Drizzle ORM. Covers select, insert, update, delete, relational queries, and adding new tables.

## Implement Working with Drizzle

Write type-safe database queries with Drizzle ORM. Covers select, insert, update, delete, relational queries, and adding new tables.

**See:**

- Resource: `using-drizzle-queries` in Fullstack Recipes
- URL: https://fullstackrecipes.com/recipes/using-drizzle-queries

---

### Writing Queries

Use Drizzle's query API for type-safe database operations:

```typescript
import { db } from "@/lib/db/client";
import { chats } from "@/lib/chat/schema";
import { eq, desc } from "drizzle-orm";

// Select all
const allChats = await db.select().from(chats);

// Select with filter
const userChats = await db
  .select()
  .from(chats)
  .where(eq(chats.userId, userId))
  .orderBy(desc(chats.createdAt));

// Select single record
const chat = await db
  .select()
  .from(chats)
  .where(eq(chats.id, chatId))
  .limit(1)
  .then((rows) => rows[0]);
```

### Inserting Data

```typescript
import { db } from "@/lib/db/client";
import { chats } from "@/lib/chat/schema";

// Insert single record
const [newChat] = await db
  .insert(chats)
  .values({
    userId,
    title: "New Chat",
  })
  .returning();

// Insert multiple records
await db.insert(messages).values([
  { chatId, role: "user", content: "Hello" },
  { chatId, role: "assistant", content: "Hi there!" },
]);
```

### Updating Data

```typescript
import { db } from "@/lib/db/client";
import { chats } from "@/lib/chat/schema";
import { eq } from "drizzle-orm";

await db
  .update(chats)
  .set({ title: "Updated Title" })
  .where(eq(chats.id, chatId));
```

### Deleting Data

```typescript
import { db } from "@/lib/db/client";
import { chats } from "@/lib/chat/schema";
import { eq } from "drizzle-orm";

await db.delete(chats).where(eq(chats.id, chatId));
```

### Using Relational Queries

For queries with relations, use the query API:

```typescript
import { db } from "@/lib/db/client";

const chatWithMessages = await db.query.chats.findFirst({
  where: eq(chats.id, chatId),
  with: {
    messages: {
      orderBy: (messages, { asc }) => [asc(messages.createdAt)],
    },
  },
});
```

### Adding New Tables

1. Create the schema in the feature's library folder:

```typescript
// src/lib/feature/schema.ts
import { pgTable, text, uuid, timestamp } from "drizzle-orm/pg-core";

export const items = pgTable("items", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
```

1. Import the schema in `src/lib/db/client.ts`:

```typescript
import * as itemSchema from "@/lib/feature/schema";

const schema = {
  ...authSchema,
  ...chatSchema,
  ...itemSchema,
};
```

1. Generate and run migrations:

```bash
bun run db:generate
bun run db:migrate
```

---

## References

- [Drizzle ORM Select](https://orm.drizzle.team/docs/select)
- [Drizzle ORM Insert](https://orm.drizzle.team/docs/insert)
- [Drizzle ORM Relational Queries](https://orm.drizzle.team/docs/rqb)

Overview

This skill teaches how to write type-safe database queries using Drizzle ORM in a TypeScript full-stack app. It covers selecting, inserting, updating, deleting, relational queries, and adding new tables with practical code examples. The guidance is geared toward production-ready patterns and easy integration into feature libraries. Expect clear snippets for common CRUD operations and relations.

How this skill works

The skill shows how to use Drizzle's query API and typed table schemas to perform database operations via a shared db client. It demonstrates select(), insert(), update(), delete(), and db.query.* relational patterns, including ordering and limiting results. It also walks through adding a new table schema, importing it into the central schema object, and generating migrations. Code examples use TypeScript types and Drizzle helpers (eq, desc, orderBy).

When to use it

Building or maintaining server-side data access in a TypeScript app
Implementing type-safe CRUD operations with Postgres and Drizzle ORM
Adding relationships and eager-loading related rows in queries
Introducing new tables/features and wiring them into migrations
Ensuring compile-time safety for SQL-like operations

Best practices

Define table schemas per feature and export them from feature folders
Centralize schemas in the DB client so migrations and queries share types
Prefer db.query.* for relational queries to eager-load relations with ordering
Use returning() on inserts when you need the created row with generated fields
Wrap writes in transactions when performing multiple related inserts/updates/deletes

Example use cases

Fetch all user chats ordered by creation date with db.select().from(...).orderBy(desc(...))
Insert a new chat and immediately return its generated id and timestamps using .insert(...).returning()
Update a chat title with db.update().set(...).where(eq(...))
Delete a chat by id with db.delete(...).where(eq(...))
Add a new items table in a feature folder, import its schema into the central schema object, then generate and run migrations

FAQ

How do I load related rows (like messages for a chat)?

Use the relational API: db.query.chats.findFirst({ where: eq(chats.id, id), with: { messages: { orderBy: (m, { asc }) => [asc(m.createdAt)] } } }) to eager-load ordered messages.

How do I add a new table to the project?

Create a pgTable schema in the feature folder, export it, merge it into the central schema object in the DB client, then run the generate and migrate commands to produce and apply migrations.