Drizzle skill: 10 schema + query patterns you can ship

The prompt

Add a Drizzle `pgTable` for `agents` to `src/database/schemas/agent.ts`. Columns: a text `id` primary key defaulted with `idGenerator('agents')`, a `userId` FK to `users.id` with `onDelete: 'cascade'`, a `slug` varchar(100) unique, a `chatConfig` JSONB column typed against a `LobeAgentChatConfig` interface, and the standard created/updated/accessed timestamps via the `...timestamps` spread from `_helpers.ts`. Add a unique index on `(clientId, userId)`. Export `$inferInsert` and `$inferSelect` types.

What slides.md looks like

export const agents = pgTable(
  'agents',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => idGenerator('agents'))
      .notNull(),
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),
    slug: varchar('slug', { length: 100 }).unique(),
    chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(),
    ...timestamps,
  },
  (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],
);

export type NewAgent = typeof agents.$inferInsert;
export type AgentItem = typeof agents.$inferSelect;

Pairs with

The prompt

I just added a `lastSeenAt` timestamptz column to the `users` schema. Generate the Drizzle migration with `drizzle-kit generate` (config at `drizzle.config.ts`, dialect postgresql, strict true), show me the generated SQL before anything touches the database, and only then run `drizzle-kit migrate`. If the generated file does a destructive column rename instead of an add, stop and tell me — I want to hand-edit it into an additive migration with a backfill.

What slides.md looks like

# drizzle.config.ts already points at the schema + migrations dir
npx drizzle-kit generate
# → packages/database/migrations/0007_curly_magneto.sql

-- 0007_curly_magneto.sql (review before applying)
ALTER TABLE "users" ADD COLUMN "last_seen_at" timestamp with time zone;

npx drizzle-kit migrate   # applies pending migrations in order

Pairs with

The prompt

Write a repository method `getRunTopics(runId)` that returns eval-run topics joined to their test cases and topics. Use the `db.select()` builder with explicit `leftJoin` — do NOT use `db.query.agentEvalRunTopics.findMany` or `with:`, our house style forbids the relational API. Select `runId`, `score`, the full `testCase` row, and the full `topic` row. Filter by `runId`, order by `createdAt` ascending.

What slides.md looks like

const rows = await this.db
  .select({
    runId: agentEvalRunTopics.runId,
    score: agentEvalRunTopics.score,
    testCase: agentEvalTestCases,
    topic: topics,
  })
  .from(agentEvalRunTopics)
  .leftJoin(
    agentEvalTestCases,
    eq(agentEvalRunTopics.testCaseId, agentEvalTestCases.id),
  )
  .leftJoin(topics, eq(agentEvalRunTopics.topicId, topics.id))
  .where(eq(agentEvalRunTopics.runId, runId))
  .orderBy(asc(agentEvalRunTopics.createdAt));

Pairs with

The prompt

Write a method that returns each eval dataset with its test-case count. Use `db.select()` with `count(agentEvalTestCases.id).as('testCaseCount')`, a `leftJoin` on `datasetId`, and `groupBy(agentEvalDatasets.id)`. Then add a composite index on `agentEvalTestCases` over `(datasetId, sortOrder)` so the join and ordering are index-backed. Return the index as an array entry from the table's second callback argument.

What slides.md looks like

const rows = await this.db
  .select({
    id: agentEvalDatasets.id,
    name: agentEvalDatasets.name,
    testCaseCount: count(agentEvalTestCases.id).as('testCaseCount'),
  })
  .from(agentEvalDatasets)
  .leftJoin(
    agentEvalTestCases,
    eq(agentEvalDatasets.id, agentEvalTestCases.datasetId),
  )
  .groupBy(agentEvalDatasets.id);

// in the schema table callback:
(t) => [index('test_cases_dataset_sort_idx').on(t.datasetId, t.sortOrder)],

Pairs with

The prompt

Create a junction table `agents_knowledge_bases` linking `agents` and `knowledgeBases`. Columns: `agentId` and `knowledgeBaseId` as text FKs (both `onDelete: 'cascade'`), a `userId` text FK for row scoping (also cascade), an `enabled` boolean defaulting true, and the `...timestamps` spread. Make the primary key composite over `(agentId, knowledgeBaseId)` using `primaryKey({ columns: [...] })` returned from the table callback.

What slides.md looks like

export const agentsKnowledgeBases = pgTable(
  'agents_knowledge_bases',
  {
    agentId: text('agent_id')
      .references(() => agents.id, { onDelete: 'cascade' })
      .notNull(),
    knowledgeBaseId: text('knowledge_base_id')
      .references(() => knowledgeBases.id, { onDelete: 'cascade' })
      .notNull(),
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),
    enabled: boolean('enabled').default(true),
    ...timestamps,
  },
  (t) => [primaryKey({ columns: [t.agentId, t.knowledgeBaseId] })],
);

Pairs with

The prompt

From the `agents` table, derive a drizzle-zod insert schema with `createInsertSchema(agents)`, then narrow it for the public create endpoint: omit `id`, `userId`, and the timestamp columns (the server sets those), and require `slug` to be a 3–100 char slug. Export the narrowed schema as `createAgentInput` and infer its TypeScript type. Show me how to parse `req.body` with it in the route handler.

What slides.md looks like

import { createInsertSchema } from 'drizzle-zod';
import { z } from 'zod';

export const insertAgentSchema = createInsertSchema(agents);

export const createAgentInput = insertAgentSchema
  .omit({ id: true, userId: true, createdAt: true, updatedAt: true, accessedAt: true })
  .extend({ slug: z.string().min(3).max(100) });

export type CreateAgentInput = z.infer<typeof createAgentInput>;

// route handler
const input = createAgentInput.parse(req.body);

Pairs with

The prompt

Add soft-delete to the `agents` table: a nullable `deletedAt` timestamptz column (absent means live). Write a `softDelete(id)` repository method that sets `deletedAt` and bumps `updatedAt` to now instead of issuing a DELETE. Write a `listActive(userId)` method that filters `isNull(agents.deletedAt)` and `eq(agents.userId, userId)`. Do not invent a status string like 'deleted' — use the nullable timestamp as the source of truth.

What slides.md looks like

// schema
deletedAt: timestamptz('deleted_at'),  // null = live row

// soft delete instead of DELETE
await this.db
  .update(agents)
  .set({ deletedAt: new Date(), updatedAt: new Date() })
  .where(eq(agents.id, id));

// only-live read
const rows = await this.db
  .select()
  .from(agents)
  .where(and(eq(agents.userId, userId), isNull(agents.deletedAt)));

Pairs with

The prompt

Write an upsert for `userSignupLogs` keyed on `id`. Build the update `set` only from values that are actually present — use a `compactUndefined` helper to drop undefined scalars rather than wrapping every column in COALESCE. For the JSONB `stageResults` column, append the new stage via a named helper `appendStageResult(stage, result)` so the method reads as business intent. Always bump `updatedAt`.

What slides.md looks like

const updateValues = compactUndefined({
  email: record.email ?? undefined,
  ip: record.ip ?? undefined,
});

await db
  .insert(userSignupLogs)
  .values(values)
  .onConflictDoUpdate({
    target: userSignupLogs.id,
    set: {
      ...updateValues,
      stageResults: appendStageResult(stage, result),
      updatedAt: new Date(),
    },
  });

Pairs with

The prompt

Write a method `getTaskTree(rootTaskId, userId)` that walks a self-referencing `tasks` table with a recursive CTE. Keep it as raw `db.execute<TaskTreeRow>(sql...)` — there's no clean WITH RECURSIVE builder and a rewrite would add depth-based roundtrips. But interpolate the schema column refs (`tasks.id`, `tasks.parentTaskId`) instead of string literals, scope every leg of the recursion to `createdByUserId = userId`, and type the rows with a narrow `TaskTreeRow` interface.

What slides.md looks like

interface TaskTreeRow {
  id: string;
  parent_task_id: string | null;
}

const { rows } = await db.execute<TaskTreeRow>(sql`
  WITH RECURSIVE task_tree AS (
    SELECT ${tasks.id}, ${tasks.parentTaskId}
    FROM ${tasks}
    WHERE ${tasks.id} = ${rootTaskId}
      AND ${tasks.createdByUserId} = ${userId}
    UNION ALL
    SELECT ${tasks.id}, ${tasks.parentTaskId}
    FROM ${tasks}
    JOIN task_tree ON ${tasks.parentTaskId} = task_tree.id
    WHERE ${tasks.createdByUserId} = ${userId}
  )
  SELECT * FROM task_tree
`);

Pairs with

The prompt

Write a `scripts/seed.ts` that seeds the local database: 20 users, then 100 agents distributed across those users, then 300 agent/knowledge-base junction rows. Use `@faker-js/faker` for names and slugs. Type each batch with the table's `$inferInsert` so a schema change breaks the seed at compile time. Insert parents before children to satisfy the foreign keys, and wrap the whole thing in a single transaction so a failure rolls back cleanly.

What slides.md looks like

import { faker } from '@faker-js/faker';

await db.transaction(async (tx) => {
  const userRows: (typeof users.$inferInsert)[] = Array.from(
    { length: 20 },
    () => ({ id: idGenerator('users'), email: faker.internet.email() }),
  );
  await tx.insert(users).values(userRows);

  const agentRows: (typeof agents.$inferInsert)[] = userRows.flatMap((u) =>
    Array.from({ length: 5 }, () => ({
      id: idGenerator('agents'),
      userId: u.id!,
      slug: faker.helpers.slugify(faker.word.words(2)),
    })),
  );
  await tx.insert(agents).values(agentRows);
});

Pairs with