Testing the Persistence Layer

Test against a real database

Persistence code needs tests that run real SQL. @DataJpaTest starts a slice of Spring Boot with only JPA components (entities, repositories, EntityManager, Flyway), wraps each test in a transaction that is rolled back afterwards, and by default replaces the datasource with an embedded database such as H2. Embedded databases differ from production in SQL dialect, types and constraints, so mappings and native queries that pass on H2 can fail on PostgreSQL. Prefer Testcontainers, which starts a real database in a Docker container for tests; Spring Boot 3.1+ supports it neatly with @ServiceConnection, and @AutoConfigureTestDatabase(replace = Replace.NONE) keeps the real datasource. Use TestEntityManager or the EntityManager with flush() and clear() before assertions, so you test what actually reached the database rather than objects still in the persistence context. Test mappings (save and reload), custom queries, constraints, optimistic locking conflicts and, for critical paths, the number of queries executed to catch N+1 regressions.

A repository test with Testcontainers

flush and clear force a real round trip to the database.

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class CustomerRepositoryTest {

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

    @Autowired CustomerRepository customers;
    @Autowired TestEntityManager em;

    @Test
    void findsByEmailIgnoringCase() {
        customers.save(new Customer("Asha Rao", "Asha@Example.com"));
        em.flush();
        em.clear();                                     // next read must hit the database

        Optional<Customer> found = customers.findByEmailIgnoreCase("asha@example.com");

        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Asha Rao");
    }

    @Test
    void rejectsDuplicateEmail() {
        customers.saveAndFlush(new Customer("A", "dup@example.com"));
        assertThatThrownBy(() -> customers.saveAndFlush(new Customer("B", "dup@example.com")))
            .isInstanceOf(DataIntegrityViolationException.class);
    }
}

Flush and clear before asserting

Without flush() and clear(), a test may read the entity straight from the first-level cache and pass even though the mapping or query is broken. Clearing forces Hibernate to load from the database.

Quick check: Why is testing JPA code against a real database (e.g. via Testcontainers) preferred over H2?

• Real databases match production dialect, types and constraints, so tests catch issues H2 would miss
• H2 is not supported by Spring
• Testcontainers is faster than in-memory databases in every case
• H2 cannot run JPQL