Your application talks to PostgreSQL or MySQL in production. Its integration tests need a database too. There are two popular ways to give them one: an embedded in-memory database such as H2 or HSQLDB, or a throwaway container running the same engine as production, usually started through a library like Testcontainers. This is a comparison, and I will tell you which I pick.
What the in-memory option offers
Convenience, mainly. Nothing to install: the database is a library on the classpath, it starts inside the test process in well under a second, it vanishes when the tests end, and it works on any laptop or CI agent without Docker. For a team of freshers on locked-down office machines, that matters. Tests run fast, so people actually run them.
H2 and others offer compatibility modes that imitate the syntax of a few real engines, and this lulls people into trust. Compatibility here is partial by design. Differences that commonly bite:
- Locking and isolation. How concurrent transactions block or fail differs between engines. A race that appears in production may simply not occur in the embedded database.
- Types and functions. JSON columns, array types, date arithmetic, text-search features and engine-specific functions may be missing or behave differently.
- Case sensitivity and collation. Whether a search for “asha” matches “Asha”, and how strings sort, depends on engine and settings. MySQL’s default collations are case-insensitive, while PostgreSQL’s comparisons are case-sensitive by default.
- Constraint and error behaviour. Error codes, messages and exactly when a constraint violation is raised vary, which matters when your code catches a specific error.
- Migrations. Schema scripts written for production may use syntax the embedded engine cannot run, so teams keep a second, simplified schema just for tests. Now the tests are not testing the schema you ship.
- Query plans. Index use and performance are different, so slow queries never show up.
Each difference produces one of two bad outcomes: a test fails for a reason that does not exist in production (annoying), or a test passes for a bug that does exist in production (expensive).
What the real engine in a container offers
You run the genuine PostgreSQL image, same major version as production, and apply your real migration scripts to it. Behaviour matches because the software is the same. Tests catch the migration that fails on the real engine, the query that uses an engine-specific feature wrongly, the unique constraint that triggers on the second concurrent insert. For anything involving the database as a collaborator, that is what you need.
The costs are real. Starting a container takes seconds, often several, so you need to share one container across the whole test run instead of starting it per test class. Testcontainers needs a container runtime such as Docker available, which means your CI agents and developer laptops must support it, and some corporate environments make that awkward. Tests also need data cleanup between cases, by rolling back a transaction per test or truncating tables, so they do not depend on each other’s leftovers. And the first run on a machine pulls an image of a few hundred megabytes.
Side by side
| Concern | Embedded in-memory | Real engine in container |
|---|---|---|
| Start-up time | Under a second | Seconds, paid once per run if shared |
| Fidelity to production | Partial, varies by feature | High, same engine and version |
| Environment needs | None | Container runtime |
| Migrations tested | Often a separate schema | The real scripts |
| Concurrency behaviour | Differs | Matches production |
My choice
Use the real engine for integration tests. The whole value of an integration test is that it exercises the real interaction between your code and its dependency. Replace the dependency with an imitation and you have partly turned it back into a unit test while still paying the cost of the setup. The speed argument is weaker than it sounds: with a shared container, a hundred repository tests typically run in a few seconds of actual test time, and the long pole is the one-off start.
Keep the embedded database where it is honest: pure unit-level tests of business logic should not touch a database at all, and for simple spikes or demos an embedded database is fine. If a project is truly constrained and must use one, restrict the code under test to standard SQL features, run the same suite nightly against the real engine, and treat any difference as a finding.
Where this advice weakens: if your application is deliberately portable across several database products, you need tests against each, and a matrix of containers may be the cost of doing business. If your database is a managed cloud service with proprietary behaviour, a container of the open-source engine is closer than H2 but still not identical, so keep a thin layer of tests against a real cloud instance in a pre-release stage.
Leave a Reply