Test Migration Rollbacks in CI

Apply, roll back, and re-apply every migration against a throwaway database, and fail the build when the down path breaks or drifts.

~35 mindatabasemigrationsci

Task

Prove every schema migration can be rolled back and re-applied without losing objects the application depends on.

When to use this

A migration adds a column and an index, but the down migration drops the column and forgets the index, so a rollback leaves the schema subtly different and the next deploy fails.

Prerequisites

A migration tool with up and down commands (Flyway, Prisma Migrate, Knex)
PostgreSQL 13 or newer in Docker or locally
pg_dump available in CI

Steps

1. Apply every migration to a throwaway database and capture the schema baseline.

createdb app_migrate_test && DATABASE_URL=postgres://localhost/app_migrate_test pnpm migrate:up && pg_dump --schema-only --no-owner app_migrate_test > before.sql

Expected output: The latest version is applied and before.sql contains all tables, indexes, and constraints.

2. Roll the last migration back and re-apply it.

pnpm migrate:down && pnpm migrate:up

Expected output: Both commands exit 0 and the migration history ends at the same version it started from.

3. Compare the schema after the cycle with the baseline.

pg_dump --schema-only --no-owner app_migrate_test > after.sql && diff -u before.sql after.sql

Expected output: No structural differences; ownership and comment noise is normalized out of the dump.

4. Run the application against the rolled-back schema to prove the previous version still works.

pnpm migrate:down
DATABASE_URL=postgres://localhost/app_migrate_test pnpm start &
curl -fsS http://localhost:3000/healthz
curl -fsS 'http://localhost:3000/api/orders?limit=1'
pnpm migrate:up

Expected output: Health check and a key read both succeed on version N-1, so a rollback is operationally usable.

5. Run the full cycle per migration in CI, from a fresh database each time.

pnpm test:migrations

Expected output: The job fails on the first migration whose down path errors or produces schema drift.

Troubleshooting

Problem

The tool has no down migration for a change

Solution

Write one, or mark the migration irreversible explicitly. Irreversible changes need a restore drill and a documented rollback plan instead.

Problem

The diff reports sequence values or ownership changes

Solution

Normalize the dump: exclude owners, comments, and sequence last_value, and compare only structure and constraints.

Problem

Rollback succeeds but data is gone

Solution

Dropping a column or table is destructive. Use expand-and-contract migrations, or require a verified backup before any down migration runs.

Limitations

Some migrations are inherently irreversible, especially data transformations and dropped columns.
Schema diffing needs normalization; a naive diff produces false failures and gets ignored.
It cannot prove that data survives a rollback; destructive changes need separate backup verification.

Rollbacks are code too

Most teams test the forward path and discover the down path during an incident. A rollback that errors, leaves an orphan index, or drops a column the previous release still reads turns a routine deploy problem into an outage. The only way to trust a rollback is to execute it regularly, against the same schema and the same data shape as production.

Test the whole cycle, not just down

The meaningful property is round-trip stability: apply all migrations, roll back one step, re-apply, and end with the same schema. A down migration that runs without error but forgets to recreate an index passes a naive check and fails the next query plan. Capture pg_dump --schema-only before and after, normalize ownership and comments, and diff. Any structural difference is a bug in the migration.

pnpm migrate:down && pnpm migrate:up
pg_dump --schema-only --no-owner app_migrate_test > after.sql
diff -u before.sql after.sql

Prove the old release still runs

A schema can be structurally identical and still break the previous application version if the down migration removed a column that version reads. Run the N-1 container against the rolled-back schema and hit a health endpoint plus one read path. This catches the common mistake of rolling back the schema but not the code, or vice versa.

Automate per migration

Iterating from the newest migration down to the oldest finds the exact step that is broken, instead of a generic failure. Run each step in a fresh database in CI, and stop at the first error. Keep the job fast by using a template database or a container that starts empty; the whole matrix usually fits in a few minutes.

Handle the irreversible cases

Data transformations, dropped columns, and enum value removals cannot be undone by SQL alone. Mark them irreversible in the migration file, require a backup and a restore drill, and prefer expand-and-contract: add the new structure, dual-write, backfill, switch reads, then remove the old structure in a later release. That sequence makes rollback a code change rather than a data recovery event.