H2 Database — Fast Embeddable Java SQL Database

Introduction

H2 is a pure-Java relational database that can run embedded inside a JVM, as a standalone server, or entirely in memory. Its small footprint and fast startup make it a default choice for integration tests, local development, and applications that need a lightweight SQL database without external dependencies.

What H2 Does

• Provides a full SQL database engine in a single 2.5 MB JAR file
• Runs in embedded, server, or mixed mode without external processes
• Supports in-memory databases for fast, disposable test environments
• Includes a built-in web console for browsing data and running queries
• Offers compatibility modes for PostgreSQL, MySQL, Oracle, and SQL Server syntax

Architecture Overview

H2 is implemented entirely in Java. In embedded mode, it runs inside the application's JVM and accesses data files directly on disk or holds them in memory. In server mode, it listens on a TCP port and accepts JDBC connections from remote clients. The storage engine uses a page-based format with MVCC for concurrent access. An optional web-based console is served by an embedded HTTP server.

Self-Hosting & Configuration

• Add the H2 JAR to your Java project via Maven, Gradle, or manual download
• Use jdbc:h2:mem:dbname for an in-memory database that exists only during the JVM lifetime
• Use jdbc:h2:file:./data/mydb for a persistent file-based database
• Start the TCP server with java -jar h2.jar to accept remote JDBC connections
• Launch the web console with java -jar h2.jar -web for browser-based administration

Key Features

• Sub-second startup time for test and development workflows
• Full ACID transactions with MVCC and row-level locking
• Built-in full-text search with Apache Lucene integration
• Encryption at rest with AES for persistent database files
• Extensive SQL standard support including window functions and CTEs

Comparison with Similar Tools

• SQLite — C-based and language-agnostic; H2 is JVM-native with tighter Java integration
• Apache Derby — another pure-Java database but H2 is generally faster and smaller
• HSQLDB — similar scope; H2 has broader SQL compatibility modes and more active development
• PostgreSQL — full-featured server; H2 is for embedded and test scenarios where no server is needed
• Testcontainers — spins up real databases in Docker for tests; H2 is faster but may differ from production SQL

FAQ

Q: Is H2 suitable for production use? A: H2 works well for small-to-medium production workloads and desktop applications. For large-scale, high-concurrency production systems, a dedicated server database is usually a better fit.

Q: Can H2 emulate PostgreSQL or MySQL syntax? A: Yes. Compatibility modes adjust SQL parsing to accept syntax specific to PostgreSQL, MySQL, Oracle, or SQL Server, reducing migration friction in tests.

Q: How does the in-memory mode work? A: An in-memory database lives entirely in the JVM heap. It is created when the first connection opens and destroyed when the last connection closes, which makes it ideal for unit and integration tests.

Q: Does H2 support stored procedures? A: H2 supports user-defined functions and aliases written in Java. Traditional stored procedures in PL/SQL style are not natively supported.

Sources

• https://github.com/h2database/h2database
• https://h2database.com/