Drizzle ORM skill for Codex

Done well means: the TypeScript schema is the single source of truth, every change ships as a reviewed SQL migration from drizzle-kit generate, queries read like the SQL they emit, and nothing in production depends on drizzle-kit push.

Inspect first

1. Read AGENTS.md for the package manager and how migrations are applied (CLI step, app startup, or CI job).
2. Check versions in package.json: drizzle-orm and drizzle-kit. Versions 0.x use relations v1 (relations()); 1.x (including @rc/@beta tags) uses defineRelations() and a folder-per-migration layout. Follow what is installed.
3. Open drizzle.config.ts for dialect, schema path, out folder and credentials.
4. List the out folder (drizzle/ by default) to see whether the team uses generate plus migrate (folder has SQL files) or only push (no files).
5. Find the drizzle() call to learn the driver.

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",            // "mysql" | "sqlite" | "turso" | "singlestore"
  schema: "./src/db/schema.ts",     // or a glob / folder
  out: "./drizzle",
  dbCredentials: { url: process.env.DATABASE_URL! }, // direct URL, not a transaction pooler
  strict: true,
  verbose: true,
});

Schema

import { pgTable, uuid, text, integer, timestamp, index, uniqueIndex, pgEnum } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const orderStatus = pgEnum("order_status", ["pending", "paid", "shipped"]);

export const orders = pgTable("orders", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenantId: uuid("tenant_id").notNull(),
  customerId: uuid("customer_id").notNull().references(() => customers.id, { onDelete: "restrict" }),
  status: orderStatus("status").notNull().default("pending"),
  totalCents: integer("total_cents").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (t) => [
  index("orders_tenant_created_idx").on(t.tenantId, t.createdAt.desc()),
  index("orders_customer_idx").on(t.customerId),
  index("orders_pending_idx").on(t.createdAt).where(sql`${t.status} = 'pending'`),
]);

export type Order = typeof orders.$inferSelect;
export type NewOrder = typeof orders.$inferInsert;

• Export every table, enum and relation from the schema entry: drizzle-kit only sees exports.
• Use the array form for the third argument (indexes, checks, composite keys); the object form is deprecated.
• Index foreign keys yourself; references() creates only the constraint.
• timestamp without withTimezone is timestamp without time zone; use withTimezone: true.
• .$onUpdate(() => new Date()) runs in the app, not the database; a raw SQL update will not touch it.
• Name indexes explicitly so diffs stay stable across refactors.

Migrations: generate and migrate

npx drizzle-kit generate --name add_order_status   # writes SQL from the schema diff
npx drizzle-kit migrate                            # applies pending migrations
npx drizzle-kit check                              # consistency of the migration history
npx drizzle-kit generate --custom --name backfill_status  # empty file for hand-written SQL

Or apply at deploy time from code:

import { migrate } from "drizzle-orm/node-postgres/migrator";
await migrate(db, { migrationsFolder: "./drizzle" });

Decision table:

Situation	Use
Shared, staging or production database	generate + migrate, files committed
Local prototype or disposable branch database	push is acceptable
Existing database, adopting Drizzle	pull to introspect, then baseline
Data backfill or CREATE INDEX CONCURRENTLY	generate --custom, hand-written SQL

Rules:

• generate asks interactively whether a column was renamed or dropped and recreated. Codex cannot answer TTY prompts reliably: make a rename its own change, run generate in a terminal the user controls, or write the ALTER TABLE ... RENAME COLUMN in a custom migration. Never accept a drop-and-create for a rename.
• Read every generated SQL file before committing. Check for DROP, type changes that rewrite the table, and NOT NULL additions on populated tables.
• Put CREATE INDEX CONCURRENTLY alone in a custom migration; check that the migrator in use does not wrap it in a transaction, or run it as a separate deploy step.
• push against any shared database, and push --force, require explicit user confirmation: push applies destructive statements with no history to roll back from.
• Never edit a migration that has been applied elsewhere; add a new one.

Queries

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

const recent = await db
  .select({ id: orders.id, total: orders.totalCents, customer: customers.name })
  .from(orders)
  .innerJoin(customers, eq(customers.id, orders.customerId))
  .where(and(eq(orders.tenantId, tenantId), gte(orders.createdAt, since)))
  .orderBy(desc(orders.createdAt), desc(orders.id))
  .limit(50);

const [created] = await db.insert(orders).values(newOrder).returning();

await db.insert(customers).values(rows)
  .onConflictDoUpdate({ target: [customers.tenantId, customers.email], set: { name: sql`excluded.name` } });

await db.update(orders).set({ status: "paid" }).where(eq(orders.id, id));

• update and delete without .where() affect every row. Build filters so an empty input cannot produce a missing where.
• sql template interpolations are parameters. sql.raw() inlines text: never pass user input.
• Aggregates: sql<number>\count(*)`.mapWith(Number)since Postgres returnscount` as bigint text.
• Select explicit columns in hot paths instead of db.select().from(t).

Relational queries

v0 (relations()):

export const ordersRelations = relations(orders, ({ one, many }) => ({
  customer: one(customers, { fields: [orders.customerId], references: [customers.id] }),
  items: many(orderItems),
}));
const db = drizzle({ client: pool, schema });
const rows = await db.query.orders.findMany({
  columns: { id: true, totalCents: true },
  with: { items: { columns: { sku: true, qty: true } } },
  where: (o, { eq }) => eq(o.tenantId, tenantId),
  limit: 50,
});

v1 (defineRelations(), passed as relations to drizzle(); where becomes an object filter such as where: { tenantId }):

export const relations = defineRelations({ orders, customers, orderItems }, (r) => ({
  orders: {
    customer: r.one.customers({ from: r.orders.customerId, to: r.customers.id }),
    items: r.many.orderItems(),
  },
}));

Relational queries emit a single SQL statement with JSON aggregation, so nested with never causes N+1. For very wide fan-outs, compare against two plain selects joined in memory.

Transactions

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

Use tx, not db, inside the callback; a stray db call runs outside the transaction.

Drivers

Runtime	Import	Note
Node, long-lived	drizzle-orm/node-postgres (pg Pool)	Set pool max explicitly
Node, postgres.js	drizzle-orm/postgres-js	Behind a transaction pooler pass prepare: false
Serverless/edge Postgres	drizzle-orm/neon-http or neon-serverless	HTTP driver has no interactive transactions
SQLite / Turso	drizzle-orm/better-sqlite3, drizzle-orm/libsql
Cloudflare D1	drizzle-orm/d1	Migrations via wrangler or drizzle-kit
MySQL	drizzle-orm/mysql2	No RETURNING; use $returningId()

Gotchas

• Two schema files defining the same table name give confusing diffs; keep one owner.
• drizzle-kit reads dbCredentials at CLI time; .env may not be loaded unless the config imports dotenv/config.
• Postgres enums: drizzle-kit can add values, but removing or renaming needs a manual migration.
• Running migrate through a transaction-mode pooler can fail on session-level features; use the direct URL for migrations.
• In v1, push and pull cover all schemas, not only public; set schemaFilter if the database holds schemas the app must not manage.

Verification

• npx tsc --noEmit passes; inferred types match expectations at call sites.
• npx drizzle-kit generate right after your change reports no further schema changes.
• npx drizzle-kit check is clean.
• Apply all migrations to a fresh database (container or branch) and run the tests.
• For a performance fix, log SQL with drizzle({ client, logger: true }) and run EXPLAIN ANALYZE on the emitted statement.

Output

**Schema:** <tables/columns/indexes changed>
**Migration:** drizzle/<name> (generated | custom), destructive statements: none | <list>
**Apply with:** drizzle-kit migrate | migrate() at startup | CI job
**Checks run:** tsc, drizzle-kit check, fresh-database apply, tests