Testing Down Migrations Automatically in CI

The incident starts calmly: a deploy goes wrong, the on-call engineer reaches for the rollback everyone assumed existed, and the down migration errors on the first statement — column "region_code" does not exist, or worse, it succeeds and quietly drops a table that the previous release still reads from. The up migration was reviewed, tested, and shipped; the down migration was written in thirty seconds, never executed, and trusted anyway. A rollback you have never run is not a rollback — it is a hope. The fix is to make CI run every down migration on every pull request and prove the schema round-trips: apply up, apply down, apply up again, and assert the database is byte-for-byte the shape it should be at each stop. This page builds that round-trip test so a broken or lossy down fails the build instead of the incident bridge.

Symptom / Error Signatures

A down migration that has never been executed fails in a handful of recognisable ways, and each one has a signature you can catch in CI rather than in production.

The most common is a statement that assumes state the down migration itself removed or never created. On PostgreSQL you see ERROR: column "region_code" does not exist or ERROR: table "orders_new" does not exist when the down tries to reverse a step in the wrong order. A down migration that renamed instead of restored surfaces as ERROR: relation "orders" already exists on the way back up.

Framework runners wrap the same failures in their own words. Alembic raises NotImplementedError the moment a revision’s downgrade() body is a bare pass. Rails’ ActiveRecord prints ActiveRecord::IrreversibleMigration when a change block contains an operation it cannot auto-invert. Liquibase throws liquibase.exception.RollbackImpossibleException: No inverse to ... available for a changeset with no rollback clause, and Flyway reports No undo migration for version 42 because undo scripts are a separate, opt-in artifact.

The most dangerous signature is no error at all: the down runs clean but the resulting schema does not match the pre-migration baseline. A pg_dump --schema-only diff between “before the up” and “after the down” comes back non-empty — a leftover index, a default that was not restored, a column left NOT NULL. That silent drift is exactly what the round-trip assertion exists to surface.

Root Cause Analysis

A down migration is only trustworthy if it is the exact inverse of the up — and “exact inverse” is a property no linter can read from the SQL text. It is emergent: it depends on operation ordering, on whether each up step is individually reversible, and on how the migration tool models reversal. That is why the round-trip has to be executed, not reviewed.

The core invariant is simple. Let S0 be the schema before the migration, S1 the schema after up. A correct down migration returns the database to S0; re-applying up returns it to S1. So a round-trip test dumps the schema at three points — after up, after down, after up again — and asserts two equalities: dump(after down) == dump(S0) and dump(after up) == dump(after up-again). Any inequality is a defect in the down migration, full stop.

The two equalities a round-trip test must prove: down returns the schema to the baseline, and re-applying up reproduces the release schema exactly.

Tools diverge sharply in how they even let you express and run a down migration, which changes how the CI test has to invoke them.

Tool	How a down is expressed	Auto-reversible?	Round-trip invocation in CI
Flyway	Separate `U`-prefixed undo script (`U42__...sql`), Teams edition	No — hand-written	`flyway migrate` → `flyway undo` → `flyway migrate`
Liquibase	`rollback` block or `rollbackSQL` per changeset	Partial — infers for simple changes	`update` → `rollback-count 1` → `update`
Alembic	Explicit `downgrade()` in each revision	No — you write it	`upgrade head` → `downgrade -1` → `upgrade head`
Rails / ActiveRecord	`change` (auto-invert) or explicit `def down`	Partial — `change` inverts known ops	`db:migrate` → `db:rollback` → `db:migrate`

The partial-inversion tools are the trap. Liquibase and Rails will happily infer a rollback for the operations they recognise and leave you with a silent gap for the ones they do not — a raw-SQL changeset, a data backfill, a change_column that loses the original type. The migration tool comparison covers these reversal models in depth; the practical consequence for testing is that the round-trip must actually run the tool’s own down command against a real database, because only execution reveals whether the inferred inverse exists and is correct.

Immediate Mitigation

If you have just discovered a down migration does not work — mid-incident, or in a pre-deploy check — the goal is to confirm the round-trip on a disposable database before you touch production, and to make the failure loud.

1. Spin up a throwaway database and capture the baseline. Everything is measured against this dump.

# bash · CI runner or local shell · ephemeral container, safe to destroy
# Context: never point this at production — it applies and reverts DDL repeatedly.
docker run -d --name rt_db -e POSTGRES_PASSWORD=ci -p 5433:5432 postgres:16
export DB_URL="postgres://postgres:ci@localhost:5433/postgres"
pg_dump --schema-only --no-owner "$DB_URL" > /tmp/s0.sql   # baseline S0

2. Apply up, then dump S1. This is the schema the release expects.

# bash · ephemeral DB only · migration role
# Context: use your tool's up command; Alembic shown, swap for flyway/liquibase/rails.
alembic -x db_url="$DB_URL" upgrade head
pg_dump --schema-only --no-owner "$DB_URL" > /tmp/s1.sql

3. Apply down, then assert you are back at the baseline. A non-empty diff here is the bug.

# bash · ephemeral DB only · migration role
# Context: downgrade exactly one revision; the dump must equal S0 or the down is lossy.
alembic -x db_url="$DB_URL" downgrade -1
pg_dump --schema-only --no-owner "$DB_URL" > /tmp/s0_again.sql
diff -u /tmp/s0.sql /tmp/s0_again.sql || { echo "DOWN MIGRATION IS NOT AN INVERSE"; exit 1; }

4. Re-apply up and assert determinism. Proves the up is idempotent across a rollback cycle.

# bash · ephemeral DB only · migration role
# Context: second up must reproduce S1 exactly — otherwise the migration is order-dependent.
alembic -x db_url="$DB_URL" upgrade head
pg_dump --schema-only --no-owner "$DB_URL" > /tmp/s1_again.sql
diff -u /tmp/s1.sql /tmp/s1_again.sql || { echo "UP IS NOT DETERMINISTIC AFTER ROLLBACK"; exit 1; }
docker rm -f rt_db

The runner drives up, down, and up again against a disposable database; a pg_dump at each stop feeds two diff gates that fail the build the moment the schema drifts.

If any diff fails, do not widen the comparison to make it pass — that hides exactly the drift you are hunting. Fix the down migration to restore the missing object, and treat a truly irreversible step (a dropped column with data) as a signal to stage the change through expand-and-contract methodology so the destructive half never needs a down at all.

Permanent Fix / Long-Term Pattern

The durable control is a single CI job that performs the round-trip on the exact migrations a pull request adds, against a snapshot with realistic data, and blocks the merge on any dump inequality. Wire it into the same stage as the rest of your automated migration testing so up-correctness, lock behaviour, and reversibility are all proven in one pass.

# scripts/round-trip-test.sh — proves up→down→up is loss-free for the PR's new migrations
# Context: ephemeral DB restored from a sanitized snapshot; destroyed after. Non-zero exit fails the build.
set -euo pipefail
NORM="grep -vE '^--|^$'"                    # normalize dumps: strip comments + blank lines

dump() { pg_dump --schema-only --no-owner "$DB_URL" | eval "$NORM"; }

dump > s0.txt                                # baseline before the PR's migrations
migrate up                                   # your tool's forward command
dump > s1.txt
migrate down                                 # reverse exactly the PR's new migrations
diff s0.txt <(dump) || { echo "::error::down is not an inverse of up"; exit 1; }
migrate up
diff s1.txt <(dump) || { echo "::error::up is non-deterministic after rollback"; exit 1; }
echo "round-trip OK"

Three design points make the test robust in practice. First, normalize the dumps before comparing — strip comments, ordering artifacts, and dump-version headers so a cosmetic difference never masquerades as drift (or worse, hides real drift). Second, round-trip only the migrations the PR adds, not the whole history, by computing the current head before checkout and downgrading back to it; this keeps the test fast and scoped to the change under review. Third, run it against a populated snapshot, because a down migration that includes a data step — restoring a backfilled column, for example — only proves loss-free against real rows; borrow the restore harness from testing migrations against production-like snapshots.

The round-trip gate sits beside the lock-regression and snapshot-apply gates; a clean round-trip lets the merge through, while any dump diff blocks it and surfaces the drift.

Reversibility of the down script itself is a separate discipline from testing it: the test proves a down is correct, but writing one that is safe to run against live traffic — non-destructive, ordered contract-last, guarded — is covered under writing safe down migrations for automated rollback. Pair the two: author the down defensively with idempotent script design guards so it survives a partial failure, then let the round-trip test prove it is a true inverse before it can ever be the thing that auto-reverting migrations on health-check failure fires automatically.

Verification Checklist

CI runs up → down → up on every pull request that adds a migration, against a database with realistic data.
The schema dump after down is compared to the pre-migration baseline and any non-empty diff fails the build.
The schema dump after the second up is compared to the first and any difference fails the build.
Dumps are normalized (comments, blank lines, and dump-version headers stripped) before comparison.
A migration with a deliberately broken down is kept as a fixture and confirmed to fail the round-trip.
Genuinely irreversible steps are staged through expand-and-contract so no destructive down is required.

Up next: return to Automated Migration Testing for the full proof stage this round-trip test lives inside.

Frequently Asked Questions

Why test the down migration if we practise forward-only rollbacks? Because “forward-only” is a deployment preference, not a guarantee your down scripts are absent — most tools still generate or accept them, and the moment one exists an engineer will reach for it under pressure. Even if you never run down in production, the round-trip test doubles as a proof that your up migration is deterministic and that your schema is fully described by the migration history. If you truly commit to forward-only, delete the down bodies so the test fails loudly rather than leaving a rollback nobody has verified.

How do I round-trip a migration that includes a data backfill, not just DDL? Run it against a populated snapshot and compare the schema dump for structural reversibility, then add explicit row-count or checksum assertions for the data half — a down that restores a dropped column’s values must be checked against known rows, which a schema-only dump cannot see. When the data cannot be restored (the source was truly deleted), that is a signal the step is irreversible and belongs in a contract phase behind expand-and-contract, not in a rollback path.

The dump diff shows a difference that looks harmless — can I ignore it? No — investigate it, then either fix the down or normalize the noise deliberately. A “harmless” leftover default or index ordering is often the visible edge of a real inversion gap, and suppressing it by widening the comparison retrains the test to pass on drift. Normalize only provably cosmetic artifacts (dump-version headers, comment lines, deterministic ordering), and keep every structural object inside the assertion.

Related Articles