Database

Database Migration Testing: Rollback Verification, Data Integrity, and Zero-Downtime Patterns

HelpMeTest

19 May 2026 — 9 min read

Untested migrations are one of the highest-risk deployments in software engineering — a bad migration can corrupt production data in seconds with no undo. This post covers a systematic approach to testing forward migrations, rollback paths, data integrity invariants, and zero-downtime patterns like expand/contract, all using TestContainers so your CI pipeline tests against real Postgres.

Key Takeaways

Test both the up and down migration Running only the forward migration misses the rollback path — and rollbacks are what you need most during an incident when you're under pressure.

Data integrity tests run after migration, not before Write assertions that verify row counts, value constraints, and referential integrity hold after the migration completes — the schema change is only half the job.

Expand/contract makes zero-downtime possible Any migration that removes a column or changes a type must happen in two deployments: one that adds/expands, one that contracts after the old code is gone.

Isolate each migration test in its own container Reusing a container across migration tests causes state pollution — each migration scenario gets a fresh Postgres instance.

Gate CI on migration tests before every deploy Migration failures discovered in CI are a 5-minute fix. Migration failures discovered in production are a potential all-hands incident.

Most teams test their application code thoroughly and their migrations almost not at all. A migration that runs ALTER TABLE or backfills millions of rows is often pushed directly to production after a quick eyeball review. The consequences range from a failed deploy to permanent data loss. This post establishes a testing discipline for migrations that catches problems before they reach production.

Why Migration Testing Is Different

Application code bugs usually manifest as wrong behavior that can be fixed with another deploy. Migration bugs are different:

Dropping the wrong column is instant and irreversible without a backup
A bad UPDATE that runs without a WHERE clause corrupts every row
A migration that holds a table lock for 10 minutes causes a production outage
A rollback that doesn't exist or doesn't work leaves you stuck in a half-migrated state

Testing migrations requires a different mindset: you're testing a transformation of state, not a function's return value.

Setting Up the Migration Test Framework

The foundation is TestContainers — a fresh Postgres per test scenario, no shared state.

// test-utils/migrationTestContext.ts
import {
  PostgreSqlContainer,
  StartedPostgreSqlContainer,
} from '@testcontainers/postgresql';
import { Client } from 'pg';

export interface MigrationTestContext {
  connectionString: string;
  client: Client;
  container: StartedPostgreSqlContainer;
  runMigration: (sql: string) => Promise<void>;
  runQuery: <T = Record<string, unknown>>(sql: string, params?: unknown[]) => Promise<T[]>;
  tableExists: (tableName: string) => Promise<boolean>;
  columnExists: (tableName: string, columnName: string) => Promise<boolean>;
  getRowCount: (tableName: string, whereClause?: string) => Promise<number>;
}

export async function createMigrationContext(): Promise<MigrationTestContext> {
  const container = await new PostgreSqlContainer('postgres:16-alpine').start();
  const connectionString = container.getConnectionUri();

  const client = new Client({ connectionString });
  await client.connect();

  const runMigration = async (sql: string) => {
    await client.query(sql);
  };

  const runQuery = async <T>(sql: string, params: unknown[] = []): Promise<T[]> => {
    const result = await client.query(sql, params);
    return result.rows as T[];
  };

  const tableExists = async (tableName: string): Promise<boolean> => {
    const rows = await runQuery<{ exists: boolean }>(
      `SELECT EXISTS (
        SELECT FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_name = $1
      )`,
      [tableName]
    );
    return rows[0].exists;
  };

  const columnExists = async (tableName: string, columnName: string): Promise<boolean> => {
    const rows = await runQuery<{ exists: boolean }>(
      `SELECT EXISTS (
        SELECT FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = $1
        AND column_name = $2
      )`,
      [tableName, columnName]
    );
    return rows[0].exists;
  };

  const getRowCount = async (tableName: string, whereClause?: string): Promise<number> => {
    const sql = `SELECT COUNT(*) as count FROM ${tableName}${whereClause ? ` WHERE ${whereClause}` : ''}`;
    const rows = await runQuery<{ count: string }>(sql);
    return parseInt(rows[0].count, 10);
  };

  return {
    connectionString,
    client,
    container,
    runMigration,
    runQuery,
    tableExists,
    columnExists,
    getRowCount,
  };
}

export async function destroyMigrationContext(ctx: MigrationTestContext) {
  await ctx.client.end();
  await ctx.container.stop();
}

Testing Forward and Rollback Migrations

Store each migration as a pair of SQL files:

migrations/
  0001_create_users/
    up.sql
    down.sql
  0002_add_posts/
    up.sql
    down.sql
  0003_add_user_bio/
    up.sql
    down.sql

-- migrations/0001_create_users/up.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrations/0001_create_users/down.sql
DROP TABLE IF EXISTS users;

-- migrations/0002_add_posts/up.sql
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN NOT NULL DEFAULT FALSE,
  author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published) WHERE published = TRUE;

-- migrations/0002_add_posts/down.sql
DROP TABLE IF EXISTS posts;

-- migrations/0003_add_user_bio/up.sql
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- migrations/0003_add_user_bio/down.sql
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

Now test each migration independently:

// migrations/0001_create_users.test.ts
import * as fs from 'fs/promises';
import * as path from 'path';
import {
  createMigrationContext,
  destroyMigrationContext,
  type MigrationTestContext,
} from '../test-utils/migrationTestContext';

const MIGRATION_DIR = path.join(__dirname, '0001_create_users');

let ctx: MigrationTestContext;

beforeEach(async () => {
  ctx = await createMigrationContext();
}, 30000);

afterEach(async () => {
  await destroyMigrationContext(ctx);
});

describe('0001_create_users up migration', () => {
  it('creates the users table', async () => {
    const upSql = await fs.readFile(path.join(MIGRATION_DIR, 'up.sql'), 'utf-8');
    await ctx.runMigration(upSql);

    expect(await ctx.tableExists('users')).toBe(true);
  });

  it('creates all expected columns', async () => {
    const upSql = await fs.readFile(path.join(MIGRATION_DIR, 'up.sql'), 'utf-8');
    await ctx.runMigration(upSql);

    expect(await ctx.columnExists('users', 'id')).toBe(true);
    expect(await ctx.columnExists('users', 'email')).toBe(true);
    expect(await ctx.columnExists('users', 'name')).toBe(true);
    expect(await ctx.columnExists('users', 'created_at')).toBe(true);
  });

  it('enforces unique constraint on email', async () => {
    const upSql = await fs.readFile(path.join(MIGRATION_DIR, 'up.sql'), 'utf-8');
    await ctx.runMigration(upSql);

    await ctx.runQuery(`INSERT INTO users (email, name) VALUES ('a@test.com', 'Alice')`);

    await expect(
      ctx.runQuery(`INSERT INTO users (email, name) VALUES ('a@test.com', 'Alice 2')`)
    ).rejects.toThrow(/unique/i);
  });
});

describe('0001_create_users down migration (rollback)', () => {
  it('removes the users table', async () => {
    const upSql = await fs.readFile(path.join(MIGRATION_DIR, 'up.sql'), 'utf-8');
    const downSql = await fs.readFile(path.join(MIGRATION_DIR, 'down.sql'), 'utf-8');

    await ctx.runMigration(upSql);
    expect(await ctx.tableExists('users')).toBe(true);

    await ctx.runMigration(downSql);
    expect(await ctx.tableExists('users')).toBe(false);
  });

  it('is idempotent — running down twice does not throw', async () => {
    const upSql = await fs.readFile(path.join(MIGRATION_DIR, 'up.sql'), 'utf-8');
    const downSql = await fs.readFile(path.join(MIGRATION_DIR, 'down.sql'), 'utf-8');

    await ctx.runMigration(upSql);
    await ctx.runMigration(downSql);

    // IF EXISTS in the down migration makes this safe
    await expect(ctx.runMigration(downSql)).resolves.not.toThrow();
  });
});

Data Integrity Tests After Migration

Schema migrations often include data transformations. These are the riskiest operations — a missing WHERE clause or wrong type cast can silently corrupt data.

Suppose migration 0004 splits a full_name column into first_name and last_name:

-- migrations/0004_split_name/up.sql
ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

-- Backfill: split on first space
UPDATE users
SET
  first_name = SPLIT_PART(name, ' ', 1),
  last_name = NULLIF(SUBSTRING(name FROM POSITION(' ' IN name) + 1), '');

-- After backfill, make not null with fallback
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;

-- migrations/0004_split_name/down.sql
ALTER TABLE users DROP COLUMN IF EXISTS first_name;
ALTER TABLE users DROP COLUMN IF EXISTS last_name;

Test the data integrity after migration:

// migrations/0004_split_name.test.ts
describe('0004_split_name data integrity', () => {
  it('backfills first_name and last_name for all existing users', async () => {
    // Apply prerequisite migrations first
    await ctx.runMigration(await readMigration('0001_create_users', 'up'));

    // Seed data BEFORE the migration runs
    await ctx.runQuery(`
      INSERT INTO users (email, name) VALUES
        ('alice@test.com', 'Alice Wonderland'),
        ('bob@test.com', 'Bob'),
        ('carol@test.com', 'Carol Ann Smith')
    `);

    // Now run the migration under test
    await ctx.runMigration(await readMigration('0004_split_name', 'up'));

    type UserRow = { email: string; first_name: string; last_name: string | null };
    const users = await ctx.runQuery<UserRow>(
      'SELECT email, first_name, last_name FROM users ORDER BY email'
    );

    const alice = users.find((u) => u.email === 'alice@test.com')!;
    expect(alice.first_name).toBe('Alice');
    expect(alice.last_name).toBe('Wonderland');

    const bob = users.find((u) => u.email === 'bob@test.com')!;
    expect(bob.first_name).toBe('Bob');
    expect(bob.last_name).toBeNull(); // Single-name case

    const carol = users.find((u) => u.email === 'carol@test.com')!;
    expect(carol.first_name).toBe('Carol');
    expect(carol.last_name).toBe('Ann Smith'); // Multi-part last name preserved
  });

  it('preserves all rows — no data is lost during migration', async () => {
    await ctx.runMigration(await readMigration('0001_create_users', 'up'));

    const SEED_COUNT = 100;
    const values = Array.from({ length: SEED_COUNT }, (_, i) =>
      `('user${i}@test.com', 'User ${i} Name')`
    ).join(', ');

    await ctx.runQuery(`INSERT INTO users (email, name) VALUES ${values}`);

    const countBefore = await ctx.getRowCount('users');
    await ctx.runMigration(await readMigration('0004_split_name', 'up'));
    const countAfter = await ctx.getRowCount('users');

    expect(countAfter).toBe(countBefore);
  });

  it('no first_name values are NULL after migration', async () => {
    await ctx.runMigration(await readMigration('0001_create_users', 'up'));
    await ctx.runQuery(`INSERT INTO users (email, name) VALUES ('test@test.com', 'Test User')`);
    await ctx.runMigration(await readMigration('0004_split_name', 'up'));

    const nullCount = await ctx.getRowCount('users', 'first_name IS NULL');
    expect(nullCount).toBe(0);
  });
});

Testing Zero-Downtime Expand/Contract Pattern

Zero-downtime deployments require that your database is always compatible with both the old and new version of your application code simultaneously. The expand/contract pattern enforces this:

Expand: Add new columns/tables (old code ignores them, new code uses them)
Deploy new code that writes to both old and new structure
Contract: Remove old columns/tables (once old code is gone)

Testing the Expand Phase

-- migrations/0005_expand_email_verified/up.sql
-- EXPAND: add new column with a default — backward compatible
-- Old code ignores this column; new code reads and writes it
ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE users
  ADD COLUMN email_verified_at TIMESTAMPTZ;

describe('expand phase: email_verified column', () => {
  it('new column has a safe default — existing rows are not broken', async () => {
    await applyMigrationsUpTo(ctx, '0001_create_users');

    // Insert a user with old code (no email_verified column known)
    await ctx.runQuery(
      `INSERT INTO users (email, name) VALUES ('old-user@test.com', 'Old User')`
    );

    // Apply the expand migration
    await ctx.runMigration(await readMigration('0005_expand_email_verified', 'up'));

    // Old user gets the default — no null constraint violation
    const [user] = await ctx.runQuery<{ email_verified: boolean }>(
      `SELECT email_verified FROM users WHERE email = 'old-user@test.com'`
    );
    expect(user.email_verified).toBe(false);
  });

  it('new code can write to the new column while old code ignores it', async () => {
    await applyMigrationsUpTo(ctx, '0005_expand_email_verified');

    // New code writes both old and new fields
    await ctx.runQuery(`
      INSERT INTO users (email, name, email_verified, email_verified_at)
      VALUES ('new-user@test.com', 'New User', TRUE, NOW())
    `);

    // Old code writes only old fields — should still succeed
    await ctx.runQuery(`
      INSERT INTO users (email, name)
      VALUES ('old-style@test.com', 'Old Style User')
    `);

    expect(await ctx.getRowCount('users')).toBe(2);
  });
});

Testing the Contract Phase

-- migrations/0006_contract_remove_name/up.sql
-- CONTRACT: remove old 'name' column after all code uses first_name/last_name
-- PREREQUISITE: 0004_split_name must have run; deploy must have shipped new code
ALTER TABLE users DROP COLUMN name;

describe('contract phase: drop name column', () => {
  it('drops the name column', async () => {
    await applyMigrationsUpTo(ctx, '0004_split_name');
    expect(await ctx.columnExists('users', 'name')).toBe(true);

    await ctx.runMigration(await readMigration('0006_contract_remove_name', 'up'));
    expect(await ctx.columnExists('users', 'name')).toBe(false);
  });

  it('first_name and last_name are still intact after contract', async () => {
    await applyMigrationsUpTo(ctx, '0004_split_name');

    await ctx.runQuery(`
      INSERT INTO users (email, name, first_name, last_name)
      VALUES ('test@test.com', 'Test User', 'Test', 'User')
    `);

    await ctx.runMigration(await readMigration('0006_contract_remove_name', 'up'));

    const [user] = await ctx.runQuery<{ first_name: string; last_name: string }>(
      `SELECT first_name, last_name FROM users WHERE email = 'test@test.com'`
    );
    expect(user.first_name).toBe('Test');
    expect(user.last_name).toBe('User');
  });

  it('rollback restores the name column', async () => {
    await applyMigrationsUpTo(ctx, '0006_contract_remove_name');

    // The down migration must restore the column (even if empty)
    await ctx.runMigration(await readMigration('0006_contract_remove_name', 'down'));

    expect(await ctx.columnExists('users', 'name')).toBe(true);
  });
});

CI Pipeline for Migration Testing

# .github/workflows/migration-tests.yaml
name: Migration Tests

on:
  push:
    paths:
      - 'migrations/**'
      - 'test-utils/migrationTestContext.ts'
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  migration-tests:
    runs-on: ubuntu-latest
    timeout-minutes: 15

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'

      - run: npm ci

      - name: Run migration tests
        run: npx jest --testPathPattern='migrations/.*\.test\.ts' --runInBand --verbose
        env:
          TESTCONTAINERS_RYUK_DISABLED: 'true'

      - name: Verify full migration sequence
        run: npx ts-node test-utils/verifyMigrationSequence.ts

A helper script that verifies all migrations apply cleanly in sequence:

// test-utils/verifyMigrationSequence.ts
import * as fs from 'fs/promises';
import * as path from 'path';
import { createMigrationContext, destroyMigrationContext } from './migrationTestContext';

async function verifySequence() {
  const ctx = await createMigrationContext();

  try {
    const migrationsDir = path.join(__dirname, '..', 'migrations');
    const entries = await fs.readdir(migrationsDir, { withFileTypes: true });
    const migrationDirs = entries
      .filter((e) => e.isDirectory())
      .map((e) => e.name)
      .sort(); // alphabetical order = chronological order with numbered prefixes

    console.log(`Applying ${migrationDirs.length} migrations in sequence...`);

    for (const dir of migrationDirs) {
      const upPath = path.join(migrationsDir, dir, 'up.sql');
      const sql = await fs.readFile(upPath, 'utf-8');
      await ctx.runMigration(sql);
      console.log(`  ✓ ${dir}`);
    }

    // Verify rollback sequence in reverse
    console.log('\nRolling back in reverse order...');
    for (const dir of [...migrationDirs].reverse()) {
      const downPath = path.join(migrationsDir, dir, 'down.sql');
      const sql = await fs.readFile(downPath, 'utf-8');
      await ctx.runMigration(sql);
      console.log(`  ✓ ${dir} (rolled back)`);
    }

    console.log('\nAll migrations applied and rolled back successfully.');
  } finally {
    await destroyMigrationContext(ctx);
  }
}

verifySequence().catch((err) => {
  console.error('Migration sequence verification failed:', err);
  process.exit(1);
});

Common Migration Bugs This Testing Catches

The patterns above catch the most common migration bugs before they reach production:

Missing rollback: The verifyMigrationSequence script ensures every up.sql has a working down.sql. Rollbacks written months after the up migration are often wrong.

Non-idempotent down migrations: Testing down twice verifies that IF EXISTS guards are present on every DROP statement.

Backfill correctness: Seeding data before the migration runs and asserting the expected shape after is the only way to know your UPDATE statement does what you think.

Sequence ordering bugs: Applying migrations in order in a fresh container catches forward references — a migration that ALTER TABLE on a table created by a later migration will fail immediately.

Lock-inducing operations: While not automated here, running migrations against a container with active connections (using pg_sleep in a background query) can reveal migrations that deadlock or time out.

Migration testing is infrastructure testing, and it demands the same rigor as application testing. A migration that works perfectly on a 10-row development database can lock tables for minutes on a 50-million-row production database — but at least with this test suite, you'll know the SQL is correct before you find out about the scale problem.