Migration Testing and Verification

In this lesson, you will learn how to implement automated verification suites to ensure your database schema and data remain consistent and correct immediately after a migration execution.

The Verification Gap

Even with idempotent migrations and transactional safety, a migration might succeed technically while failing logically—for instance, leaving a column nullable when it should be NOT NULL, or losing data during a type conversion. To bridge this, we treat the database state as an object to be tested, just like application code.

Schema State Assertions

Before validating data, verify the structural contract. You should use a testing framework to query the database metadata (e.g., information_schema in PostgreSQL/MySQL) to confirm the schema matches the expected state.

-- Asserting a schema state in a migration test
SELECT count(*) 
FROM information_schema.columns 
WHERE table_name = 'users' 
  AND column_name = 'email' 
  AND is_nullable = 'NO';

If your test runner executes this after the migration, it provides an immediate "fail-fast" feedback loop. If the constraint isn't there, the CI/CD pipeline stops before the code tries to write invalid data.

Data Integrity Verification

Beyond structure, you must verify the integrity of the data transformation. If you refactored a column or performed a bulk update, write a "smoke test" that samples the data integrity.

A common pattern is the Snapshot Comparison:

1. Take a small, representative sample of production-like data before the migration (or use a known seed).
2. Execute the migration.
3. Query the data and verify that business invariants still hold true.

For example, if you migrated a price column from an integer to a decimal, your test should explicitly assert that the precision is maintained:

-- Verification test script
SELECT CASE 
  WHEN (price * 100) % 1 = 0 THEN 'Pass' 
  ELSE 'Fail' 
END as precision_check
FROM products 
WHERE id = 123;

Why this matters

Automated migration verification transforms database changes from "hope-based" deployments into predictable, verified releases. By automating these checks, you catch regressions that simple unit tests on the application layer might miss because they don't interact with the raw database state.

Key Takeaway: Automated migration testing ensures that the database structural contract and data integrity invariants are programmatically validated as a mandatory gate in your deployment pipeline.