Chapter 40Lesson 2

Production-safe migrations

Reading and hand-editing Drizzle Kit migrations so a schema change ships without locking tables or breaking a live deploy.

Last lesson ended with a rule: read the generated SQL before you commit it. It never said what to read for.

Here is what that gap costs. A teammate adds an index to speed up a slow query. The schema change is one line, Drizzle Kit emits one line of SQL, and that SQL is correct: it builds exactly the index asked for. The diff looks trivial, so the PR sails through review. Then it ships at 2pm, and CREATE INDEX takes a lock that blocks every write to invoices for the forty minutes it spends scanning two million rows. The schema was right; the deploy was an outage.

drizzle-kit generate produces SQL that is correct in form but not always safe under live traffic. The generator diffs two snapshots of your schema. It sees that you added a column or an index; it cannot see that the table has two million rows, that it’s being written to right now, or that an old version of your app is still serving requests mid-deploy. You know those things, so you read every migration for locks and data loss, and hand-edit the cases the generator can’t.

By the end you’ll run every migration through a five-question checklist and know the three hand-edits that keep a deploy from taking the site down.

Generate by default, push only for throwaway data

Both commands from last lesson emit DDL . drizzle-kit generate diffs your schema into a numbered .sql file you commit. drizzle-kit push applies the diff straight to the database: no file, no snapshot, nothing in the PR to review.

For anything real, generate wins because the file is:

Reviewable. A human reads the SQL in the PR and catches the destructive operations the schema diff hides.
Replayable. CI applies the same bytes to staging that production later applies to itself, so the two never drift.
Editable. The hand-edits you’re about to learn live in that file and travel with it.

push has one failure mode. Rename invoices.customerName to clientName and the snapshot diff is indistinguishable from dropping the old column and adding a new empty one. generate spots the ambiguity and asks whether you meant a rename or a drop; push doesn’t ask. It drops the column and creates an empty one, so every customer name is silently gone, with no file or PR to catch it.

Happily wiped means a solo prototype before the schema settles, or an ephemeral branch you delete on merge. It does not mean staging or a dev branch you share: that data is someone’s afternoon of test setup, and push deletes it without asking.

Walk the decision below, noting the order of the questions.

push or generate?

Reading the generated SQL: the five-question checklist

Every PR that touches db/schema.ts ships a generated .sql file. Review the SQL, not the schema diff: the SQL is what runs against the database, and only the SQL reveals a lock or a deploy window. Ask five questions.

Does anything DROP — a column, table, constraint, or index? If so, is the drop safe in this deploy, or does it strand the old version of your app still running?
Is every rename a true rename, or is Drizzle Kit disambiguating a remove-and-add? Check the prompt when you generate; the wrong answer is silent data loss.
Does any new index sit on a table with write traffic? Without CONCURRENTLY in its own migration, it locks writes for the whole build.
Does any column-type change rewrite the table? A rewrite takes Postgres’s strongest lock; every read and write blocks until it finishes.
Does any new NOT NULL column lack a default? It fails the instant it meets a table that already has rows.

These split into two failure families: questions 1 and 2 risk losing data, questions 3 through 5 risk locks and downtime. Scanning a migration, you’re asking both: could this delete something I can’t recover, and could this freeze the table while it runs?

Data-loss risks

Could this delete something I can’t get back?

DROP — is it safe this deploy?

A rename vs. a silent remove-and-add

Lock / downtime risks

Could this freeze the table while it runs?

A new index on a hot table

A type change that rewrites the table

NOT NULL column with no default

Read every migration twice — once for data loss, once for locks.

Each of the next sections takes one question and its fix.

Building indexes without locking writes

Start with question 3: the most common hand-edit, and the easiest.

The indexes chapter covered which index to add and why; here is how to add it safely. Drizzle Kit emits CREATE INDEX "idx_invoices_status" ON "invoices" ("status"). That’s correct and instant on an empty dev table, but plain CREATE INDEX takes a lock that blocks writes for the whole build. Reads keep flowing; writes queue behind the lock. On a small table that’s a blink; on a hot table with millions of rows, it’s the forty-minute outage from the start of this lesson.

CREATE INDEX CONCURRENTLY builds the index without that lock, in several passes instead of one. You pay for it twice: it’s slower, and it cannot run inside a transaction. Drizzle wraps each migration file in a single transaction, so a concurrent index needs a file to itself with nothing else to wrap. Generate that empty file with drizzle-kit generate --custom --name add_invoices_status_index. The --custom flag produces an empty migration for hand-written SQL, the same seam the next section uses.

Compare the two. The difference is one keyword and one file:

What Drizzle Kit emits
The production hand-edit

ALTER TABLE "invoices" ADD COLUMN "status" text DEFAULT 'draft' NOT NULL;
--> statement-breakpoint
CREATE INDEX "idx_invoices_status" ON "invoices" ("status");

Correct, but it locks writes. Plain CREATE INDEX holds a write lock for the whole build, a partial outage on a busy invoices table.

CREATE INDEX CONCURRENTLY "idx_invoices_status" ON "invoices" ("status");

One keyword, alone in its own file. CONCURRENTLY drops the write lock, so Postgres builds the index while writes keep flowing. Alone in the file, it stays out of the migrate runner’s transaction.

One generated-file term: --> statement-breakpoint. Drizzle Kit inserts it to split a file into separate statements for engines that can’t batch DDL in one transaction. It is not what escapes the transaction for a concurrent index; the separate file is. Read it as “next statement.”

The course rule: every index on a table with write traffic uses CONCURRENTLY, in its own migration file.

Custom SQL migrations: changes the generator can’t write

Drizzle Kit reads db/schema.ts and emits DDL for everything the schema can describe: tables, columns, foreign keys, plain indexes. Everything it can’t model, it skips:

triggers, like the updatedAt trigger that stamps a row on every update
CREATE EXTENSION, to turn on a Postgres extension
generated columns with complex expressions
custom check constraints
partial unique indexes whose WHERE references a computed value

The schema-design chapter flagged one of these and deferred it to here: the updatedAt trigger. There you wrote .$onUpdate(...), which re-stamps the column only when a write goes through Drizzle; the reliable fix is a database trigger that fires on every update, and the schema can’t express it. Your schema declares the updated_at column, but it has no way to say “and run this function before every update.” You write that instruction by hand.

The --custom flag from the last section gives you the file to write it in. drizzle-kit generate --custom --name set_updated_at_trigger emits an empty migration, numbered in sequence like any other, for you to fill with SQL.

Won’t the next generate overwrite that SQL? No, and the reason is a clean split:

The migration files are the source of truth for the database state. db/schema.ts is the source of truth for the Drizzle types.

A trigger lives only in a migration, because the schema can’t model it. A column lives in both: the schema types it, the migration applies it. They never conflict, because each owns a different surface. And generate only ever diffs the schema and writes the next numbered file; it never rewrites a committed one. Change a column later and it emits a new migration, leaving the trigger file alone.

Step through the trigger migration to see where each piece comes from:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--> statement-breakpoint
CREATE TRIGGER invoices_set_updated_at
BEFORE UPDATE ON "invoices"
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

An empty file from drizzle-kit generate --custom --name set_updated_at_trigger: Drizzle Kit gives you the numbered shell, you write everything inside.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--> statement-breakpoint
CREATE TRIGGER invoices_set_updated_at
BEFORE UPDATE ON "invoices"
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

What the schema can’t express: run this function before every update. It exists only here, never in db/schema.ts.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--> statement-breakpoint
CREATE TRIGGER invoices_set_updated_at
BEFORE UPDATE ON "invoices"
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Change an unrelated column later and run generate: it writes a new file for that change and leaves this one untouched.

1 / 1

Three column changes that break a live deploy

Three patterns share one root cause. The migration runs in a single instant, but the deploy is a window: two versions of your app coexist while traffic shifts from old to new, and the rows already in the table already hold values. The generator sees neither the window nor the existing rows, and every problem below traces back to one of those two blind spots.

Adding a NOT NULL column with no default

Mark a new column NOT NULL and Drizzle Kit emits ALTER TABLE "invoices" ADD COLUMN "client_ref" text NOT NULL. It fails on contact: the instant the column exists, every existing row violates NOT NULL, because none had a value for a column that didn’t exist a second ago.

Two fixes:

Add the column nullable, backfill the existing rows, then flip it to NOT NULL in a later migration.
Or ship it with a DEFAULT the existing rows can take, so they’re all valid the moment the column lands.

This is question 5 on the checklist and the lowest-stakes of the three: the nullable route costs you exactly one extra migration.

Renaming a column

The generated ALTER TABLE "invoices" RENAME COLUMN "customer_name" TO "client_name" is correct SQL. The trap is the deploy window. Until the new version fully takes over, old instances still draining requests query customer_name, a column the rename just removed; Postgres throws and the request 500s. Ninety seconds of every request failing is an outage.

Scrub through the cutover below and watch the moment the old version and the database disagree:

Before the migration

Old app version queries customer_name

Postgres invoices

id customer_name amount

200 every request OK

Steady state. The running app queries customer_name, and the invoices table has that column. Everything works.

The migration runs — rename in place

Old app version queries customer_name

Postgres invoices

id customer_name renamed away client_name amount

500 every request 500s

The column is renamed to client_name in one instant — but the old app version is still serving traffic and still asks for customer_name, which is now gone. Postgres throws; every request 500s. This is the broken window.

New version takes over

New app version queries client_name

Postgres invoices

id client_name amount

200 requests OK again

The new app version deploys and queries client_name, which the DB now has. Green again — but the gap between steps 2 and 3 was a live outage, however brief.

The fix: expand-contract

Old app version queries customer_name

New app version queries client_name

Postgres invoices

id customer_name client_name new amount

200 no red window opens

Expand-contract instead: client_name is added alongside customer_name, so both app versions find a column they can read while they coexist. The old column is dropped only later, once nothing reads it. No red window ever opens.

The safe shape: add the new column, dual-write from your app so both stay populated, backfill the existing rows, switch reads to the new column, and drop the old column in a later deploy once nothing reads it. That’s three migrations and at least two deploys. Wiring those dual-writes in app code is its own topic you’ll do hands-on later; for now, rename-in-place is unsafe and the multi-step shape is the fix.

Changing a column type

An incompatible type change, say text to integer, makes Postgres rewrite the entire table. To do that it takes an ACCESS EXCLUSIVE lock and holds it for the whole rewrite, blocking every read and write until it finishes. On a large table that’s a full outage for as long as the rewrite runs.

Not every type change rewrites, though: widening certain types is metadata-only, with no row touched. So the rule isn’t “type changes are dangerous”; it’s to check whether this particular change rewrites the table, which the Postgres docs spell out per type and which you confirm by rehearsing the migration against staging.

When a change does rewrite, the fix is the same multi-step shape as the rename: add a new column with the new type, backfill it in batches, switch reads, drop the old column.

The pattern has a name

All three share one shape worth memorizing: expand-backfill-contract (often just expand-contract).

Expand: add the new shape alongside the old one. Nothing breaks, because everything that worked before still finds what it’s looking for.
Backfill: populate the new shape for existing rows, in batches when the table is large so you don’t lock it.
Contract: drop the old shape, but only once nothing reads it anymore.

Any destructive change takes three deploys at minimum, and it works for the reason at the top of this section: both app versions can always read a column they understand for the whole window they coexist.

Expand

App reads / writes customer_name

Postgres invoices

customer_name client_name new · empty

Add the new column. Nothing breaks yet.

Backfill

App dual-writes customer_nameclient_name

Postgres invoices

customer_name client_name now filled

Copy existing rows into the new column, in batches.

Contract

App reads / writes client_name

Postgres invoices

customer_name dropped client_name

Drop the old column once nothing reads it.

Expand-backfill-contract — three deploys, no broken window.

When the checklist flags a drop, a rename, or a rewriting type change, the answer is “that’s an expand-contract, not a one-shot.”

Before moving on, sort a handful of operations into the two responses you now have.

Each chip is a line Drizzle Kit generated, plus the one fact about the table it can't see. Sort by your two questions: could it lose data, or could it freeze the table? Drag each item into the bucket it belongs to, then press Check.

Ships as generated Correct SQL, safe under live traffic — let it through

Needs a hand-edit / multi-step Correct SQL, but locks or strands the deploy — rework it

ADD COLUMN "note" text · nullable, no default

ADD COLUMN "archived_at" timestamptz · nullable timestamp

CREATE INDEX "idx_audit_logs_created_at" … · on a brand-new, empty table

CREATE INDEX "idx_invoices_status" … · on a 2-million-row, write-heavy table

ADD COLUMN "client_ref" text NOT NULL · no default, table already has rows

RENAME COLUMN "customer_name" TO "client_name" · on a live, deployed table

ALTER COLUMN "amount" SET DATA TYPE integer · text to integer, rewrites the table

When a migration fails, fix forward

Migrations fail eventually, and the instinct is to roll back. In production you do the opposite: you fix forward.

Drizzle Kit emits up migrations only, by design: no down file, no built-in undo. You never roll back in production, because a rollback runs against data that has changed since the migration ran, making it riskier than moving forward. Instead, the next migration corrects the previous one.

There is one local-only carve-out. drizzle-kit drop removes the most recent unapplied migration in dev, for when you generated a file you didn’t mean to and haven’t run anywhere. Once a migration has been applied anywhere, even on your laptop, editing it drifts the snapshot from the database (the footgun from last lesson), so to undo an applied migration you revert the schema edit and generate a corrective one.

What happens when a migrate run dies mid-flight depends on whether the failing statement ran in a transaction.

Most failures roll back cleanly. Each migration file runs in one transaction, so a failed statement rolls the whole file back: __drizzle_migrations gets no row, and the database is untouched. Fix the SQL or the bad data and re-run migrate.
The exception runs outside a transaction: your CREATE INDEX CONCURRENTLY. If it builds halfway and fails, Postgres can’t roll it back, so it leaves an invalid index on the table.

Rehearse every migration’s failure mode against staging first, so you never discover that a migration locks a table on the production one.

Shipping the migration: CI, deploy, and verifying it applied

A reviewed file isn’t a shipped change. There are two seams where migrate runs:

CI runs it against staging. On every merge to main, CI applies pending migrations to the long-lived staging Neon branch, so a misbehaving migration does so where the blast radius is test data, not customers.
The deploy pipeline runs it as a pre-deploy step, before the new app version swaps in (on Vercel, a build-and-deploy hook). Order matters: the schema migrates first, then the app version that depends on it goes live. This is also why expand-contract exists, since for a destructive change “migrate then deploy” still isn’t safe: the old version keeps serving traffic while the migration runs.

Two conventions carry over from last lesson. Wrap the production run in a db:migrate:prod script in package.json, so the command is grep-able in your pipeline config instead of buried as a bare CLI call in YAML. And point it at DATABASE_URL_UNPOOLED, wired into the deploy environment separately from the app’s pooled URL, because long DDL transactions die under transaction-mode pooling.

Then verify it applied. After a deploy, query the production branch:

select * from __drizzle_migrations order by created_at desc limit 5;

Each row is keyed by the content hash of its migration file, so the latest hash should match the file you committed and reviewed. If they don’t match, the deploy applied something other than what you reviewed: a different version of the file, a migration from a forgotten branch. Stop and investigate before serving traffic.

Putting it together: review this migration PR

Now run the checklist for real: read a diff and catch what’s unsafe before it ships.

This PR adds a status column and an index, renames customer_name, and re-adds archived_at. The schema diff looks fine, four small lines. But you know what the generator doesn’t: invoices is live, two million rows, constant writes. Read the SQL, click each line you’d block, and say why. At least one line is safe, so don’t flag it just to be safe.

You're reviewing this generated migration for a teammate. `invoices` is a live, two-million-row table under constant write traffic. Click any line to leave a review comment, then press Submit review.

drizzle/0007_add_invoices_status.sql

ALTER TABLE "invoices" ADD COLUMN "archived_at" timestamptz;
--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "status" text NOT NULL;
--> statement-breakpoint
ALTER TABLE "invoices" RENAME COLUMN "customer_name" TO "client_name";
--> statement-breakpoint
CREATE INDEX "idx_invoices_status" ON "invoices" ("status");

Generated SQL is correct, but correct isn’t safe under load, and you’re the part of the pipeline that knows the difference.

External resources

The authoritative sources behind this lesson, worth a read when you hit any of these cases for real.

Drizzle Kit — Migrations overview

orm.drizzle.team

The generate / migrate / push commands and how each fits the review-then-apply workflow.

Drizzle Kit — Custom migrations

orm.drizzle.team

The --custom flag that emits an empty migration file for hand-written SQL like triggers and concurrent indexes.

PostgreSQL — CREATE INDEX

postgresql.org

The canonical source for CONCURRENTLY's lock behavior and the invalid-index recovery procedure.

Prisma — The expand and contract pattern

prisma.io

A step-by-step walk through dual-writes and backfill for a zero-downtime schema change.