Claude Code + Drizzle ORM: PostgreSQL, Migrations, Zod, and CI

Drizzle ORM is a TypeScript-first ORM that keeps you close to SQL while giving your application strong static types. Instead of hiding the database behind a large generated client, Drizzle lets you describe tables in TypeScript and then write queries that still look like SQL. That makes it a good fit for Claude Code: the agent can read a schema, trace relations, produce migrations, and explain why a query uses a particular index.

The risk is that database work looks finished too early. A schema file can compile while a migration is dangerous. A query can return data while leaking too many columns. A seed script can work once and fail on the second run. A transaction can keep the database connection open while waiting for email or a webhook. Claude Code is useful here, but only when the request includes the whole database workflow.

This guide uses a small blog API as the running example: PostgreSQL, drizzle-orm, drizzle-kit, node-postgres, seed data, transactions, Zod validation, and CI checks. Keep the official Drizzle ORM docs, Drizzle Kit docs, transaction guide, and Drizzle Zod docs nearby. For Claude Code itself, use the official Claude Code documentation. Internally, pair this article with the Prisma ORM comparison, database migration guide, and Zod validation guide.

The Workflow

Do not ask Claude Code for “a Drizzle setup” and stop there. Ask for the schema, migration configuration, generated SQL review notes, queries, transactions, seed data, validation, and CI.

flowchart LR
  A["Requirements prompt"] --> B["Typed schema.ts"]
  B --> C["drizzle-kit generate"]
  C --> D["SQL migration review"]
  D --> E["Queries, transactions, seed"]
  E --> F["Zod validation"]
  F --> G["CI: generate, check, migrate, test"]

Use a prompt like this:

Implement the database layer with Drizzle ORM.

Stack:
- PostgreSQL
- drizzle-orm, drizzle-kit, node-postgres
- tables: User, Post, Category, Comment, AuditLog
- Post status: draft, published, archived
- unique email and slug
- list posts by status, publishedAt, author, category, and search
- cascade only comments and join rows when a post is deleted
- do not delete posts when a user is deleted

Return:
1. package.json scripts
2. drizzle.config.ts
3. db/schema.ts
4. db/client.ts
5. create/list/publish query functions
6. db/seed.ts
7. Zod validation before insert
8. GitHub Actions CI
9. migration SQL review checklist

This prompt gives Claude Code enough product context to make database decisions visible. The important part is not only getting code; it is getting reviewable code.

Project Setup

The following scripts cover local development and CI. db:check is especially useful because it checks migration consistency before a pull request becomes a deployment problem.

{
  "type": "module",
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:check": "drizzle-kit check",
    "db:studio": "drizzle-kit studio",
    "db:seed": "tsx db/seed.ts",
    "typecheck": "tsc --noEmit",
    "test": "vitest run"
  },
  "dependencies": {
    "drizzle-orm": "latest",
    "pg": "latest",
    "zod": "latest"
  },
  "devDependencies": {
    "@types/pg": "latest",
    "drizzle-kit": "latest",
    "tsx": "latest",
    "typescript": "latest",
    "vitest": "latest"
  }
}

// drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Use generate and review the generated SQL before applying it. Direct push-style workflows are convenient for prototypes, but production databases need a migration history that can be read in a pull request.

Type-Safe Schema

The schema below is intentionally practical: users cannot cascade-delete posts, posts can cascade-delete comments and join-table rows, and public listing queries have indexes that match their filters.

// db/schema.ts
import { relations } from "drizzle-orm";
import {
  boolean,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const postStatus = pgEnum("post_status", [
  "draft",
  "published",
  "archived",
]);

export const users = pgTable(
  "users",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
    role: varchar("role", { length: 40 }).default("editor").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("users_email_unique").on(table.email),
    index("users_role_idx").on(table.role),
  ],
);

export const categories = pgTable(
  "categories",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
  },
  (table) => [uniqueIndex("categories_slug_unique").on(table.slug)],
);

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 160 }).notNull(),
    title: varchar("title", { length: 160 }).notNull(),
    body: text("body").notNull(),
    status: postStatus("status").default("draft").notNull(),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "restrict" }),
    viewCount: integer("view_count").default(0).notNull(),
    featured: boolean("featured").default(false).notNull(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("posts_slug_unique").on(table.slug),
    index("posts_status_published_at_idx").on(table.status, table.publishedAt),
    index("posts_author_id_idx").on(table.authorId),
  ],
);

export const postCategories = pgTable(
  "post_categories",
  {
    postId: uuid("post_id")
      .notNull()
      .references(() => posts.id, { onDelete: "cascade" }),
    categoryId: uuid("category_id")
      .notNull()
      .references(() => categories.id, { onDelete: "cascade" }),
  },
  (table) => [primaryKey({ columns: [table.postId, table.categoryId] })],
);

export const comments = pgTable(
  "comments",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    postId: uuid("post_id")
      .notNull()
      .references(() => posts.id, { onDelete: "cascade" }),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "restrict" }),
    body: text("body").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    index("comments_post_created_at_idx").on(table.postId, table.createdAt),
    index("comments_author_id_idx").on(table.authorId),
  ],
);

export const auditLogs = pgTable(
  "audit_logs",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    action: varchar("action", { length: 80 }).notNull(),
    targetId: uuid("target_id").notNull(),
    metadata: jsonb("metadata").$type<Record<string, unknown>>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index("audit_logs_action_created_at_idx").on(table.action, table.createdAt)],
);

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  comments: many(comments),
  categories: many(postCategories),
}));

export const categoriesRelations = relations(categories, ({ many }) => ({
  posts: many(postCategories),
}));

export const postCategoriesRelations = relations(postCategories, ({ one }) => ({
  post: one(posts, { fields: [postCategories.postId], references: [posts.id] }),
  category: one(categories, {
    fields: [postCategories.categoryId],
    references: [categories.id],
  }),
}));

export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  author: one(users, { fields: [comments.authorId], references: [users.id] }),
}));

export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Ask Claude Code to explain every index. If it cannot tie an index to a query or screen, the index may be noise. If a critical list query has no matching index, the schema is not finished.

Migrations

Generate, check, then apply:

npm run db:generate
npm run db:check
npm run db:migrate

Review the generated SQL before it reaches production:

CREATE TYPE "public"."post_status" AS ENUM('draft', 'published', 'archived');

CREATE TABLE "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "email" varchar(255) NOT NULL,
  "name" varchar(120) NOT NULL,
  "role" varchar(40) DEFAULT 'editor' NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE "posts"
  ADD CONSTRAINT "posts_author_id_users_id_fk"
  FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE restrict;

CREATE UNIQUE INDEX "users_email_unique" ON "users" ("email");
CREATE UNIQUE INDEX "posts_slug_unique" ON "posts" ("slug");
CREATE INDEX "posts_status_published_at_idx" ON "posts" ("status", "published_at");

The review is straightforward but strict: look for unexpected drops, new NOT NULL columns on existing tables, broad cascades, missing unique constraints, and indexes that do not match real WHERE and ORDER BY clauses.

Queries and Transactions

// db/client.ts
import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });

// db/posts.ts
import { and, desc, eq, ilike, sql } from "drizzle-orm";
import { z } from "zod";
import { db } from "./client";
import {
  auditLogs,
  categories,
  comments,
  postCategories,
  posts,
  users,
} from "./schema";
import { createPostInputSchema } from "./validation";

type CreatePostInput = z.infer<typeof createPostInputSchema>;

export async function createPost(input: CreatePostInput) {
  const data = createPostInputSchema.parse(input);

  return db.transaction(async (tx) => {
    const [post] = await tx
      .insert(posts)
      .values({
        slug: data.slug,
        title: data.title,
        body: data.body,
        authorId: data.authorId,
      })
      .returning();

    for (const slug of data.categorySlugs) {
      const [category] = await tx
        .insert(categories)
        .values({ slug, name: slug })
        .onConflictDoUpdate({
          target: categories.slug,
          set: { name: slug },
        })
        .returning();

      await tx
        .insert(postCategories)
        .values({ postId: post.id, categoryId: category.id })
        .onConflictDoNothing();
    }

    await tx.insert(auditLogs).values({
      action: "post.create",
      targetId: post.id,
      metadata: { slug: post.slug },
    });

    return post;
  });
}

export async function listPublishedPosts(params: {
  page?: number;
  perPage?: number;
  search?: string;
} = {}) {
  const page = Math.max(params.page ?? 1, 1);
  const perPage = Math.min(Math.max(params.perPage ?? 20, 1), 50);
  const where = params.search
    ? and(eq(posts.status, "published"), ilike(posts.title, `%${params.search}%`))
    : eq(posts.status, "published");

  const [items, [{ total }]] = await Promise.all([
    db
      .select({
        id: posts.id,
        slug: posts.slug,
        title: posts.title,
        publishedAt: posts.publishedAt,
        authorName: users.name,
        commentCount: sql<number>`count(${comments.id})::int`,
      })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .leftJoin(comments, eq(comments.postId, posts.id))
      .where(where)
      .groupBy(posts.id, posts.slug, posts.title, posts.publishedAt, users.name)
      .orderBy(desc(posts.publishedAt), desc(posts.createdAt))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db
      .select({ total: sql<number>`count(*)::int` })
      .from(posts)
      .where(where),
  ]);

  return {
    items,
    pagination: {
      page,
      perPage,
      total,
      totalPages: Math.ceil(total / perPage),
    },
  };
}

export async function publishPost(postId: string) {
  return db.transaction(async (tx) => {
    const [current] = await tx
      .select({ id: posts.id, slug: posts.slug, status: posts.status })
      .from(posts)
      .where(eq(posts.id, postId))
      .limit(1);

    if (!current) throw new Error("Post not found");
    if (current.status === "published") return current;

    const [published] = await tx
      .update(posts)
      .set({
        status: "published",
        publishedAt: new Date(),
        updatedAt: new Date(),
      })
      .where(eq(posts.id, postId))
      .returning({ id: posts.id, slug: posts.slug, status: posts.status });

    await tx.insert(auditLogs).values({
      action: "post.publish",
      targetId: published.id,
      metadata: { slug: published.slug },
    });

    return published;
  });
}

Keep external side effects out of transactions. If publishing should send email, write an outbox row in the transaction and let a worker send it after commit.

Seed Data and Zod

Seed scripts should be idempotent. They are run repeatedly by developers and CI.

// db/seed.ts
import { db, pool } from "./client";
import { categories, postCategories, posts, users } from "./schema";

async function main() {
  const [user] = await db
    .insert(users)
    .values({ email: "masa@example.com", name: "Masa", role: "admin" })
    .onConflictDoUpdate({
      target: users.email,
      set: { name: "Masa", role: "admin", updatedAt: new Date() },
    })
    .returning();

  const [category] = await db
    .insert(categories)
    .values({ slug: "drizzle", name: "Drizzle ORM" })
    .onConflictDoUpdate({
      target: categories.slug,
      set: { name: "Drizzle ORM" },
    })
    .returning();

  const [post] = await db
    .insert(posts)
    .values({
      slug: "claude-code-drizzle-demo",
      title: "Claude Code Drizzle demo",
      body: "A seeded post for local verification.",
      status: "published",
      authorId: user.id,
      publishedAt: new Date(),
    })
    .onConflictDoUpdate({
      target: posts.slug,
      set: { title: "Claude Code Drizzle demo", updatedAt: new Date() },
    })
    .returning();

  await db
    .insert(postCategories)
    .values({ postId: post.id, categoryId: category.id })
    .onConflictDoNothing();
}

main()
  .finally(async () => {
    await pool.end();
  });

Runtime validation still matters. TypeScript types do not inspect API payloads.

// db/validation.ts
import { createInsertSchema } from "drizzle-orm/zod";
import { z } from "zod";
import { posts } from "./schema";

export const createPostInputSchema = createInsertSchema(posts, {
  slug: (schema) =>
    schema
      .min(3)
      .max(160)
      .regex(/^[a-z0-9-]+$/, "Use lowercase letters, numbers, and hyphens"),
  title: (schema) => schema.min(1).max(160),
  body: (schema) => schema.min(50),
})
  .pick({ slug: true, title: true, body: true, authorId: true })
  .extend({
    categorySlugs: z.array(z.string().min(1).max(120)).min(1).max(5),
  });

Think of Zod as the runtime boundary and database constraints as the final integrity boundary. They overlap, but they do not replace each other.

CI Check

# .github/workflows/drizzle.yml
name: drizzle

on:
  pull_request:

jobs:
  db:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: app
          POSTGRES_DB: app_test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    env:
      DATABASE_URL: postgresql://app:app@localhost:5432/app_test
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: npm
      - run: npm ci
      - run: npm run db:generate
      - run: npm run db:check
      - run: npm run db:migrate
      - run: npm run db:seed
      - run: npm run typecheck
      - run: npm test

CI proves that the generated migration can run against an empty test database. It does not prove that a production data migration is safe. Ask Claude Code to include that limitation in the PR summary.

Use Cases, Pitfalls, and CTA

Use case	What matters	Claude Code prompt
Editorial CMS	slug uniqueness, published indexes, author relations	”Explain which index supports each list query.”
SaaS admin	tenant boundaries, audit logs, restricted deletes	”Do not let user deletion remove business records.”
Course platform	progress updates, access checks, transactions	”Keep progress and audit logs consistent in one transaction.”
Revenue content	CTA events, product links, article completion	”Add analytics tables without weakening existing constraints.”

Common pitfalls are predictable. Do not merge schema changes without reading SQL. Do not write one-time seed scripts. Do not call email, Stripe, Slack, or webhooks inside a transaction. Do not reuse one Zod schema for form input, API input, and database insert when those contracts differ. Do not let perPage grow without a limit. Do not let Claude Code edit unrelated migrations or tables while working on a small feature.

For monetization, the natural path is not “buy something because this article has code.” The useful offer is help turning this workflow into a repository-specific rule set: CLAUDE.md, migration review prompts, CI gates, seed strategy, and database review training. For teams, start with the Claude Code training and consultation page. For self-study, compare this Drizzle workflow with Supabase integration and CI/CD setup.

In a small verification project, the most valuable change was asking Claude Code to review the generated SQL, not just produce TypeScript. The first draft made deletion rules too aggressive. After adding an explicit review table for onDelete, indexes, and seed idempotency, the output became much easier to trust. Drizzle ORM works best when Claude Code produces the fast first pass and humans keep the migration, constraints, and production data risks visible.