Testing Database Migrations in CI with Docker and Testcontainers

A migration that has only ever run on your laptop is untested. The reliable way to test one is to run it, on every change, against a fresh database of the same engine and version as production, then look at the schema it produced. Docker makes that cheap, Testcontainers makes it a unit test, and a CI matrix makes it cover every version you support. Here is how to set it up and what it catches.

Why an in-memory or mocked database is not enough#

H2 in PostgreSQL mode, SQLite, or a mocked repository will tell you that your application logic works. None of them will tell you that your migration works, because a migration is a conversation with a specific engine's dialect, type system and locking rules. The failures that reach production are precisely the ones a substitute cannot reproduce: a CREATE INDEX CONCURRENTLY inside a transaction, a MySQL key length limit, a column type that Oracle spells differently, a foreign key to a column of a different type, an identifier that is a reserved word on one engine only.

There is also the question of what the migration leaves behind. "It ran without an error" is a weak assertion. The strong assertion is "the schema now has these tables, these columns, these keys", and you can only make it against the real thing. The article on SQLite in development lists the specific ways a stand-in engine misleads you.

A Testcontainers example with JUnit 5 and Flyway#

Testcontainers starts a Docker container from a test and removes it afterwards. With the PostgreSQL module and the JUnit 5 integration on the classpath, a migration test is a few lines:

@Testcontainers
class MigrationsTest {

    @Container
    static final PostgreSQLContainer postgres = new PostgreSQLContainer("postgres:18");

    @Test
    void migrationsApplyCleanlyAndCreateTheSchema() throws Exception {
        Flyway flyway = Flyway.configure()
                .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
                .locations("classpath:db/migration")
                .load();
        flyway.migrate();

        try (Connection connection = DriverManager.getConnection(
                postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())) {
            Set<String> tables = new TreeSet<>();
            try (ResultSet rs = connection.getMetaData()
                    .getTables(null, "public", "%", new String[] {"TABLE"})) {
                while (rs.next()) {
                    tables.add(rs.getString("TABLE_NAME"));
                }
            }
            tables.remove("flyway_schema_history");
            assertThat(tables).containsExactly("owners", "pets", "specialties",
                    "types", "vet_specialties", "vets", "visits");
        }
    }
}

Three things are worth noticing. The image tag pins the engine version, so the test is a statement about PostgreSQL 18 and nothing else. The test asserts on the schema read back through JDBC metadata, not on the fact that Flyway returned. And the bookkeeping table the migration tool creates for itself is removed before the assertion, because it is not part of your schema. The same shape works with Liquibase, with a MySQL, MariaDB, SQL Server or Oracle container, and in any language that has a Testcontainers port. Artifact names differ between Testcontainers releases, so check the Testcontainers documentation for the module you need.

The same idea without a test framework#

If the migrations are plain SQL and you do not want a JVM test for them, run the migration tool's container against a service container in the CI job. On GitHub Actions:

jobs:
  migrate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:18
        env:
          POSTGRES_PASSWORD: secret
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@v4
      - name: Apply the migrations
        run: |
          docker run --rm --network host \
            -v "$PWD/db/migration:/flyway/sql" \
            flyway/flyway:11 \
            -url=jdbc:postgresql://localhost:5432/postgres \
            -user=postgres -password=secret migrate
      - name: Check the schema
        run: |
          docker run --rm --network host -e PGPASSWORD=secret postgres:18 \
            psql -h localhost -U postgres -c "\dt" | grep -q " pets "

The health check matters: without it the first step can run before the database accepts connections. Locally, the same two containers come from a docker compose file, which gives every developer the identical setup.

Cover every version you support with a matrix#

Production is on one version today and another next year, and a library may have to support several. A matrix runs the same job once per version:

strategy:
  fail-fast: false
  matrix:
    postgres: ["14", "15", "16", "17", "18"]
services:
  postgres:
    image: postgres:${{ matrix.postgres }}

In a Testcontainers suite, a parameterized test does the same with a list of image tags. The result is a table of green and red cells that tells you exactly which version broke, which is far more useful than a single failure after an upgrade. It is also how you learn early that a feature you started using, say MERGE or a new index type, does not exist on the oldest version you still promise to support.

Do not stop at versions of one engine. If the product ships on PostgreSQL and MySQL, both belong in the matrix, and the migration files that differ between them should be tested on each. Transactional DDL alone makes a failed migration behave differently on the two, and that is something you want to see in CI, not in a deployment.

Startup times and image caching#

A PostgreSQL container answers connections a few seconds after it starts. MySQL and MariaDB take somewhat longer. SQL Server and Oracle take tens of seconds, and a minute or more when the image has to be pulled first. Plan around it:

• Start one container per test class or per suite, not per test. A clean state per test is cheaper to get by dropping and recreating the schema, or by running each test in a transaction that is rolled back.
• Pull images in a step of their own so that the pull time is visible in the job log and not blamed on the tests.
• Pin image tags. postgres:latest changes under you and makes a green build red for reasons unrelated to your change.
• On a developer machine, Testcontainers can keep a container running between runs with withReuse(true) and testcontainers.reuse.enable=true in the user's Testcontainers properties file. It is a local convenience; leave it off in CI.
• Large images cost bandwidth on every hosted runner. The PostgreSQL image is small; SQL Server's is more than a gigabyte. If a matrix pulls it twenty times a day, a registry mirror or a self-hosted runner with a warm cache pays for itself.

Assert the schema, not just the exit code#

Once the migration has run, decide what the test should promise. The options, from weakest to strongest:

The last row is the one most teams skip and most teams need. Fresh installs run every migration from the beginning; production ran them one release at a time, sometimes with a manual fix in between. If the two end states differ, you have schema drift, and only a comparison will find it.

What this catches before it reaches production#

• Dialect mistakes: a PostgreSQL SERIAL in a MySQL script, a DATETIME in a PostgreSQL one, MySQL's 64 character identifier limit, Oracle's 30 byte limit on older versions.
• Ordering mistakes: a foreign key to a table created two migrations later, which passed when a developer ran the migrations one at a time by hand.
• History problems: an edited migration that the tool refuses because of its checksum, a version number used twice, a repeatable migration that is not actually repeatable.
• Locking behaviour: a statement that needs to run outside a transaction, or a migration that would hold an exclusive lock for minutes on a table the size of production's. Restore a sanitized snapshot into the container and the test measures it.
• Silent no-ops: files the tool ignored because of a naming convention, so that the migrate command succeeded and created nothing.

Where SchemaDrawer fits#

SchemaDrawer applies the same principle as a service. You upload the migration files of a project written for any of 22 tools, name the engine and version, and it runs the migration in Docker against a throw-away PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite, reads the schema back over JDBC and returns an SVG diagram of the tables, columns, primary keys and foreign keys. The GitHub Action opens a pull request with the updated diagram, which turns the schema assertion above into something a reviewer can look at: if the diagram changed in a way nobody intended, the review catches it. And because the migration really ran, a migration that fails on the chosen engine fails the job with the tool's own error message.

Key takeaways#

• Test migrations against the real engine and version, in a container that the test creates and destroys.
• Assert on the schema that comes out, read through JDBC metadata or a dump, and remove the tool's own bookkeeping tables first.
• Run a matrix over every engine version you support, and over every engine if you ship on more than one.
• Pin image tags, start one container per suite, and budget for the startup time of the slower engines.
• Compare a fresh install against an upgraded database to catch drift that no single migration test can see.