Testing Migrations: H2 vs Testcontainers vs Real MySQL

Part 20 of 24

May 03, 2026 Abhay 8 min read

Testing Migrations: H2 vs Testcontainers vs Real MySQL

The CI pipeline from Article 19 validates migrations against a MySQL service container. But that’s not the same as testing that your application code works correctly after the migration runs. This article covers how to structure Spring Boot tests that validate both the migration and the application behaviour — and makes the case for replacing H2 with Testcontainers as your primary testing database.

Three Testing Strategies

Strategy	Database	Speed	Fidelity	Use For
H2 in-memory	H2 (in-memory)	Fastest	Low — MySQL-specific SQL fails	Unit tests that mock repositories
H2 with MySQL mode	H2 (MySQL compat)	Fast	Medium — most SQL works, some quirks	Basic integration tests on simple schemas
Testcontainers	Real MySQL 8.x	Slower	High — identical to production	Repository integration tests, migration validation

The recommendation: use Testcontainers for any test that runs SQL against the database. H2 is acceptable only for tests that mock the repository layer entirely.

Why H2 Falls Short for Migration Testing

H2 is a Java in-memory database designed for testing convenience, not MySQL compatibility. The gaps matter for Liquibase migrations:

What H2 does not support:

ENUM column type — silently converts to VARCHAR
ON UPDATE CURRENT_TIMESTAMP
FULLTEXT indexes
MySQL-specific functions: JSON_OBJECT, REGEXP_LIKE, FIND_IN_SET
ROW_FORMAT, ENGINE=InnoDB
MySQL ENUM in CREATE TABLE DDL

If your changelog contains any of these (and our e-commerce schema does — ENUM columns, JSON, CURRENT_TIMESTAMP), your Liquibase migrations will run against H2 in a silently degraded form. Tests pass. Production fails.

H2 MySQL compatibility mode (MODE=MySQL in the JDBC URL) closes many gaps but not all. It is adequate for simple schemas with no MySQL-specific features. For the e-commerce schema built throughout this series, it is insufficient.

Strategy 1: H2 for Pure Unit Tests

When tests mock the repository layer and never touch SQL, H2 (or no database at all) is fine:

// No Liquibase needed — repository is mocked
@ExtendWith(MockitoExtension.class)
class UserServiceTest {

    @Mock
    UserRepository userRepository;

    @InjectMocks
    UserService userService;

    @Test
    void shouldReturnUserWhenFound() {
        var user = new User(1L, "test@example.com", "Test User");
        when(userRepository.findById(1L)).thenReturn(Optional.of(user));

        var result = userService.getUser(1L);

        assertThat(result).isPresent();
        assertThat(result.get().email()).isEqualTo("test@example.com");
    }
}

No Spring context, no database, no Liquibase. Tests run in milliseconds.

For tests that need a minimal Spring context but mock the database tier:

@SpringBootTest
@TestPropertySource(properties = {
    "spring.liquibase.enabled=false",
    "spring.autoconfigure.exclude=org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration"
})
class UserControllerTest {
    // Controller layer test — no DB needed
}

Strategy 2: H2 with MySQL Mode (Acceptable for Simple Cases)

# src/test/resources/application-test.yml
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false
    driver-class-name: org.h2.Driver
  jpa:
    hibernate:
      ddl-auto: none
  liquibase:
    enabled: true
    contexts: test,default

Add the dbms: mysql precondition to MySQL-specific changesets so they are skipped on H2:

- changeSet:
    id: "20260618-003"
    author: abhay
    preConditions:
      onFail: MARK_RAN
      dbms:
        type: mysql
    changes:
      - sql:
          sql: ALTER TABLE products ADD FULLTEXT INDEX ft_products_search (name, description);

Limitation: ENUM columns created via createTable are silently converted to VARCHAR in H2. Constraints that depend on ENUM values will not be enforced. Use this approach only if your schema has no MySQL-specific features.

Strategy 3: Testcontainers (Recommended)

Testcontainers spins up a real MySQL Docker container for each test run. Your migrations run against the exact same MySQL version as production.

Dependencies

<!-- pom.xml -->
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>testcontainers</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>mysql</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>junit-jupiter</artifactId>
  <scope>test</scope>
</dependency>

Spring Boot manages the Testcontainers version via its BOM — no version needed.

Approach 1: @ServiceConnection (Spring Boot 3.1+, recommended)

Spring Boot 3.1 introduced @ServiceConnection, which auto-wires the container’s JDBC URL directly into the Spring context — no @DynamicPropertySource boilerplate:

// src/test/java/com/example/config/TestcontainersConfig.java
@TestConfiguration(proxyBeanMethods = false)
public class TestcontainersConfig {

    @Bean
    @ServiceConnection
    MySQLContainer<?> mysqlContainer() {
        return new MySQLContainer<>("mysql:8.0")
            .withDatabaseName("ecommerce")
            .withUsername("lb_user")
            .withPassword("lb_pass")
            .withReuse(true);    // Reuse container across test classes for speed
    }
}

// src/test/java/com/example/repository/UserRepositoryTest.java
@SpringBootTest
@Import(TestcontainersConfig.class)
@ActiveProfiles("test")
class UserRepositoryTest {

    @Autowired
    UserRepository userRepository;

    @Test
    void shouldPersistAndRetrieveUser() {
        var saved = userRepository.save(new User(
            null, "test@example.com", "Test User",
            "hash", Role.CUSTOMER, Status.ACTIVE
        ));

        assertThat(saved.getId()).isNotNull();

        var found = userRepository.findByEmail("test@example.com");
        assertThat(found).isPresent();
        assertThat(found.get().getFullName()).isEqualTo("Test User");
    }

    @Test
    void shouldEnforceUniqueEmailConstraint() {
        userRepository.save(new User(null, "dup@example.com", "User One", "hash", Role.CUSTOMER, Status.ACTIVE));

        assertThatThrownBy(() ->
            userRepository.saveAndFlush(new User(null, "dup@example.com", "User Two", "hash", Role.CUSTOMER, Status.ACTIVE))
        ).isInstanceOf(DataIntegrityViolationException.class);
    }
}

Liquibase runs automatically at Spring context startup (before any test method). All migration changesets are applied against the real MySQL container. No special configuration needed beyond TestcontainersConfig.

application-test.yml:

# src/test/resources/application-test.yml
spring:
  liquibase:
    contexts: test,default    # Activate test context for seed data
  jpa:
    hibernate:
      ddl-auto: none
    show-sql: false

Approach 2: @DynamicPropertySource (Spring Boot 2.x / 3.0)

For older Spring Boot versions:

@SpringBootTest
@Testcontainers
class UserRepositoryTest {

    @Container
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
        .withDatabaseName("ecommerce")
        .withUsername("lb_user")
        .withPassword("lb_pass");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", mysql::getJdbcUrl);
        registry.add("spring.datasource.username", mysql::getUsername);
        registry.add("spring.datasource.password", mysql::getPassword);
    }

    @Autowired
    UserRepository userRepository;

    @Test
    void shouldPersistUser() {
        // Liquibase has already run at context startup
        // ...
    }
}

Container Reuse: Speeding Up Test Suites

Spinning up a MySQL container per test class is slow (5-10 seconds per container). Use container reuse to start the container once and share it:

// In TestcontainersConfig.java — already shown above with .withReuse(true)
// Also requires ~/.testcontainers.properties:
// testcontainers.reuse.enable=true

With reuse enabled, the container starts once per JVM session. All test classes share it. Tests must be idempotent — use @Transactional to roll back data after each test, or use dedicated test data with unique identifiers.

@SpringBootTest
@Import(TestcontainersConfig.class)
@Transactional    // Rolls back after each test — leaves DB clean for the next
class OrderRepositoryTest {

    @Autowired
    OrderRepository orderRepository;

    @Test
    void shouldCreateOrder() {
        // Data created here is rolled back after this test
        var order = orderRepository.save(new Order(...));
        assertThat(order.getId()).isNotNull();
    }
}

Testing Migration Idempotency

A critical test: running Liquibase update twice should produce the same result as running it once. Test this explicitly:

@SpringBootTest
@Import(TestcontainersConfig.class)
class MigrationIdempotencyTest {

    @Autowired
    SpringLiquibase liquibase;

    @Autowired
    DataSource dataSource;

    @Test
    void migrationShouldBeIdempotent() throws LiquibaseException {
        // Liquibase already ran at context startup
        // Run it again — should apply 0 changesets
        try (var connection = dataSource.getConnection()) {
            var database = DatabaseFactory.getInstance()
                .findCorrectDatabaseImplementation(new JdbcConnection(connection));

            var liquibaseInstance = new Liquibase(
                liquibase.getChangeLog(),
                new ClassLoaderResourceAccessor(),
                database
            );

            // status() returns the list of unrun changesets
            var unrun = liquibaseInstance.listUnrunChangeSets(null, null);
            assertThat(unrun)
                .describedAs("Re-running update should find 0 pending changesets")
                .isEmpty();
        }
    }
}

Testing Checksum Stability

Changesets that have been applied must not have their MD5 change. This test catches accidental edits to deployed changesets:

@Test
void noDeployedChangesetShouldHaveChecksumMismatch() throws Exception {
    try (var connection = dataSource.getConnection()) {
        var result = connection.createStatement().executeQuery(
            "SELECT ID, AUTHOR, MD5SUM FROM DATABASECHANGELOG WHERE EXECTYPE = 'EXECUTED'"
        );

        // If validate() passes, no mismatch exists
        // This test simply verifies validate runs without exception
        // The real validation is in the CI gate (Article 19)
    }

    // Validate via Spring Liquibase
    assertThatCode(() -> liquibase.afterPropertiesSet())
        .describedAs("Liquibase validate should pass with no checksum mismatches")
        .doesNotThrowAnyException();
}

Testing Rollback Blocks

Test that rollback SQL exists and executes for every changeset added in a PR. This mirrors the updateTestingRollback CI gate but runs within the test suite:

@Test
void allPendingChangesetsShouldHaveRollback() throws LiquibaseException {
    // This test only catches issues if there ARE pending changesets
    // In a normal test run, all changesets have been applied by Spring startup
    // Use this in a test that runs against an EMPTY database to catch new changesets
    try (var connection = dataSource.getConnection()) {
        var database = DatabaseFactory.getInstance()
            .findCorrectDatabaseImplementation(new JdbcConnection(connection));

        var liquibaseInstance = new Liquibase(
            "db/changelog/db.changelog-master.yaml",
            new ClassLoaderResourceAccessor(),
            database
        );

        // If any changeset lacks rollback, this throws
        assertThatCode(() ->
            liquibaseInstance.futureRollbackSQL(
                new OutputStreamWriter(OutputStream.nullOutputStream())
            )
        ).doesNotThrowAnyException();
    }
}

Test Structure Recommendation

src/test/java/com/example/
├── config/
│   └── TestcontainersConfig.java      ← Shared MySQL container bean
├── unit/                               ← Pure unit tests (Mockito, no Spring)
│   ├── UserServiceTest.java
│   └── OrderServiceTest.java
├── repository/                         ← Repository tests (Testcontainers + Liquibase)
│   ├── UserRepositoryTest.java
│   └── OrderRepositoryTest.java
├── migration/                          ← Migration-specific tests
│   ├── MigrationIdempotencyTest.java
│   └── MigrationRollbackTest.java
└── api/                                ← Full stack API tests (Testcontainers)
    └── UserControllerIntegrationTest.java

src/test/resources/
├── application-test.yml               ← Test-specific Liquibase config
└── db/data/
    └── test-seed.sql                  ← Optional: test-only seed data

Common Mistakes

Using H2 for MySQL-specific schemas: If your schema uses ENUM, FULLTEXT, JSON, ON UPDATE CURRENT_TIMESTAMP, or any MySQL function — H2 will silently degrade or fail. Switch to Testcontainers for any test that exercises these features.

Not using @Transactional with shared containers: Without @Transactional, test data from one test method persists into the next. Tests become order-dependent and intermittently fail. Add @Transactional to repository tests or explicitly delete test data in @AfterEach.

Spinning up a new container per test class: Without withReuse(true) and the testcontainers reuse property, a 200-test suite spawns 200 containers. The test suite takes 30 minutes. With reuse, it takes 2 minutes.

Best Practices

Testcontainers for all SQL-touching tests — the extra startup time (amortized with reuse) is worth the fidelity
@ServiceConnection in Spring Boot 3.1+ — eliminates @DynamicPropertySource boilerplate entirely
withReuse(true) and testcontainers.reuse.enable=true — keeps a single MySQL container alive for the entire test session
@Transactional on repository tests — automatic rollback keeps the database clean between tests
Migration-specific test class — explicit tests for idempotency and rollback availability, separate from feature tests
Match MySQL version in tests to production — new MySQLContainer<>("mysql:8.0.36") pinned to the exact patch version

What You’ve Learned

H2 silently degrades MySQL-specific features — not suitable for testing schemas with ENUM, FULLTEXT, or JSON
Testcontainers runs real MySQL in Docker — high fidelity, manageable performance with container reuse
@ServiceConnection (Spring Boot 3.1+) auto-wires the container URL into the Spring context
Liquibase runs automatically at Spring context startup — migrations are validated on every test run
withReuse(true) shares one container across all test classes — essential for large test suites
@Transactional on test classes rolls back data after each test — keeps the database state clean

Next: Article 21 — Kubernetes Deployments: Jobs, Init Containers, and Helm Hooks — running Liquibase migrations safely in a Kubernetes environment before application pods start.