Round-Trip Tests Catch Bad Down Migrations, Not Data Loss

Coding agents write a lot of database migrations now, and the pattern of failure is consistent: the up script gets real scrutiny because it has to run for the feature to work, while the down script gets merged on vibes. A model writing ALTER TABLE has your target schema in context; it rarely has the baseline schema it's supposed to restore. So the down script it produces is a plausible-looking guess — syntactically valid, confidently wrong about a constraint name, a column default, an index that existed before. You find out during an incident, which is the one time you wanted that script to be boring.

There's a cheap defense making the rounds: round-trip the migration in CI against a disposable database and diff the schema. It's a good check and you should run it. But it's worth being precise about what it proves — because the thing it can't prove is exactly the thing the industry spent the last decade concluding down migrations can't deliver.

The round-trip test costs almost nothing

The mechanics fit in a CI job you can write in an afternoon. Spin up a throwaway Postgres (a service container is fine), load the current schema, snapshot it, apply the migration forward, apply it backward, snapshot again, and demand a byte-identical result:

pg_dump --schema-only -f baseline.sql "$DB"
psql "$DB" -v ON_ERROR_STOP=1 -f migrations/0042_split_billing/up.sql
psql "$DB" -v ON_ERROR_STOP=1 -f migrations/0042_split_billing/down.sql
pg_dump --schema-only -f after.sql "$DB"
diff baseline.sql after.sql   # any output fails the build

Text-diffing dumps gets noisy if you're comparing across server versions; a semantic comparison with migra or atlas schema diff is sturdier. Rails users have had a one-liner version of this forever — db:migrate:redo applies and reverts the latest migration, and running it in CI catches most IrreversibleMigration surprises — though it doesn't assert the schema actually matches the starting point, which is the part that catches subtle drift.

The reason this check pays off specifically for generated migrations: the failure output is a targeted repair prompt. "Down migration left index idx_orders_user_id missing" is exactly the context the model lacked the first time. You fix the down script, not the whole migration.

Schema symmetry is not reversibility

Here's the trap. Consider the most common destructive migration there is:

-- up.sql
ALTER TABLE users DROP COLUMN legacy_plan;
-- down.sql
ALTER TABLE users ADD COLUMN legacy_plan text;

This round-trips perfectly. The schema diff is empty, CI is green, and the down migration has destroyed every value in that column. Schema symmetry proves the down script restores the shape of your database. It says nothing about the substance.

You can extend the harness to catch this — seed representative rows before the round trip and checksum them after. A SELECT md5(string_agg(...)) per table before and after will flag any migration pair that loses data in transit, including the drop-and-recreate case above. That's a meaningfully stronger contract, and almost nobody tests it, because it forces you to confront that many migrations are inherently irreversible: dropped columns, narrowed types, merged tables, backfills that collapse information. No down script, however carefully generated and tested, can un-lose data.

The industry already ruled on this

That's why the tooling landscape looks the way it does. Prisma doesn't generate down migrations at all — if you want one, you construct it yourself with migrate diff. Flyway treats undo migrations as a paid Teams-tier feature, and Redgate's own guidance is candid that undo scripts can't recover destroyed data. Large shops mostly converged on roll-forward-only: a bad migration is fixed by shipping another migration, and actual disaster recovery is a snapshot restore, not a down method. The down script quietly became a local-development convenience — handy for iterating on a branch, not a production recovery plan.

The tools that genuinely solve rollback solve it by refusing to play the game. pgroll keeps both the old and new schema live simultaneously through views during an expand/contract window, so "rollback" means discarding a schema version that's still there — no reverse script to get wrong. Atlas attacks it from the review side: atlas migrate lint flags destructive and backward-incompatible statements at PR time, which is arguably the more honest check — instead of asking "can this be undone?", it asks "does this destroy something?"

So there's a real irony in the current moment. AI codegen is reviving an artifact — the symmetric down migration — at exactly the point the ecosystem had finished admitting it was mostly theater. Agents produce down scripts by default because their training data is full of them, and teams that had comfortably gone roll-forward-only are now merging reverse migrations nobody intended to run.

What to actually do

Run the round-trip test — it's nearly free, it catches the class of error generated code is most prone to, and its failures are self-explaining. Add data checksums if you want the honest version. But classify what you've built correctly: it's a lint for generated SQL, not a rollback guarantee.

For production, the hierarchy hasn't changed. Destructive changes go through expand/contract (or pgroll, which automates it), so old and new code can coexist and "rollback" is a deploy decision rather than a schema operation. Recovery from a genuinely bad migration is point-in-time restore. And a down script that's never been executed against realistic data should be treated as what it is: a comment with delusions of grandeur. Test it in CI precisely so you know it works in the one place you'll ever run it — your laptop.