How do you test database-dependent code — real test containers, mocks, or an in-memory database?

#Three Ways to Test Code That Talks to a Database

Ask ten engineers how they test a repository class and you'll get three answers, often from the same engineer depending on which layer they're testing: mock the database, run against an in-memory database, or spin up a real one in a container. They are not interchangeable, and picking the wrong one for the wrong layer is one of the most common ways a bug slips through a "well-tested" service.

#Mocks: Fast, but They Test Your Assumptions, Not Your SQL

Mocking the repository interface (or the database driver) is the right tool when you're testing business logic that happens to depend on persistence, not the persistence itself. A pricing engine that calls orderRepository.findById(id) and then applies a discount doesn't need a real database to prove the discount math is correct — a stub returning a canned Order is faster and clearer. The failure mode is using mocks to test the query itself: a mock always returns exactly what you told it to return, so a broken WHERE clause, a wrong join, or a typo in a column name will pass every mocked test and only blow up the first time it touches real data.

#In-Memory Databases: Convenient, and Quietly Lying to You

H2 and similar in-memory engines are attractive because they need no Docker, start in milliseconds, and ship "compatibility modes" that claim to emulate PostgreSQL?, MySQL, or Oracle. In practice the emulation is syntactic, not semantic. Common divergences that bite teams in production:

• JSON/JSONB: H2's JSON support doesn't implement PostgreSQL's jsonb operators (->, ->>, @>) with the same indexing or containment semantics.
• Upserts: PostgreSQL's INSERT ... ON CONFLICT DO UPDATE behaves differently from H2's MERGE — constraint names, partial indexes, and EXCLUDED references don't map cleanly between the two.
• Case sensitivity: unquoted identifiers fold differently between H2's default mode and Postgres, so a query that "just works" in the test suite can fail against the real schema.
• Locking and isolation: row-level locking, SELECT ... FOR UPDATE, and isolation-level behavior under concurrent access are not faithfully reproduced by an in-memory engine.

A test suite that is 100% green against H2 can still ship a query that has never once actually run against the database engine that is running in production.

#Testcontainers: Real Engine, Still Hermetic

Testcontainers spins up the actual production database — same image, same version — inside a Docker container scoped to the test run, and tears it down afterward. You get real fidelity without a shared, stateful dev database that tests can corrupt for each other. The cost is startup time, usually mitigated with a singleton container pattern: start one container for the whole test process and reuse it across test classes instead of paying the boot cost per class.

Wrap long lines
@Testcontainers
class OrderRepositoryTest {

    companion object {
        @Container
        @JvmStatic
        val postgres: PostgreSQLContainer<*> = PostgreSQLContainer("postgres:16-alpine")
            .withDatabaseName("orders_test")
            .withUsername("test")
            .withPassword("test")
            .withReuse(true) // singleton pattern: survives across test classes

        @JvmStatic
        @DynamicPropertySource
        fun registerProps(registry: DynamicPropertyRegistry) {
            registry.add("spring.datasource.url", postgres::getJdbcUrl)
            registry.add("spring.datasource.username", postgres::getUsername)
            registry.add("spring.datasource.password", postgres::getPassword)
        }
    }

    @Autowired
    lateinit var repository: OrderRepository

    @Test
    fun `upsert updates metadata in place instead of creating a duplicate row`() {
        repository.upsert(orderId = "ord-1", metadataJson = """{"tags": ["urgent"]}""")
        repository.upsert(orderId = "ord-1", metadataJson = """{"tags": ["urgent", "vip"]}""")

        assertEquals(1, repository.countByOrderId("ord-1"))
        assertTrue(repository.findById("ord-1").metadataJson.contains("vip"))
    }
}

// The repository under test relies on a real PostgreSQL-only feature:
@Query(
    value = """
        INSERT INTO orders (order_id, metadata)
        VALUES (:orderId, CAST(:metadataJson AS JSONB))
        ON CONFLICT (order_id)
        DO UPDATE SET metadata = EXCLUDED.metadata
    """,
    nativeQuery = true,
)
fun upsert(orderId: String, metadataJson: String)

This test would pass against H2 running in a PostgreSQL-compatibility mode with a rewritten, simplified version of the query — and tell you nothing about whether the real ON CONFLICT clause, the real JSONB cast, and the real unique constraint actually cooperate. Run against Testcontainers, it exercises the exact SQL that will run in production.

#Picking the Right Tool Per Layer

The strongest interview answer draws the line explicitly: mock at the boundary of business logic, use Testcontainers whenever the thing under test is the query or the schema itself, and treat an in-memory database as a convenience for wiring checks rather than a source of truth about correctness.

🧠 Check your understanding
0/2 · 0/2 answered
1. Why can a test suite pass 100% against an H2 in-memory database and still fail against production PostgreSQL?
H2 cannot run inside CI pipelinesH2 is slower than PostgreSQL, so timing-sensitive tests failH2's compatibility modes emulate SQL syntax but not full semantics — JSONB operators, upserts, and locking behave differentlyPostgreSQL requires a paid license that CI environments don't have
2. What is the 'singleton container' pattern in Testcontainers used for?
Preventing more than one developer from running tests simultaneouslyStarting one container per test method to guarantee isolationReusing a single running container across multiple test classes to avoid repeated startup costEnsuring only one row exists in the test database at a time