Database Migration Testing
Migration testing verifies schema and data changes against production-shaped data — forward, backward, and under volume — before the migration ever touches a live database.
Definition
Migration testing is the practice of exercising migration scripts as code: run them against production-scale anonymized snapshots or copies, verify forward correctness (schema, constraints, data transformation), test rollback or forward-only compensation, and measure duration under realistic volume. Expanded migrations split deploy from rollout so old and new code both run.
Context and scope
Integrated into CI with ephemeral databases from dumps or anonymized production snapshots; combined with expand-contract migration strategy for zero-downtime changes.
Common misconceptions
- Migrations run once, so tests cannot help — one-shot scripts are exactly the risk class tests target; a failed migration on prod is the most expensive deploy
- Testing against an empty schema covers it — empty schemas take different plans, hit different constraint orders, and finish in milliseconds where production takes hours
- Rollback scripts make migrations safe — rollback of data transformations loses the data written in between; forward-only with compensation is often the honest design
Anti-patterns
- Running migrations first in production because staging has no data and finishing the deploy during an outage
- Writing rollback scripts for destructive changes and losing transactions committed between versions
- Testing lock behavior only on toy tables and shipping a migration that blocks writes for an hour
Related concepts
Related examples
Authoritative source
What it is
A migration is code that runs exactly once on data you cannot replace. Migration testing applies ordinary test discipline to that code: representative inputs, expected outputs, duration and locking measured, reversibility decided deliberately.
How it works
CI provisions a database from an anonymized production snapshot, applies migrations in order, and asserts: schema matches the expected definition, constraints hold on transformed data, and idempotency or failure semantics behave as designed. Duration and lock windows are measured at production volume; anything beyond the maintenance budget fails the gate.
When to test
Every schema and data migration, including trivial-looking column changes that rewrite tables on some engines. Pair with expand-contract phases so application deploys decouple from schema flips.
Pitfalls
Snapshots age: statistics and data distributions drift from live production, changing plans and durations. Anonymization can break uniqueness constraints the migration depends on. Multi-tenant systems need testing across tenant schema variants or the migration works for nine hundred and breaks for the nine hundred and first.