Automatically Create Schemas for H2 In-Memory Database

1. Introduction

The H2 database engine is a popular open-source database built on Java. In this brief tutorial, we’ll learn how to automatically create a schema for the H2 in-memory database.

2. What Is H2?

The H2 database engine is a Java-based database that is both SQL and JDBC (Java Database Connectivity) compliant. It has a number of features that set it apart from other relational databases:

• Persistence: it can operate as a purely in-memory database or using a file system.
• Mode: runs as a stand-alone server or embedded inside another application.

Both of these characteristics make H2 a great choice for development and testing purposes. However, because of its transient nature, it can also present some challenges.

When connecting to an H2 in-memory database, the schema may not exist. This is because in-memory databases are transient in nature and only survive as long as the application they are running inside is also running. Once that application is terminated, the entire contents of the in-memory database are lost.

Next, we’ll see a few different ways to initialize an H2 in-memory database when connecting to it.

3. Automatically Creating Schemas in H2

There are several approaches for automatically creating schemas for H2 in-memory databases. As with most things, there are pros and cons to each, and the choice of which one to use depends on multiple factors.

3.1. Plain Java

The example below shows how to initialize an in-memory H2 database with plain Java code and JDBC. This is a good option for applications that aren’t using Spring or other frameworks that provide database connectivity:

Connection conn = DriverManager.getConnection(
  "jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung",
  "admin",
  "password");

In the above example, we use the connection URL to specify the schema to create. We can also pass additional commands to further initialize the database:

Connection conn = DriverManager.getConnection(
  "jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;CREATE TABLE users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);",
  "admin",
  "password");

Because all of these commands can make the URL difficult to read, H2 also supports initializing an in-memory database by referencing an SQL file. First, we create the file with the initialization statements:

CREATE SCHEMA IF NOT EXISTS baeldung;
SET SCHEMA baeldung;
CREATE TABLE users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);

Then we use a slightly modified connection URL to reference the file:

Connection conn = DriverManager.getConnection(
  "jdbc:h2:mem:baeldung;INIT=RUNSCRIPT FROM 'h2init.sql';",
  "admin",
  "password");

3.2. Spring Boot

When working with Spring Boot applications, we can also utilize familiar Spring data properties to initialize H2 in-memory databases.

First, we can provide all of the initialization statements in the URL itself, just like above. We start by defining the Spring properties for the H2 data source:

spring.datasource.url=jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;

Then we can use the default Datasource bean as we do in any normal application:

public void initDatabaseUsingSpring(@Autowired DataSource ds) {
    try (Connection conn = ds.getConnection()) {
        conn.createStatement().execute("create table users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);");
        conn.createStatement().execute("insert into users (name, email) values ('Mike', '[email protected]')");
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Just like with plain Java code, we can also reference a SQL file in the connection URL that contains all of the initialization statements. All we have to do is update our properties:

spring.datasource.url=jdbc:h2:mem:baeldung;INIT=RUNSCRIPT FROM 'src/main/resources/h2init.sql';

Notably, H2 doesn’t load resources through Spring Boot. Therefore, we have to reference the file path in its full context of where the application is running.

And now we can use the Datasource like before, but without having to initialize the schema first:

private void initDatabaseUsingSpring(@Autowired DataSource ds) {
    try (Connection conn = ds.getConnection()) {
        conn.createStatement().execute("insert into users (name, email) values ('Mike', '[email protected]')");
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Finally, when using Spring Boot, we can also leverage the SQL init mode without relying on H2 features. We simply rename our initialization file to data.sql and change our properties slightly:

spring.datasource.url=jdbc:h2:mem:baeldung
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=password
spring.sql.init.mode=embedded

We should note that our properties make no mention of a schema or initialization file.

3.3. Spring XML

Additionally, if we are using plain Spring XML to configure a Datasource, we can include the initialization statements there as well.

Let’s see how to create the schema:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:mem:testdb;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;"/>
    <property name="username" value="admin"/>
    <property name="password" value="password"/>
</bean>

As we saw before, having multiple initialization statements in the URL can make it difficult to read the properties. Therefore, it’s a good idea to put them into a single SQL file and reference that in the URL:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'src/main/resources/h2init.sql';"/>
    <property name="username" value="admin"/>
    <property name="password" value="password"/>
</bean>

4. Connect to a Specific Schema in H2 Database

At the outset, a database refers to a collection of data and meta-data, whereas a schema is a collection of objects (tables, indexes, etc.) stored in a database. When we connect to an H2 database instance, we connect to a database. However, we can set the current schema to a specific schema on connection.

Furthermore, the H2 database management system supports two types of databases: disk-based and in-memory. Accordingly, a disk-based H2 database stores the data in the filesystem on a disk. Further,  an in-memory H2 database stores the data in memory. Therefore, an in-memory database is transient, and thus data is never persisted on disk.

When we don’t specify a schema, H2 sets it to the default schema, which is PUBLIC. Let’s discuss connecting to the database and setting a non-default schema.

4.1. Using the H2 Shell

Let’s launch the H2 shell with the java org.h2.tools.Shell command. Thereafter, we are prompted to specify a URL. We specify a file-based URL when we want to use a disk-based H2 database (for example, TEST_DB):

jdbc:h2:~/TEST_DB

On providing a value for each of the prompts; Driver, User (e.g. sa), and Password (e.g. empty option), we are connected to the H2 shell as indicated by a sql> prompt. H2 creates the TEST_DB, if it doesn’t already exist, on the disk as a file called TEST_DB.mv.

Thereupon, let’s find the current schema:

sql>SELECT CURRENT_SCHEMA;
CURRENT_SCHEMA
PUBLIC

The current schema is the default, PUBLIC.

We can connect to a specific schema by including the schema name to set in the connection URL itself. However, let’s first create an example schema TEST_SCHEMA while connected to the H2 shell:

sql>CREATE SCHEMA TEST_SCHEMA AUTHORIZATION SA;

Thereafter, let’s verify it created the new schema:

sql>SHOW SCHEMAS;
SCHEMA_NAME
TEST_SCHEMA
INFORMATION_SCHEMA
PUBLIC

Let’s quit the H2 shell with the quit command. Let’s now connect to the TEST_DB again. Furthermore, this time we set the schema to TEST_SCHEMA in the connection URL, when prompted, using the INIT property:

jdbc:h2:~/TEST_DB;INIT=SET SCHEMA TEST_SCHEMA

On connecting to the H2 shell, let’s find the current schema:

sql>SELECT CURRENT_SCHEMA;
CURRENT_SCHEMA
TEST_SCHEMA

Indeed, it’s the TEST_SCHEMA we want to connect to.

Furthermore, we can create the schema that we want to connect to, or more precisely, set to, in the connection URL itself. Let’s create a new H2 database called TEST_DB_2 and in it create a new schema called FIRST_SCHEMA. Accordingly, we should use the directory path to the database on Windows by including \\ between directory names. Furthermore, we should escape the delimiter ; between multiple SQL commands in the INIT using a single \ in the connection URL:

jdbc:h2:C:\\Users\\user1\\TEST_DB_2;INIT=CREATE SCHEMA IF NOT EXISTS FIRST_SCHEMA\;SET SCHEMA FIRST_SCHEMA

On connecting, let’s verify the current schema:

sql>SELECT CURRENT_SCHEMA;
CURRENT_SCHEMA
FIRST_SCHEMA

Indeed, it’s the FIRST_SCHEMA we created and set in the connection URL.

Likewise, we can specify a URL for an in-memory H2 database when we want to connect to a specific database schema. To demonstrate, let’s create an in-memory database called test_db and a schema called TEST_SCHEMA. Furthermore, we create and set the schema in the connection URL itself:

jdbc:h2:mem:test_db;INIT=CREATE SCHEMA TEST_SCHEMA\;SET SCHEMA TEST_SCHEMA

On connecting, let’s verify the schema connected to:

sql>SELECT CURRENT_SCHEMA;
CURRENT_SCHEMA
TEST_SCHEMA

Indeed, it’s the TEST_SCHEMA we created and set in the connection URL.

4.2. Using a JDBC Application

We can use the JDBC API and H2’s JDBC driver to connect to an H2 database. Furthermore, we can connect to a specific schema. We have the option to connect to an existing database and schema or create a new database and schema. Accordingly, we specify the database and schema to connect to in the database connection URL itself. Let’s create a new database called TEST_DB and, in it, a new schema called TEST_SCHEMA. Additionally, we set the schema to connect to. We use the INIT property in the connection URL when we want to run SQL statement/s on connecting to a database.

Let’s use a JUnit 5 test to verify that we connect to the schema we want to connect to:

@Test
public static void whenConnectingToSpecificSchema_ShouldReturnTheSpecificSchema() throws Exception {
    Connection conn = DriverManager.getConnection(
      "jdbc:h2:~/TEST_DB;INIT=CREATE SCHEMA IF NOT EXISTS TEST_SCHEMA\\;SET SCHEMA TEST_SCHEMA",
      "sa",
      "");
      
    Statement stmt=conn.createStatement();
      
    ResultSet rs=stmt.executeQuery("SELECT CURRENT_SCHEMA");
    rs.first();
      
    String actualSchema = rs.getString(1);
    String expectedSchema = new String("TEST_SCHEMA");

    Assertions.assertTrue(actualSchema.equals(expectedSchema));
}

The JUnit test should pass when have indeed connected to the schema we need to connect to, TEST_SCHEMA in the example. Similarly, we can connect to an in-memory H2 database.

5. Conclusion

The H2 in-memory database is one of the more popular in-memory and embedded database options for Java developers. Because it’s fast and has a small footprint, it is great for use cases like software tests and automated pipelines.

In this article, we saw several ways to ensure that our H2 in-memory database is automatically initialized and ready to use for querying when our application starts. Whether we use plain JDBC or Spring framework, it only takes a few lines of configuration to ensure our in-memory database is fully initialized and ready to use on startup.