Testing Migrations with Testcontainers

The migration passed unit tests, which ran against SQLite. It failed in staging on PostgreSQL, because ALTER TABLE ... ALTER COLUMN ... TYPE behaves differently, and nobody had exercised the down migration at all. Testing migrations against anything other than the real engine at the real version tests the wrong thing. Testcontainers — libraries for Java, Go, Python, Node.js, .NET and others that start throwaway database containers from test code — make the real engine cheap enough to use in every test run: each test suite gets a fresh PostgreSQL or MySQL of the production version, applies the migrations, and asserts on the result. This guide builds a migration test suite on Testcontainers: applying all migrations from scratch, round-tripping up and down, proving idempotency, checking lock behaviour, and running the previous application version against the new schema. It belongs to Automated Migration Testing.

Symptom / Error Signatures

Migration test gaps show up as production-only failures:

• Migrations that pass against SQLite, H2 or an in-memory fake but fail on PostgreSQL or MySQL syntax or semantics (ERROR: syntax error at or near "IF" on MySQL for ADD COLUMN IF NOT EXISTS, different default handling, different transaction behaviour).
• Down migrations that fail the first time they are used — during an incident.
• Migrations that fail on retry after a partial failure because they are not idempotent.
• Previous application versions failing against the new schema during rolling deploys, discovered in production.
• Flaky tests caused by a shared development database whose state differs between runs.

Root Cause Analysis

Migrations are code whose behaviour depends almost entirely on the database engine: DDL syntax, transactional behaviour, lock modes, type coercion, defaults. Testing them against a different engine, or a different version of the same one, validates syntax that production will never run and misses the behaviour that matters. A shared long-lived test database adds state that makes tests order-dependent.

Testcontainers solves both problems by starting a disposable container for the exact image you name (postgres:16.4, mysql:8.0.39) from inside the test process, exposing its connection details, and removing it afterwards. Startup takes seconds; a container per test suite, with a transaction or a schema reset per test, keeps runs isolated and fast.

Immediate Mitigation

1. Replace the fake database in migration tests with a container of the production engine and version.

# Python · tests/test_migrations.py · pytest + testcontainers + alembic
# WARNING: pin the image tag to production's exact version; "latest" makes tests drift.
import pytest, sqlalchemy as sa
from alembic import command
from alembic.config import Config
from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")
def pg_url():
    with PostgresContainer("postgres:16.4") as pg:
        yield pg.get_connection_url()

def alembic_cfg(url):
    cfg = Config("alembic.ini")
    cfg.set_main_option("sqlalchemy.url", url)
    return cfg

def test_upgrade_from_empty(pg_url):
    command.upgrade(alembic_cfg(pg_url), "head")

def test_latest_round_trips(pg_url):
    cfg = alembic_cfg(pg_url)
    command.upgrade(cfg, "head")
    command.downgrade(cfg, "-1")
    command.upgrade(cfg, "head")

2. Add an idempotency check for migrations that must be rerunnable — non-transactional ones, and any your runbooks expect to retry — by applying the SQL twice.

3. Assert on locks for risky migrations. Run the migration in one connection while another holds a transaction open on the target table, with a short lock_timeout, and assert the migration either completes (because its lock is compatible) or fails fast with 55P03 rather than hanging — the technique behind catching table lock regressions in migration tests.

Permanent Fix / Long-Term Pattern

Make the Testcontainers suite the standard migration test for every service, run on every pull request that touches migrations. Beyond the basic checks, add the one that catches the most production incidents: run the previous application version’s integration tests against the new schema. Check out the last released commit’s test suite (or use its built test image), point it at a container migrated to the new head, and run it. Any failure means the migration is not backward compatible with the code that will be running during the deploy — the property described in enforcing backward compatibility checks in pull requests.

# Shell · CI job · run the previous release's tests against the new schema
# WARNING: uses the container URL exported by the migration step; never a shared database.
git worktree add ../prev "$(git describe --tags --abbrev=0)"
DATABASE_URL="$TEST_DB_URL" alembic upgrade head          # new migrations
( cd ../prev && DATABASE_URL="$TEST_DB_URL" pytest tests/integration -q )

Keep the suite fast by reusing one container per test session and resetting state between tests (a transaction rolled back per test, or TRUNCATE of application tables). Cache the image in CI. Pin versions and update them together with production upgrades. For production-sized behaviour — duration, lock time, replica lag — Testcontainers is the wrong tool; complement it with rehearsals on snapshots, as in testing migrations against production-like snapshots.

Verification Checklist

• Migration tests run against a container of the exact production engine and version, not a fake or shared database.
• Every pull request applies all migrations from empty and round-trips the newest one.
• Migrations expected to be retried are applied twice in tests.
• Risky migrations have lock assertions with a concurrent open transaction.
• The previous release’s integration tests run against the new schema before merge.

Frequently Asked Questions

Is Testcontainers fast enough for every pull request? Yes, with a cached image and one container per test session. Starting PostgreSQL or MySQL takes a few seconds; applying hundreds of migrations to an empty database usually takes seconds more.

Why not use Docker Compose services in CI instead? That works too. Testcontainers ties the database lifecycle to the test code, which makes local runs identical to CI runs and lets tests start several databases (for example two versions) when needed.

Should migrations be tested against production data? Not in this suite. Testcontainers tests correctness on an empty or seeded database. Duration, lock time and data-dependent failures need rehearsals on production-like snapshots.

How do I test MySQL migrations? Use the MySQL module with the exact production version (for example mysql:8.0.39). The same checks apply, with extra attention to implicit commits: a failed multi-statement migration leaves partial changes, which the tests should expose.

What does the previous-version test catch? Backward-incompatible migrations: dropped or renamed columns the old code uses, new NOT NULL columns the old code does not write, and type changes the old code cannot read. Those are exactly the failures that occur during rolling deploys.