Executing SQL Scripts in H2 Database

When writing integration tests for database-driven applications, it is often necessary to initialise schemas, preload test data, or run custom SQL scripts to simulate real-world scenarios. H2 is a lightweight, in-memory relational database that integrates seamlessly with Spring Boot, providing a fast and isolated environment ideal for testing. In this article, we will walk through several approaches to executing SQL scripts in a Spring Boot test context using H2.

1. Project Setup with Spring Boot and H2

Below is a Spring Boot project configuration that includes the required dependencies for H2 and testing.

 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

This configuration includes the required dependencies for Spring Data JPA, H2 Database, and the Spring Boot test starter. With this setup, the project is ready to use H2 in test environments.

2. Auto-Executing SQL Scripts via schema.sql and data.sql

Spring Boot automatically detects and executes schema.sql and data.sql from the classpath. This is the simplest way to initialise schema and data in tests.

src/test/resources/schema.sql

CREATE TABLE IF NOT EXISTS employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(100),
    lastname VARCHAR(100),
    department VARCHAR(100)
);

src/test/resources/data.sql

INSERT INTO employee (firstname, lastname, department) VALUES ('Alice', 'Joe', 'Engineering');
INSERT INTO employee (firstname, lastname, department) VALUES ('Fred', 'Perry', 'Sales');

src/test/resources/application.properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none

When Spring Boot detects schema.sql and data.sql on the classpath, it runs these scripts on application startup. This is perfect for initialising an in-memory H2 database for testing.

Verifying Data with a Test Class

Let’s write a simple repository and test to verify the preloaded data from the SQL scripts.

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstname;
    private String lastname;
    private String department;
    
    // Getters and setters
}

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

@SpringBootTest
public class EmployeeRepositoryTest {

    @Autowired
    private EmployeeRepository repository;

    @Test
    void testEmployeeDataLoaded() {
        List<Employee> employees = repository.findAll();
        assertThat(employees).hasSize(2);
    }
}

This test confirms that the two employees defined in data.sql were correctly loaded into the H2 in-memory database when the application started.

3. Executing SQL Scripts via JDBC URL

In certain scenarios, we may want to initialise the H2 database by executing SQL scripts directly through the JDBC URL. This method is useful when we want the database to load schema and data automatically when the connection is established, without relying on Spring Boot’s built-in script runners.

H2 allows specifying initialisation scripts using the INIT parameter in the JDBC URL. The scripts must be accessible on the file system or classpath.

src/test/resources/application.properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:/scripts/init_db.sql';
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

In this configuration, the INIT clause tells H2 to run the specified SQL scripts immediately after the database is created. The RUNSCRIPT command reads and executes the file.

The RUNSCRIPT command is a built-in SQL command that executes the contents of an SQL script file against the current database connection. It reads SQL statements from a file or resource and runs them in sequence, allowing you to create tables, insert data, or perform any valid SQL operation in bulk. The file path can be a local file system path or a classpath: resource.

Alternatively, the database configuration can be defined in YAML by adding the corresponding properties to the application.yml file.

spring:
  datasource:
    url: jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'classpath:/scripts/init_db.sql';
    # url: jdbc:h2:file:./data/testdb # For file-based database
    username: sa
    password: password
    driverClassName: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect

src/test/resources/scripts/init_db.sql

CREATE TABLE product (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(100),
    price DECIMAL(10, 2)
);

INSERT INTO product (name, category, price)
VALUES ('Laptop', 'Electronics', 1200.00);

INSERT INTO product (name, category, price)
VALUES ('Office Chair', 'Furniture', 350.50);

Notice that the SQL script (init_db.sql) is placed in src/test/resources/scripts/ and referenced in the JDBC URL in the application.properties file. This ensures that it runs before the test context is fully initialised, so the data is ready for verification when the test begins.

    @Autowired
    private EntityManager entityManager;
    
    @Test
    void testProductsLoadedFromInitScript() {
        Query nativeQuery = entityManager.createNativeQuery("SELECT name FROM product");
        List<String> productNames = nativeQuery.getResultList();

        assertThat(productNames).containsExactlyInAnyOrder("Laptop", "Office Chair");
        assertThat(productNames).hasSize(2);
    }

When this test runs, Spring Boot starts the application context with the in-memory H2 database, and the INIT=RUNSCRIPT parameter in the JDBC URL automatically executes init_db.sql, creating the product table and inserting two rows.

The assertions then verify that the resulting list contains exactly "Laptop" and "Office Chair" in any order, and that it has exactly two elements, confirming that both products were inserted successfully.

4. Using the RunScript Class from H2 Tools

In addition to configuration-based approaches, H2 provides a programmatic API for executing SQL scripts via the org.h2.tools.RunScript utility. This is useful when we want full control over when and how scripts are executed within our Java code.

The RunScript class reads SQL statements from a file or Reader and executes them against an H2 Connection.

    @Test
    void testExecuteScriptUsingRunScript() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1", "sa", "password");

        RunScript.execute(conn, new InputStreamReader(
                getClass().getResourceAsStream("/scripts/init_db.sql")));

        var stmt = conn.createStatement();
        var rs = stmt.executeQuery("SELECT COUNT(*) FROM product");

        rs.next();
        int count = rs.getInt(1);

        assertThat(count).isEqualTo(2);
        conn.close();
    }

This test connects directly to the H2 in-memory database and uses RunScript.execute(...) to execute the SQL scripts. These scripts must be available on the classpath.

5. Conclusion

In this article, we explored multiple ways to execute SQL scripts in an H2 database within a Spring Boot test environment. We covered automatic execution using schema.sql and data.sql, embedding scripts in the JDBC URL via INIT=RUNSCRIPT, and a programmatic approache using the org.h2.tools.RunScript API. By applying these techniques, you can reliably initialize schemas, preload data, and maintain consistent database states for repeatable and isolated integration tests.

6. Download the Source Code

This article explored executing SQL scripts in Java using the H2 database.