Audience: Backend developers — anyone writing or reverting a Drizzle migration Scope: How reverse migrations are written, run and verified, and how far back the current set reaches

Down Migrations

1. Why this exists

drizzle-kit generates forward SQL only and ships no down runner. Without one, reverting a migration means hand-writing reverse SQL and hand-deleting its ledger row, with nothing checking that the ledger and the schema still agree afterwards. The convention and runner below replace that.

2. Coverage

The migration history has 33 forward migrations, 0000_skoolsewa_baseline through 0032_exam_schema_hardening. Down files exist for 14 of them:

The runner reverts a contiguous run from the newest applied migration downwards, and refuses to start if any file in that run is missing. With the set above, the deepest revert that runs without writing more files is --to=0026. Reverting further first needs down files for 0025 and everything below it on the way.

Every new migration ships its .down.sql sibling in the same change. A forward migration merged without one extends the gap.

3. Convention

Down SQL lives in down/, one file per forward migration, named after it:

packages/db/src/migrations/
  0029_exam_module.sql
  0030_exam_module_constraints_and_seeds.sql
  0031_exam_integrity_corrections.sql
  0032_exam_schema_hardening.sql
  down/
    0029_exam_module.down.sql
    0030_exam_module_constraints_and_seeds.down.sql
    0031_exam_integrity_corrections.down.sql
    0032_exam_schema_hardening.down.sql

A down file reverses exactly its own migration, so that applying the files newest first restores the schema as it stood before each step. What that takes:

• Drop in dependency order. Children before parents, and a table before the unique index its foreign key references. 0029_exam_module.down.sql drops the seventeen exam tables child-first so that no CASCADE is needed and nothing outside the module can be dropped by accident.
• Un-bolt what the migration added to objects it did not create. Dropping a table takes its own columns, indexes and constraints; nothing takes a column, constraint, index, trigger or sequence the migration attached to a table that already existed.
• Restore a definition the migration replaced, not merely remove the new one. 0031_exam_integrity_corrections.down.sql re-adds the original results-publication CHECK after dropping the relaxed one.
• Delete seed rows by their natural key. 0030's down file removes the seeded exam types by code, the default grading scale by name, and the notification and reminder defaults by event and offset_minutes.
• Refuse rather than rewrite data. A down file that re-imposes a constraint the forward migration relaxed fails on exactly the rows that relaxation made legal. That is correct: there is no honest automatic answer, and the runner's single transaction means the refusal leaves nothing half-reverted. Each such file says so at the top.
• Never use CONCURRENTLY. The runner executes inside a transaction, where it is not allowed.

Some reversals are destructive by nature. 0029_exam_module.down.sql deletes every exam, mark, correction and result. Take a pg_dump before running it anywhere that holds data.

4. 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.

pnpm db:migrate:down                 # revert the most recent applied migration
pnpm db:migrate:down --to=0029       # revert down to AND INCLUDING 0029
pnpm db:migrate:down --dry-run       # print the plan, change nothing

How it works:

• Applied migrations are found by hash. drizzle.__drizzle_migrations.hash is the SHA-256 of the forward file's contents, so the runner re-hashes each journal entry's .sql file and matches on that. An edited forward migration no longer matches its row, which is deliberate — editing an applied migration is already a defect, and reverting the wrong row would hide it.
• Every required down file is checked before any runs. A missing one is named and nothing changes.
• The whole set runs in one transaction: each down file, then the deletion of its ledger row, newest first. If any file fails or refuses, every earlier revert in the set rolls back too, so the ledger and the schema always agree.
• The journal is never edited. After a revert, pnpm db:migrate re-applies the forward migrations normally.
• It refuses a database URL that looks like production (prod or production as a word). Reverse production by hand, with a backup taken first.
• It connects through DIRECT_DATABASE_URL when set, otherwise DATABASE_URL, read from apps/api/.env — never through a transaction-mode pooler.

5. Verifying a down file — reading it is not enough

A down migration that reads correctly can still be wrong. The gate is a full cycle on a scratch database that ends in byte-identical schema dumps.

Apply the forward files in order and dump the schema after the step before your migration and after your migration. Then apply the down files newest first, dumping after each, and compare each dump with the forward dump from the same step. Finally re-apply the forward files and compare with the head dump:

# a throwaway Postgres 18, so no real database is at risk
docker run -d --rm --name downprobe -e POSTGRES_PASSWORD=probe -p 127.0.0.1:55432:5432 postgres:18-alpine

dump() {
  pg_dump --schema-only --no-owner --no-privileges "$URL" \
    | grep -vE '^(--|SET |SELECT pg_catalog.set_config|\\(un)?restrict )' | sed '/^$/d' > "$1.sql"
}
# apply 0000…NNNN with `psql -v ON_ERROR_STOP=1 -1 -f`, dumping up_NNNN at each step
# apply the down files newest first, dumping down_to_NNNN at each step
diff up_0031.sql down_to_0031.sql    # must be empty, and so on for every step
# re-apply the forward files, then:
diff up_0032.sql reup_0032.sql       # must be empty

docker stop downprobe

pg_dump 18 writes a random \restrict token into every dump, which is why the filter drops those lines; without it no two dumps are ever identical.

The re-apply does the catching. A DROP … IF EXISTS that matches nothing raises no error on the way down; it surfaces as already exists on the way back up, or as a line in the diff. The defects this shape of cycle finds are almost always the same one: something the migration attached to an object it did not create, which no table drop removes.

The exam module's 0029–0032 down files were verified this way: each step down matched the schema before its migration exactly, and re-applying the four forward files reproduced the head schema exactly.

See Also

• Exams — backend — the migrations 0029–0032 and their constraints