Mocking database calls with H2 and Spring Test

Spring Boot applications spend a surprising amount of time talking to a database, and a surprising amount of test flakiness starts there. A query that works on a developer's laptop in Sydney can drift out of sync with the schema deployed to staging, especially when the team relies on shared dev databases refreshed overnight. Replacing those calls with mocks or stubs is one way to keep tests fast, but it usually means rewriting the production code path. Using an embedded H2 database in Spring tests gives you a middle ground: the same JPA repositories, the same queries, the same transactions, all running against an in-memory engine that boots in a couple of seconds and disappears after the suite.

For teams across Australia, this trade-off matters more than it does in markets with cheap cloud spend. Atlassian's Sydney campus hosts many of the test rigs that feed Bitbucket Cloud, and engineers there have shared how they layer H2 into slice tests for speed while reserving Testcontainers for deeper integration jobs. The same pattern shows up at REA Group in Melbourne and at the big four banks in Sydney and Melbourne, where APRA's CPS 230 requirements push teams toward deterministic, repeatable test data. No dramas if you set it up right; chaos if you don't.

Why an in-memory database fits unit and slice tests

The job of a unit or slice test is to exercise one layer of the system in isolation. For the persistence layer, that layer is the JPA repositories, the entity mappings and the query methods. Mocking those with Mockito removes the database entirely, but it also removes the part of the code most likely to break under a schema change. A repository mock that returns a hand-built entity will happily pass even when the actual SQL blows up in production.

H2 sits in a useful middle spot. It is a real SQL engine, written in Java, that runs entirely inside the JVM. Spring Boot ships with a starter that detects H2 on the classpath and configures an embedded DataSource automatically. Queries are parsed and executed against tables that live in memory, which means that repository code, JPQL and native SQL all run for real. A failing index, a wrong column type or a missing constraint surfaces during the test rather than during a 2am page.

The catch is fidelity. H2 understands most of what Hibernate generates, but it does not understand every dialect. A Postgres-specific JSONB column or a MySQL ON DUPLICATE KEY UPDATE clause will not behave the same way in H2. That is fine for a slice test focused on a single repository, but it means H2 cannot fully stand in for the production database on its own. Many teams in Brisbane and Canberra treat it as a first line of defence and reach for Testcontainers when they need to validate cross-database behaviour.

Approach	Speed	Fidelity	Best for	Drawback
H2 in-memory with @DataJpaTest	Sub-second context	Medium, depends on MODE	Repository and query logic	Diverges from Postgres-specific features
Testcontainers with real Postgres or MySQL	5–15 s context	High	End-to-end and migration tests	Needs Docker on the build agent
@MockBean repositories	Fastest	Low	Service-layer unit tests	Misses SQL and mapping bugs
In-memory Map repositories	Fastest	Very low	Pure logic tests with no JPA	Removes JPA itself from the loop

Wiring H2 into a Spring Boot test slice

Spring Boot's @DataJpaTest annotation is the simplest entry point. It auto-configures an embedded database, applies your entity classes and disables full Spring Boot autoconfiguration, which keeps the context small and the suite quick. By default, @DataJpaTest looks for an embedded database on the classpath and uses it. Add com.h2database:h2 to your test dependencies and H2 is picked up without further wiring.

Once the slice is active, you can drop schema.sql and data.sql files under src/test/resources to shape the schema and seed reference data. For Hibernate-managed schemas, set spring.jpa.hibernate.ddl-auto=create-drop in an application-test.yml profile. The profile keeps production configuration untouched and is a common pattern in Australian teams that run their CI on AWS regions in Sydney (ap-southeast-2) or Melbourne (ap-southeast-4).

If you need to assert against real database state, Spring offers TestEntityManager, a slim wrapper around EntityManager that is auto-wired into the slice. You can persist, flush and find entities without touching the underlying DataSource directly. This is usually enough to verify that a save call wrote the right row, or that a custom query returns the expected projection. For the API layer, pair the slice with @WebMvcTest and @MockBean the repositories, keeping the controller test independent of the database altogether.

Picking the right H2 compatibility mode

H2 ships with a MODE setting that tells it to mimic another database. The common choices are PostgreSQL, MySQL, Oracle, MariaDB and MSSQLServer. Switching modes changes how identifiers are quoted, how sequences are handled and which SQL functions are available. For most Spring Boot applications, the default PostgreSQL mode is a sensible pick because Hibernate's dialect generation lines up well with it.

Setting the mode happens through the JDBC URL: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE. Each option resolves a specific class of mismatch. DATABASE_TO_LOWER mirrors how Postgres stores unquoted identifiers in lower case. CASE_INSENSITIVE_IDENTIFIERS lets you write entity table names in the casing your developers prefer without surprising relation does not exist failures. Australian teams that have moved from Oracle to Postgres often need both flags turned on to keep the schema scripts portable across environments.

Mode does not cover every gap. H2 still does not understand Postgres range types, gen_random_uuid() from pgcrypto, or MySQL's spatial functions. When the entity model leans on those features, a pure H2 test will pass locally and then break in a shared staging environment. The pragmatic answer, used by engineers at Canva in Sydney and at several fintechs in Melbourne, is to keep H2 for fast repository tests and run a smaller Testcontainers suite against the real engine as a nightly job, scheduled after business hours AEST so the build queue is empty by morning.

Replacing the real DataSource with a test-only bean

Sometimes @DataJpaTest is not enough. The code under test might load its own DataSource bean, use multiple datasources or depend on database-specific features that conflict with H2's defaults. In those cases, a @TestConfiguration class lets you swap the bean out for the duration of the test.

The pattern is straightforward. Define a static inner class annotated with @TestConfiguration, expose a DataSource @Bean that wraps new EmbeddedDatabaseBuilder().setType(H2).build(), and import the configuration class with @Import(MyTestConfig.class) on the test class. The @Primary annotation is useful when the production configuration still defines a competing bean. Inside the override, you can also register a BeanPostProcessor or a custom ConnectionFactory if your project uses R2DBC rather than JDBC.

For multi-tenant or sharded applications, the same trick scales. Each tenant's DataSource is replaced with a separately named H2 instance, often pointed at the same in-memory store. Be careful with MODE here: setting it on the global JDBC URL applies to every tenant, which is usually what you want. If you need per-tenant modes, build each URL explicitly through EmbeddedDatabaseBuilder rather than reusing the Spring Boot autoconfiguration.

Limitations that bite teams in production

The first limitation is concurrency. H2's in-memory mode is single-connection by default, which serialises tests that touch the database. Open multiple browser tabs in a parallel @SpringBootTest and you can see deadlocks that do not exist in Postgres. The fix is DB_CLOSE_DELAY=-1 in the JDBC URL, which keeps the schema alive between connections inside the same JVM, or MULTI_THREADED=1 for a real multi-threaded engine.

The second is schema drift. H2 will tolerate types and lengths that Postgres rejects, so a column defined as varchar(255) in H2 might quietly accept 10,000 characters and fail only in production. Running the migration scripts (Flyway or Liquibase) against H2 inside the test catches most of these, but it is worth checking that the migrations actually run. @DataJpaTest does not execute Flyway by default; you need flyway-core on the test classpath and the right spring.flyway.enabled flag.

The third is transaction behaviour. H2's default isolation level is READ_COMMITTED, which matches Postgres but not Oracle's READ_COMMITTED snapshot semantics. Tests that rely on repeatable-read behaviour across two connections can pass locally and fail in Oracle-backed environments. Spring's @Transactional on tests still rolls back changes after each method, which hides some of these differences, but integration tests that use @SpringBootTest(webEnvironment = RANDOM_PORT) and a real HTTP client do not get that rollback for free.

Practical recommendations for a robust setup

• Add H2 to the test scope only, never to main, so production builds never accidentally start an embedded database.
• Use a dedicated application-test.yml profile and put every H2-specific flag there, including MODE, DB_CLOSE_DELAY and any NON_KEYWORDS overrides your schema needs.
• Run your Flyway or Liquibase migrations during the slice test by setting spring.flyway.enabled=true and pointing the URL at the in-memory database.
• Keep the schema script under version control and review it in pull requests the same way you review production migrations.
• Reserve Testcontainers for a smaller suite that runs nightly, and wire it into the CI matrix with a label such as integration so engineers can opt in locally.
• Document the H2 MODE and dialect choices in the test README, especially when the project supports multiple production databases.
• If you operate under APRA's CPS 230 or the ACSC Essential Eight, treat deterministic test data as a control objective and review the data-seeding scripts in the same change as the production migration.

The thing worth remembering is that H2 is a tool, not a replacement for thinking about test data. It buys you fast feedback on the JPA layer and lets you ship repository changes with confidence, but it does not absolve you from running the real database before release. Treat it as the first rung on a testing ladder that ends with Testcontainers, pre-production environments and the careful, auditable data seeding that Australian regulators increasingly expect from teams running critical infrastructure.