My Spring Boot Tests Passed, but Production Failed: The H2 vs. PostgreSQL Trap

Tags: #java #springboot #testing #postgresql #programming

Your Spring Boot application passes every test. Your CI pipeline is green. You deploy with confidence.

Then production fails because a database query that worked in your tests does not work with PostgreSQL.

How can this happen?

One possible reason is that your tests use H2, an in-memory database, while production uses PostgreSQL.

Although both databases support SQL, they do not behave identically. Their SQL syntax, data types, constraints, locking behavior, and other database-specific features can differ.

In this article, we will build a small example, understand the problem, and replace misleading database tests with integration tests against a real PostgreSQL instance using Testcontainers.

1. The Problem: Your Test Database Is Not Your Production Database

Consider a Spring Boot application that manages customer registrations.

The application uses:

• Java and Spring Boot
• Spring Data JPA
• H2 for automated tests
• PostgreSQL in production

A typical test configuration might look like this:

# src/test/resources/application.properties
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.hibernate.ddl-auto=create-drop

This configuration is convenient because H2 runs in memory. Developers do not need to install or start a database to execute their tests.

A simple repository test might pass:

@DataJpaTest
class CustomerRepositoryTest {

    @Autowired
    private CustomerRepository repository;

    @Test
    void shouldSaveCustomer() {
        Customer customer = new Customer();
        customer.setEmail("[e-mail]");

        Customer saved = repository.save(customer);

        assertNotNull(saved.getId());
    }
}

Everything looks good.

But what have we actually verified?

We have verified that a basic persistence operation works with this test configuration. We have not verified every SQL statement, constraint, or database behavior used by the production application.

That distinction matters.

2. A Real Example: PostgreSQL-Specific SQL

Suppose the application needs to register a customer only if the email address does not already exist.

PostgreSQL supports the following statement:

INSERT INTO customers (email)
VALUES ('[e-mail]')
ON CONFLICT (email) DO NOTHING;

The ON CONFLICT clause lets PostgreSQL handle a uniqueness conflict without raising a duplicate-key error.

This is useful when multiple requests attempt to register the same email address.

However, this statement is PostgreSQL-specific. H2 does not necessarily support the same syntax, even when configured in PostgreSQL compatibility mode.

Imagine implementing it in a Spring Data repository:

public interface CustomerRepository
        extends JpaRepository<Customer, Long> {

    @Modifying
    @Transactional
    @Query(value = """
        INSERT INTO customers (email)
        VALUES (:email)
        ON CONFLICT (email) DO NOTHING
        """, nativeQuery = true)
    int insertIfAbsent(@Param("email") String email);
}

This repository method relies on PostgreSQL syntax.

A basic H2 test that only calls repository.save() will not catch a problem with insertIfAbsent(). If a test invokes the native query, it may fail under H2 instead.

The important lesson is not that H2 is a bad database. It is that a test must exercise the behavior it claims to verify, using a database that supports that behavior.

3. Why H2 and PostgreSQL Can Behave Differently

Here are several areas where database differences can matter.

Area	Potential problem
SQL syntax	A native query works in PostgreSQL but fails in H2.
Data types	PostgreSQL types such as JSONB have no exact equivalent in every database.
Constraints	Constraint enforcement and supported features may differ.
Transactions	Isolation, locking, and concurrent updates may behave differently.
Migrations	A migration works on H2 but fails against PostgreSQL.
Query execution	Different query planners can produce different performance characteristics.

Not every difference will affect every application. The risk depends on which features the application uses.

If your application relies heavily on native SQL, database-specific types, migrations, or concurrent transactions, testing against the actual database engine becomes particularly important.

4. The Solution: Use Testcontainers with PostgreSQL

Testcontainers allows integration tests to start disposable containers for dependencies such as PostgreSQL.

Instead of testing database interactions against H2, we can run the same database engine used in production.

For this example, you need:

• Java 17 or later
• Spring Boot 3.3 or later
• Maven
• Docker running locally or in your CI environment

The examples use Spring Boot's @ServiceConnection support.

Step 1: Add the Maven dependencies

Add the following dependencies to your pom.xml, keeping the versions managed by your Spring Boot dependency management.

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-testcontainers</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

</dependencies>

You do not need to hardcode Testcontainers versions if your project uses a compatible dependency-management configuration that supplies them. Otherwise, import a compatible Testcontainers BOM or specify the appropriate versions.

Step 2: Create the entity

Create a Customer entity:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String email;

    public Long getId() {
        return id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

For simplicity, this example uses a generated ID and an email field.

In a real application, you should also define the email uniqueness constraint in your database schema.

Step 3: Create the repository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

public interface CustomerRepository
        extends JpaRepository<Customer, Long> {

    @Modifying
    @Transactional
    @Query(value = """
        INSERT INTO customers (email)
        VALUES (:email)
        ON CONFLICT (email) DO NOTHING
        """, nativeQuery = true)
    int insertIfAbsent(@Param("email") String email);
}

For the ON CONFLICT (email) clause to work as intended, PostgreSQL must have a unique constraint or unique index on email.

For example, a production migration could include:

CREATE TABLE customers (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

In a mature application, use your database migration tool to manage this schema rather than relying on Hibernate to create production tables.

Step 4: Create a PostgreSQL integration test

Now create a test that starts a real PostgreSQL container.

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(
    replace = AutoConfigureTestDatabase.Replace.NONE
)
class CustomerRepositoryPostgresTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private CustomerRepository repository;

    @Test
    void shouldInsertCustomerIfEmailDoesNotExist() {

        int firstInsert =
            repository.insertIfAbsent("[e-mail]");

        int secondInsert =
            repository.insertIfAbsent("[e-mail]");

        assertThat(firstInsert).isEqualTo(1);
        assertThat(secondInsert).isEqualTo(0);

        assertThat(repository.findAll())
            .hasSize(1);
    }
}

Remove the unused DynamicPropertyRegistry and DynamicPropertySource imports if your IDE adds them automatically; this example uses @ServiceConnection instead.

The test checks three things:

1. The first insert creates a customer.
2. The second insert does not create a duplicate.
3. The database contains exactly one customer.

The test uses PostgreSQL's own SQL parser and constraint behavior.

Important: This example assumes the test schema contains the unique constraint and identity column shown in the migration above. In a real project, run your actual migrations as part of test setup so the test validates the same schema that production uses.

With Testcontainers, Docker must be available when the test executes.

5. Should You Replace H2 Completely?

Not necessarily.

H2 is still useful for lightweight tests when your application uses portable SQL and you want fast feedback.

A sensible testing strategy separates different kinds of tests.

Unit tests

Test business logic without requiring a database.

Fast persistence tests

Use H2 where its behavior is sufficient for the specific scenario being tested.

Database integration tests

Use Testcontainers with PostgreSQL to verify repository queries, constraints, migrations, and database-dependent behavior.

End-to-end tests

Verify important workflows across the application and its dependencies.

This gives you a balance between speed and confidence.

You do not need to start a PostgreSQL container for every simple unit test. Instead, make sure that database-dependent behavior is covered by tests using the correct engine.

6. What About Database Migrations?

The same principle applies to migrations.

Suppose a Flyway migration contains:

CREATE INDEX idx_customers_lower_email
ON customers (LOWER(email));

PostgreSQL supports expression indexes such as this one. A migration written for PostgreSQL may not work on H2.

A test that uses H2 to validate the migration can fail for reasons unrelated to the production database, or give false confidence if the migration is not actually executed during testing.

A stronger approach is to start PostgreSQL with Testcontainers and run the same Flyway migrations used by the application.

For example, an integration test can verify that the application context starts successfully after all migrations run. You can then add assertions for important tables, constraints, indexes, and query behavior.

That catches schema problems before deployment.

7. Measure the Difference Instead of Guessing

You can make this experiment more useful by comparing the two approaches in your own project.

Create a test suite that exercises the same repository behavior using H2 and PostgreSQL.

Record:

• Which tests pass and fail.
• Whether database-specific queries execute correctly.
• Whether migrations complete successfully.
• How unique constraints behave.
• How concurrent transactions behave in the scenarios your application relies on.

Do not assume PostgreSQL integration tests will always be faster or slower by a particular percentage. Container startup, schema initialization, hardware, and test design all influence runtime.

The objective is not to prove that H2 is universally wrong. It is to discover whether your test database accurately represents the behavior your application depends on.

8. Best Practices for Spring Boot Database Testing

Here is the checklist I recommend:

• Use unit tests for isolated business logic.
• Use Testcontainers for database-specific repository behavior.
• Run the same database migrations used in production.
• Test uniqueness and other important constraints.
• Include concurrency tests for critical workflows.
• Keep test data isolated and predictable.
• Avoid relying on compatibility modes as a guarantee of identical behavior.
• Pin your container image to an intentional database version.
• Run integration tests in CI with Docker or an equivalent supported container runtime.
• Keep a smaller fast test suite for rapid development feedback.

Remember that using the same database engine does not guarantee identical production behavior. Production may still differ in configuration, extensions, permissions, data volume, network conditions, and concurrency.

But using PostgreSQL in your integration tests eliminates an important category of database-engine mismatch.

Conclusion

A green test suite does not automatically mean your application is production-ready.

It means the behavior covered by those tests passed under the conditions in which they ran.

If production uses PostgreSQL, but your tests only use H2, there may be gaps in your confidence around native SQL, migrations, constraints, and transaction behavior.

Testcontainers gives you a practical way to close those gaps by running integration tests against the database engine your application actually uses.

The key lesson is simple:

Test your business logic in isolation, but test database-dependent behavior against the database you intend to run in production.

Have you ever encountered a bug that passed your tests but failed with the production database? What was the cause? Share your experience in the comments.