Database

Testing Flyway Migrations: Strategies for Safe Database Changes

HelpMeTest

17 May 2026 — 8 min read

Flyway migrations can silently corrupt production databases if shipped without proper testing. This post covers a layered testing strategy: unit tests with H2 for fast feedback, Testcontainers for real-database fidelity, rollback testing, checksum validation, and CI pipeline integration.

Key Takeaways

Test migrations against a real database engine. H2 is fine for unit tests but has SQL compatibility gaps. Always validate critical migrations against the same engine version running in production using Testcontainers.

Rollback testing is not optional. Flyway Community doesn't support undo scripts, but you can simulate rollback by running a separate migration that reverses the change — and your tests should assert both directions.

Checksum validation catches silent drift. If someone edits a migration file after it has been applied, Flyway throws an error. Your CI pipeline should assert this behavior on every pull request.

Why Flyway Migrations Fail in Production

Database migrations are among the highest-risk changes in any software system. Unlike application code, a bad migration can corrupt data, lock tables for minutes, or leave the schema in a state that blocks all subsequent deployments. Flyway is the most widely-used Java migration tool, but running flyway migrate in production without a test strategy is a gamble.

The most common failure modes are:

SQL dialect mismatch: A migration written and tested against H2 uses syntax that PostgreSQL or MySQL rejects.
Data-dependent failures: A NOT NULL column addition works on an empty test database but fails in production where existing rows need default values.
Checksum drift: A developer edits a migration file after it has been applied to staging, causing the next deployment to halt with a checksum mismatch.
Migration ordering bugs: Two branches add migrations with the same version prefix, causing a conflict on merge.

A proper testing strategy catches all of these before they reach production.

Flyway Concepts You Need to Know

Flyway identifies migrations by a naming convention: V{version}__{description}.sql. The version must be unique and sortable — Flyway applies migrations in ascending version order. Each applied migration has its checksum stored in the flyway_schema_history table. If the file changes after application, the next flyway migrate call fails.

Flyway also supports repeatable migrations (prefix R__) for views, stored procedures, and other objects that should be re-applied whenever they change.

db/migration/
  V1__create_users_table.sql
  V2__add_email_index.sql
  V3__create_orders_table.sql
  R__create_reporting_view.sql

Unit Testing with H2

For fast, in-process migration tests, H2's in-memory mode is the standard choice. Add the dependencies:

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

A minimal JUnit 5 test that runs all migrations and validates the resulting schema:

@Test
void migrationsApplyCleanly() {
    DataSource dataSource = new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2)
        .build();

    Flyway flyway = Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration")
        .load();

    MigrateResult result = flyway.migrate();

    assertThat(result.success).isTrue();
    assertThat(result.migrationsExecuted).isGreaterThan(0);
}

To verify that the schema is what you expect after migration, use JDBC metadata:

@Test
void usersTableHasExpectedColumns() throws SQLException {
    // Apply migrations first (reuse the setup above)
    try (Connection conn = dataSource.getConnection()) {
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet columns = meta.getColumns(null, null, "USERS", null);

        Set<String> columnNames = new HashSet<>();
        while (columns.next()) {
            columnNames.add(columns.getString("COLUMN_NAME").toLowerCase());
        }

        assertThat(columnNames).contains("id", "email", "created_at", "updated_at");
    }
}

H2 Compatibility Mode

H2 supports a MySQL or PostgreSQL compatibility mode that reduces dialect mismatches:

DataSource dataSource = new SimpleDriverDataSource(
    new org.h2.Driver(),
    "jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH",
    "sa", ""
);

Even with compatibility mode, some PostgreSQL features (like JSONB, UUID primary keys with gen_random_uuid(), or partial indexes) will fail in H2. Use Testcontainers for those.

Real-Database Testing with Testcontainers

Testcontainers spins up a real Docker container for your database engine during tests. This eliminates the dialect gap entirely.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.19.7</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <version>1.19.7</version>
    <scope>test</scope>
</dependency>

@Testcontainers
class FlywayPostgresIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine")
        .withDatabaseName("testdb")
        .withUsername("testuser")
        .withPassword("testpass");

    private DataSource dataSource;
    private Flyway flyway;

    @BeforeEach
    void setUp() {
        dataSource = new DriverManagerDataSource(
            postgres.getJdbcUrl(),
            postgres.getUsername(),
            postgres.getPassword()
        );

        flyway = Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration")
            .cleanDisabled(false)
            .load();

        flyway.clean(); // Start fresh for each test
        flyway.migrate();
    }

    @Test
    void migrationsApplyOnRealPostgres() {
        MigrateResult result = flyway.info().applied().length > 0
            ? flyway.info()
            : flyway.migrate();

        ValidateResult validation = flyway.validateWithResult();
        assertThat(validation.validationSuccessful).isTrue();
    }

    @Test
    void ordersTableHasForeignKeyToUsers() throws SQLException {
        try (Connection conn = dataSource.getConnection();
             ResultSet rs = conn.getMetaData().getImportedKeys(null, "public", "orders")) {

            boolean hasFk = false;
            while (rs.next()) {
                if ("users".equals(rs.getString("PKTABLE_NAME"))) {
                    hasFk = true;
                    break;
                }
            }
            assertThat(hasFk).isTrue();
        }
    }
}

For Spring Boot applications, @DataJpaTest with replace = AutoConfigureTestDatabase.Replace.NONE plus a Testcontainers-provided datasource is the cleanest pattern:

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class RepositoryMigrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @DynamicPropertySource
    static void registerProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
        registry.add("spring.flyway.enabled", () -> "true");
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void canPersistUserAfterMigration() {
        User user = new User();
        user.setEmail("test@example.com");
        User saved = userRepository.save(user);
        assertThat(saved.getId()).isNotNull();
    }
}

Rollback Testing

Flyway Community Edition does not support undo scripts (U{version}__...sql). That's a paid feature in Flyway Teams. However, you can implement rollback testing with explicit down-migration scripts:

-- V3__add_subscription_tier.sql (forward migration)
ALTER TABLE users ADD COLUMN subscription_tier VARCHAR(20) NOT NULL DEFAULT 'free';
CREATE INDEX idx_users_subscription_tier ON users(subscription_tier);

-- V3_1__rollback_subscription_tier.sql (manual rollback migration)
DROP INDEX IF EXISTS idx_users_subscription_tier;
ALTER TABLE users DROP COLUMN IF EXISTS subscription_tier;

Test both directions:

@Test
void rollbackMigrationRestoresOriginalSchema() throws SQLException {
    // Apply through V3
    flyway.migrate();
    assertColumnExists(dataSource, "users", "subscription_tier");

    // Apply rollback V3_1
    // (In practice, deploy rollback migration, test, then clean up)
    assertColumnExists(dataSource, "users", "subscription_tier"); // still there

    // Verify the rollback script removes the column
    try (Connection conn = dataSource.getConnection();
         Statement stmt = conn.createStatement()) {
        stmt.execute("ALTER TABLE users DROP COLUMN IF EXISTS subscription_tier");
    }

    assertColumnNotExists(dataSource, "users", "subscription_tier");
}

private void assertColumnExists(DataSource ds, String table, String column) throws SQLException {
    try (Connection conn = ds.getConnection()) {
        ResultSet rs = conn.getMetaData().getColumns(null, null, table.toUpperCase(), column.toUpperCase());
        assertThat(rs.next()).isTrue();
    }
}

For teams using PostgreSQL, you can also wrap migrations in explicit transactions and test that a failure rolls back cleanly:

@Test
void failedMigrationDoesNotPartiallyApplyChanges() {
    // Inject a deliberately broken migration
    Flyway flywayWithBrokenMigration = Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration", "classpath:db/broken")
        .cleanDisabled(false)
        .load();

    assertThatThrownBy(flywayWithBrokenMigration::migrate)
        .isInstanceOf(FlywayException.class);

    // Schema should be at the last successful version, not partially migrated
    MigrationInfo[] applied = flyway.info().applied();
    assertThat(Arrays.stream(applied).noneMatch(m -> m.getVersion().toString().startsWith("999")))
        .isTrue();
}

Checksum Validation Testing

Flyway's checksum mechanism is your guard against edited migration files. Your test suite should assert that the validation command passes on a clean run and fails when a file is altered:

@Test
void checksumValidationPassesForUnmodifiedMigrations() {
    flyway.migrate();

    ValidateResult result = flyway.validateWithResult();
    assertThat(result.validationSuccessful)
        .withFailMessage("Checksum validation failed: %s", result.getAllErrorMessages())
        .isTrue();
}

In CI, run Flyway's validate command as a dedicated step:

flyway -url=jdbc:postgresql://localhost:5432/testdb \
       -user=postgres \
       -password=secret \
       -locations=filesystem:db/migration \
       validate

If a developer has altered a migration file, the validate command will exit non-zero and fail the pipeline before any code reaches staging.

Migration Version Conflict Detection

When two feature branches both add a V4__...sql migration, merging them causes a conflict that Flyway will refuse to apply. Detect this in CI before merge:

#!/bin/bash
<span class="hljs-comment"># check-migration-conflicts.sh
MIGRATION_DIR=<span class="hljs-string">"src/main/resources/db/migration"

<span class="hljs-comment"># Find duplicate version numbers
duplicates=$(<span class="hljs-built_in">ls <span class="hljs-string">"$MIGRATION_DIR"/V*.sql 2>/dev/null \
    <span class="hljs-pipe">| sed <span class="hljs-string">'s/.*\/V\([0-9_]*\)__.*/\1/' \
    <span class="hljs-pipe">| <span class="hljs-built_in">sort <span class="hljs-pipe">| <span class="hljs-built_in">uniq -d)

<span class="hljs-keyword">if [ -n <span class="hljs-string">"$duplicates" ]; <span class="hljs-keyword">then
    <span class="hljs-built_in">echo <span class="hljs-string">"ERROR: Duplicate migration versions found: $duplicates"
    <span class="hljs-built_in">exit 1
<span class="hljs-keyword">fi

<span class="hljs-built_in">echo <span class="hljs-string">"No migration version conflicts detected."

Add this as a pre-commit hook or a CI step that runs on every pull request.

CI Integration

Here is a complete GitHub Actions workflow that runs Flyway migration tests on every push:

name: Database Migration Tests

on:
  push:
    paths:
      - 'src/main/resources/db/migration/**'
      - 'src/test/**'
  pull_request:

jobs:
  migration-tests:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Set up JDK 21
        uses: actions/setup-java@v4
        with:
          java-version: '21'
          distribution: 'temurin'

      - name: Check for migration version conflicts
        run: bash scripts/check-migration-conflicts.sh

      - name: Run migration tests
        run: ./mvnw test -Dtest=Flyway*Test,*MigrationTest -pl :your-module
        env:
          SPRING_DATASOURCE_URL: jdbc:postgresql://localhost:5432/testdb
          SPRING_DATASOURCE_USERNAME: testuser
          SPRING_DATASOURCE_PASSWORD: testpass

      - name: Validate checksums
        run: |
          mvn flyway:validate \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

Testing Data Migrations

Structural migrations (ALTER TABLE, CREATE INDEX) are only half the story. Data migrations — backfilling columns, transforming values, splitting tables — need their own test assertions.

-- V5__backfill_user_display_names.sql
UPDATE users
SET display_name = CONCAT(first_name, ' ', last_name)
WHERE display_name IS NULL
  AND first_name IS NOT NULL;

@Test
void backfillMigrationSetsDisplayNameForExistingUsers() throws SQLException {
    // Arrange: insert test data at V4 state
    flyway.migrate(); // applies through V4

    try (Connection conn = dataSource.getConnection();
         Statement stmt = conn.createStatement()) {
        stmt.execute("""
            INSERT INTO users (id, first_name, last_name, email, created_at)
            VALUES (1, 'Alice', 'Smith', 'alice@example.com', NOW())
        """);
    }

    // Act: apply V5 backfill
    // (In a real test you'd target a specific migration range)
    // For simplicity, assume V5 is the next unapplied migration

    // Assert: verify backfill ran correctly
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(
             "SELECT display_name FROM users WHERE id = 1")) {
        ResultSet rs = ps.executeQuery();
        assertThat(rs.next()).isTrue();
        assertThat(rs.getString("display_name")).isEqualTo("Alice Smith");
    }
}

Performance Testing for Long-Running Migrations

Some migrations lock tables. A migration that rewrites a 50M-row table will freeze production for minutes. Measure migration duration in your test environment:

@Test
void migrationCompletesWithinAcceptableTime() {
    long start = System.currentTimeMillis();
    flyway.migrate();
    long duration = System.currentTimeMillis() - start;

    // Fail fast if a migration takes more than 30 seconds in the test environment
    assertThat(duration)
        .withFailMessage("Migration took %dms — may be too slow for production", duration)
        .isLessThan(30_000);
}

For production-scale testing, populate the database with realistic data volumes before running the migration:

-- seed-large-dataset.sql (applied before the migration under test)
INSERT INTO orders (user_id, amount, status, created_at)
SELECT
    (random() * 10000)::int + 1,
    (random() * 1000)::numeric(10,2),
    CASE WHEN random() > 0.5 THEN 'completed' ELSE 'pending' END,
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

Putting It All Together

A mature Flyway testing strategy has four layers:

Layer	Tool	When	What it catches
Version conflict check	Shell script	Pre-commit / PR	Duplicate version numbers
Unit migration tests	JUnit 5 + H2	Every build	Schema structure, basic SQL errors
Integration tests	Testcontainers + PostgreSQL	Every build	Dialect issues, FK constraints, real indexes
Checksum validation	`flyway validate` in CI	Every build	Edited migration files

Each layer is fast enough to run on every pull request. The integration tests are the slowest — typically 30–90 seconds for a Testcontainers startup — but they're the only tests that give you confidence the migration will work against the exact same engine version running in production.

Don't rely on any single layer. H2 tests catch the easy bugs quickly. Testcontainers tests catch the subtle dialect and constraint issues. Checksum validation is your safety net against accidental edits. Together, they make database deployments boring in the best possible way.

HelpMeTest can run your migration tests automatically on every pull request, alerting you before broken migrations reach production — sign up free