Testing Database Migrations with Flyway and Testcontainers

Database migrations are executable changes to the structure and data of an application’s persistence layer. They create tables, add indexes, change constraints, and sometimes transform existing records. Because a migration can succeed in development while failing against a real database engine, it deserves the same disciplined testing as application code.

Flyway provides a controlled way to version and apply those changes. Testcontainers starts disposable database instances in Docker, allowing tests to run against PostgreSQL, MySQL, MariaDB, or another supported engine rather than an in-memory substitute. Together, the tools make migration tests repeatable and close to production behaviour.

This approach is useful for Java and Groovy services, REST backends, and systems with asynchronous workers that depend on a shared schema. It also suits Australian delivery teams working across Sydney, Melbourne, Brisbane, or Perth, where a reliable pipeline can catch database problems before a release moves between time zones or reaches a customer.

Why migration tests need a real database

A migration often depends on vendor-specific behaviour. PostgreSQL sequences, JSONB fields, partial indexes, locking rules, and transactional DDL do not behave exactly like H2. A test that passes against H2 may fail when the application is deployed to Amazon RDS in the Australian region or to a managed database used by a local financial services company.

Migration tests also expose ordering errors. A later script might reference a column that an earlier script never creates, or a foreign key may be added before existing data has been cleaned up. Flyway records applied versions in its schema history table, so the test can verify that a complete migration chain works from an empty database.

A disposable container provides isolation between test runs. Rather than sharing a developer’s database in Sydney or relying on a long-lived CI instance, each test suite starts with known state, applies all migrations, and removes the database afterwards. Failures become easier to reproduce because the database engine and migration inputs are explicit.

Designing a useful Flyway test fixture

The most valuable baseline test starts with an empty database and runs flyway.migrate(). It should use the same migration locations, schemas, placeholders, and naming conventions as the application. A typical Java setup creates a Flyway instance from the container’s JDBC URL:

Flyway flyway = Flyway.configure()
    .dataSource(container.getJdbcUrl(), container.getUsername(),
                container.getPassword())
    .locations("classpath:db/migration")
    .load();

flyway.migrate();

The test should then inspect the resulting schema through JDBC or a library such as jOOQ, and execute representative SQL. Checking that a table exists is useful, but inserting a realistic record and reading it back verifies column types, defaults, constraints, and generated keys together.

A migration test can also assert Flyway’s metadata. The expected number of migrations, version sequence, and successful state help identify missing files or an accidental naming change. Keep these assertions focused on contract and behaviour rather than internal implementation details, so a harmless index rename does not create unnecessary maintenance.

Building containers that match production

Use the same database family and, where practical, the same major version as production. A PostgreSQL 16 container is a more meaningful test target for a PostgreSQL 16 service than an embedded database. Pinning the image version avoids a surprise caused by a new image being pulled during a build.

With JUnit 5 and Testcontainers, a shared container can be declared as a class field:

@Testcontainers
class MigrationTest {
    @Container
    static PostgreSQLContainer<?> database =
        new PostgreSQLContainer<>("postgres:16-alpine");
}

The container waits until the database is ready, but the application’s own connection pool may still need correct configuration. Keep credentials and database names supplied by the container rather than hard-coding local values. If the test uses Spring Boot, dynamic properties can point spring.datasource.url, username, and password to the running instance.

Reusable containers can reduce build time on a laptop, but they weaken isolation and can hide assumptions about initial state. For migration verification, a fresh container is usually the safer default. Testcontainers’ Ryuk cleanup process and the CI runner’s Docker configuration should also be checked when builds run in hosted environments.

Testing upgrades and existing data

A clean migration test proves that a new environment can be created. It does not prove that an upgrade works against data produced by earlier versions. Upgrade tests should create a database at a known historical version, insert representative records, apply the newer migrations, and verify that the data remains valid.

One practical pattern is to migrate to version V5, load fixture data, then call migrate() again after the test class has access to the later migration files. The assertions should cover renamed fields, backfilled values, preserved relationships, and constraints introduced during the upgrade. Include edge cases such as null values, duplicate records, long strings, and timestamps around daylight saving changes.

For an Australian application, time-related data deserves special care. Sydney and Melbourne observe daylight saving while Brisbane does not, so a data transformation involving local timestamps can produce different results from a UTC-based test. Store instants consistently and use migration fixtures that exercise the date and time rules the application actually supports.

When a migration is intentionally destructive, make that decision visible in the test. Assert that obsolete data is archived or removed according to the release contract. A test that merely checks that the script completes may miss silent loss caused by an overly broad UPDATE or DELETE.

Verifying failure, rollback, and repeatability

A good migration suite tests more than the happy path. Run Flyway validation after migration to detect altered checksums, missing scripts, or changes to files that have already been applied. In a controlled test, deliberately introduce an invalid migration and verify that the failure is reported clearly and the schema is not treated as complete.

Rollback expectations must reflect the database engine. Flyway does not automatically provide a universal undo strategy for every migration, and some DDL operations cannot be rolled back in the way developers expect. If the team uses undo migrations, test them separately. Otherwise, document the forward-fix procedure and verify that a failed deployment leaves enough information for recovery.

Repeatability matters as well. A test should be able to run twice without relying on state left by the first execution. Repeatable migrations need particular attention: changes to their checksum cause them to run again, so assertions should confirm that rerunning them does not duplicate rows or recreate incompatible objects.

Migration tests should run before integration tests that exercise REST endpoints or message consumers. When a backend processes queued work asynchronously, the database schema must be ready before workers start. Teams that need help reviewing a difficult migration or test setup can use the Test Detective contact page as a relevant point of reference.

Fitting migration checks into continuous delivery

Run the migration suite in pull-request builds and again in the release pipeline. A developer working from Melbourne may catch a broken schema before a team in Perth begins its day, while a Sydney deployment pipeline can reject the same change before it reaches production. Fast feedback is particularly important when several services share a database or release on different schedules.

Separate lightweight checks from heavier upgrade scenarios where necessary. A clean-schema test can run on every commit, while a matrix of PostgreSQL versions or a long sequence of historical upgrades can run nightly. The slower tests still need clear ownership because postponing them indefinitely turns a known risk into an operational incident.

Container startup is often the largest cost. Pull images in CI ahead of the test phase, use sensible database settings, and avoid starting one container per individual test method. Test classes can share a container while resetting schemas or using transaction boundaries where that does not conceal migration behaviour.

The comparison below shows where each kind of check contributes. No single test replaces the others; together they cover creation, upgrade, application integration, and operational confidence.

Check	Database target	Main risk covered	Typical frequency
Clean migration	Fresh Testcontainers instance	Broken ordering, invalid DDL, missing dependencies	Every pull request
Flyway validation	Disposable or CI database	Changed checksums and missing versions	Every pull request
Upgrade migration	Database seeded at an older version	Data loss and incompatible transformations	Pull request or nightly
Application integration	Migrated container with the service	Incorrect mappings, constraints, and queries	Every pull request
Deployment rehearsal	Production-like staging database	Permissions, locks, duration, and configuration	Before release

Reliable migration testing establishes a simple chain of evidence: the schema can be created from nothing, existing data can move safely between versions, and the application can use the result with the real database engine. Flyway supplies version control and ordering; Testcontainers supplies isolation and realism. The detail worth remembering is that a migration is production code, and it should be tested as a change to live data rather than treated as a SQL file that merely needs to execute.