My Tests Were Green on the Wrong Database — Moving to Real PostgreSQL with Flyway

In Part 1 I ended with a question I was genuinely sitting with: REST controllers first, or persistence first?

Persistence won — in the project's own ROADMAP order. And it turned out to be the right call, because it exposed something uncomfortable: my repository tests were green, but they were green on the wrong database.

The problem: H2 was hiding the truth

Up to this point, the app's tests ran against H2, an in-memory stand-in. That's fine for fast unit tests. It's not fine as proof that your schema works, because the database your app will actually run on is PostgreSQL — different engine, different constraint behavior, different failure modes.

A test passing on H2 only proves the code works in a world that doesn't exist. So Phase 3 had one job: make the persistence layer real, reproducible, and proven on the real engine.

Shipped: four PRs, one evening

PR #13 — ROADMAP close-out, and one docs-vs-code call: my ROADMAP listed a POOR book condition that my enum didn't have, so I changed the code, not the doc. POOR was appended to BookCondition after FAIR.
PR #14 — Flyway plus the baseline migration.
PR #15 — Environment-based database config and documentation.
PR #16 — Repository tests on real PostgreSQL, plus a matching CI service.
48 tests when the evening ended.

Flyway owns the schema now

The baseline migration, V1_initial_schema.sql, creates all five tables and mirrors the JPA entities exactly — including named constraints, so a failure message points at a real, findable thing instead of an anonymous auto-generated name:

CREATE TABLE book_copies (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
barcode VARCHAR(255) NOT NULL,
is_available BOOLEAN NOT NULL,
times_borrowed INTEGER NOT NULL,
condition VARCHAR(255) NOT NULL,
acquired_date DATE NOT NULL,
book_id BIGINT NOT NULL REFERENCES books (id),
owner_id BIGINT NOT NULL REFERENCES library_members (id),
visibility VARCHAR(255) NOT NULL,
CONSTRAINT book_copy_barcode UNIQUE (barcode)
);

That owner_id foreign key is the ownership work from Part 1, now real in the database — a copy cannot exist without a member who owns it. The other tables follow the same pattern: library_members with a named library_member_email unique constraint, books with book_isbn, and borrowings linking book, copy, and borrower.

The key decision is in the profile that runs against it:

spring.datasource.url=${DB_URL:jdbc:postgresql://localhost:5432/library}
spring.datasource.username=${DB_USERNAME:library}
spring.datasource.password=${DB_PASSWORD:}
spring.datasource.driver-class-name=org.postgresql.Driver
spring.flyway.enabled=true
spring.jpa.hibernate.ddl-auto=validate

ddl-auto=validate is the whole philosophy in one line. Hibernate no longer creates or updates the schema — Flyway applies the migration, and Hibernate checks the entities agree with it, refusing to start if they don't. The migration is the source of truth. Never edit an applied migration; schema changes ship as new V2_..., V3_... files.

Config that doesn't leak secrets

Database settings moved to environment variables with a .env.example in the repo, and the README gained a "Database Setup (PostgreSQL)" section so anyone cloning the project can reproduce the setup from scratch. No credentials live in the code.

One honest detail: there's no Docker on my machine, so the usual Testcontainers route was out. PostgreSQL 17 runs natively on my machine instead. Not the fashionable path — the path that actually works in my setup. The CI side still gets the standard treatment: a postgres:17 service with a health check, matching the test setup.

The test that finally proves something

Only one test class moved off H2 — BookCopyRepositoryTest. The other 45 tests stay where they are; this was never about moving everything, it was about proving the repository layer against the real engine.

The gotcha that would have silently undone the whole thing: @DataJpaTest swaps in an embedded database by default. Without one annotation, my "PostgreSQL test" would have been an H2 test wearing a PostgreSQL costume:

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@TestPropertySource(locations = "classpath:application-pgtest.properties")
class BookCopyRepositoryTest {

Replace.NONE is the line that makes it real. The test profile points at a dedicated library_test database, with Flyway on and ddl-auto=validate — so the test schema is built by the same migration the app uses, not by Hibernate improvising one.

And the test I'm proudest of is a failure. Saving a second copy with a duplicate barcode throws a PersistenceException whose root cause names the real book_copy_barcode unique constraint. That's proof the constraint exists on the real engine — not just in an annotation, not just in H2's approximation of it.

What I learned

1. An in-memory database is a stand-in, not a substitute. It's great for speed, dangerous as proof.

2. Named constraints are documentation. When a test fails, it should tell you exactly which rule broke.

3. validate beats create-drop once a schema exists. Letting the ORM silently rebuild your schema hides drift between your entities and your migrations — the exact drift that breaks you in production.

4. The unglamorous work is the platform. Nobody demos a migration file. But without it, every feature built on top is sitting on a schema nobody can reproduce.

What's next

Persistence is done: externalized config, reproducible migrations, integration tests on the real database. The next chapter is the REST API — controllers and global exception handling on top of the service layer, so this stops being a library only other code can use and starts becoming a platform members can actually call.

I'm working on that today, and it gets its own post next week — after it's actually finished, not before. That's the rule for this series: shipped first, posted second.

If you've moved a project from H2 (or SQLite, or any in-memory stand-in) to a real database, what broke first — the schema, the tests, or your assumptions? I'd genuinely like to hear it.

Code, if you want to follow along or tell me what you'd do differently:
GitHub - github.com/Pekins1/LibraryManagementSystem

— Rahkel