TDD for Database Migrations: Stop Guessing if Your SQL Works

We test our Java code, our APIs, our frontend, and even our infrastructure. Yet database migrations—one of the most critical and high-risk parts of any application deployment—often remain a total "black box".

For most teams, testing database migrations looks like this:

1. Write a SQL migration file (V1_1__add_user_statuses.sql).
2. Run the application locally or deploy it to a staging environment.
3. Watch it fail because of a syntax error, a mismatched column type, or a violated constraint.
4. Manually clean up the database state, tweak the SQL, and repeat.

This is a painful, slow outer loop. If a migration is buggy, you catch it late. And if you need to test how a migration script transforms existing production data, setting that up manually for every version is a nightmare.

Enter trymigrate: the TDD sandbox for database migrations.

---

🧐 What is trymigrate?

trymigrate is an open-source JUnit 5 extension that orchestrates Flyway, Testcontainers, and SchemaCrawler into a seamless, automated test suite.

It lets you treat your database schema like regular application code. You write tests, target specific migration versions, assert schema structure, seed scenario-based test data, and automatically lint your SQL for database design anti-patterns.

---

🔄 How it Works: The Flyway Lifecycle in trymigrate

To understand how trymigrate simplifies database testing, let's look at how it interacts with Flyway:

1. Auto-Discovery: Flyway scans your project (src/main/resources/db/migration or a custom configured location) for standard versioned migrations (e.g., V1.0__init.sql, V1.1__add_users.sql).
2. Chronological Ordering: When you run your JUnit test class, trymigrate scans for all @TrymigrateWhenTarget annotations on your test methods and sorts the tests dynamically in ascending chronological order of their version targets.
3. Targeted Migrations: Before executing a test targeting version 1.0, trymigrate boots up your Testcontainer, configures Flyway's target parameter to "1.0", and runs flyway.migrate(). It then passes the active database connection to your test method.
4. Data Seeding & Schema Evolution: If the next test targets "1.1" and contains seeded data, trymigrate executes the SQL seed script on the database while it is still at version 1.0. Then, it changes Flyway's target to "1.1", and runs flyway.migrate(). Flyway applies the V1.1 script directly on top of the seeded database state, letting you verify data transformations or constraint additions under realistic conditions!

---

🛠️ Show, Don't Tell: A Concrete Example

Here is a complete schema test utilizing trymigrate-postgresql and assertj-schemacrawler to test a baseline schema and a subsequent data-transforming migration.

package my.app;

import io.github.bekoenig.assertj.schemacrawler.api.SchemaCrawlerAssertions;
import io.github.bekoenig.trymigrate.core.Trymigrate;
import io.github.bekoenig.trymigrate.core.TrymigrateGivenData;
import io.github.bekoenig.trymigrate.core.TrymigrateWhenTarget;
import io.github.bekoenig.trymigrate.core.lint.TrymigrateVerifyLints;
import io.github.bekoenig.trymigrate.core.plugin.TrymigrateRegisterPlugin;
import io.github.bekoenig.trymigrate.core.plugin.customize.TrymigrateFlywayCustomizer;
import org.junit.jupiter.api.Test;
import org.testcontainers.postgresql.PostgreSQLContainer;
import schemacrawler.schema.Catalog;
import schemacrawler.tools.lint.LintSeverity;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static java.util.function.Predicate.isEqual;
import static org.assertj.core.api.Assertions.assertThat;

@Trymigrate // 1. Activate the extension
@TrymigrateVerifyLints(failOn = LintSeverity.medium) // 2. Fail if new medium/high lints are introduced
class SchemaMigrationTest {

    // 3. Register your database container (managed automatically)
    @TrymigrateRegisterPlugin
    private final PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:18");

    // 4. Configure Flyway options
    @TrymigrateRegisterPlugin
    private final TrymigrateFlywayCustomizer flyway = config -> config
            .defaultSchema("app_schema")
            .locations("classpath:db/migration");

    @Test
    @TrymigrateWhenTarget("1.0") // 5. Run migrations up to V1.0 before running this test
    void should_establish_baseline(Catalog catalog) {
        // Assert structure using SchemaCrawler
        SchemaCrawlerAssertions.assertThat(catalog)
                .table("app_schema", "users")
                .column("id")
                .matchesColumnDataTypeName(isEqual("serial"));
    }

    @Test
    @TrymigrateWhenTarget("1.1") // 6. Targets V1.1
    @TrymigrateGivenData("db/testdata/seed_users.sql") // 7. Seeds data *before* V1.1 migration script runs
    void should_migrate_user_statuses(DataSource dataSource, Catalog catalog) throws Exception {
        // Assert schema structure evolution
        SchemaCrawlerAssertions.assertThat(catalog)
                .table("app_schema", "users")
                .column("status")
                .isNotNull();

        // Assert data transformation logic (using JDBC DataSource)
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT status FROM app_schema.users LIMIT 1")) {
            assertThat(rs.next()).isTrue();
            assertThat(rs.getString("status")).isEqualTo("ACTIVE");
        }
    }
}

---

🛡️ Smart Linting: The Delta-Based Quality Gate

Integrating a database linter (like SchemaCrawler's linting engine) in real projects can be daunting. Legacy databases might have hundreds of minor naming standard violations or missing primary keys. If your build fails on all of them, no one will use the tool.

trymigrate solves this with Delta-Linting:

• The library creates a Verification Point for each test method version target.
• The quality gate only checks and fails on new lints introduced in the current migration step.
• Legacy violations are filtered out from the quality gate check, while full HTML reports are still generated version-by-version in your target/trymigrate-lint-reports/ folder.
• You can easily suppress rules class-wide via @TrymigrateExcludeLint or version-wide via @TrymigrateSuppressLint.

---

🤝 Join the Project: How You Can Contribute!

trymigrate is designed to be highly modular and extensible. It relies on a Java SPI (Service Provider Interface) plugin system, meaning you can hook into almost any step of the lifecycle.

I am actively looking for contributors! Here are some areas where you can jump in and make an impact:

• 🔌 Database Support Modules: Add and test configurations for new database engines (e.g., Snowflake, CockroachDB, SQLite).
• 💾 Custom Data Loaders: Implement custom TrymigrateDataLoader plugins to support loading seed datasets from CSV, JSON, or DBMS-specific bulk load features.
• 📊 Tool Reporters: Help write TrymigrateLintsReporter implementations to automatically push lint violations to SonarQube, Slack, or Jira.
• 📝 Raw SQL Linting: Build integrations for linting raw migration .sql scripts using tools like SQLFluff before they hit the database.

Check out my GitHub Repository to get started, review our CONTRIBUTING.md, or open an issue!

Let's bring database schema modifications out of the dark and into the modern testing pipeline.