Repository tests with @DataJpaTest and Testcontainers

The problem with testing repositories against H2

When you test a Spring Data JPA repository using the default @DataJpaTest setup, Spring Boot automatically configures an in-memory H2 database. It's fast, it requires no external infrastructure, and it works great for your CI/CD pipeline. Except it doesn't actually test what your code will do in production.

H2 is convenient, but it's not your database. If your application runs on PostgreSQL 16 in production, you need to test against PostgreSQL 16. The SQL dialects differ. Functions behave differently. String comparisons, numeric precision, date handling, and custom types all have subtle incompatibilities. A query that works perfectly in H2 might fail, truncate data, or return wrong results when it hits your real database.

We've all seen it: a test passes locally and in the CI pipeline, but the feature breaks the moment it touches production. Often the root cause traces back to repository tests that validated logic against the wrong database engine. Repository tests with @DataJpaTest and Testcontainers solve this by spinning up a real, containerized instance of your actual database.

Why dialect differences matter more than you think

Let's say your repository has a query like this:

@Query("SELECT o FROM Order o WHERE CAST(o.amount AS STRING) LIKE :pattern")
List<Order> findByAmountPattern(@Param("pattern") String pattern);

In H2, this works. In PostgreSQL, you might need CAST(o.amount AS VARCHAR) instead, or use the ::text operator. The test passes in H2, fails in production, and now you're debugging production data at 2 AM.

Or consider boolean columns. H2 treats them one way. PostgreSQL another. Pagination with OFFSET and LIMIT behaves identically, but complex sorting combined with custom functions might not. Aggregate functions, window functions, JSON operators—the list goes on.

The deeper issue: H2 is too forgiving. It's designed to be compatible with many databases, which means it smooths over differences rather than enforcing them. Your production database is strict. Testing against Postgres, MySQL, or whatever you actually use means you catch these issues weeks before deployment, not weeks after.

Introducing Testcontainers for real database testing

Testcontainers is a Java library that manages Docker containers during your test lifecycle. You define a container in your test class—PostgreSQL, MySQL, MongoDB, whatever—and Testcontainers spins it up before tests run and tears it down after. Your tests run against a real, isolated database instance.

Add the dependency first:

// build.gradle
testImplementation 'org.testcontainers:testcontainers:1.19.7'
testImplementation 'org.testcontainers:postgresql:1.19.7'
testImplementation 'org.springframework.boot:spring-boot-testcontainers'

Now your repository test looks like this:

import org.springframework.boot.test.autoconfigure.data.jdbc.DataJdbcTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.test.context.testng.AbstractTestNGSpringContextTests;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

@DataJpaTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @Autowired
    OrderRepository orderRepository;

    @Test
    void findActiveOrdersReturnsCorrectResults() {
        Order order = new Order();
        order.setStatus("ACTIVE");
        order.setAmount(BigDecimal.valueOf(99.99));
        orderRepository.save(order);

        List<Order> results = orderRepository.findByStatus("ACTIVE");

        assertThat(results).hasSize(1);
        assertThat(results.get(0).getAmount()).isEqualByComparingTo(BigDecimal.valueOf(99.99));
    }
}

The @Testcontainers annotation activates the Testcontainers extension. The @Container annotation marks the static field as a container to be managed. The @ServiceConnection annotation tells Spring Boot to automatically wire the container's connection details into your test's DataSource.

That's it. No manual URL construction, no hardcoded ports, no extra configuration. Spring Boot detects the container and configures your data source automatically.

Automatic schema migrations with Flyway

Your repository tests aren't just checking queries in isolation. They're checking queries against your actual schema. If your production migrations add a column, create an index, or rename a table, your tests need to see that same schema.

Flyway runs automatically during test startup. When Spring Boot initializes, it discovers your migration files in src/test/resources/db/migration and applies them to the Testcontainers database before your first test runs.

This means your test database is structurally identical to your production database. A test that validates a complex JOIN against multiple tables will verify that all those tables exist, have the right columns, and have the right indexes—exactly as they will in production.

@DataJpaTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withInitScript("init.sql");

    @Test
    void complexJoinQueryWorks() {
        // Flyway migrations have already run
        // Your schema is identical to production
        // This test actually validates against that real schema
    }
}

If a migration is pending or hasn't run yet, Flyway catches it during the test setup phase, not at 3 AM when a customer hits the broken feature.

Sharing containers across multiple test classes for speed

Spinning up a new PostgreSQL container for every test class is thorough, but it adds overhead. A typical PostgreSQL startup takes 5-10 seconds. With 50 test classes, that's hundreds of seconds of pure infrastructure setup. Your CI pipeline suffers.

Testcontainers supports container reuse. If a container with the same image already exists and is healthy, subsequent tests reuse it instead of creating a new one.

Create a shared test configuration:

@TestConfiguration
public class TestContainersConfig {

    @Bean
    @ServiceConnection
    public PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>("postgres:16")
            .withReuse(true);
    }
}

Then reference it in your test classes:

@DataJpaTest
@Import(TestContainersConfig.class)
class OrderRepositoryTest {

    @Autowired
    OrderRepository orderRepository;

    @Test
    void findByStatus() {
        // Uses the shared container
    }
}

@DataJpaTest
@Import(TestContainersConfig.class)
class PaymentRepositoryTest {

    @Autowired
    PaymentRepository paymentRepository;

    @Test
    void findByOrderId() {
        // Reuses the same container
    }
}

With withReuse(true), the first test class creates the container. Subsequent classes find it running and attach to it. Test execution becomes much faster without sacrificing database engine accuracy. The tradeoff is that data from one test class might leak into another if tests don't clean up properly, so make sure each test rolls back its changes via @Transactional or explicit teardown.

Practical patterns for repository test organization

A well-structured repository test focuses on one repository and validates its custom queries, not Spring Data boilerplate. Spring Data's generated finder methods are tested by Spring's own suite; you test the custom logic.

@DataJpaTest
@Testcontainers
class OrderRepositoryTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @Autowired
    OrderRepository orderRepository;

    @Autowired
    TestEntityManager testEntityManager;

    @Test
    void findRecentActiveOrdersFiltersByStatusAndDate() {
        Order old = new Order();
        old.setStatus("ACTIVE");
        old.setCreatedAt(Instant.now().minus(30, ChronoUnit.DAYS));
        testEntityManager.persistAndFlush(old);

        Order recent = new Order();
        recent.setStatus("ACTIVE");
        recent.setCreatedAt(Instant.now());
        testEntityManager.persistAndFlush(recent);

        List<Order> results = orderRepository.findRecentActiveOrders(Instant.now().minus(7, ChronoUnit.DAYS));

        assertThat(results)
            .hasSize(1)
            .extracting(Order::getId)
            .contains(recent.getId());
    }

    @Test
    void findByAmountRangeHandlesDecimalPrecisionCorrectly() {
        Order order = new Order();
        order.setAmount(BigDecimal.valueOf(100.555));
        testEntityManager.persistAndFlush(order);

        List<Order> results = orderRepository.findByAmountBetween(
            BigDecimal.valueOf(100.55),
            BigDecimal.valueOf(100.56)
        );

        assertThat(results).hasSize(1);
    }
}

Use TestEntityManager to set up test data instead of directly calling repository save methods. This ensures Hibernate applies all its validation and lifecycle callbacks, keeping your test realistic. Explicitly test edge cases—decimal precision, date boundaries, null handling, empty results—because these are where database engine differences hide.

Common mistakes

Forgetting @Transactional on tests. By default, each test runs in a transaction and rolls back. Without it, test data persists and pollutes subsequent tests. Add @Transactional at the class level or explicitly manage cleanup.

Testing business logic in repository tests. Repository tests verify that your queries work against the real schema. They don't verify business logic. For that, use service layer tests with mocked repositories. Mixing concerns makes tests harder to maintain.

Not accounting for Flyway version drift. If your test migrations differ from your production migrations, tests pass but production fails. Keep migration files in sync. Use the same Flyway configuration for both environments.

Setting container reuse globally without cleanup strategy. Reused containers can hide stateful bugs. Document your cleanup strategy clearly. Use @Transactional consistently so tests don't interfere with each other.

Ignoring performance implications. Testcontainers add overhead compared to H2. A 50-test suite might run 30 seconds slower. This is a worthwhile tradeoff for accuracy, but be aware. Consider running container-based tests in CI only, with H2 tests in fast local feedback loops if your team needs 5-second test runs.

Key takeaways

• H2 hides dialect differences that only appear in production. Real database testing catches these weeks before deployment, not weeks after.
• Testcontainers spins up a real, isolated database container for each test suite. Spring Boot's @ServiceConnection wires the connection automatically.
• Flyway migrations run during test setup, ensuring your test schema is identical to production. Schema-level bugs surface immediately.
• Container reuse with withReuse(true) dramatically speeds up multi-class test suites while maintaining accuracy. Combine it with @Transactional for isolation.
• Repository tests should focus on custom queries and edge cases against the real schema, not business logic. Use service layer tests for orchestration and business rules.
• The performance tradeoff is worth it. Catching a bug in a Postgres-specific query during development is infinitely cheaper than fixing it in production.