Lesson 04 · Database Testing & Assertions

Beyond the 1Z0-830 exam

Having a test database isn't enough — the test must set up known data, assert on the actual stored state, and leave the database clean so it can run again and alongside others. This lesson is the discipline that makes DB tests trustworthy.

Objectives

After this lesson you will be able to:

• Arrange known data and verify persisted state (not just "no exception").
• Keep DB tests idempotent and isolated.
• Test transaction outcomes (commit and rollback).

Arrange–Act–Assert against the database

A DB test follows the usual shape, but the Assert step reads back from the database:

// Arrange: known starting state
long id = dao.insert("Ada", "[email protected]");

// Act: the behaviour under test (already done by insert here)

// Assert: query the stored state — this proves the write happened
assertThat(dao.findByEmail("[email protected]"))
    .get().extracting(User::name).isEqualTo("Ada");
assertThat(dao.count()).isEqualTo(1);

Gotcha

"The method returned without throwing" is not a passing DB test. An insert can silently no-op, hit the wrong table, or be rolled back by an outer transaction. Read the data back and assert on it — that's the only proof the state actually changed.

Idempotent, isolated tests

A database test must produce the same result every run and not depend on (or pollute) other tests. Strategies, cheapest first:

Strategy	How	Trade-off
Fresh schema per test	new in-memory DB / CREATE in @BeforeEach	simplest; the lab uses this
Transaction rollback	begin in setup, rollback() in teardown	fast, no real writes persist; needs care with auto-commit
Explicit cleanup	DELETE/TRUNCATE in @AfterEach	works against shared DBs; order-sensitive

The lab uses a unique in-memory database per test (jdbc:h2:mem:<random>), so tests can't see each other's rows and never need cleanup:

@BeforeEach void setUp() throws SQLException {
    conn = DriverManager.getConnection("jdbc:h2:mem:" + UUID.randomUUID() + ";DB_CLOSE_DELAY=-1");
    dao = new UserDao(conn);
    dao.createSchema();
}
@AfterEach void tearDown() throws SQLException { conn.close(); } // drops the DB

Trap — order-dependent DB tests

A test that assumes rows left by an earlier test (or asserts count() == 3 because "the others inserted some") is fragile: it breaks when run alone, in a new order, or in parallel. Each test must establish its own known state. This is the FIRST "Isolated/Repeatable" principle (Module 14), applied to data.

Test both commit and rollback paths

Transaction logic has two outcomes; assert on both. The lab covers them:

// Rollback path: a duplicate email aborts the whole pair
boolean committed = dao.insertPairAtomically("Good", "good@x", "Bad", "dup@x");
assertThat(committed).isFalse();
assertThat(dao.findByEmail("good@x")).isEmpty();   // atomicity: NOTHING persisted
assertThat(dao.count()).isEqualTo(1);

// Commit path: both rows land
assertThat(dao.insertPairAtomically("A", "a@x", "B", "b@x")).isTrue();
assertThat(dao.findAll()).extracting(User::name).containsExactly("A", "B");

The rollback assertion is the important one — it proves atomicity, not just that the happy path works. Untested rollback logic is where data-corruption bugs hide.

SDET note

This closes Part B's data track: the same principles you learned for unit tests — determinism (Module 14), isolation and the pyramid (Module 15) — apply to data, just with a database as the fixture. Keep DB tests in the integration tier, tag them, and let CI run them separately from the fast unit gate (Module 20).

Key Takeaways

• Assert on persisted state by reading it back — "no exception" proves nothing.
• Keep tests idempotent and isolated: fresh schema per test, transaction rollback, or explicit cleanup — the lab uses a unique in-memory DB per test.
• Never write order-dependent DB tests; each establishes its own known state (FIRST applied to data).
• Test both commit and rollback paths; the rollback assertion proves atomicity.
• DB tests belong in the integration tier — tagged and run apart from the fast unit gate.

Lesson Quiz

Lesson Quiz · Database Testing & Assertions0 / 4

1. What's the best evidence that a save() actually persisted data?

   • AIt didn't throw an exception
   • BRead the row back from the database and assert on the stored values
   • CThe log says 'saved'
   • DThe method returned true

2. How does this module's lab keep DB tests isolated?

   • AIt shares one database across all tests
   • BA unique in-memory H2 database per test (random URL), dropped in teardown
   • CIt never writes data
   • DIt runs tests serially only

3. Why is a test that asserts count() == 3 because earlier tests inserted rows fragile?

   • Acount() is slow
   • BIt's order-dependent — it breaks when run alone, reordered, or in parallel
   • Ccount() can't be asserted
   • DThree is too many

4. When testing transactional logic, which path is most important to assert?

   • AOnly the commit (happy) path
   • BBoth — especially the rollback path, which proves atomicity
   • CNeither
   • DOnly performance

---

Next: Module 16 Mini-Exam, then Module 17 · API & Web-Service Testing. This module's lab is in labs/src/main/java/com/jse21/m16_databases/.