Executing SQL Scripts in H2 Database

1. Overview

When working with Spring Boot applications, especially in integration tests, using the H2 in-memory database offers a lightweight and fast option for simulating real database interactions. As developers, we often need to initialize schemas, preload data, or execute custom SQL scripts during our tests.

In this tutorial, let’s walk through the common ways we can execute SQL scripts using H2 in a Spring Boot test environment.

2. Specifying Scripts in the JDBC URL

One handy feature of H2 is that it allows us to run SQL scripts automatically when the database is initialized, right from the JDBC URL. Next, let’s demonstrate how this works through an example.

First, let’s create a SQL file init_my_db.sql under the resources/sql directory:

CREATE TABLE TASK_TABLE
(
    ID   INT PRIMARY KEY,
    NAME VARCHAR(255)
);

INSERT INTO TASK_TABLE (ID, NAME) VALUES (1, 'Start the application');
INSERT INTO TASK_TABLE (ID, NAME) VALUES (2, 'Check if data table is filled');

The script creates a table and inserts two records into it. Next, let’s see how to instruct the H2 database to execute this script automatically in a Spring Boot application.

Let’s create a simple Spring Boot YAML configuration:

spring:
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:demodb;INIT=RUNSCRIPT FROM 'classpath:/sql/init_my_db.sql'
    username: sa
    password:

In this example, we defined a datasource that connects to an in-memory H2 database. Further, we added an INIT clause in the JDBC URL:

INIT=RUNSCRIPT FROM 'classpath:/sql/init_my_db.sql'

RUNSCRIPT FROM <script_path> is an H2 database command to run a given script. Therefore, the line above tells H2 to run the resource/sql/init_my_db.sql script as soon as the in-memory database starts. It’s a great way to bootstrap schema definitions or seed test data.

Next, let’s create a test to verify if the table and expected data exist after the application starts:

List<String> expectedTaskNames = List.of("Start the application", "Check if data table is filled");
List<String> taskNames = entityManager.createNativeQuery("SELECT NAME FROM TASK_TABLE ORDER BY ID")
  .getResultStream()
  .map(Object::toString)
  .toList();
assertEquals(expectedTaskNames, taskNames);

The test passes if we run this test. That is to say, the script has been executed by H2 during the initialization.

It’s worth noting that the RUNSCRIPT command understands Java’s classpath. Therefore, we used “classpath:/sql/…” in this example. If we want to provide H2 the script through an absolute path, we can use the “file:” prefix, something like “RUNSCRIPT FROM ‘file:/path/to/my/script.sql‘“.

3. Spring Boot’s Built-in Script Detection: schema.sql and data.sql

We’ve learned that H2 will automatically execute scripts if we add the INIT=RUNSCRIPT FROM … clause to H2’s JDBC URL.

Additionally, when we rely on Spring Data JPA conventions, Spring Boot automatically detects and runs two special files from the classpath:

• schema.sql – For defining the database schema, such as creating schemas, tables, or views
• data.sql – For populating initial data

That is to say, if our application is based on Spring Data JPA, we can put the desired SQL statements into the corresponding files. Spring automatically executes them on the application startup after the datasource is created. In this way, we don’t need to modify the H2 database’s JDBC URL.

Next, let’s understand how it works through an example.

First, let’s create a resources/schema.sql file:

CREATE TABLE CITY
(
    ID   INT PRIMARY KEY ,
    NAME VARCHAR(255)
);

Here, we create a CITY table. Then, we’d like to insert some initial data into the CITY table. So, let’s put some INSERT SQL statements in the resources/data.sql file:

INSERT INTO CITY (ID, NAME) VALUES (1, 'New York');
INSERT INTO CITY (ID, NAME) VALUES (2, 'Hamburg');
INSERT INTO CITY (ID, NAME) VALUES (3, 'Shanghai');

It’s essential to note that Spring Boot executes schema.sql first, followed by data.sql.

Now, let’s write a test to verify that the table is created and the expected data is inserted automatically by Spring Boot:

List<String> expectedCityNames = List.of("New York", "Hamburg", "Shanghai");
List<String> cityNames = entityManager.createNativeQuery("SELECT NAME FROM CITY ORDER BY ID")
  .getResultStream()
  .map(Object::toString)
  .toList();
assertEquals(expectedCityNames, cityNames);

If we run this test, it passes. Therefore, Spring Boot executed the two script files as we expected.

Sometimes, we may want to place schema.sql and data.sql in a different directory, rather than at the root of our classpath. Then, we can set the following properties to achieve it:

spring:
  sql:
    init:
      schema-locations: classpath:/the/path/to/schema.sql
      data-locations: classpath:/the/path/to/data.sql

The auto-detection of schema.sql and data.sql is enabled by default. However, if it’s required, we can turn off this default behavior entirely:

spring:
  sql:
    init:
      mode: never

This can be useful to prevent unwanted SQL from being loaded during application startup.

4. Executing SQL Scripts Dynamically via EntityManager

Sometimes, we want to execute existing SQL scripts on an H2 database from our Spring Boot application programmatically, perhaps to simulate specific data scenarios or reset the database state. Then, we can execute H2’s RUNSCRIPT command via a native query.

As usual, let’s see how it works through an example.

For simplicity, we’ll reuse the CITY table. Let’s create the resources/sql/add_cities.sql file to insert three new city records into the CITY table:

INSERT INTO CITY (ID, NAME) VALUES (4, 'Paris');
INSERT INTO CITY (ID, NAME) VALUES (5, 'Berlin');
INSERT INTO CITY (ID, NAME) VALUES (6, 'Tokyo');

Now, let’s execute this script using RUNSCRIPT in a native query:

entityManager.createNativeQuery("RUNSCRIPT FROM 'classpath:/sql/add_cities.sql'")
  .executeUpdate();
List<String> expectedCityNames = List.of("New York", "Hamburg", "Shanghai", "Paris", "Berlin", "Tokyo");
List<String> cityNames = entityManager.createNativeQuery("SELECT NAME FROM CITY ORDER BY ID")
  .getResultStream()
  .map(Object::toString)
  .toList();
assertEquals(expectedCityNames, cityNames);

The test passes if we give it a run.

The RUNSCRIPT command is native to H2 and supports reading SQL files from the classpath or the file system. We can use this inside test methods or setup blocks to execute scripts as needed.

It’s worth noting that when we created a native query with the RUNSCRIPT command, we should call the executeUpdate() method to execute the script. Calling getResultList() or a similar method raises an exception, even if the SQL script file includes SELECT statements.

5. Conclusion

In this article, we’ve learned several ways to execute scripts in an H2 database. Whether we initialize the database using the INIT clause in the JDBC URL, execute scripts dynamically via EntityManager, or rely on Spring Boot’s built-in support for schema.sql and data.sql, each approach serves a specific need in the testing lifecycle.

Embracing these strategies empowers us to write cleaner, more maintainable integration tests and, ultimately, more robust applications.