Spring Data JPA test against the deployment database dialect

An in-memory substitute changes the contract

A query that passes against an embedded database can fail against the deployment engine because of SQL functions, locking, collation, JSON operators, generated columns, or index behavior. @DataJpaTest keeps the persistence slice small, while a database container gives the slice a realistic engine. Disable embedded-database replacement for that test. Container lifecycle] and migration ownership] still need explicit setup.

Select tests by risk

Run ordinary mapping and simple query tests quickly. Put a smaller set of dialect-sensitive cases against the deployment engine: unique constraints, pessimistic locks, case-sensitive comparisons, pagination under joins, and migration compatibility. Compare the engine major version with production. A container image is not proof that production extensions, collation or configuration match; record those differences as test assumptions.

Force a database-specific failure

Insert duplicate tenant and external-reference pairs, assert the database rejects the second write, then verify the transaction outcome. For a locking test, use two independent transactions and bounded timeouts, not two reads inside one test transaction. Inspect the SQL emitted by the fetch plan] and the plan for a bounded page].

Implementation contract

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class ReceiptRepositoryDialectTest {
    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> database =
        new PostgreSQLContainer<>("postgres:17");

    @Autowired ReceiptRepository receipts;
}

Cost and verification

A container adds startup time and memory use. Share an appropriate lifecycle across tests and reserve this path for behavior that depends on the real engine; do not trade away isolation by reusing dirty database state without cleanup.

Common Mistakes

• Do not infer deployment SQL behavior from an embedded database alone.
• Do not run two lock participants in the same test transaction.
• Do not assume an engine version match also matches collation, extensions or schema migrations.

Read next

Spring Boot Testcontainers: service connections and cached-context lifecycle, Spring Boot Testcontainers and Flyway: let one owner prepare the test schema, Spring Data JPA test: flush and clear before trusting a read, Spring Data JPA entity graph for a bounded to-one read, Spring Data JPA Page versus Slice: pay for totals only when needed.