Audience: Backend developers — anyone writing or reverting a Drizzle migration Scope: How reverse migrations are written, run, and where they legitimately cannot succeed

Down Migrations

1. Why this exists

drizzle-kit generates forward SQL only and ships no down runner. Reverting a migration meant hand-writing and hand-running reverse SQL against whatever environment needed it, with nothing tracking whether the ledger and the schema still agreed afterward. This is the convention and the runner that replaces that.

Coverage is now complete: every forward migration has a reverse — 40 of 40.

The convention began narrow, at 0032 onward, to stop the debt growing before paying it off. The remaining 32 (0000–0031; the file count is the authority, an earlier note said 31) were back-filled afterwards. Three of them do not do what their name suggests, and each says so at the top of its own file:

Five more succeed structurally but fail on real data, which is correct and documented in each: 0004, 0017, 0021, 0023 and 0030 all re-impose a constraint the forward migration relaxed, so they refuse exactly the rows it made legal. 0021 and 0023 are the sharp ones — they take commerce back from per-configuration to per-product, and there is no correct automatic merge for a basket holding two configurations of one phone.

2. Convention

Down SQL lives beside the forward migrations, one file per forward migration:

packages/db/src/migrations/
  0032_feedback_guest_submitter.sql
  0033_banner_typed_target.sql
  0034_banner_target_check_null_safe.sql
  0035_drop_unusable_on_sale_index.sql
  down/
    0032_feedback_guest_submitter.down.sql
    0033_banner_typed_target.down.sql
    0034_banner_target_check_null_safe.down.sql
    0035_drop_unusable_on_sale_index.down.sql

Every new migration from 0032 forward ships its .down.sql sibling in the same change. A forward migration merged without one is incomplete.

3. The runner — pnpm db:migrate:down

Registered in packages/db/package.json ("db:migrate:down": "tsx src/scripts/migrate-down.ts"). Script: packages/db/src/scripts/migrate-down.ts.

Two properties make this safe to run against a real database:

• Applies the down SQL and deletes the migration's drizzle.__drizzle_migrations ledger row in one transaction. If the schema change fails partway, the row stays and the transaction rolls back — ledger and schema always agree, and a retry starts from a known state.
• Checks every required down file exists before running any of them, naming what is missing. A --to=0032 that would need to pass through an undocumented migration on the way refuses to start rather than reverting halfway.
• Refuses to run against a URL that looks like production.
• The journal file is never edited — after a revert, pnpm db:migrate simply re-applies the forward migration normally.

A round trip was proven on the test database for this pass: reverted 0034 and 0033, confirmed the four target_* columns were gone, re-applied, confirmed all four back and the CHECK is the NULL-safe (0034) version — not the earlier one that silently accepted a NULL target_kind.

4. 0032 cannot be reverted while a guest submission exists

0032_feedback_guest_submitter.sql relaxed feedback_submissions.customer_id to nullable so a signed-out contact submission can be stored (see Feedback Admin API Reference). Its down file's reverse re-imposes customer_id SET NOT NULL — which fails the moment any row has customer_id IS NULL, i.e. the moment any guest has ever contacted the shop through POST /api/mobile/feedback/public.

The down file names the check to run first:

SELECT count(*) FROM feedback_submissions WHERE customer_id IS NULL;

A non-zero count means this migration is not revertible as-is — there is no automatic answer to "which customer sent this anonymous message," so the down file does not invent one. This is stated as a known, permanent limitation of reverting 0032, not a bug to fix: a rollback plan that pretends an anonymous row can be reassigned to a customer is not a rollback plan.

5. Verifying the set — reading them is not enough

A down migration that reads correctly can still be wrong, and this whole back-filled set was, in five different ways, while every file looked right. The gate is a full cycle on a scratch database, ending in a byte-identical schema dump:

createdb happyhouse_downcycle
DATABASE_URL=…/happyhouse_downcycle pnpm exec drizzle-kit migrate      # up
pg_dump --schema-only --no-owner --no-privileges … > /tmp/A.sql
DATABASE_URL=…/happyhouse_downcycle pnpm db:migrate:down --to=0001     # down
DATABASE_URL=…/happyhouse_downcycle pnpm exec drizzle-kit migrate      # up again
pg_dump --schema-only --no-owner --no-privileges … > /tmp/B.sql
diff /tmp/A.sql /tmp/B.sql                                             # must be empty

The re-apply does the catching. A DROP that quietly matched nothing raises no error going down; it surfaces as already exists coming back up. What that found:

1. A schema-qualified name collapsed into one identifier — "public.catalog_job_entity" rather than "public"."catalog_job_entity". Dropped nothing and reported success.
2. A column added to a table the migration did not create (outbox_events.last_error). Dropping a table takes its own columns; nothing takes that one.
3. Constraints added to a pre-existing table — same class.
4. Indexes on a pre-existing table — same class again, plus an ordering fault: fk_product_brand_series depends on uq_brand_series_brand_id_id, so tables must be dropped before the indexes their foreign keys reference.
5. Sequences (invoice_number_seq, order_number_seq, pos_sale_number_seq) — free-standing objects that no table drop touches.

The common shape, and the thing to check when writing a new one: everything a migration bolts onto an object it did not create has to be un-bolted by hand, and none of it is visible until the re-apply.

See Also

• Feedback admin API: /docs/developer/feedback/admin-api-reference
• Banners backend doc (migrations 0033/0034): /docs/developer/banners/backend