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: trueThe --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.