Database Migration Testing: Flyway and Liquibase Guide

Database migrations are critical operations that can make or break production deployments. As discussed in API Testing Architecture: From Monoliths to Microservices, proper database testing is essential for maintaining system reliability. This comprehensive guide covers testing strategies for database migrations using Flyway and Liquibase, ensuring safe schema changes and zero-downtime deployments.

Understanding Database Migration Challenges

Database migrations present unique testing challenges:

Irreversibility: Many schema changes cannot be easily undone
Data preservation: Existing data must remain intact and accessible
Zero-downtime requirements: Production systems often cannot afford maintenance windows
Version compatibility: Application code must work with multiple schema versions
Performance impact: Large migrations can lock tables and degrade performance (learn more about testing performance in Database Performance Testing)

Flyway Setup and Configuration

Flyway uses SQL-based migrations with version control:

# Install Flyway CLI
brew install flyway

# Initialize Flyway project
flyway init

# Create migration directory structure
mkdir -p db/migration

Flyway Configuration (flyway.conf):

flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=dbuser
flyway.password=dbpassword
flyway.schemas=public
flyway.locations=filesystem:db/migration
flyway.validateOnMigrate=true
flyway.outOfOrder=false
flyway.baselineOnMigrate=true

Example Migration (V1__create_users_table.sql):

-- V1__create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

Migration Testing with Flyway:

// flyway-test.js
const { Client } = require('pg');
const { execSync } = require('child_process');

describe('Flyway Migrations', () => {
  let client;

  beforeAll(async () => {
    // Clean database
    execSync('flyway clean -configFiles=flyway-test.conf');

    client = new Client({
      host: 'localhost',
      port: 5432,
      database: 'test_db',
      user: 'test_user',
      password: 'test_password'
    });

    await client.connect();
  });

  test('should apply migrations successfully', () => {
    const result = execSync('flyway migrate -configFiles=flyway-test.conf');
    expect(result.toString()).toContain('Successfully applied');
  });

  test('should create users table with correct schema', 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;

    expect(columns).toContainEqual({
      column_name: 'id',
      data_type: 'integer',
      is_nullable: 'NO'
    });

    expect(columns).toContainEqual({
      column_name: 'email',
      data_type: 'character varying',
      is_nullable: 'NO'
    });
  });

  test('should have correct indexes', 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('idx_users_email');
    expect(indexNames).toContain('idx_users_username');
  });

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

Liquibase Setup and Configuration

Liquibase uses XML, YAML, JSON, or SQL changesets:

<!-- liquibase.properties -->
changeLogFile=db/changelog/db.changelog-master.xml
url=jdbc:postgresql://localhost:5432/mydb
username=dbuser
password=dbpassword
driver=org.postgresql.Driver

Example Changelog (db.changelog-master.xml):

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">

  <changeSet id="1" author="developer">
    <createTable tableName="products">
      <column name="id" type="SERIAL">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="name" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="price" type="DECIMAL(10,2)">
        <constraints nullable="false"/>
      </column>
      <column name="stock" type="INTEGER" defaultValue="0"/>
    </createTable>
  </changeSet>

  <changeSet id="2" author="developer">
    <createIndex tableName="products" indexName="idx_products_name">
      <column name="name"/>
    </createIndex>
  </changeSet>

  <changeSet id="3" author="developer">
    <addColumn tableName="products">
      <column name="category" type="VARCHAR(100)"/>
    </addColumn>
  </changeSet>

</databaseChangeLog>

Liquibase Testing:

// liquibase-test.js
const { execSync } = require('child_process');
const { Client } = require('pg');

describe('Liquibase Migrations', () => {
  let client;

  beforeAll(async () => {
    client = new Client({
      connectionString: 'postgresql://test_user:test_password@localhost:5432/test_db'
    });

    await client.connect();

    // Clear database
    execSync('liquibase dropAll --defaultsFile=liquibase-test.properties');
  });

  test('should apply all changesets', () => {
    const result = execSync('liquibase update --defaultsFile=liquibase-test.properties');
    expect(result.toString()).toContain('successfully');
  });

  test('should track changeset execution', async () => {
    const result = await client.query('SELECT * FROM databasechangelog');

    expect(result.rows.length).toBeGreaterThan(0);
    expect(result.rows[0]).toHaveProperty('id');
    expect(result.rows[0]).toHaveProperty('author');
    expect(result.rows[0]).toHaveProperty('filename');
  });

  test('should create products table with all columns', async () => {
    const result = await client.query(`
      SELECT column_name
      FROM information_schema.columns
      WHERE table_name = 'products'
    `);

    const columnNames = result.rows.map(r => r.column_name);

    expect(columnNames).toContain('id');
    expect(columnNames).toContain('name');
    expect(columnNames).toContain('price');
    expect(columnNames).toContain('stock');
    expect(columnNames).toContain('category');
  });

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

Rollback Testing Strategies

Testing rollback capabilities is crucial for production safety:

Flyway Rollback Testing

Flyway doesn’t support automatic rollbacks, but you can create undo migrations:

-- V2__add_status_column.sql
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- U2__remove_status_column.sql (undo migration)
ALTER TABLE users DROP COLUMN status;

Testing Rollback:

describe('Flyway Rollback', () => {
  test('should rollback migration successfully', async () => {
    // Apply migration
    execSync('flyway migrate -configFiles=flyway-test.conf');

    // Verify column exists
    let result = await client.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'status'
    `);
    expect(result.rows.length).toBe(1);

    // Apply undo migration
    execSync('flyway undo -configFiles=flyway-test.conf');

    // Verify column removed
    result = await client.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'status'
    `);
    expect(result.rows.length).toBe(0);
  });
});

Liquibase Rollback Testing

Liquibase has built-in rollback support:

<changeSet id="4" author="developer">
  <addColumn tableName="products">
    <column name="description" type="TEXT"/>
  </addColumn>
  <rollback>
    <dropColumn tableName="products" columnName="description"/>
  </rollback>
</changeSet>

Testing Liquibase Rollback:

describe('Liquibase Rollback', () => {
  test('should rollback to previous version', async () => {
    // Get current version
    const versionBefore = await client.query(
      'SELECT COUNT(*) FROM databasechangelog'
    );

    // Apply new changeset
    execSync('liquibase update --defaultsFile=liquibase-test.properties');

    // Rollback one changeset
    execSync('liquibase rollbackCount 1 --defaultsFile=liquibase-test.properties');

    // Verify rollback
    const versionAfter = await client.query(
      'SELECT COUNT(*) FROM databasechangelog'
    );

    expect(versionAfter.rows[0].count).toBe(versionBefore.rows[0].count);
  });

  test('should rollback by tag', async () => {
    // Tag current state
    execSync('liquibase tag version-1.0 --defaultsFile=liquibase-test.properties');

    // Apply more changes
    execSync('liquibase update --defaultsFile=liquibase-test.properties');

    // Rollback to tag
    execSync('liquibase rollback version-1.0 --defaultsFile=liquibase-test.properties');

    // Verify state matches tag
    const result = await client.query(
      "SELECT tag FROM databasechangelog WHERE tag = 'version-1.0'"
    );

    expect(result.rows.length).toBeGreaterThan(0);
  });
});

Data Integrity Validation

Ensure migrations preserve and correctly transform data. For deeper insights into comprehensive database testing strategies, refer to our Database Testing Deep Dive article:

// data-integrity-test.js
describe('Data Integrity During Migration', () => {
  beforeEach(async () => {
    // Insert test data
    await client.query(`
      INSERT INTO users (email, username)
      VALUES
        ('user1@example.com', 'user1'),
        ('user2@example.com', 'user2'),
        ('user3@example.com', 'user3')
    `);
  });

  test('should preserve existing data during migration', async () => {
    const countBefore = await client.query('SELECT COUNT(*) FROM users');

    // Apply migration that adds column
    execSync('flyway migrate -configFiles=flyway-test.conf');

    const countAfter = await client.query('SELECT COUNT(*) FROM users');

    expect(countAfter.rows[0].count).toBe(countBefore.rows[0].count);
  });

  test('should correctly transform data', async () => {
    // Migration: V3__split_username.sql
    // Splits 'username' into 'first_name' and 'last_name'

    await client.query(`
      UPDATE users SET username = 'John Doe' WHERE email = 'user1@example.com'
    `);

    execSync('flyway migrate -target=3 -configFiles=flyway-test.conf');

    const result = await client.query(`
      SELECT first_name, last_name
      FROM users
      WHERE email = 'user1@example.com'
    `);

    expect(result.rows[0].first_name).toBe('John');
    expect(result.rows[0].last_name).toBe('Doe');
  });

  test('should handle null values correctly', async () => {
    await client.query(`
      INSERT INTO users (email, username) VALUES ('null@example.com', NULL)
    `);

    execSync('flyway migrate -configFiles=flyway-test.conf');

    const result = await client.query(`
      SELECT * FROM users WHERE email = 'null@example.com'
    `);

    expect(result.rows.length).toBe(1);
    expect(result.rows[0].username).toBeNull();
  });

  afterEach(async () => {
    await client.query('TRUNCATE users CASCADE');
  });
});

Testing Migrations in CI/CD

Integrate migration testing into your CI/CD pipeline. For comprehensive guidance on setting up CI/CD for testing, see our guide on CI/CD Pipeline for Testers:

# .github/workflows/db-migration-test.yml
name: Database Migration Tests

on:
  pull_request:
    paths:
      - 'db/migration/**'
      - 'db/changelog/**'

jobs:
  test-migrations:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:14
        env:
          POSTGRES_DB: test_db
          POSTGRES_USER: test_user
          POSTGRES_PASSWORD: test_password
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v3

      - name: Setup Node.js
        uses: actions/setup-node@v3
        with:
          node-version: '18'

      - name: Install Flyway
        run: |
          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.16.0/flyway-commandline-9.16.0-linux-x64.tar.gz | tar xvz
          sudo ln -s `pwd`/flyway-9.16.0/flyway /usr/local/bin

      - name: Run migration tests
        run: |
          npm install
          npm run test:migrations
        env:
          DB_HOST: localhost
          DB_PORT: 5432
          DB_NAME: test_db
          DB_USER: test_user
          DB_PASSWORD: test_password

      - name: Test rollback
        run: npm run test:rollback

      - name: Validate migration checksums
        run: flyway validate -configFiles=flyway-test.conf

Zero-Downtime Deployment Patterns

Expand-Contract Pattern

// Phase 1: Expand - Add new column (backwards compatible)
// V4__add_email_verified_column.sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

// Phase 2: Migrate data
// V5__migrate_email_verification.sql
UPDATE users
SET email_verified = (
  SELECT EXISTS(
    SELECT 1 FROM email_verifications
    WHERE email_verifications.user_id = users.id
  )
);

// Phase 3: Contract - Remove old table (after deploy)
// V6__remove_email_verifications_table.sql
DROP TABLE email_verifications;

Testing Zero-Downtime Migration:

describe('Zero-Downtime Migration', () => {
  test('application works during expand phase', async () => {
    // Apply expand migration
    execSync('flyway migrate -target=4 -configFiles=flyway-test.conf');

    // Simulate old application code
    await client.query(`
      INSERT INTO users (email, username)
      VALUES ('test@example.com', 'testuser')
    `);

    // Verify both old and new schema work
    const result = await client.query('SELECT * FROM users WHERE email = $1', [
      'test@example.com'
    ]);

    expect(result.rows[0]).toHaveProperty('email');
    expect(result.rows[0]).toHaveProperty('email_verified');
  });

  test('data migration preserves integrity', async () => {
    // Insert test data
    await client.query(`
      INSERT INTO users (email, username) VALUES ('user@example.com', 'user')
    `);
    await client.query(`
      INSERT INTO email_verifications (user_id) VALUES (1)
    `);

    // Apply data migration
    execSync('flyway migrate -target=5 -configFiles=flyway-test.conf');

    const result = await client.query('SELECT email_verified FROM users WHERE id = 1');

    expect(result.rows[0].email_verified).toBe(true);
  });
});

Blue-Green Database Migrations

// blue-green-migration-test.js
describe('Blue-Green Migration', () => {
  let blueClient, greenClient;

  beforeAll(async () => {
    // Blue database (production)
    blueClient = new Client({
      connectionString: 'postgresql://user:pass@localhost:5432/blue_db'
    });

    // Green database (new version)
    greenClient = new Client({
      connectionString: 'postgresql://user:pass@localhost:5433/green_db'
    });

    await blueClient.connect();
    await greenClient.connect();
  });

  test('should replicate data to green database', async () => {
    // Get data from blue
    const blueData = await blueClient.query('SELECT COUNT(*) FROM users');

    // Apply migrations to green
    execSync('flyway migrate -url=jdbc:postgresql://localhost:5433/green_db');

    // Replicate data
    await replicateData(blueClient, greenClient);

    // Verify data in green
    const greenData = await greenClient.query('SELECT COUNT(*) FROM users');

    expect(greenData.rows[0].count).toBe(blueData.rows[0].count);
  });

  test('should handle schema differences', async () => {
    // Green has new schema
    const greenColumns = await greenClient.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users'
    `);

    const blueColumns = await blueClient.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users'
    `);

    expect(greenColumns.rows.length).toBeGreaterThanOrEqual(
      blueColumns.rows.length
    );
  });

  afterAll(async () => {
    await blueClient.end();
    await greenClient.end();
  });
});

Testing with Multiple Database Versions

// multi-version-test.js
const databaseVersions = ['postgres:12', 'postgres:13', 'postgres:14', 'postgres:15'];

describe.each(databaseVersions)('Migrations on %s', (dbVersion) => {
  let client;
  let containerId;

  beforeAll(async () => {
    // Start database container
    const result = execSync(
      `docker run -d -e POSTGRES_PASSWORD=test ${dbVersion}`
    ).toString().trim();

    containerId = result;

    // Wait for database to be ready
    await new Promise(resolve => setTimeout(resolve, 5000));

    const port = execSync(
      `docker port ${containerId} 5432`
    ).toString().split(':')[1].trim();

    client = new Client({
      host: 'localhost',
      port: parseInt(port),
      database: 'postgres',
      user: 'postgres',
      password: 'test'
    });

    await client.connect();
  });

  test('migrations should work', async () => {
    const tempConfig = `
      flyway.url=jdbc:postgresql://localhost:${client.port}/postgres
      flyway.user=postgres
      flyway.password=test
    `;

    require('fs').writeFileSync('flyway-temp.conf', tempConfig);

    const result = execSync('flyway migrate -configFiles=flyway-temp.conf');

    expect(result.toString()).toContain('Successfully applied');
  });

  afterAll(async () => {
    await client.end();
    execSync(`docker stop ${containerId}`);
    execSync(`docker rm ${containerId}`);
  });
});

Migration Testing Best Practices

Testing Checklist

Test migrations on clean database
Test migrations on database with existing data
Verify all indexes are created correctly
Test foreign key constraints
Validate default values and constraints
Test rollback procedures
Verify data integrity after migration
Test performance of large data migrations
Check for locking issues on large tables
Test migrations on multiple database versions
Validate migration checksums
Test concurrent migration scenarios

Migration Testing Comparison

Aspect	Flyway	Liquibase
Rollback Support	Manual undo migrations	Built-in rollback
Format	SQL files	XML, YAML, JSON, SQL
Learning Curve	Low (familiar SQL)	Medium (XML syntax)
Version Control	File-based versioning	Changeset IDs
Conditional Logic	Limited	Extensive (preconditions)
Database Support	Wide	Wide
Diff Generation	Commercial only	Built-in

Conclusion

Effective database migration testing requires comprehensive strategies covering schema validation, data integrity, rollback procedures, and zero-downtime deployment patterns. By implementing thorough testing with Flyway or Liquibase, integrating tests into CI/CD pipelines, and following best practices, you can ensure safe and reliable database changes.

Key takeaways:

Always test migrations on a clean database and with existing data
Implement and test rollback procedures for every migration
Use expand-contract pattern for zero-downtime deployments
Validate data integrity before and after migrations
Test across multiple database versions
Automate migration testing in CI/CD pipelines

Robust migration testing builds confidence in database changes and prevents costly production incidents.