---
name: drizzle-postgres-migrations
description: Use Drizzle ORM with Postgres for type-safe queries and safe migrations. Use when modeling a new schema, adding/altering columns on a live table, writing complex joins, or designing concurrent-safe online migrations. Covers add-NOT-NULL-with-default, lockless index creation, and zero-downtime renames.
category: backend
version: 0.1.0
tags: [drizzle, postgres, sql, migration, orm]
recommended_npm: ["drizzle-orm", "drizzle-kit", "postgres", "pg"]
license: MIT
author: claude-code-skills
---

Drizzle is a thin SQL-first ORM. You write the schema in TypeScript, get typed query builders, and migrations are diff-generated SQL files you can hand-edit.

## Schema definition

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

export const products = pgTable(
  "products",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    name: text("name").notNull(),
    slug: text("slug").notNull(),
    priceCents: integer("price_cents").notNull(),
    archivedAt: timestamp("archived_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    slugIdx: uniqueIndex("products_slug_idx").on(t.slug),
    archivedIdx: index("products_archived_idx").on(t.archivedAt),
  }),
);

export type Product = typeof products.$inferSelect;
export type NewProduct = typeof products.$inferInsert;
```

## Generating migrations

```bash
npx drizzle-kit generate           # diffs schema.ts vs ./drizzle/* SQL files
npx drizzle-kit migrate            # applies pending migrations transactionally
npx drizzle-kit studio             # local GUI
```

## Online migration playbook (zero-downtime, > 1M rows)

**Adding a NOT NULL column to a large table:**

1. Generate adds the column as nullable + sets a default for new rows.
2. Hand-edit the migration to backfill in batches:
   ```sql
   ALTER TABLE products ADD COLUMN seller_id uuid;
   UPDATE products SET seller_id = '...' WHERE seller_id IS NULL AND id IN (
     SELECT id FROM products WHERE seller_id IS NULL LIMIT 5000
   );
   -- run repeatedly via a job runner until 0 rows remain
   ```
3. Once backfill is done, a follow-up migration adds the constraint:
   ```sql
   ALTER TABLE products ALTER COLUMN seller_id SET NOT NULL;
   ```

**Adding an index on a hot table:** always `CREATE INDEX CONCURRENTLY` — never blocks writes:
```sql
CREATE INDEX CONCURRENTLY products_seller_idx ON products(seller_id);
```

**Renaming a column safely** (multi-deploy):
1. Add the new column, dual-write from app code.
2. Backfill from old column.
3. Switch reads to the new column.
4. Drop the old column in a later deploy.

## Querying

```ts
import { eq, and, isNull, desc } from "drizzle-orm";

const active = await db.select().from(products)
  .where(and(eq(products.slug, slug), isNull(products.archivedAt)))
  .limit(1);

const page = await db.query.products.findMany({
  where: (p, { gt }) => gt(p.priceCents, 1000),
  orderBy: (p) => desc(p.createdAt),
  limit: 20,
});
```

## Anti-patterns

- ❌ `ALTER TABLE ... ADD COLUMN x text NOT NULL` on a large table — Postgres rewrites the whole table; locks for minutes.
- ❌ Using `.execute(sql\`...\`)` for everything instead of the typed query builder — you lose autocomplete and type inference.
- ❌ Dropping columns in the same deploy that removes their reads — old replicas still query them; deploy in two steps.
- ❌ Running `drizzle-kit push` against production — it skips the migration file trail.
- ❌ Storing money in `numeric` without choosing precision — pick `integer` cents or `numeric(12, 2)` consistently.
- ❌ Creating an index without `CONCURRENTLY` on a hot OLTP table.

## Quality gates

- Every migration applies under 5s in CI against a copy of prod schema.
- No migration drops a column the app still reads from (CI grep against current TS source).
- `EXPLAIN ANALYZE` on the slow paths shows index usage; no Seq Scans on tables > 100k rows.