Spring Boot

Spring Boot H2 Database: In-Memory Setup, H2 Console, Testing and Testcontainers

Set up H2 in Spring Boot: in-memory and file modes, H2 console, schema.sql and data.sql, compatibility modes, @DataJpaTest, and when to use Testcontainers.

What Is H2 and When Should You Use It?

H2 is a lightweight relational database written in Java. It runs embedded inside your JVM, starts in milliseconds, needs no installation, and can also run as a standalone server. In Spring Boot it is the usual choice for fast local development, demos, and repository tests. H2 is not a replacement for your production database. It imitates MySQL, PostgreSQL, Oracle and SQL Server through compatibility modes, but the imitation is incomplete, so a query that passes on H2 can still fail in production. Use H2 for speed and convenience, and use Testcontainers when you must prove behaviour against the real engine. Spring Boot 3 ships H2 2.x. Databases created by H2 1.4.x use an incompatible file format, and a few keywords and defaults changed, which explains several of the errors listed later on this page.

Text

Situation                                      Use
---------------------------------------------  ---------------------------------
Local development, quick feedback              H2 in-memory
Demo or prototype that must run anywhere       H2 in-memory or file
Repository tests with standard SQL / JPQL      @DataJpaTest + H2
Native queries, JSONB, ON CONFLICT, MERGE      Testcontainers with the real DB
Flyway / Liquibase migrations validation       Testcontainers with the real DB
Production                                     A real database, not H2

Add the H2 Dependency

Spring Boot manages the H2 version, so you add the dependency without a version number. With spring-boot-starter-data-jpa (or spring-boot-starter-jdbc) on the classpath and no datasource URL configured, Spring Boot detects H2 and creates an embedded in-memory database with a generated unique name. Scope matters. The runtime scope keeps H2 on the classpath and it is packaged into your executable JAR, so it does not stay out of production by itself. If H2 is only for tests, use test scope. If you want it for local development only, put it in a Maven profile (or a Gradle configuration) that you do not activate when building the production artifact. Gradle equivalent: runtimeOnly 'com.h2database:h2' for development, or testRuntimeOnly 'com.h2database:h2' for tests only.

XML

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

<!-- Tests only: not packaged into the production JAR -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

<!-- OR, for local development, activate only with -Pdev -->
<profiles>
    <profile>
        <id>dev</id>
        <dependencies>
            <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <scope>runtime</scope>
            </dependency>
        </dependencies>
    </profile>
</profiles>

In-Memory H2 Configuration

For an in-memory database you only need a JDBC URL. The two parts worth understanding are the database name and DB_CLOSE_DELAY=-1. By default an in-memory H2 database is deleted when its last connection closes, which can wipe your data between pool connections. DB_CLOSE_DELAY=-1 keeps it alive until the JVM exits. A fixed name such as appdb lets the H2 console and the application connect to the same database. You do not need to set the driver class or the Hibernate dialect: Spring Boot infers the driver from the URL and Hibernate 6 detects the dialect from the connection. Hibernate's ddl-auto create-drop builds the schema at startup and drops it at shutdown, which is right for development and tests.

yaml

spring:
  datasource:
    url: jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1
    username: sa
    password: ""
  jpa:
    hibernate:
      ddl-auto: create-drop      # create schema on start, drop on stop
    show-sql: true
    properties:
      hibernate:
        format_sql: true

# No driver-class-name and no dialect needed: both are detected automatically.
# Without any spring.datasource.url, Boot creates an embedded DB with a random name.

File-Based and Server Mode H2

An in-memory database disappears when the application stops. A file-based URL stores data on disk so it survives restarts, which suits demos and small tools. Add AUTO_SERVER=TRUE if the application and another process (such as an external SQL client) must open the same file at the same time; the first process becomes the server for the others. If you upgraded from Spring Boot 2 to 3, an existing 1.4.x database file will not open with H2 2.x. Export it with the old version's SCRIPT command and import into a new file, or recreate it.

yaml

spring:
  datasource:
    url: jdbc:h2:file:./data/appdb;AUTO_SERVER=TRUE
    username: sa
    password: ""
  jpa:
    hibernate:
      ddl-auto: update           # keep existing data between restarts

# Server mode (separate H2 process, TCP):
#   url: jdbc:h2:tcp://localhost:9092/~/appdb

H2 Compatibility Modes (MySQL, PostgreSQL, Oracle, SQL Server)

Compatibility modes make H2 accept the syntax and some behaviours of another database, so the same SQL is more likely to run during tests. Choose the mode that matches production and add the identifier settings listed here. H2 folds unquoted identifiers to upper case by default, while PostgreSQL folds them to lower case; DATABASE_TO_LOWER=TRUE aligns H2 with that, and it is the most common fix for "table not found" errors after enabling a mode. Treat the modes as approximations. PostgreSQL arrays, JSONB and some LATERAL joins are not fully supported, MySQL's ON DUPLICATE KEY UPDATE is only partly supported, and Oracle's CONNECT BY and MERGE behave differently. When a feature is database specific, test it on the real engine with Testcontainers.

yaml

spring:
  datasource:
    # PostgreSQL
    url: jdbc:h2:mem:appdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH;DB_CLOSE_DELAY=-1

    # MySQL / MariaDB
    # url: jdbc:h2:mem:appdb;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;DB_CLOSE_DELAY=-1

    # Oracle
    # url: jdbc:h2:mem:appdb;MODE=Oracle;DB_CLOSE_DELAY=-1

    # SQL Server
    # url: jdbc:h2:mem:appdb;MODE=MSSQLServer;DB_CLOSE_DELAY=-1

# Keep exactly one url active. Verify the settings for your H2 version in the
# official H2 compatibility documentation.

Enable the H2 Console

The H2 console is a browser-based SQL client for looking at your in-memory data while the application runs. Enable it explicitly with spring.h2.console.enabled=true (it also turns on automatically when Spring Boot DevTools is present). It needs a servlet web application, so spring-boot-starter-web must be on the classpath. On the login page, the JDBC URL must match spring.datasource.url exactly, including the database name. A mismatch is the most common reason the console shows an empty database: it silently creates a brand new in-memory database instead of attaching to yours. Keep the console for development. web-allow-others defaults to false, which limits it to localhost; leave it that way.

yaml

# application-dev.yml (dev profile only)
spring:
  h2:
    console:
      enabled: true
      path: /h2-console           # http://localhost:8080/h2-console
      settings:
        web-allow-others: false   # localhost only

# Console login page:
#   Driver Class : org.h2.Driver
#   JDBC URL     : jdbc:h2:mem:appdb     (must equal spring.datasource.url)
#   User Name    : sa
#   Password     : (empty)

H2 Console with Spring Security

With Spring Security on the classpath the console returns 401, 403 or a blank frame, because it uses iframes and posts forms. Allow it in a dedicated security filter chain: permit the console path, relax the frame options to same origin, and exclude the path from CSRF. Register this configuration only in the dev profile so the console can never be reachable in production. Because it is its own filter chain with a higher precedence, your main application chain keeps its normal rules.

Java

@Configuration
@Profile("dev")
public class H2ConsoleSecurityConfig {

    @Bean
    @Order(1)   // evaluated before the main application filter chain
    SecurityFilterChain h2ConsoleChain(HttpSecurity http) throws Exception {
        http
            .securityMatcher("/h2-console/**")
            .authorizeHttpRequests(auth -> auth.anyRequest().permitAll())
            .csrf(csrf -> csrf.disable())                       // console posts forms
            .headers(headers -> headers
                .frameOptions(frame -> frame.sameOrigin()));    // console uses iframes
        return http.build();
    }
}

Initialise the Schema and Data with schema.sql and data.sql

Spring Boot can run schema.sql and data.sql from src/main/resources at startup. By default (mode: embedded) this happens for embedded databases such as H2. Choose one owner for the schema. Either Hibernate creates it (ddl-auto: create-drop) or schema.sql does (ddl-auto: none), not both. If you want Hibernate to create the tables and also run data.sql, set spring.jpa.defer-datasource-initialization to true; otherwise data.sql runs before Hibernate has created the tables and fails with "table not found". The scripts below use the identity syntax that works in H2 2.x without a compatibility mode.

yaml

spring:
  sql:
    init:
      mode: embedded             # default; use "always" for non-embedded databases
      schema-locations: classpath:schema.sql
      data-locations: classpath:data.sql
  jpa:
    hibernate:
      ddl-auto: none             # schema.sql owns the DDL

# Option B: Hibernate creates tables, data.sql only seeds rows
#   spring.jpa.hibernate.ddl-auto: create-drop
#   spring.jpa.defer-datasource-initialization: true
#   (remove schema-locations)

Example schema.sql and data.sql

A minimal pair of scripts for a users and orders model. Use IF NOT EXISTS so the script is safe to run against a file database that already has tables. Avoid naming a table USER: it is a reserved keyword in H2 2.x, so use users, as below.

sql

-- src/main/resources/schema.sql
CREATE TABLE IF NOT EXISTS users (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(255)  NOT NULL UNIQUE,
    role        VARCHAR(20)   NOT NULL DEFAULT 'USER',
    active      BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id     BIGINT        NOT NULL,
    reference   VARCHAR(50)   NOT NULL UNIQUE,
    total       DECIMAL(10,2) NOT NULL,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- src/main/resources/data.sql
INSERT INTO users (name, email, role) VALUES
    ('Alice Smith', 'alice@example.com', 'ADMIN'),
    ('Bob Jones',   'bob@example.com',   'USER'),
    ('Carol White', 'carol@example.com', 'USER');

INSERT INTO orders (user_id, reference, total) VALUES
    (1, 'ORD-001', 99.99),
    (1, 'ORD-002', 149.50),
    (2, 'ORD-003', 29.99);

H2 with Flyway or Liquibase

If production uses Flyway, run the same migrations on H2 instead of maintaining a second set of scripts. Turn off Spring's script initialisation and let Hibernate only validate or do nothing, so the migrations are the single source of truth. Migrations written for PostgreSQL or MySQL may use syntax H2 cannot run, even in compatibility mode. When that happens, either keep migrations to portable SQL or validate them against a real database with Testcontainers.

yaml

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration   # the same migrations as production
  sql:
    init:
      mode: never                       # Flyway owns the schema
  jpa:
    hibernate:
      ddl-auto: validate                # or none

Test Repositories with @DataJpaTest and H2

@DataJpaTest starts only the JPA part of your application: entities, Spring Data repositories, Hibernate and an embedded database. It does not load controllers or services, so it starts quickly. Each test runs in a transaction that rolls back afterwards, so tests do not leak data into each other. By default @DataJpaTest replaces any configured DataSource with an embedded one, so your spring.datasource.url is ignored. That is usually what you want for fast tests, and it is why the next section shows how to opt out.

Java

@DataJpaTest   // configures H2, JPA and repositories; no web layer
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private TestEntityManager entityManager;   // for arranging test data

    @Test
    void findByEmail_returnsUser_whenExists() {
        entityManager.persistAndFlush(new User("Alice", "alice@example.com"));

        Optional<User> found = userRepository.findByEmail("alice@example.com");

        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Alice");
    }

    @Test
    void existsByEmail_returnsTrueOnlyForKnownEmail() {
        entityManager.persistAndFlush(new User("Bob", "bob@example.com"));

        assertThat(userRepository.existsByEmail("bob@example.com")).isTrue();
        assertThat(userRepository.existsByEmail("nobody@example.com")).isFalse();
    }

    @Test
    @Sql("/test-data/users.sql")   // load a script before this test
    void findByRole_returnsAllAdmins() {
        assertThat(userRepository.findByRole(User.Role.ADMIN)).hasSize(2);
    }
}

Use Your Own H2 URL or Mode in Tests

To test against a specific H2 compatibility mode, tell @DataJpaTest not to replace the datasource, then supply the URL in test properties. Without replace = NONE, the URL below is ignored and you silently test against a default H2 database. Use @Sql with execution phases when a single test needs setup and cleanup scripts. Prefer setting up data in the test itself where you can, since scripts spread test logic across files.

Java

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)  // keep my URL
@TestPropertySource(properties = {
    "spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1",
    "spring.jpa.hibernate.ddl-auto=create-drop"
})
class UserRepositoryPostgresModeTest {

    @Autowired UserRepository userRepository;

    @Test
    @Sql(scripts = "/test-data/users.sql",
         executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
    @Sql(scripts = "/test-data/cleanup.sql",
         executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
    void findsSeededUsers() {
        assertThat(userRepository.findAll()).isNotEmpty();
    }
}

H2 vs Testcontainers: Which One to Use

H2 and Testcontainers answer different questions. H2 asks "does my repository logic work?" quickly. Testcontainers asks "does my code work on the exact database I run in production?" and is slower because it starts a Docker container. Use both. A large number of fast H2 slice tests cover standard CRUD, JPQL and derived queries, and a smaller set of Testcontainers tests covers everything that is database specific. Neither replaces the other.

Text

Use H2 when                                     Use Testcontainers when
----------------------------------------------  ---------------------------------------------
Standard SQL, JPQL, derived queries             Native queries with vendor syntax
Speed matters (about a second to start)         You need production-identical behaviour
Docker is not available (some CI systems)       JSON, arrays, full-text search, ON CONFLICT
Local development feedback loop                 Stored procedures, triggers, extensions
                                                Validating Flyway / Liquibase migrations
                                                Constraints, indexes, exact data types

Recommended test pyramid:
  @DataJpaTest + H2                -> many fast repository tests
  @DataJpaTest/@SpringBootTest + TC -> fewer integration tests, run in CI

Testcontainers Example with @ServiceConnection

Since Spring Boot 3.1, @ServiceConnection wires the container's URL, username and password into the datasource for you, so the old @DynamicPropertySource block is no longer needed. Combine it with @AutoConfigureTestDatabase(replace = NONE) so @DataJpaTest uses the container instead of swapping in H2. This test would fail on H2 because it relies on a PostgreSQL feature, which is exactly the case where Testcontainers earns its extra startup time.

Java

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

    @Container
    @ServiceConnection   // Spring Boot 3.1+: no manual property wiring
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    ProductRepository productRepository;

    @Test
    void fullTextSearch_returnsMatchingProducts() {
        // Uses PostgreSQL tsvector search: not available in H2
        assertThat(productRepository.fullTextSearch("widget")).isNotEmpty();
    }
}

// Dependencies: org.springframework.boot:spring-boot-testcontainers,
// org.testcontainers:junit-jupiter, org.testcontainers:postgresql (all test scope)

Common Spring Boot H2 Errors and How to Fix Them

Most H2 problems come from five causes: a console URL that does not match the application, initialisation order, identifier case, H2 2.x reserved words, and a file database opened by two processes. The table lists the messages people search for and the usual fix.

Text

Error / symptom                                          Cause                                           Fix
-------------------------------------------------------  ----------------------------------------------  ------------------------------------------
Console login shows an empty database                    JDBC URL differs from spring.datasource.url     Use the exact same URL and database name
Table "USERS" not found                                  data.sql ran before Hibernate created tables,   spring.jpa.defer-datasource-initialization=true
                                                         or identifier case mismatch                     or use schema.sql; check DATABASE_TO_LOWER
Syntax error ... expected "identifier" (table "user")    USER is a reserved word in H2 2.x               Rename to users or quote the name
Unsupported database file version or invalid file        File created by H2 1.4.x opened by H2 2.x       Export with old version, import into new DB
 header
Database may be already in use                           Two processes opened the same file DB           Add AUTO_SERVER=TRUE or close the other one
Data disappears between requests (in-memory)             Database closed with last connection            Add DB_CLOSE_DELAY=-1 to the URL
Sequence "..._SEQ" not found                             Entity uses AUTO generation, schema.sql uses    Use GenerationType.IDENTITY or let Hibernate
                                                         identity columns                                create the schema
H2 console returns 403 or a blank page with Security     Frame options / CSRF blocking the console       Dev-profile filter chain, sameOrigin frames
Test ignores my datasource URL                           @DataJpaTest replaced it with embedded DB       @AutoConfigureTestDatabase(replace = NONE)
Query works in H2 but fails on production database       H2 compatibility is incomplete                  Verify the query with Testcontainers

H2 Safety Checklist Before You Ship

H2 problems rarely show up in development; they show up in production. This checklist keeps the convenience of H2 from leaking into a deployed system.

Text

[ ] H2 dependency is test scope, or only in a dev profile, not in the production build
[ ] spring.h2.console.enabled is true only in the dev profile
[ ] web-allow-others is false; the console is never reachable from the network
[ ] The H2 console security chain is registered only with @Profile("dev")
[ ] Production datasource URL, driver and credentials come from environment variables
[ ] No ddl-auto create-drop or update against a real database; use migrations
[ ] Migrations and native queries verified against the real engine (Testcontainers)
[ ] H2 compatibility mode matches production, and its limits are known to the team
[ ] File-based H2 databases, if any, are backed up and not used for critical data

Frequently Asked Questions

How do I use H2 in Spring Boot? Add the H2 dependency, and optionally set spring.datasource.url to jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1. Spring Boot detects H2 and creates the datasource automatically. How do I open the H2 console in Spring Boot? Set spring.h2.console.enabled=true and open /h2-console. Enter the same JDBC URL as spring.datasource.url. With Spring Security you must also permit the path, allow same-origin frames, and exclude it from CSRF. Why is the H2 console empty? The JDBC URL on the login page does not match your application's URL, so the console created a new empty in-memory database. Does H2 data persist after a restart? Not in memory mode. Use a file URL such as jdbc:h2:file:./data/appdb to keep data on disk. Why does data.sql fail with "table not found"? data.sql ran before Hibernate created the tables. Set spring.jpa.defer-datasource-initialization=true, or create the tables with schema.sql. Should I use H2 or Testcontainers for tests? Use H2 for fast tests of standard SQL and JPQL, and Testcontainers for anything database specific. Most projects use both. Can H2 replace my production database? It is not designed to. Compatibility modes are approximations, and H2 lacks many production features. Why is my table called USER failing in H2? USER is a reserved keyword in H2 2.x. Rename the table to users, or quote the identifier.

SQL Server Integration

Redis Integration