Testing PostgreSQL Migrations: Flyway, Liquibase, and Rollback Safety

Testing PostgreSQL Migrations: Flyway, Liquibase, and Rollback Safety

Database migrations are one of the highest-risk operations in any software project. A bad migration can corrupt data, lock tables for minutes, or leave your schema in an inconsistent state that breaks every query. Yet migration testing is often treated as an afterthought — teams run flyway migrate in CI and call it done, with no verification that the resulting schema matches expectations or that rollbacks actually work.

This post covers practical strategies for testing PostgreSQL migrations using Flyway and Liquibase, including how to write tests that catch schema drift, validate rollbacks, and give you confidence before a migration ever touches production data.

Why Migration Testing Matters

Schema drift is the silent killer of database reliability. It happens when your application code expects one schema but the database has another — maybe someone ran an ad-hoc ALTER TABLE in production, or a migration partially applied before failing, or two branches were merged in the wrong order.

Without migration tests, you discover schema drift at the worst possible time: during a production deploy at 2am.

Three specific failure modes justify investing in migration tests:

Rollback failures. Most teams write up migrations but never test down migrations. When a deployment goes wrong and you need to roll back, you discover the rollback script hasn't been maintained and doesn't work. By the time you've fixed it, you've been down for 30 minutes.

Data loss bugs. Migrations that drop columns or rename tables can silently discard data if written incorrectly. Tests that verify data survival across a migration catch these before production.

Performance regressions. A migration that adds an index on a large table runs fine in development with 1000 rows but causes a 10-minute table lock in production with 50 million rows. Integration tests with realistic data volumes expose this early.

Setting Up Testcontainers for PostgreSQL

The foundation of migration testing is a real PostgreSQL instance that gets created fresh for each test run. Testcontainers provides exactly this — it spins up a Docker container, runs your migrations, and tears it down when done.

npm install --save-dev testcontainers @testcontainers/postgresql
npm install --save-dev jest
// test/helpers/postgres.js
const { PostgreSqlContainer } = require('@testcontainers/postgresql');

let container;
let connectionString;

async function startPostgres() {
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('testuser')
    .withPassword('testpass')
    .start();

  connectionString = container.getConnectionUri();
  return connectionString;
}

async function stopPostgres() {
  if (container) {
    await container.stop();
  }
}

module.exports = { startPostgres, stopPostgres, getConnectionString: () => connectionString };

This gives you a fresh PostgreSQL 16 instance for every test suite. The container starts in about 2-3 seconds on most machines.

Testing Migrations with Flyway

Flyway is a popular Java-based migration tool with a Node.js wrapper. Here's how to integrate it into a Jest test suite:

npm install --save-dev node-flyway
npm install pg
// test/migrations/flyway.test.js
const { Flyway } = require('node-flyway');
const { Client } = require('pg');
const { startPostgres, stopPostgres, getConnectionString } = require('../helpers/postgres');

let client;

beforeAll(async () => {
  const connectionString = await startPostgres();
  
  const flyway = new Flyway({
    url: connectionString,
    defaultSchema: 'public',
    locations: ['filesystem:./migrations'],
    validateOnMigrate: true,
  });

  const result = await flyway.migrate();
  
  if (!result.success) {
    throw new Error(`Migration failed: ${JSON.stringify(result.flywayResponse)}`);
  }

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

afterAll(async () => {
  await client?.end();
  await stopPostgres();
});

test('migrations applied successfully', async () => {
  const result = await client.query(
    "SELECT COUNT(*) FROM flyway_schema_history WHERE success = true"
  );
  expect(parseInt(result.rows[0].count)).toBeGreaterThan(0);
});

test('users table has expected columns', async () => {
  const result = await client.query(`
    SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_name = 'users'
    ORDER BY ordinal_position
  `);

  const columns = result.rows.reduce((acc, row) => {
    acc[row.column_name] = { type: row.data_type, nullable: row.is_nullable };
    return acc;
  }, {});

  expect(columns).toMatchObject({
    id: { type: 'integer', nullable: 'NO' },
    email: { type: 'character varying', nullable: 'NO' },
    created_at: { type: 'timestamp without time zone', nullable: 'NO' },
  });
});

test('expected indexes exist', async () => {
  const result = await client.query(`
    SELECT indexname FROM pg_indexes
    WHERE tablename = 'users'
  `);
  
  const indexNames = result.rows.map(r => r.indexname);
  expect(indexNames).toContain('users_email_key');
  expect(indexNames).toContain('users_pkey');
});

The key insight here is that you're testing the outcome of migrations, not just that they ran without error. Checking column types, nullability, and index existence catches migration bugs that would otherwise slip through.

Validating Flyway Checksums and Ordering

Flyway enforces that migration files don't change after they've been applied. This prevents a common mistake where a developer edits a migration file that's already been applied to production. Testing that this validation actually fires:

test('flyway rejects modified migration files', async () => {
  const connectionString = await startPostgres();
  
  // First migration run
  const flyway1 = new Flyway({
    url: connectionString,
    locations: ['filesystem:./migrations'],
  });
  await flyway1.migrate();

  // Simulate a modified migration (wrong checksum)
  const flyway2 = new Flyway({
    url: connectionString,
    locations: ['filesystem:./migrations-tampered'],
  });
  
  const result = await flyway2.migrate();
  expect(result.success).toBe(false);
  expect(result.flywayResponse.errorDetails.errorCode).toBe('CHECKSUM_MISMATCH');
});

Liquibase Changeset Validation

Liquibase takes a different approach with XML/YAML changesets. Here's how to test Liquibase migrations:

npm install --save-dev liquibase
// test/migrations/liquibase.test.js
const { LiquibaseConfig, Liquibase, POSTGRESQL_DEFAULT_CONFIG } = require('liquibase');
const { startPostgres, stopPostgres, getConnectionString } = require('../helpers/postgres');

beforeAll(async () => {
  await startPostgres();
}, 30000);

afterAll(async () => {
  await stopPostgres();
});

test('liquibase validate passes', async () => {
  const connectionString = getConnectionString();
  const url = new URL(connectionString);
  
  const config = new LiquibaseConfig({
    ...POSTGRESQL_DEFAULT_CONFIG,
    url: `jdbc:postgresql://${url.host}${url.pathname}`,
    username: url.username,
    password: url.password,
    changeLogFile: './db/changelog/db.changelog-master.yaml',
  });

  const liquibase = new Liquibase(config);
  
  // validate checks changeset syntax and structure without applying
  await expect(liquibase.validate()).resolves.not.toThrow();
});

test('liquibase status shows pending changesets', async () => {
  const connectionString = getConnectionString();
  const url = new URL(connectionString);

  const config = new LiquibaseConfig({
    ...POSTGRESQL_DEFAULT_CONFIG,
    url: `jdbc:postgresql://${url.host}${url.pathname}`,
    username: url.username,
    password: url.password,
    changeLogFile: './db/changelog/db.changelog-master.yaml',
  });

  const liquibase = new Liquibase(config);
  const status = await liquibase.status();
  
  // On a fresh database, all changesets should be pending
  expect(status).toContain('changesets have not been applied');
});

For Liquibase, the validate command is your first line of defense — it parses all your changeset files and verifies they're syntactically correct and internally consistent before a single SQL statement executes.

Testing Rollbacks: Down Migrations That Actually Work

This is where most projects fail. Writing a rollback test forces you to maintain down migrations, which is exactly the discipline you need.

// test/migrations/rollback.test.js
const { Client } = require('pg');
const { startPostgres, stopPostgres, getConnectionString } = require('../helpers/postgres');
const { Flyway } = require('node-flyway');

test('migrations can be rolled back cleanly', async () => {
  await startPostgres();
  const connectionString = getConnectionString();
  
  const flyway = new Flyway({
    url: connectionString,
    locations: ['filesystem:./migrations'],
    team: true, // required for undo in Flyway Teams
  });

  // Apply all migrations
  await flyway.migrate();
  
  // Verify table exists after migration
  const client = new Client({ connectionString });
  await client.connect();
  
  const beforeUndo = await client.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_name = 'orders'
    )
  `);
  expect(beforeUndo.rows[0].exists).toBe(true);

  // Roll back the last migration
  await flyway.undo();
  
  // Verify table was removed by rollback
  const afterUndo = await client.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_name = 'orders'
    )
  `);
  expect(afterUndo.rows[0].exists).toBe(false);
  
  await client.end();
  await stopPostgres();
}, 60000);

For teams not using Flyway Teams (which is paid), you can test rollbacks by writing your down migration as a separate script and testing it directly:

test('manual rollback script restores previous schema state', async () => {
  const client = new Client({ connectionString: getConnectionString() });
  await client.connect();

  // Apply migration
  const upSQL = require('fs').readFileSync('./migrations/V3__add_orders_table.sql', 'utf8');
  await client.query(upSQL);

  // Verify it applied
  const tableExists = await client.query(
    "SELECT to_regclass('public.orders') IS NOT NULL AS exists"
  );
  expect(tableExists.rows[0].exists).toBe(true);

  // Apply rollback
  const downSQL = require('fs').readFileSync('./migrations/U3__add_orders_table.sql', 'utf8');
  await client.query(downSQL);

  // Verify rollback worked
  const tableGone = await client.query(
    "SELECT to_regclass('public.orders') IS NOT NULL AS exists"
  );
  expect(tableGone.rows[0].exists).toBe(false);

  await client.end();
});

Testing Data Survival Across Migrations

Some migrations transform existing data — renaming columns, splitting tables, migrating formats. Tests that verify data integrity across these migrations are critical:

test('email migration preserves existing user data', async () => {
  const client = new Client({ connectionString: getConnectionString() });
  await client.connect();

  // Apply migrations up to V4 (before email normalization migration)
  const flyway = new Flyway({
    url: getConnectionString(),
    locations: ['filesystem:./migrations'],
    target: '4',
  });
  await flyway.migrate();

  // Insert test data in old format
  await client.query(`
    INSERT INTO users (email, name) VALUES
    ('  User@Example.COM  ', 'Test User'),
    ('ANOTHER@TEST.ORG', 'Another User')
  `);

  // Apply V5 migration (normalizes emails to lowercase, trimmed)
  const flyway2 = new Flyway({
    url: getConnectionString(),
    locations: ['filesystem:./migrations'],
    target: '5',
  });
  await flyway2.migrate();

  // Verify data was transformed correctly
  const result = await client.query('SELECT email FROM users ORDER BY id');
  expect(result.rows[0].email).toBe('user@example.com');
  expect(result.rows[1].email).toBe('another@test.org');

  await client.end();
});

Schema Snapshot Testing

For catching unexpected changes, schema snapshot tests compare the current schema against a known-good baseline:

test('schema matches approved snapshot', async () => {
  const client = new Client({ connectionString: getConnectionString() });
  await client.connect();

  const result = await client.query(`
    SELECT 
      t.table_name,
      c.column_name,
      c.data_type,
      c.character_maximum_length,
      c.is_nullable,
      c.column_default
    FROM information_schema.tables t
    JOIN information_schema.columns c ON c.table_name = t.table_name
    WHERE t.table_schema = 'public'
      AND t.table_type = 'BASE TABLE'
    ORDER BY t.table_name, c.ordinal_position
  `);

  // Jest snapshot — fails if schema changes unexpectedly
  expect(result.rows).toMatchSnapshot();

  await client.end();
});

The first time this test runs, Jest saves the schema as a snapshot. Every subsequent run compares against it. When you intentionally change the schema, you update the snapshot with jest --updateSnapshot. Unintentional changes fail the test.

CI Integration

Pull this all together in a CI pipeline:

# .github/workflows/test-migrations.yml
name: Migration Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Set up Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Run migration tests
        run: npx jest test/migrations/ --runInBand --forceExit
        env:
          TESTCONTAINERS_RYUK_DISABLED: true

The --runInBand flag runs tests serially, which avoids Docker resource contention when multiple test files each spin up their own containers. For larger test suites, you can run containers in parallel if you ensure each test gets its own isolated container.

Conclusion

Migration testing doesn't require a massive infrastructure investment. With Testcontainers providing isolated PostgreSQL instances and a few hundred lines of Jest tests, you get:

  • Confidence that migrations apply cleanly to a fresh database
  • Verified rollback scripts that actually work under pressure
  • Schema snapshot tests that catch unexpected drift
  • Data integrity verification for transformative migrations

The investment pays off the first time a bad migration is caught before it touches production. At that point, the 2-3 second container startup overhead feels like a bargain.

Read more

Start now free