Opinion: Your Tests Can't See What a Migration Destroys — Dry-Run It on a Clone

Opinion: Your Tests Can't See What a Migration Destroys — Dry-Run It on a Clone

A green test suite is the wrong tool for judging an AI-generated migration, because tests run against the post-migration schema and never observe the intermediate states where data disappears. The up migration is the visible artifact that gets reviewed, while the down migration is treated as an afterthought even though it is the only safety net when the deployment goes wrong. Free model access makes the problem structural: generation cost drops to zero, so migration volume rises, and every additional migration multiplies the surface for unreviewed data loss. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

Tests validate the destination, not the journey

When a test suite runs against a migrated database, it confirms that the application can read the new schema, but it cannot confirm that the migration preserved the data it was supposed to preserve. The test runner connects after the migration has executed, so it never sees the moment when a column is dropped, a table is renamed, or a constraint is silently relaxed. A migration that passes every test can still destroy production data, because the tests were designed to validate application behavior, not migration safety.

The standard mitigation is a staging database, but staging is a poor substitute for a dry run because it has different data, different volume, and different usage patterns. The dry run I recommend uses a clone of the production schema with a representative data sample, and it exercises both directions of the migration with data integrity checks at every step. The clone does not need to be large; a few thousand rows per table is enough to expose most destructive patterns.

The dry-run workflow in five steps

The workflow is deliberately mechanical, because the goal is to remove judgment from the verification process and reserve human attention for the migration's intent:

1. Clone the schema and load a data sample — dump the current schema, create a fresh database, and load a representative slice of production data.
2. Snapshot the baseline — record row counts, null rates, and constraint counts before the migration touches anything.
3. Apply the up migration and snapshot again — run the AI-generated up migration and record the same metrics.
4. Apply the down migration and snapshot a third time — run the down migration and record the metrics once more.
5. Compare all three snapshots — the up migration should change only what the patch intends, and the down migration should restore the baseline exactly.

The script below implements steps two through five for PostgreSQL:

#!/usr/bin/env bash
# migration_dry_run.sh — verify both directions of a migration on a clone
set -euo pipefail
DB="${1:-migration_dry_run}"
UP="${2:-up.sql}"
DOWN="${3:-down.sql}"

snapshot() {
  local phase="$1"
  psql -d "$DB" -Atc "SELECT '${phase}', count(*) FROM information_schema.tables WHERE table_schema='public';"
  psql -d "$DB" -Atc "SELECT '${phase}', count(*) FROM pg_constraint;"
  psql -d "$DB" -Atc "SELECT '${phase}', count(*) FROM pg_attribute WHERE attnotnull;"
}

echo "[1/4] baseline"
snapshot "baseline"

echo "[2/4] up migration"
psql -v ON_ERROR_STOP=1 -d "$DB" -f "$UP"
snapshot "after_up"

echo "[3/4] down migration"
psql -v ON_ERROR_STOP=1 -d "$DB" -f "$DOWN"
snapshot "after_down"

echo "[4/4] diff baseline vs after_down"
# A real comparison diffs per-table row counts; this is a structural check.
psql -d "$DB" -Atc "SELECT 'tables_restored', count(*) FROM information_schema.tables WHERE table_schema='public';"

The script captures three structural metrics — table count, constraint count, and not-null attribute count — which are enough to catch the most common destructive migration patterns. A production-grade version should also record per-table row counts and column null rates, then diff them across the three snapshots. The key property is not that the down migration executes without error; it is that the schema and data return to the baseline state.

The data checks that catch what schema checks miss

Schema restoration is necessary but not sufficient, because data damage can survive a successful down migration. A column type change might convert values irreversibly, and the down migration will restore the type while the data stays corrupted. The checks that catch these problems are the same ones you would run for a data-quality audit:

• Per-table row counts — any table whose size changed beyond the migration's intent is a red flag.
• Column null rates — a column that was never null before the migration and is null after it indicates data loss.
• Duplicate detection — a migration that adds a unique constraint should not silently merge rows.
• Foreign key integrity — the down migration should restore the exact set of referenced rows.

These checks are cheap to run on a clone, and the free server option makes the clone itself affordable. The point of the dry run is not to prove that the migration is correct; it is to prove that the migration is reversible, which is a weaker and more honest claim. A migration that cannot prove its down side is unfinished, regardless of how many tests pass.

Who should not use this approach

Teams using managed databases that do not support down migrations will find this workflow inapplicable, because there is no down migration to verify. Teams with schemaless databases should adapt the checks to document counts and field presence instead of table counts. The dry run also assumes you can create a clone, which requires access to a schema dump and a representative data sample; if you cannot produce either, the workflow will not protect you.

Free model access makes migration generation cheap, but it does not make migration verification cheap. The up migration is the part you can see, and the down migration is the part that saves you when the deployment goes wrong. Run the dry run on a clone, verify both directions, and treat any migration that fails the round-trip as a rejected patch. If you have a migration horror story from a generated patch, share the failure mode; the community needs real examples of what the dry run would have caught.