One of the needs we usually have when working with SQL databases is to use an independent database to have a set of tests. That’s the goal of this entry on In-Memory H2 Database with Spring Boot, to see how we can use an in-memory database for our services.

What is H2?

H2 is a Java-written relational database that operates as an in-memory database. One of its strengths is that it can work as an embedded database in Java applications or run in client-server mode. That is, when added to our applications as another dependency and once the connection is configured, it will allow us to perform tests and work as if it were a relational database. But it should be noted that it is in-memory, so it should never be used for production environments.

In this link, we can see the different modes of operation of H2.

Configure an H2 database with Spring Boot

To use an H2 database in Spring Boot, it will be necessary to add the H2 dependencies to our project.

Maven Dependency for H2 with Spring Boot

We are going to add the only maven dependency needed to incorporate H2 as an embedded database:

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

Typically, developers use this database for testing purposes, so it’s advisable to include the corresponding scope.

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
                        <scope>test</scope>
		</dependency>

And don’t forget to add the spring data dependency in your pom.xml to perform auto-configuration and connection with the database, which will facilitate database connections thanks to Hibernate.

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

Once we have added the necessary maven dependency, we will need to configure the application.properties or application.yaml file to connect to our database.

Configure Spring Boot Connection with H2

To establish the connection with our H2 database, it is necessary to tell Spring Boot how to connect, which we do through the application.yaml or application.properties file.

By default, Spring Boot will configure our application to connect to our in-memory H2 database with username sa and an empty password.

Although we can parameterize our application with the following parameters:

spring.datasource.url: jdbc:h2:mem:testdb
spring.datasource.driverClassName: org.h2.Driver
spring.datasource.username: sa
spring.datasource.password: password
spring.jpa.database-platform: org.hibernate.dialect.H2Dialect
spring.h2.console.enabled: true

We should note that we are using an in-memory database, so the application will delete the database when it finishes. Therefore, to persist the data physically, we are going to add the following parameter:

spring.datasource.url=jdbc:h2:file:/data/refactorizando/h2

Accessing the H2 graphical interface

The H2 Database has a graphical interface that is accessed through the browser, which will function as a database manager, allowing us to see tables, roles, and perform queries. The H2 console is not enabled by default in Spring, so we are going to add the following command for configuration:

spring.h2.console.enabled=true

Once the application starts, we can access the console through this endpoint http://localhost:8080/h2-console.

Let’s not forget that the URL we have put in our properties file is the same as we put in JDBC URL.

Once we have connected, we can start performing queries.

In case we want the H2 access address to be different from /h2-console, we can override the property in the application.properties or yaml.

spring.h2.console.path=/h2-console

Problems and Solution with H2

One of the most typical problems that can occur when using H2 is not having defined the dialect, and we would have an error like the following:

Table "ALL_SEQUENCES" not found; SQL statement:

To solve this error, we need to make sure we have added the following line:

properties.hibernate.dialect: org.hibernate.dialect.H2Dialect

Conclusion

H2 works as an in-memory database that is perfect for our Spring Boot applications to perform testing. In this article on H2 In-Memory Database with Spring Boot, we have seen its configuration. If you want to see a complete example, you can check out our GitHub repository, where we use H2.