trymigrate

The TDD Sandbox for Database Migrations. Stop guessing if your SQL works. Start verifying it—version by version, script by script.

---

📖 Table of Contents

• 🧐 What is trymigrate?
• 🚀 Quick Start
• 🧪 The TDD Experience
• 🛡️ Automated Quality Gates
• 💾 Data Seeding & Scenarios
• 🏗️ Multi-Schema Support
• 🗄️ Database Lifecycle
• 🔌 Supported Databases
• 📖 API Reference
• 🧩 Plugin System
• ⚙️ Configuration
• 🤝 Contributing

---

🧐 What is trymigrate?

Database migrations are often the most fragile part of an application. trymigrate turns the "black box" of SQL scripts into a testable, versioned, and linted part of your codebase. It orchestrates Flyway, SchemaCrawler, and Testcontainers into a seamless JUnit 5 extension.

Treat your schema like code. Write a test, write the SQL, and verify the exact state of your database at any version.

---

📋 Prerequisites

Before you start, ensure you have the following installed:

• Java 17 or higher.
• Docker (required by Testcontainers for orchestrating database instances).

---

🚀 Quick Start

1. Add the Dependency (e.g., for PostgreSQL):

<dependency>
    <groupId>io.github.bekoenig.trymigrate</groupId>
    <artifactId>trymigrate-postgresql</artifactId>
    <version>${trymigrate.version}</version>
    <scope>test</scope>
</dependency>

2. Write Your First Test:

@Trymigrate // 1. Activate the extension
class MySchemaTest {

    // 2. Register your database container
    @TrymigrateRegisterPlugin
    static final PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @Test
    @TrymigrateWhenTarget("1.0") // 3. Set the target version
    void should_HaveCorrectUserTable(Catalog catalog) {
        // 4. Assert your schema state
        assertThat(catalog).table("public", "users").column("email").isNotNull();
    }
}

---

🧪 The TDD Experience

trymigrate encourages an iterative workflow. Verify that version 1.0 provides a solid foundation before evolving to 1.1.

Available Parameters

Test methods can receive the following parameters automatically:

Parameter	Type	Purpose
Catalog	schemacrawler.schema.Catalog	The full database model for structural assertions.
Lints	schemacrawler.tools.lint.Lints	All current schema violations (Full State minus global excludes).
DataSource	javax.sql.DataSource	A live connection to the test database for data-level assertions.

@Trymigrate
@TrymigrateVerifyLints(failOn = LintSeverity.critical)
class SchemaEvolutionTest {

    // 1. Define your container (managed automatically)
    @TrymigrateRegisterPlugin
    private final PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    // 2. Optional: Configure Flyway (schemas, locations, etc.)
    @TrymigrateRegisterPlugin
    private final TrymigrateFlywayCustomizer flyway = config -> config
        .defaultSchema("app_schema")
        .locations("classpath:db/migration")
        .cleanDisabled(false);

    @Test
    @TrymigrateWhenTarget("1.0")
    void should_EstablishBaseline(Catalog catalog) {
        assertThat(catalog).table("app_schema", "users")
            .column("id").isPrimaryKey().hasType("uuid")
            .column("email").isNotNull();
    }

    @Test
    @TrymigrateWhenTarget("1.1")
    @TrymigrateGivenData("INSERT INTO app_schema.users (id, email) VALUES (gen_random_uuid(), 'test@example.com');")
    void should_MigrateDataSafety(Catalog catalog, Lints lints) {
        // Verify migration 1.1 didn't break existing data or constraints
        assertThat(catalog).table("app_schema", "users").hasColumn("last_login");
        assertThat(lints).isEmpty(); // No new regressions
    }
}

---

🛡️ Automated Quality Gates

Every migration version is automatically inspected for architectural anti-patterns using SchemaCrawler's powerful linting engine.

• Verification Points & Smart Diffing: Each test method acts as a Verification Point. The automated quality gate only reports new lints introduced since the previous Verification Point. Legacy issues are filtered out to keep your focus sharp.

• Complete State Visibility: While the quality gate focuses on the delta, the injected Lints parameter always provides the full current state (minus global excludes). This allows you to verify if existing violations were fixed or transformed.

• Intermediate Reporting: Even if a test method skips several migration versions (e.g., from 1.0 to 1.5), trymigrate still performs linting and generates reports for every intermediate migration version. You can find these in target/trymigrate-lint-reports/.

• The Gatekeeper: Use @TrymigrateVerifyLints to fail tests if new violations (e.g., missing primary keys, bad naming) exceed your severity threshold. Supported levels are: INFO, LOW, MEDIUM, HIGH, CRITICAL.

• Multi-Test Efficiency: If multiple tests target the same version, the quality gate is only checked for the first test (according to execution order). Subsequent tests for the same version do not need to repeat the check as the version has already been verified.

Suppressing & Excluding

Handle legacy debt or edge cases with granular control:

• @TrymigrateExcludeLint: Globally ignore specific rules or objects for the entire test class.
• @TrymigrateSuppressLint: Locally allow specific lints for a single migration version without removing them from the reports.

Custom Linter Configuration

For advanced needs, use TrymigrateLintersConfigurer to fluently configure, enable, or disable specific linters. This allows you to override default severities, restrict linters to specific naming patterns, or register custom linter providers without SPI:

@TrymigrateRegisterPlugin
private final TrymigrateLintersConfigurer linterConfig = config -> config
    .register(new MyCustomLinterProvider()) // Register a custom linter
    .configure("schemacrawler.tools.linter.LinterTableWithNoRemarks") // Configure an existing linter
        .severity(LintSeverity.high) // Set severity to high
        .tableInclusionPattern("app_schema\\..*") // Apply only to tables starting with "APP_"
    .disable("schemacrawler.tools.linter.LinterTableSql"); // Disable a specific linter

Default Linting & Scope:

• Defaults: By default, trymigrate applies a comprehensive set of SchemaCrawler linters as defined in io.github.bekoenig.trymigrate.core.internal.lint.config.CoreLinters.
• Flyway History: The Flyway schema history table is automatically excluded from linting to prevent false positives. However, it remains present in the Catalog.
• Schema Scope: Linting is strictly limited to schemas managed by Flyway. It is not possible to extend linting to schemas outside of Flyway's control.

Tip

You can also register custom linters via the standard Java SPI mechanism by adding your LinterProvider implementation to META-INF/services/schemacrawler.tools.lint.LinterProvider.

---

💾 Data Seeding & Scenarios

Testing migrations often requires more than just an empty schema. trymigrate allows you to seed data at specific points in the timeline to verify transformation logic or handle data-dependent constraints.

The Role of @TrymigrateGivenData

This annotation seeds data immediately before the migration script of the target version is executed. It supports two formats by default:

• Raw SQL Strings: Directly execute statements like INSERT INTO ....

• Classpath Resources: Provide a path to a file ending in .sql (e.g., db/testdata/baseline.sql).

• Initial Inventory (Baseline Data): Seed representative, production-like records into version 1.0 to verify that your 1.1 migration script correctly handles data transformations (e.g., migrating a JSON blob into structured columns).

• Scenario-based Testing: Load specific datasets to test edge cases, such as very large tables or "dirty" data, before a NOT NULL or UNIQUE constraint is applied.

Custom Loaders with TrymigrateDataLoader

While trymigrate handles SQL out of the box, you can implement TrymigrateDataLoader to support custom formats or DBMS-specific loading mechanisms:

@TrymigrateRegisterPlugin
private final TrymigrateDataLoader postgresCopyLoader = new TrymigrateDataLoader() {
    @Override
    public boolean supports(String resource, String extension, TrymigrateDatabase database) {
        return "pgbin".equals(extension); // Trigger for .pgbin files
    }

    @Override
    public void load(String resource, Connection connection, TrymigrateDatabase database) {
        // Use the native PostgreSQL COPY API for massive speed
    }
};

---

🏗️ Multi-Schema Support

Building enterprise-grade architectures? trymigrate handles complex databases with multiple schemas natively:

• Isolated Reporting: Reports are organized by schema for a clear audit trail.
• Cross-Schema Assertions: Verify foreign keys and joins that span multiple schemas using a single injected Catalog.
• Selective Enforcement: Enforce strict standards in new schemas while being lenient with legacy ones.

Catalog Customization

By default, trymigrate uses Flyway's schema configuration (e.g., flyway.defaultSchema or flyway.schemas) to determine which schemas to crawl. You can provide custom implementations of TrymigrateCatalogCustomizer to override or refine this behavior.

Note

The Catalog includes all discovered objects within the configured schemas, including the Flyway migration history table (e.g., flyway_schema_history). This differs from linting, which excludes the history table by default.

Example:

// In your test class:
@TrymigrateRegisterPlugin
private final TrymigrateCatalogCustomizer catalogCustomizer = new TrymigrateCatalogCustomizer() {
    @Override
    public void customize(LimitOptionsBuilder builder) {
        // Override default schema selection and include only schemas starting with "APP_"
        // and exclude those starting with "SYS_" using SchemaCrawler's RegularExpressionRule.
        builder.includeSchemas(new RegularExpressionRule("APP_.*", "SYS_.*"));
    }
};

---

🗄️ Database Lifecycle

Efficiently manage your database state across test suites.

🐳 Testcontainers Integration

Any JdbcDatabaseContainer annotated with @TrymigrateRegisterPlugin is automatically managed:

• Instance Field: The container is started before and stopped after the test class.
• Static Field: The container is not stopped after the test class. It is shared across multiple test classes for maximum performance and only disposed of when the JVM exits.

🧼 Fresh State with @TrymigrateCleanBefore

The database instance is reused for all test methods within a class. Data seeded in one method remains visible in subsequent ones. Use @TrymigrateCleanBefore to trigger a flyway clean before a test starts to ensure a fresh state (especially when seeding data or testing specific versions in isolation).

Important

Flyway 9+ disables clean by default. You must set .cleanDisabled(false) in your TrymigrateFlywayCustomizer for this to work.

---

📖 API Reference

Annotations

Annotation	Scope	Purpose
@Trymigrate	Class	Entry point. Activates the extension.
@TrymigrateWhenTarget	Method	Sets the Flyway version to migrate to. Supports "latest".
@TrymigrateGivenData	Method	Seeds SQL or custom data before the target migration.
@TrymigrateCleanBefore	Method	Wipes the database before the current migration version.
@TrymigrateVerifyLints	Class	Configures the quality gate severity threshold.
@TrymigrateExcludeLint	Class	Globally drops specific lints (hidden from reports and quality gate).
@TrymigrateSuppressLint	Method	Locally allows specific lints (visible in reports, ignored by quality gate).
@TrymigrateRegisterPlugin	Field	Registers customizers or Testcontainers.
@TrymigrateDiscoverPlugins	Class	Fine-tunes SPI-based plugin discovery.

---

🧩 Plugin System

trymigrate is a modular engine. Extend almost every aspect by implementing these interfaces:

Interface	Purpose
TrymigrateFlywayCustomizer	Configure Flyway (locations, placeholders, schemas).
TrymigrateLintersConfigurer	Fluently configure SchemaCrawler linters (Severity, Regex).
TrymigrateCatalogCustomizer	Customize the database crawl (filter types, schemas).
TrymigrateLintOptionsCustomizer	Customize general linting options (e.g., report titles).
TrymigrateDataLoader	Support custom data formats (CSV, JSON, etc.).
TrymigrateDatabase	Abstraction for custom DB lifecycle/connection.
TrymigrateLintsReporter	Send lint results to Slack, Jira, or custom tools.

Registration & Hierarchy

Plugins can be registered in two ways, forming a clear hierarchy:

1. Local Registration (@TrymigrateRegisterPlugin): Register plugins directly as fields within your test class. These have the highest priority and override any global settings.
2. Global Registration (Java SPI): Register plugins via the standard Java SPI mechanism (META-INF/services/). These are automatically discovered and applied to all tests.

Discovery & Control

Fine-tune how global plugins are discovered using @TrymigrateDiscoverPlugins:

• Selective Loading: Use @TrymigrateDiscoverPlugins(origin = MyDatabasePlugin.class) to only load plugins belonging to a specific hierarchy (e.g., only PostgreSQL-specific customizers).
• Exclusion: Use @TrymigrateDiscoverPlugins(exclude = {LegacyLinter.class, GenericReporter.class}) to explicitly block certain plugins or entire interface groups from being loaded.

---

⚙️ Configuration

JVM Property	Description	Default
trymigrate.container.db-port	Pin host port for local debugging (e.g. 5432:5432).	Random
trymigrate.lint.report.html.skip-empty	Only generate HTML reports if lints are found.	true
trymigrate.lint.report.html.basedir	Root directory for reports.	target/

---

🔌 Supported Databases

Trymigrate provides pre-configured modules for all major databases:

PostgreSQL	MySQL	MariaDB	SQL Server
trymigrate-postgresql	trymigrate-mysql	trymigrate-mariadb	trymigrate-sqlserver
Oracle	DB2	HSQLDB	...and more
trymigrate-oracle	trymigrate-db2	trymigrate-hsqldb

---

🤝 Contributing

We love contributions! Whether it's a bug report, a new database module, or a feature request.

Important

By executing tests for enterprise modules (e.g., DB2 or SQL Server), you programmatically accept the respective vendor's license agreements, as the test code explicitly triggers the acceptance (e.g., via .acceptLicense()). Ensure you are familiar with the vendor's terms before running these tests.

1. Fork the repository.
2. Build: ./mvnw clean install
3. Check CONTRIBUTING.md for details.

---

⚖️ License

Distributed under the MIT License. See LICENSE for more information.

---

Bringing the power of TDD to the foundation of your application.

Note

This documentation was created with the support of AI to ensure clarity and technical precision.