H2 database — prototyping

Data layer is usually a core element of the application architecture. Not only during development, when creating some samples and test cases, you might find yourself in a need of something lightweight. In some cases, the only requirements for the database is to be able to quickly set it up and run without too much work or thought. Often, you don't even need any type of console application for accessing the data. That's usually the case when executing some automated/integration tests. However, during the data model prototyping (JPA database initialization) or tests creation which base on database columns, I really like to see what's inside.

Initially I had been using standalone databases like MySQL or Oracle DB. It wasn't very pleasant to set them up each time on any new environment or ask anyone else to do it. Later, upon hearing about Docker I immediately switched to that. It was like a blessing up to some point. However, later I realized, that I don't really need so much overhead when I'm doing some small case (not production-ready yet) projects or demo samples. From that point I've been using different in-memory databases like HSQLDB, Apache Derby, SQLite and H2 Database.

Only recently I've found that H2 Database has everything what I need for prototyping and testing. I can set it up almost instantly together with Spring Boot by adding a dependency to the pom file:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.194</version>
</dependency>

I select the scope depending on what I need. Compile — if I want to use some additional tools from the library. Runtime for normal use of the application (prototyping). Finally, test — for running integration tests. This gives me the feeling of easiness. I don't need to think of starting and configuring the database — at least at this point — since Spring Boot takes care of it (JPA starter).

Console and in-memory mode

What's more, H2 comes with a web server console. Using Spring Boot (web and JPA starters) you can enable it by adding spring.h2.console.enabled=true to your application.properties. The web console will start at default port under 'h2-console' path e.g. http://localhost:8080/h2-console. Assuming that you have your entity classes prepared, you will see their tables auto initialized in the default memory db instance jdbc:h2:mem:testdb.

Not using Spring Boot? No worries, starting the servers is as easy as executing org.h2.tools.Server.main(). In a servlet context org.h2.server.web.DbStarter might also come handy. For an in-depth explanation please refer to the documentation. There is also an advanced section which contains information about using the database in other frameworks/languages (.NET). A manual connection to the database together with web server start in standard Java can be made with the following code:

Connection connection = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
Statement statement = connection.createStatement();
if(statement.execute("Select * from dual")) {
    System.out.println("Successfully connected to the jdbc:h2:mem:testdb");
}
Server.startWebServer(connection);

This should also start and open the console in your web browser on some random port.

Server mode

Next useful case you might consider is connecting to the database from outside of the application, from a different process/host. This is usually a bit hard as the database is running in-memory. By default, the db can be accessed within the same virtual machine and class loader. However, it's also possible to expose it using TCP server thanks to H2 tools. Exemplary bean in Spring, responsible for creating, starting and stopping the server can be defined as follows:

@Bean(initMethod = "start", destroyMethod = "stop")
public Server inMemoryH2DatabaseServer() throws SQLException {
    return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9090");
}

Now the database can be accessed on the 9090 port, using jdbc driver (org.h2.Driver) at the jdbc:h2:tcp://localhost:9090/mem:testdb path. With this it's possible to connect to the database from a different application, for example from JMeter, to verify the data or run load tests. To use the org.h2.tools.Server class, you need to add H2 dependency with compile (default) scope. This is so called Server Mode.

Embedded mode

Lastly, it's also possible configure H2 to be run in the embedded mode. This way your data will be kept in a file and will be persisted throughout application runs. The configuration in Spring Boot is straightforward — define path for database file in the application.properties:

spring.datasource.url=jdbc:h2:./testdb
spring.jpa.hibernate.ddl-auto=update

We have to use spring.jpa.hibernate.ddl-auto=update to overwrite default create-drop value. You can even combine these modes and use one for "production" and the other one for running tests. It's as easy as creating separate properties file in test/resources directory.

Note that, however good, any approach may sound it always comes with some advantages and drawbacks, which is why it's always up to a person to choose the tool and decide what to sacrifice in return (maybe nothing, depends on use case). Cheers!