Java.DBMigrationTools.How can you test migrations automatically?

Automatic migration testing = spin up a real DB in CI, apply migrations, and assert schema + data + invariants. Anything less is false confidence.

What to test (in order of value)

1) “Migrations apply cleanly from empty DB”

Create fresh DB
Run migrate
Expect success (no errors, no manual steps)

2) Validation / immutability

Ensure no already-applied migration was edited
Flyway: validate
Liquibase: checksum validation (default behavior)

3) Schema assertions

Tables/columns/constraints/indexes exist
Types match what you expect
Foreign keys + unique constraints enforced

4) Data migrations correctness (if you do them)

Insert sample legacy data
Run migrate
Assert transformed data is correct
Assert no unexpected nulls / duplicates

5) Upgrade-path testing (very important in prod)

Start DB at an older version (e.g., baseline tag)
Apply migrations to latest
This catches “works on empty DB but fails in real upgrades”

6) Non-functional checks (optional but senior)

Migration time doesn’t exceed a threshold (smoke performance check)
Locks: keep migrations small / online-safe (engine-specific)

Best practice setup: Testcontainers in CI

Why: It’s the closest thing to production without needing shared DB environments.

Flyway + JUnit + Testcontainers (Java example)

import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.PostgreSQLContainer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import static org.junit.jupiter.api.Assertions.*;

class FlywayMigrationsIT {

    static final PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:16")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");

    static {
        pg.start();
    }

    @Test
    void migrations_apply_and_schema_is_correct() throws Exception {
        Flyway flyway = Flyway.configure()
                .dataSource(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword())
                .locations("classpath:db/migration") // your migrations
                .load();

        flyway.clean();     // only in tests!
        flyway.migrate();
        flyway.validate();  // detects modified applied migrations

        // Example schema assertion: table exists
        try (Connection c = DriverManager.getConnection(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword());
             ResultSet rs = c.createStatement().executeQuery("""
                 SELECT 1
                 FROM information_schema.tables
                 WHERE table_name = 'users'
                 """)) {
            assertTrue(rs.next(), "users table should exist");
        }
    }
}

Liquibase + JUnit + Testcontainers (Java example)

import liquibase.Liquibase;
import liquibase.database.Database;
import liquibase.database.DatabaseFactory;
import liquibase.database.jvm.JdbcConnection;
import liquibase.resource.ClassLoaderResourceAccessor;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.PostgreSQLContainer;

import java.sql.Connection;
import java.sql.DriverManager;

import static org.junit.jupiter.api.Assertions.*;

class LiquibaseMigrationsIT {

    static final PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:16")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");

    static {
        pg.start();
    }

    @Test
    void liquibase_updates_cleanly() throws Exception {
        try (Connection conn = DriverManager.getConnection(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword())) {
            Database db = DatabaseFactory.getInstance()
                    .findCorrectDatabaseImplementation(new JdbcConnection(conn));

            Liquibase liquibase = new Liquibase(
                    "db/changelog/db.changelog-master.xml",
                    new ClassLoaderResourceAccessor(),
                    db
            );

            liquibase.dropAll(); // only in tests!
            liquibase.update((String) null);

            // If a changeset was modified after being applied, Liquibase would fail validation by default
            // (unless someone did clearCheckSums, which you should forbid in CI).
        }
    }
}

CI pipeline recipe (works for both)

Start ephemeral DB (Testcontainers or docker-compose)
Run migrations
Run validation (Flyway validate / Liquibase default validation)
Run “schema tests” (queries on information_schema / pg_catalog)
Run “data tests” if you do backfills/transforms
(Optional) run app integration tests against the migrated DB

Common team gotchas (what breaks real CI)

✅ Use clean DB per test run (no shared DB)
✅ Ensure migrations are on the test classpath
✅ Don’t rely on “Hibernate ddl-auto” to create tables in tests
✅ Ban “fixing” with clearCheckSums / editing applied migrations
✅ Test upgrade from old version, not only “from scratch”

Interview-ready answer (2–3 sentences)

We test migrations automatically by applying them to a fresh ephemeral database in CI (usually via Testcontainers), then running validation and asserting required schema/data invariants. For production realism, we also test upgrading from an older schema version to the latest, not just migrating from empty.