H2 does not replace PostgreSQL in tests

1. The H2 trap: a green test is not a guarantee that “everything is OK”

If you are just starting to write data-layer tests, it is very easy to fall into a psychological trap: “the test is green, so I am great, the database is happy, Hibernate is happy.” In reality, the green color means only one thing: in this environment, with this database, and with these settings, the scenario passed. And now pay attention: if the database “in the test” and the database “in prod” are different, then you have effectively verified a different application.

Let us introduce a term that is named explicitly in the lesson plan: the H2 trap — a situation where a test passes on H2 (usually in-memory), but when run on PostgreSQL either the migration, or the native query, or the constraint, or a concurrent scenario breaks. And it breaks not “because you are bad at coding,” but because H2 is not “PostgreSQL on easy mode,” but a separate DBMS with its own rules.

Imagine that you are training a football team in a gym on a parquet floor. Everything looks beautiful: passes are fast, the ball rolls perfectly, people do not fall. And then the team steps onto a wet field. Formally, it is the same game, but reality is different: the traction is different, the ball behaves differently, and suddenly it turns out that the “perfect style” does not work. H2 is such a “gym on parquet.” It is useful for practicing technique, but it is not obliged to match the “wet field of PostgreSQL.”

In our shop-data-jpa course, PostgreSQL is the source of truth for the project. We designed the schema, migrations, constraints, optimistic locking, and the placeOrder() / cancelOrder() scenarios for PostgreSQL. Therefore, tests that are supposed to give a reliable signal should at least be close to PostgreSQL reality. H2 can be an accelerator for feedback, but it should not be the only “judge” deciding whether your data layer is correct.

2. Why H2 is convenient in tests

Before we start criticizing H2 (politely, without toxicity), it is important to admit that H2 is popular not because of a developer conspiracy, but because it really is convenient. It starts fast, often does not require Docker, does not need ports or a separate process, and gives you the feeling that “I can check the repository in 5 seconds and go drink tea.” And that is a great feeling, if you understand the boundaries.

Most often, H2 appears in a project in two ways. First, you explicitly add the com.h2database:h2 dependency to testImplementation, and Spring Boot happily starts an embedded database for tests. Second, you use @DataJpaTest, and by default it likes to replace your DataSource with an embedded one (if available). At that moment, a beginner may sincerely think: “I am testing JPA, so I am testing my database.” But in reality, they are testing JPA+Hibernate on top of H2, and the dialect/behavior is no longer the same.

The strength of H2 is that it gives a fast and cheap signal. If you check that ProductRepository.findByStatus(...) returns the right rows, that @ManyToOne works, that orphanRemoval really deletes the child entity, then H2 is often good enough as a “mechanics check.” Especially if the test task does not depend on PostgreSQL-specific behavior and does not verify SQL dialect details.

But H2 has a weakness: it creates a false sense of compatibility. A dangerous formula appears in your head: “If it works on H2, then it will work on PostgreSQL too.” That is roughly like saying: “If the code runs on my laptop, then it will run on the server.” Sometimes yes. Guarantees? None.

Another subtlety: many people try to “fix” the differences by putting H2 into PostgreSQL mode (MODE=PostgreSQL). That can indeed remove part of the problems (especially syntax), but it does not turn H2 into PostgreSQL. It is more like “the translator’s accent,” not a change of language. Some differences disappear, some remain, and some even become less visible — which, frankly, is even more dangerous: the tests look plausible, but they are checking the wrong thing.

3. Which database is actually running in the test

There is a very simple skill that dramatically improves test quality: in any unclear situation, first find out which database is actually running. It sounds trivial, but in practice it saves hours of life. Because arguing with a test about “why it fails on CI but not locally” is pointless if you have different databases in those two places.

In a service test (@SpringBootTest), you can obtain the DataSource and read the connection metadata. This is not “hacking,” it is normal diagnostics. The moment you see H2 or PostgreSQL in the output, half of the magic disappears.

Mini example of a diagnostic test:

import java.sql.Connection;
import javax.sql.DataSource;
import org.junit.jupiter.api.Test; 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DatabaseProductNameTest {

    @Autowired
    DataSource dataSource; // The connection source that Spring actually started in the test

    @Test
    void printsDatabaseName() throws Exception {
        // Open a real JDBC connection from the application context
        try (Connection c = dataSource.getConnection()) {
            // Quick diagnosis: "PostgreSQL" or "H2"
            System.out.println(c.getMetaData().getDatabaseProductName()); // Example: PostgreSQL
        }
    }
}

Yes, that is println in a test. Yes, in an ideal world we do not print extra stuff. But as a temporary “flashlight,” it works perfectly. And according to the course requirements, if we do print something, we honestly label the approximate output nearby.

Sometimes it is useful to look not only at the “product name,” but also at the JDBC URL, because it often immediately shows which profile and configuration were applied:

import java.sql.Connection;
import javax.sql.DataSource;
import org.junit.jupiter.api.Test; 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DatabaseUrlTest {

    @Autowired
    DataSource dataSource; // Important: this is the DataSource actually used in the test

    @Test
    void printsJdbcUrl() throws Exception {
        // The URL usually gives away the truth instantly: mem H2 or real PostgreSQL
        try (Connection c = dataSource.getConnection()) {
            System.out.println(c.getMetaData().getURL()); // Example: jdbc:postgresql://... or jdbc:h2:mem:...
        }
    }
}

Why do we need this specifically in shop-data-jpa? Because the project is already “production-like”: we have Flyway, constraints, optimistic locking, and scenarios that are sensitive to real PostgreSQL behavior. If the tests accidentally drifted onto H2 (or the other way around), you can get a “green” suite that lies, or a “red” suite that fails not because of the code, but because of an environment mismatch.

And one more practical thought: a very bad habit is to write tests as if the database “just exists somehow.” A normal habit is to know which DataSource you started. Confidence in a test begins not with assertEquals, but with understanding the environment.

4. Dialect and SQL

When we use Spring Data JPA, an illusion appears: “We write in Java, so the database does not matter.” That illusion lasts until the first native query or the first PostgreSQL-specific migration. After that, the database suddenly becomes very important, because you start speaking to it in its native language.

In general, differences between H2 and PostgreSQL are especially noticeable in three places: in DDL migrations, in native SQL queries, and in identifier/case behavior (a surprisingly frequent source of pain).

The most “classic” example is the case of table and column names. By default, PostgreSQL folds unquoted identifiers to lower case. H2 by default likes upper case. This means that the migration create table stock_item (...) in PostgreSQL will create stock_item, while in H2 (in some modes) it may end up as STOCK_ITEM. And your native query from stock_item may suddenly “not find the table.” You will look at the screen and think: “But I just created it, there it is!” And it is there — just in a different case.

Here is what such a repository method looks like at first glance:

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface StockItemRepository extends JpaRepository<StockItem, Long> {

    // Important: this is native SQL, which means a direct contract with a specific DBMS and its dialect
    @Query(value = """
            select *
            from stock_item
            where available_quantity < :limit
            """, nativeQuery = true)
    List<StockItem> findLowStock(int limit); // limit — a parameter of the native query (:limit)
}

On PostgreSQL, this will most likely work exactly as you expect. On H2, it may work, or it may not, depending on the mode and settings. And that “may” is the problem: the test becomes dependent on how carefully you have “pinned” H2 to Postgres-like behavior.

To avoid sounding vague, let us summarize typical “SQL fault lines” in a compact table. This is not a list of “all differences in the world,” just a map of the terrain where surprises happen most often.

The most painful conclusion for an educational project: as soon as you have Flyway as the source of truth, you start caring that migrations are actually reproducible. If your test environment is H2, and the migrations are written for PostgreSQL, you have a choice among three bad options: either you simplify migrations down to the “common denominator,” or you maintain two migration versions (one for Postgres, one for H2), or you disable Flyway in tests. And each of those options reduces the value of tests as “truth.”

So the course logic is simple: H2 can be useful for quick repository checks, but anything tied to migrations and the real SQL dialect requires a PostgreSQL-like environment. Otherwise, the test starts checking “a similar reality,” and we need the real one.

5. Schema, types, and id generation

Even if you do not have a single native query (although in our project we do — the low-stock report), differences can appear at the schema and type level. And that is an unpleasant class of problems, because it looks like “but everything is the same: String, BigDecimal, LocalDateTime...” Yet identical Java types do not guarantee identical SQL reality.

Let us take a very “ours” example: money. In the mini-shop, the product price (Product.price) and the order total (CustomerOrder.totalAmount) live as BigDecimal. In PostgreSQL, you would usually store this as numeric(19, 2) or a close variant. H2 also supports decimal, but nuances of rounding, comparison, type casting, and even DDL behavior can differ. As a result, a test may pass because H2 “forgave” some detail (for example, scale), while PostgreSQL later behaves differently, and you get unexpected discrepancies in calculations or persistence.

Now an example that is especially important to us as a JPA project: id generation. In PostgreSQL, real projects often use sequences. In the course we discussed the SEQUENCE and IDENTITY strategies, and on PostgreSQL this is not theory: Hibernate really generates SQL according to the chosen strategy. H2 can also emulate sequences/identity, but the behavior and “small details” (names, call order, DDL specifics) may differ.

If you want to see how “real” the database is, it is sometimes useful not only to look at the Java code, but also at the migration. Even a small DDL snippet already shows how tightly you are tied to PostgreSQL reality:

create table product (
    id bigserial primary key, -- Postgres-specific: bigserial creates a sequence and a default for id
    sku varchar(64) not null unique, -- The uniqueness constraint should work the same, but error details may differ
    price numeric(19, 2) not null -- Money: precision and scale matter
);

These three lines look simple, but everything is hidden in them: bigserial (PostgreSQL-specific), unique (behavior of constraints), numeric(19,2) (money precision). On H2, you either rewrite this, or enable compatibility mode, or get a migration failure. And in each case you are already living in a world where “one thing in tests, another thing in PostgreSQL.”

Another important angle is constraints (constraints). In the course, we said many times that Bean Validation does not replace database constraints. Therefore, tests that check DataIntegrityViolationException (for example, duplicate sku) must be as close as possible to the real database. H2 may behave differently in small details: somewhere the constraint will trigger at a different moment, somewhere the message will be different, somewhere the order of checks will differ. And if you build tests that are too tightly bound to the error text, you end up with a fragile structure.

There is a practical rule here: if you are testing the business reaction to a constraint, check the semantics, not the “string in the message.” Semantics means that a duplicate sku does not pass and leads to a clear error at the service level. The message string is a random detail of a specific DBMS and a specific driver version.

And yes, I know you want to write assertTrue(e.getMessage().contains("SKU")). That is a very human desire. It just makes the test as reliable as a weather forecast based on knee pain.

6. Transactions and concurrency

Transactions are one of the most dangerous zones for “false confidence.” Because both H2 and PostgreSQL “support transactions,” but supporting transactions is like “being able to drive a car”: formally yes in both cases, but the quality of driving can differ dramatically.

For our project, this is especially important because of two course topics: rollback and optimistic locking. We already check rollback with service tests: if placeOrder() fails in the middle because there is not enough stock, the order must not be partially persisted. Optimistic locking (@Version in StockItem) protects against lost updates and should lead to a version conflict if the second update comes from stale state.

On H2, such scenarios sometimes do not reproduce the same way as on PostgreSQL, or they reproduce differently. The reason is simple: a different locking implementation, a different concurrent access model, a different isolation behavior. As a result, you may write a test that is green on H2, but in reality it did not prove what you thought it proved.

A typical beginner’s mental model looks like this: “I called the service twice, so I tested concurrency.” But concurrency is not “two calls,” it is two independent transactional contexts that read and write the same row. If the database behaves differently, your test becomes “pseudo-concurrent” and loses meaning. It may only check that your code throws an exception (or not), but not that the database correctly handled the version conflict.

Here it is important to think pragmatically: H2 is useful when we check “mapping shape and basic repository mechanics.” But when we check behavior that depends on the real transactional model and the real PostgreSQL schema, we need an environment where PostgreSQL is not an “idea,” but a real working database.

And one more thing that is often underestimated: Flyway + transactions. On PostgreSQL, migrations, constraints, and transactions live in one world. On H2, that world may look similar, but it is not guaranteed. And right now we are at the final stage of the course, where we want tests to be a “predictable proof,” not a “calming postcard.”

7. Careful use of H2

After all of that, it is easy to go to the extreme: “Let us throw away H2 and never think about it again.” That is also not the most mature conclusion. A more mature conclusion sounds like this: H2 is a fast signal tool, and PostgreSQL is a truth tool.

In our shop-data-jpa project, it is convenient to think of the test environment as two layers. The first layer is fast tests that check that mappings are alive, repositories are not broken, derived queries and simple JPQL work. For such a layer, H2 may be justified, because it gives speed and cheap feedback. The second layer is tests that must confirm production-like behavior: Flyway migrations are applied, constraints really work, native queries are correct, optimistic locking is reproducible. This layer should run in an environment as close as possible to PostgreSQL.

It is very important not to mix those two layers in your head. If you consciously use H2 for quick checks, you must not conclude: “Okay, the data layer is done.” It only means: “Apparently, we did not break the basic mechanics.” And “done” begins where tests run on a PostgreSQL-like database and confirm critical scenarios.

Another useful discipline is to call things by their real names. If you have tests that are knowingly working on H2, you can explicitly mark this in the configuration/profiles or at least in the test naming logic. This is not bureaucracy; it is care for your future self. Because in a month you will forget why a test is “somehow” green, while the bug in PostgreSQL still exists.

And yes, the obvious question is: “Okay, but how do we bring up PostgreSQL directly in tests so that it is isolated, reproducible, and does not depend on my local database?” — that is exactly the next engineering step. Here we are only recording the problem and the quality criterion: H2 does not replace PostgreSQL, because it is not PostgreSQL. And to test PostgreSQL truth, you have to test on a PostgreSQL-like environment.

8. Common mistakes when using H2 in data-layer tests

Mistake #1: assuming that H2 is “PostgreSQL on easy mode.”
H2 is a separate DBMS. Even if you enable compatibility mode, that still does not make it PostgreSQL. If a test is critical to dialect, migrations, and constraints, H2 may give a beautiful, fast, and wrong signal.

Mistake #2: not knowing which database is actually running in the test.
A very common situation: a student thinks the test is running on PostgreSQL from Docker Compose, but in reality @DataJpaTest replaced the DataSource with embedded H2. The fix is simple: sometimes you just need to ask for DatabaseProductName and see the truth with your own eyes.

Mistake #3: testing native queries only on H2 and drawing conclusions about PostgreSQL.
A native query is a direct contract with a specific database, schema, and dialect. If you run a native query on H2, you are testing H2. At best you are checking that the “syntax is similar,” but not the “behavior in PostgreSQL.”

Mistake #4: binding yourself to error message text and SQLState.
Error messages and state codes are an area where different databases and drivers can differ greatly. In data-layer tests, it is better to check the exception type and the business semantics of the service reaction, not “which string the database wrote.”

Mistake #5: trying to keep two world schemas — separate migrations for H2 and for PostgreSQL.
That quickly turns into a mini-hell: you change a migration for PostgreSQL, forget to adjust the H2 version, the tests on H2 are green, and the real schema has drifted away. In the end, migrations stop being a source of truth and become a source of randomness.