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

AI agents are generating database down migrations that often fail to restore the original schema, and a round-trip test in CI can catch schema drift but not data loss, according to a developer article by Rachel Goldstein. The test, which applies and reverts a migration against a disposable database and diffs the schema, costs little to implement but cannot prove reversibility for destructive operations like dropping columns, which inherently lose data. The article notes that tools like Prisma and Flyway have already moved away from relying on down migrations for data restoration.

Dev Tools https://sourcefeed.dev/c/dev-tools Article Round-Trip Tests Catch Bad Down Migrations, Not Data Loss AI agents are reviving the down migration just as the industry finished giving up on it. Rachel Goldstein https://sourcefeed.dev/u/rachel goldstein 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 https://github.com/djrobstep/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 https://www.prisma.io/docs/orm/prisma-migrate doesn't generate down migrations at all — if you want one, you construct it yourself with migrate diff . Flyway https://documentation.red-gate.com/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 https://pgroll.com 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 https://atlasgo.io 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. Sources & further reading - Prove a Generated Migration Can Undo Itself Before It Touches Your Data https://dev.to/codepy 1473/prove-a-generated-migration-can-undo-itself-before-it-touches-your-data-gi0 — dev.to - Generating down migrations https://www.prisma.io/docs/orm/prisma-migrate/workflows/generating-down-migrations — prisma.io - Undo migrations https://documentation.red-gate.com/flyway/flyway-concepts/migrations/undo-migrations — documentation.red-gate.com - Introducing pgroll: zero-downtime, reversible, schema migrations for Postgres https://xata.io/blog/pgroll-schema-migrations-postgres — xata.io - Verifying Migration Safety https://atlasgo.io/versioned/lint — atlasgo.io Rachel Goldstein https://sourcefeed.dev/u/rachel goldstein · Dev Tools Editor Rachel has been embedded in the developer tooling ecosystem for nearly eight years, covering everything from IDE wars and package-manager drama to the quiet rise of AI-assisted coding. She has a soft spot for open-source maintainers and an unhealthy number of terminal emulators installed on a single laptop. Discussion 0 No comments yet Be the first to weigh in.