Database Testing

Flyway Database Migration Testing: Validating Migrations in CI/CD

11 Oct 2026 — 6 min read

Database migrations are a deployment risk. A bad migration can corrupt data, block a release, or cause hours of downtime while you figure out how to roll back. Flyway is the most popular Java database migration tool, but most teams only run migrations — they don't test them.

This guide covers how to validate Flyway migrations before they reach production: unit testing migration logic, integration testing the full migration sequence, rollback testing, and CI/CD pipeline setup.

Why Test Database Migrations

Most teams trust migrations too much. They write a SQL file, run flyway migrate locally, see it succeed, and ship it. What they miss:

Data loss bugs — a DROP COLUMN that removes data still in use
Performance issues — an ALTER TABLE on a 10M-row table that locks for minutes
Version ordering bugs — a migration that fails because it assumes a column exists that hasn't been added yet in production
Rollback failures — discovering you can't roll back a migration after a bad deployment
Cross-database bugs — SQL that works on MySQL 5.7 but fails on 8.0, or local vs. production version differences

Testing migrations catches all of these before production.

Flyway Basics

Flyway manages schema versions using SQL files with a naming convention:

V1__create_users_table.sql
V2__add_email_column.sql
V3__create_orders_table.sql
U3__undo_create_orders_table.sql  # Undo migration (Flyway Teams)
R__refresh_active_users_view.sql  # Repeatable migration

V — versioned migrations, run once in version order
U — undo migrations (reversal of a versioned migration, Teams edition only)
R — repeatable migrations, re-run whenever their checksum changes

Setting Up Flyway for Testing

Maven Configuration

<dependencies>
  <dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>10.10.0</version>
  </dependency>
  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
  </dependency>
</dependencies>

Test Database Configuration

For unit and integration tests, use H2 with MySQL compatibility mode or a real MySQL container:

# src/test/resources/application-test.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=MySQL;DATABASE_TO_LOWER=TRUE
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=none  # Let Flyway own schema creation

Testing Migration Success

The simplest and most important test: verify that all migrations run without errors on a fresh database.

Spring Boot Auto-Migration Test

Spring Boot runs Flyway automatically on startup when spring.flyway.enabled=true. This test verifies the full migration sequence:

@SpringBootTest
@ActiveProfiles("test")
class FlywayMigrationTest {

    @Autowired
    private Flyway flyway;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void allMigrationsShouldRunSuccessfully() {
        MigrationInfo[] migrations = flyway.info().all();
        
        for (MigrationInfo migration : migrations) {
            assertNotEquals(
                MigrationState.FAILED,
                migration.getState(),
                "Migration " + migration.getVersion() + " failed: " + migration.getDescription()
            );
        }
    }

    @Test
    void databaseShouldBeAtLatestVersion() {
        MigrationInfo current = flyway.info().current();
        MigrationInfo[] pending = flyway.info().pending();
        
        assertNotNull(current, "No migrations have been applied");
        assertEquals(0, pending.length, "There are unapplied pending migrations");
    }
}

Testing Schema Structure After Migration

After migrations run, verify the resulting schema:

@SpringBootTest
@ActiveProfiles("test")
class SchemaIntegrityTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void usersTableShouldExistWithRequiredColumns() {
        // This will throw if the table doesn't exist
        jdbcTemplate.execute("SELECT id, email, name, status, created_at FROM users LIMIT 1");
    }

    @Test
    void foreignKeyConstraintsShouldExist() {
        // Test that inserting an order with a non-existent user fails
        assertThrows(DataIntegrityViolationException.class, () -> {
            jdbcTemplate.update(
                "INSERT INTO orders (user_id, total_amount) VALUES (99999, 100.00)"
            );
        });
    }

    @Test
    void indexesShouldExistForPerformance() {
        // Verify critical indexes exist by checking EXPLAIN output
        List<Map<String, Object>> explain = jdbcTemplate.queryForList(
            "EXPLAIN SELECT * FROM users WHERE email = 'test@test.com'"
        );
        
        String key = (String) explain.get(0).get("key");
        assertNotNull(key, "Query on email should use an index");
    }
}

Testing Individual Migrations

For complex migrations, test each migration step in isolation using Flyway's target parameter:

class IncrementalMigrationTest {

    private static DataSource dataSource;
    private Flyway flyway;

    @BeforeAll
    static void setupDataSource() {
        dataSource = new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .build();
    }

    @BeforeEach
    void cleanDatabase() {
        Flyway.configure()
            .dataSource(dataSource)
            .cleanDisabled(false)
            .load()
            .clean();
    }

    @Test
    void v1_shouldCreateUsersTable() {
        flyway = Flyway.configure()
            .dataSource(dataSource)
            .target("1")
            .load();
        flyway.migrate();

        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        // V1 creates users — should succeed
        jdbc.execute("SELECT id FROM users LIMIT 1");
        
        // V2 adds email — should not exist yet
        assertThrows(Exception.class, () ->
            jdbc.execute("SELECT email FROM users LIMIT 1")
        );
    }

    @Test
    void v2_shouldAddEmailColumn() {
        flyway = Flyway.configure()
            .dataSource(dataSource)
            .target("2")
            .load();
        flyway.migrate();

        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        jdbc.execute("SELECT email FROM users LIMIT 1");
    }

    @Test
    void allVersions_shouldMigrateInSequence() {
        flyway = Flyway.configure()
            .dataSource(dataSource)
            .load();
        
        MigrateResult result = flyway.migrate();
        
        assertTrue(result.success);
        assertTrue(result.migrationsExecuted > 0);
    }
}

Data Migration Testing

Migrations that transform existing data are the most dangerous. Test the data transformation logic explicitly:

class DataMigrationTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    private Flyway flyway;

    @Test
    void v5_migrateUserTierToNewFormat() {
        // Setup: insert data in old format (before V5)
        // First, run only up to V4
        Flyway preV5 = Flyway.configure()
            .dataSource(dataSource)
            .target("4")
            .load();
        preV5.migrate();
        
        // Insert data in old format
        jdbcTemplate.update(
            "INSERT INTO users (id, tier_flag) VALUES (1, 1), (2, 0), (3, 1)"
        );
        
        // Now run V5 (the data migration)
        Flyway v5Only = Flyway.configure()
            .dataSource(dataSource)
            .target("5")
            .load();
        v5Only.migrate();
        
        // Verify data was transformed correctly
        // Old: tier_flag=1 → New: tier='PREMIUM', tier_flag=0 → tier='STANDARD'
        List<Map<String, Object>> users = jdbcTemplate.queryForList(
            "SELECT id, tier FROM users ORDER BY id"
        );
        
        assertEquals("PREMIUM", users.get(0).get("tier"));
        assertEquals("STANDARD", users.get(1).get("tier"));
        assertEquals("PREMIUM", users.get(2).get("tier"));
    }
}

Rollback Testing with Undo Migrations

Flyway Teams edition supports undo migrations (U prefix). Test that undo migrations actually work:

@Test
void undoMigrationShouldRestoreSchema() {
    // Apply V3
    Flyway applyFlyway = Flyway.configure()
        .dataSource(dataSource)
        .target("3")
        .load();
    applyFlyway.migrate();
    
    // Verify V3 state
    JdbcTemplate jdbc = new JdbcTemplate(dataSource);
    jdbc.execute("SELECT * FROM orders LIMIT 1");  // V3 created orders table
    
    // Undo V3
    Flyway undoFlyway = Flyway.configure()
        .dataSource(dataSource)
        .load();
    undoFlyway.undo();
    
    // Verify orders table no longer exists
    assertThrows(Exception.class, () ->
        jdbc.execute("SELECT * FROM orders LIMIT 1")
    );
    
    // Verify we're back to V2
    MigrationInfo current = undoFlyway.info().current();
    assertEquals("2", current.getVersion().toString());
}

Manual Rollback Testing

When undo migrations aren't available, test your manual rollback scripts:

@Test
void manualRollbackScriptShouldRestoreDataIntegrity() throws Exception {
    // Apply migration
    jdbcTemplate.execute(new ClassPathResource("db/migration/V5__add_audit_log.sql"));
    
    // Verify post-migration state
    jdbcTemplate.execute("SELECT * FROM audit_log LIMIT 1");
    
    // Apply rollback
    jdbcTemplate.execute(new ClassPathResource("db/rollback/V5__rollback_audit_log.sql"));
    
    // Verify rollback worked
    assertThrows(Exception.class, () ->
        jdbcTemplate.execute("SELECT * FROM audit_log LIMIT 1")
    );
}

CI/CD Pipeline Integration

GitHub Actions with Real MySQL

name: Database Migration Tests

on: [push, pull_request]

jobs:
  migration-tests:
    runs-on: ubuntu-latest
    
    services:
      mysql:
        image: mysql:8.0
        env:
          MYSQL_ROOT_PASSWORD: root
          MYSQL_DATABASE: migration_test
        options: >-
          --health-cmd="mysqladmin ping"
          --health-interval=10s
          --health-timeout=5s
          --health-retries=5
        ports:
          - 3306:3306
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Set up Java
        uses: actions/setup-java@v3
        with:
          java-version: '21'
          distribution: 'temurin'
      
      - name: Run migration tests
        run: mvn test -Dspring.profiles.active=mysql-test
        env:
          SPRING_DATASOURCE_URL: jdbc:mysql://127.0.0.1:3306/migration_test
          SPRING_DATASOURCE_USERNAME: root
          SPRING_DATASOURCE_PASSWORD: root
      
      - name: Validate migration checksums
        run: |
          mvn flyway:validate \
            -Dflyway.url=jdbc:mysql://127.0.0.1:3306/migration_test \
            -Dflyway.user=root \
            -Dflyway.password=root

Flyway Validate in Pipeline

flyway validate catches a critical class of bugs — migration files that were changed after being applied. Include it in every pipeline run:

# In CI, after running migrations:
mvn flyway:validate

# This fails if:
# - A migration file was edited after it ran
# - A migration file is missing
# - Checksums don't match

Pre-Production Migration Dry Run

Before migrating production, run migrations against a production clone:

# 1. Create prod clone
mysqldump -h prod-host -u admin prod_db | mysql -h staging-host test_db

# 2. Run migrations against clone
mvn flyway:migrate \
  -Dflyway.url=jdbc:mysql://staging-host/test_db \
  -Dflyway.user=admin \
  -Dflyway.password=$STAGING_PASSWORD \
  -Dflyway.dryRunOutput=/tmp/migration_dry_run.sql

# 3. Review the dry run SQL
cat /tmp/migration_dry_run.sql

# 4. Check timing on large tables
mysql -h staging-host test_db -e "EXPLAIN ALTER TABLE large_table ADD COLUMN new_col INT"

Common Migration Testing Mistakes

Testing only the happy path. Always test that migrating from a known non-empty state works correctly. Migrations applied to an empty test database may succeed while the same migration fails on a database with years of production data.

Skipping checksum validation. Flyway checksums prevent edited migrations, but only if you run validate. Teams that skip this step discover edited migrations when they cause production failures.

Not testing migration performance. An ALTER TABLE on a 50-row test table takes milliseconds. On a 50M-row production table, it can take hours and lock the table. Use production-scale data snapshots to test migration timing.

Not having rollback plans. Every migration that modifies existing data should have a tested rollback path. If you can't undo a migration, you can't safely deploy it.

Database migrations are code — they deserve the same testing discipline as application code. A migration test suite that covers the full sequence, individual migrations, data transformations, and rollback paths turns migration deployments from a source of anxiety into a routine operation.