Drizzle ORM skill for Claude Code

Drizzle is SQL with types. Work is done well when the schema file is the single source of truth, every generated migration is reviewed SQL committed to the repo, queries read like the SQL they produce, and types come from inference rather than hand-written interfaces.

Identify the setup first

cat package.json | grep -E '"(drizzle-orm|drizzle-kit|drizzle-zod|pg|postgres|@neondatabase/serverless|better-sqlite3|@libsql/client|mysql2)"'
cat drizzle.config.ts

• Driver determines the import path: drizzle-orm/node-postgres (pg), drizzle-orm/postgres-js, drizzle-orm/neon-http, drizzle-orm/better-sqlite3, drizzle-orm/libsql, drizzle-orm/d1, drizzle-orm/mysql2.
• Relational queries API: the 0.x line uses relations() per table; the 1.0 line (beta through 2025) introduces Relational Queries v2 with a single defineRelations() and object-style where. Match the installed version; do not mix styles.
• Keep drizzle-orm and drizzle-kit versions compatible (upgrade them together).

Config

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

export default defineConfig({
  dialect: "postgresql",               // "sqlite" | "mysql" | "turso" | "singlestore"
  schema: "./src/db/schema.ts",        // file, glob, or array
  out: "./drizzle",
  dbCredentials: { url: process.env.DATABASE_URL! },
  casing: "snake_case",                // camelCase keys in TS -> snake_case columns
  strict: true,                        // prompt before destructive push statements
  verbose: true,
});

If you set casing in the kit config, pass the same casing to drizzle() at runtime, or column names will not match.

Schema

// src/db/schema.ts
import { pgTable, pgEnum, integer, text, timestamp, uuid, boolean, index, uniqueIndex, check } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

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

export const users = pgTable("users", {
  id: uuid().primaryKey().defaultRandom(),
  email: text().notNull(),
  name: text(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  uniqueIndex("users_email_lower_key").on(sql`lower(${t.email})`),
]);

export const posts = pgTable("posts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  authorId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }),
  title: text().notNull(),
  status: postStatus().notNull().default("draft"),
  priceCents: integer().notNull(),
  published: boolean().notNull().default(false),
  publishedAt: timestamp({ withTimezone: true }),
}, (t) => [
  index("posts_author_id_idx").on(t.authorId),
  index("posts_status_published_at_idx").on(t.status, t.publishedAt.desc()),
  check("posts_price_nonnegative", sql`${t.priceCents} >= 0`),
]);

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Rules:

• The third argument returns an array of indexes/constraints (the older object-return form is deprecated).
• timestamp({ withTimezone: true }) for real events; Drizzle’s default is without time zone. Use mode: "date" (default) or mode: "string" consistently.
• Index every FK column explicitly; .references() creates the constraint only.
• Name indexes and constraints explicitly so migrations are stable and readable.
• Prefer generatedAlwaysAsIdentity() over serial() for integer keys on Postgres.
• Money as integer minor units or numeric({ precision: 19, scale: 4 }) (returns a string by default; parse deliberately).

Migrations with drizzle-kit

Goal	Command
Create SQL migration from schema diff	npx drizzle-kit generate --name add_posts
Apply pending migrations	npx drizzle-kit migrate (or the runtime migrate() in a release step)
Hand-written migration (data fix, CONCURRENTLY, triggers)	npx drizzle-kit generate --custom --name backfill_slugs
Push schema directly, no migration files	npx drizzle-kit push (local prototyping only)
Introspect existing DB into a schema file	npx drizzle-kit pull
Check migration folder consistency (e.g. after merges)	npx drizzle-kit check
Browse data	npx drizzle-kit studio

Programmatic migrate in a deploy step:

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";

const db = drizzle(process.env.DATABASE_URL!);
await migrate(db, { migrationsFolder: "./drizzle" });

Hard rules:

• Pick one workflow per environment. push against a database that is also managed by migration files causes drift. Never push to staging or production.
• Read every generated .sql file. drizzle-kit asks interactively whether a changed column is a rename or a new column; answering wrong produces DROP + ADD and data loss. In non-interactive environments this prompt cannot be answered, so generate locally.
• Do not edit migrations already applied anywhere; do not delete files from out/ or the meta/_journal.json entries by hand.
• After merging branches that both generated migrations, regenerate or reconcile and run drizzle-kit check before deploying.
• Large-table changes (NOT NULL, new indexes, type changes) need lock-safe SQL; write a custom migration following schema-migrations. CREATE INDEX CONCURRENTLY must run outside a transaction; if the migrator wraps migrations in a transaction, run that statement in a separate step.

Queries

Core builder (reads like SQL):

import { and, eq, gt, desc, inArray, sql, count } from "drizzle-orm";

const recent = await db
  .select({ id: posts.id, title: posts.title, author: users.name })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(and(eq(posts.status, "published"), gt(posts.publishedAt, since)))
  .orderBy(desc(posts.publishedAt), desc(posts.id))
  .limit(20);

const [{ total }] = await db.select({ total: count() }).from(posts).where(eq(posts.authorId, userId));

// Upsert
await db.insert(users).values({ email, name })
  .onConflictDoUpdate({ target: users.email, set: { name } })
  .returning();

Relational queries (nested results, no manual mapping). Requires passing the schema (and relations) to drizzle():

import * as schema from "./schema";
const db = drizzle(process.env.DATABASE_URL!, { schema, casing: "snake_case" });

const userWithPosts = await db.query.users.findFirst({
  where: (u, { eq }) => eq(u.id, userId),
  columns: { id: true, name: true },
  with: { posts: { columns: { id: true, title: true }, limit: 10 } },
});

Choose:

Need	Use
Nested object graph for an API response	db.query.*.findMany/findFirst with with
Aggregates, group by, complex joins, CTEs	Core select() builder ($with for CTEs)
Something the builder cannot express	sql`...` template (parameterized) inside a query, or db.execute(sql…)

• Keyset pagination: where(lt(posts.id, cursor)).orderBy(desc(posts.id)).limit(n); avoid large .offset().
• inArray(col, []) with an empty array is handled, but guard optional filters: an and() of all-undefined conditions yields no WHERE clause, so an update or delete would hit every row.
• sql.raw() bypasses parameterization; only for trusted identifiers.
• Hot paths: prepared statements.

const byId = db.select().from(users).where(eq(users.id, sql.placeholder("id"))).prepare("user_by_id");
const u = await byId.execute({ id });

Transactions

await db.transaction(async (tx) => {
  const [acct] = await tx.select().from(accounts).where(eq(accounts.id, from)).for("update");
  if (acct.balance < amount) tx.rollback();     // throws, rolls back
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - ${amount}` }).where(eq(accounts.id, from));
  await tx.update(accounts).set({ balance: sql`${accounts.balance} + ${amount}` }).where(eq(accounts.id, to));
}, { isolationLevel: "read committed" });

• Use tx, not db, inside the callback.
• Keep transactions short; no network calls inside.
• Drivers differ: neon-http and D1 do not support interactive transactions; use db.batch([...]) for atomic batches there.
• Prefer atomic SQL updates (balance - amount) over read-then-write.

Validation

Generate Zod schemas from tables so API validation matches the DB (drizzle-zod, or the built-in integration in newer versions):

import { createInsertSchema } from "drizzle-zod";
export const insertPostSchema = createInsertSchema(posts, { title: (s) => s.min(1).max(200) });

Gotchas

• Column order and naming come from your keys plus casing; changing casing later makes drizzle-kit think every column was renamed.
• $inferSelect reflects the schema file, not the live DB. If someone altered the DB by hand, types lie until you pull or migrate.
• numeric/decimal and bigint (in mode: "bigint") do not serialize to JSON as numbers; convert at the boundary.
• .returning() is unavailable on MySQL; use $returningId() there.
• Serverless Postgres: use the provider’s pooled URL for queries and a direct URL for migrations.

Verification

1. npx drizzle-kit generate produces no new migration after your changes are applied (schema and migrations are in sync); npx drizzle-kit check passes.
2. Review the generated SQL diff in the PR.
3. tsc --noEmit passes; no hand-written row types duplicating $inferSelect.
4. Log SQL during development with drizzle(url, { logger: true }) and EXPLAIN any slow statement (see sql-query-tuning).