Database Migration Testing in CI/CD: Catching Schema Bugs Before Production

Database migration bugs are among the most damaging deployment failures. A bad migration can corrupt data, lock tables during peak traffic, or make rollback impossible. Most teams test their application code thoroughly and test their migrations barely at all.

This guide covers how to build database migration testing into your CI/CD pipeline so schema bugs are caught before they reach production.

Why Migration Testing Is Different From Application Testing

Application code is stateless between deployments — you replace old code with new code and the system starts fresh. Database migrations are cumulative and permanent. Every migration appends to the history of every previous migration. If migration 47 has a bug, migrations 48–200 may all be invalid.

Specific risks:

Lock contention: ALTER TABLE on a large table can hold an exclusive lock for minutes, blocking all reads and writes. In production, this looks like a complete outage.

Data loss: DROP COLUMN, TRUNCATE, and DELETE during migrations are irreversible. A mistake here requires restoring from backup.

Failed deployment leaving partial migration: If a migration applies halfway and fails, the schema may be in an inconsistent state that prevents the application from starting.

Breaking old application code: If you deploy a migration before the application code that uses it, the running application may fail immediately.

Migration Testing in CI

The minimum viable migration test in CI:

# .github/workflows/ci.yml
jobs:
  migration-test:
    runs-on: ubuntu-latest
    
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Run migrations
        env:
          DATABASE_URL: postgresql://test:test@localhost:5432/testdb
        run: |
          pip install alembic
          alembic upgrade head
      
      - name: Verify schema
        run: |
          psql postgresql://test:test@localhost:5432/testdb -c "\dt"
          psql postgresql://test:test@localhost:5432/testdb -c "\d users"
      
      - name: Run application tests
        env:
          DATABASE_URL: postgresql://test:test@localhost:5432/testdb
        run: pytest tests/

This runs migrations from scratch on every CI run, verifying they apply cleanly on a fresh database.

Testing Incremental Migrations (From Current Production State)

Applying migrations from scratch doesn't test the real scenario: applying a new migration on top of an existing production schema with real data volume. To test this:

1. Dump a sanitized snapshot of your production schema (not data — just structure and a realistic row count).
2. Load the snapshot in CI.
3. Apply only the new migrations.

# Dump production schema (structure only, no data)
pg_dump --schema-only postgresql://prod-readonly:$PW@prod-host/mydb > schema_snapshot.sql

# In CI: apply snapshot then run new migrations
psql $TEST_DATABASE_URL < schema_snapshot.sql
alembic upgrade head

This catches migrations that work on empty databases but fail on schemas with existing indexes, constraints, or data.

Testing For Lock Safety

Large table alterations need to be tested for lock behavior. Use lock_timeout to make CI fail fast if a migration would hold locks too long:

-- In your migration file
SET lock_timeout = '5s';  -- Fail if lock isn't acquired in 5 seconds
SET statement_timeout = '30s';  -- Fail if statement takes more than 30 seconds

ALTER TABLE orders ADD COLUMN shipment_id UUID;

For PostgreSQL, prefer lock-safe alternatives for production:

Test that your migration uses the lock-safe path in CI by timing it against a table with 1M+ rows (use generate_series to create test data).

Rollback Testing

Every migration that changes schema structure should have a tested rollback path.

With Flyway (Teams edition) using undo migrations:

-- V5__add_user_roles.sql
CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id),
    role VARCHAR(50) NOT NULL,
    granted_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, role)
);

-- U5__add_user_roles.sql (undo)
DROP TABLE user_roles;

Test the round-trip in CI:

# Apply migration
flyway migrate

# Verify
psql $DB_URL -c "\d user_roles"

# Undo
flyway undo

# Verify table is gone
psql $DB_URL -c "\dt user_roles" 2>&1 | grep "Did not find any relation"

With Alembic:

# In migration file
def upgrade():
    op.create_table('user_roles',
        sa.Column('user_id', sa.UUID, sa.ForeignKey('users.id'), nullable=False),
        sa.Column('role', sa.String(50), nullable=False),
        sa.PrimaryKeyConstraint('user_id', 'role')
    )

def downgrade():
    op.drop_table('user_roles')

Test:

alembic upgrade head
alembic downgrade -1
alembic upgrade head

If this three-step sequence passes, the migration is reversible.

Testing Migrations With Application Code

Schema and application code must be compatible. Test two scenarios:

Forward compatibility (new schema, old app): Deploy the migration before the application code. The old running application must not break.

# Apply new migration
alembic upgrade head

# Run the CURRENT application code against the new schema
robot smoke/ --variable APP_VERSION:current

Backward compatibility (new app, old schema — during rolling deploy): New application pods may be running while old schema is still active.

# Start with old schema (don't apply migration yet)
# Run NEW application code against old schema
robot smoke/ --variable APP_VERSION:new

If both pass, you can deploy the migration and application code in any order, and rolling deploys won't cause failures.

Integration Tests That Verify Migration Correctness

Verify that migrations produce the correct result, not just that they apply without error:

# tests/test_migrations.py
def test_v5_migration_creates_user_roles_table(db):
    """user_roles table must exist after migration V5"""
    result = db.execute(
        "SELECT table_name FROM information_schema.tables "
        "WHERE table_schema='public' AND table_name='user_roles'"
    ).fetchone()
    assert result is not None

def test_user_roles_has_correct_columns(db):
    columns = db.execute(
        "SELECT column_name, data_type "
        "FROM information_schema.columns "
        "WHERE table_name='user_roles'"
    ).fetchall()
    column_names = [c[0] for c in columns]
    assert 'user_id' in column_names
    assert 'role' in column_names
    assert 'granted_at' in column_names

def test_user_roles_enforces_foreign_key(db, existing_user):
    """user_roles must reject unknown user_id"""
    with pytest.raises(Exception, match="foreign key"):
        db.execute(
            "INSERT INTO user_roles (user_id, role) VALUES (%s, %s)",
            ('00000000-0000-0000-0000-000000000000', 'admin')
        )

These tests run after migration applies and verify the schema matches the specification, not just that the SQL executed without error.

Alerting on Migration Duration

Add timing assertions to your CI pipeline:

START=$(date +%s)
alembic upgrade head
END=$(date +%s)
DURATION=$((END - START))

echo "Migration took ${DURATION} seconds"

if [ $DURATION -gt 30 ]; then
  echo "WARNING: Migration took ${DURATION}s — may need lock-safe rewrite for production"
  exit 1
fi

This catches slow migrations in CI on a small dataset. If a migration takes 30 seconds on 1,000 rows, it will take hours on 10 million rows.

Migration Deployment Order

Follow this deployment sequence:

1. Deploy migration (backward-compatible schema only — add columns, don't drop yet)
2. Deploy application (uses new schema, doesn't use removed columns)
3. Wait one release cycle (verify old code is no longer running)
4. Deploy cleanup migration (remove old columns, old tables)

This eliminates the window where old application code runs against new schema or vice versa. Never deploy a breaking schema change and the application that uses it in the same deployment.

Summary

Database migration testing isn't complex — it's disciplined. Run migrations on a fresh database in CI. Run them against a schema snapshot to simulate production. Test rollback. Verify lock safety. Test forward and backward compatibility. Add timing assertions.

The teams that do this catch migration bugs before they reach production, where a bad migration can mean an outage, a data loss incident, or a rollback that doesn't work. The setup takes a few hours. The incidents it prevents take days to recover from.