Dry-Run Database Migrations on a Clone to Prevent Data Loss

Description and use

Standard tests fail to detect data loss during database migrations. The recommended workflow involves dry-running migrations on a cloned production schema with representative data, verifying both up and down migrations by comparing snapshots of metrics like row counts, null rates, and constraints.

Problem

Standard test suites validate the post-migration schema but cannot detect data loss or corruption that occurs during the migration process, especially when AI-generated migrations are used. Staging environments often lack representative data, making them poor substitutes for verifying migration safety.

Outcome

The workflow provides a mechanical verification method to ensure database migrations are reversible and do not cause silent data loss. It shifts the focus from 'does the app work with the new schema?' to 'can we safely revert if something goes wrong?'

Workflow

• Clone the production schema and load a representative data sample (e.g., a few thousand rows per table).
• Snapshot the baseline metrics: record row counts, null rates, and constraint counts before any migration.
• Apply the 'up' migration and snapshot the metrics again.
• Apply the 'down' migration and snapshot the metrics a third time.
• Compare all three snapshots to ensure the 'up' migration changes only intended fields and the 'down' migration restores the baseline exactly.

Evidence

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.

Source

The key property is not that the down migration executes without error; it is that the schema and data return to the baseline state.

Source