Lesson 03 · Test Databases

Beyond the 1Z0-830 exam

To test data access you need a database to test against. The two mainstream choices — an in-memory H2 and a real engine in a throwaway Docker container (Testcontainers) — trade fidelity against speed and setup. Choosing well is the SDET skill here.

Objectives

After this lesson you will be able to:

• Spin up an in-memory H2 database for fast, self-contained tests.
• Use Testcontainers to test against the real engine.
• Weigh the trade-off and seed schema/data reliably.

In-memory H2 — fast and self-contained

H2 is a pure-Java database that runs inside your test JVM — no install, no Docker. A unique in-memory URL per test gives full isolation:

// fresh, isolated database per test (this module's lab):
String url = "jdbc:h2:mem:" + UUID.randomUUID() + ";DB_CLOSE_DELAY=-1";
try (Connection conn = DriverManager.getConnection(url)) {
    new UserDao(conn).createSchema();
    // ...
}   // closing the last connection drops the in-memory DB

• Fast (milliseconds), zero external dependencies → safe in the default mvn test.
• DB_CLOSE_DELAY=-1 keeps the DB alive while the connection is open; closing it cleans up.
• H2 has a PostgreSQL/Oracle compatibility mode (;MODE=PostgreSQL) that narrows the dialect gap — but doesn't close it.

Trap — H2 is not your production database

H2 is a different engine. SQL that works on H2 can fail on Postgres and vice-versa: vendor-specific types, functions, sequences, JSON/array columns, locking, and constraint behaviour all differ. A suite that's green on H2 can still break in production. Use H2 for fast feedback on portable SQL; verify engine-specific behaviour against the real thing.

Testcontainers — the real engine, disposable

Testcontainers starts a real database in a Docker container for the test, then throws it away:

@Testcontainers
class UserRepoIT {
    @Container
    static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:16");

    @Test
    void worksOnRealPostgres() throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                db.getJdbcUrl(), db.getUsername(), db.getPassword())) {
            // ...test against actual Postgres 16...
        }
    }
}

• Highest fidelity — it is the production engine (Postgres, MySQL, Oracle, …).
• Reusable for anything containerizable: Kafka, Redis, even a browser (Module 18).
• Cost: needs Docker available, and containers take seconds to start — too slow for the fast/unit gate.

Lab policy — why this is illustrative only

Per the Part B lab policy, Testcontainers labs need Docker and are excluded from the default mvn test (a JUnit tag / Maven profile). This module's runnable lab therefore uses H2 so the course stays "green by default"; the Testcontainers snippet above is for reading, not for the default build.

Choosing — and seeding

	H2 (in-memory)	Testcontainers
Fidelity	approximate dialect	exact production engine
Speed	ms	seconds (container startup)
Setup	none	Docker required
Best for	fast unit-ish DB tests, portable SQL	integration tests, engine-specific SQL

Use both: H2 for the fast feedback loop, Testcontainers for the integration layer of the pyramid (Module 15). Either way, seed deterministically — run a schema script (or a migration tool like Flyway/Liquibase) and insert known data in setup, so every run starts from an identical, known state.

Key Takeaways

• H2 in-memory is fast, pure-Java, and self-contained — ideal for the default test run; isolate with a unique URL per test.
• H2 is not your production engine — dialect/feature differences mean green-on-H2 ≠ correct on Postgres; verify engine-specific SQL for real.
• Testcontainers runs the real engine in disposable Docker — highest fidelity, but needs Docker and is slower (kept out of the fast gate).
• Use both: H2 for fast feedback, Testcontainers for integration.
• Seed deterministically (schema script or Flyway/Liquibase + known data) so tests start from a known state.

Lesson Quiz

Lesson Quiz · Test Databases0 / 5

1. Main advantage of in-memory H2 for tests?

   • AIt's identical to Postgres
   • BFast, pure-Java, no Docker — safe in the default test run
   • CIt needs no schema
   • DIt prevents SQL injection

2. Why can a suite that's green on H2 still fail in production on Postgres?

   • AH2 is slower
   • BH2 is a different engine — dialect, types, functions, and behaviour differ
   • CIt can't happen
   • DPostgres doesn't support SQL

3. What does Testcontainers provide?

   • AA faster in-memory DB
   • BA real database engine in a disposable Docker container — highest fidelity
   • CA mocking framework
   • DA connection pool

4. Why are Testcontainers tests kept out of the default fast test run?

   • AThey always fail
   • BThey require Docker and take seconds to start — too slow for the fast/unit gate
   • CJUnit can't run them
   • DThey don't use JDBC

5. How should you seed a test database for repeatability?

   • ARely on data left by previous tests
   • BRun a schema script (or Flyway/Liquibase) and insert known data in setup
   • CInsert random data each run
   • DNever seed it

---

Next: Database Testing & Assertions.