Test Spring Data Repositories with H2 in-memory database

Test Spring Data Repositories with H2 in-memory database

When writing code, it’s crucial to test every possible corner to eliminate potential bugs, be more confident about the quality of work, and ensure feature correctness. Testing Spring Data Repositories are no exception either. Particularly if you have complex custom queries modifying them increases the risk of breaking them. Surely, nobody would like to experience a query failure in production because he has changed a complex query only to add a column. Hence, it is better to be safe than sorry by writing tests for Repositories to prevent such problems. In this article, we cover how to test Spring Data Repositories with the H2 in-memory database simply and easily.

Why an in-memory database and H2

There are multiple approaches to testing Spring Data Repositories. The simplest way is to use an in-memory database since it requires almost no configurations.

And when it comes to an in-memory database selection, the recommendation is to use a database written in Java to save the hassle of finding a good JVM driver. As a result, it is not recommended to use something like SQLite despite its popularity for testing Spring Repositories. Suitable candidates are Apache Derby, H2, and HSQLDB. All of them are written in Java in which H2 is the most common and lightweight. However, using the other two shouldn’t be more complex. It ultimately boils down to your needs and taste.

Test Spring Data Repositories with H2 in-memory database

In this section, we go through an example repository, UserRepository, and show how to test custom queries in that repository.

The Repository code

Let’s assume we have the following UserRepository file that retrieves users by their first name and sorts the results ascending.

package com.madadipouya.springkafkatest.repository;

import com.madadipouya.springkafkatest.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

public interface UserRepository extends JpaRepository<User, String> {

    List<User> getByFirstNameIgnoreCaseOrderByFirstNameAscLastNameAsc(String firstName);

Adding H2 dependency

To test the repository code, we need to add the H2 library to the project. For Maven projects, open the pom.xml file and add the dependency as follows:


Configuring H2 for test cases

By default, there’s no need to configure H2 as Spring Boot automatically detects and uses it when running tests. However, if you want to add some additional configuration, such as persisting the database file to the disk, you can create the application.properties under the test/resources directory with content like this.


You can even specify a username and a password.


Accessing H2 console/client

Fortunately, H2 comes with its built-in GUI console/client to ease querying the database. The option is disabled by default. To enable it, open the application.properties file and add the following two lines.


After the application starts, you should be able to access the H2 GUI in your browser (localhost:8080/console). But remember to remove it when deploying the application to the production environment.

What about database migration scripts?

Unfortunately, H2 does not play well with database migration scripts that are written using tools like Flyway or Liquibase. Hence, it’s better to disable them in the test application.properties file.


Writing Spring Data Repository test

Now that we have covered all the possible H2 configurations, it’s time to write some repository tests for the UserRepository. The following code snippet is a fully functional repository test that uses H2 in-memory database.

package com.madadipouya.springkafkatest.kafka.repository;

import com.madadipouya.springkafkatest.entity.User;
import com.madadipouya.springkafkatest.repository.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.annotation.DirtiesContext;

import java.util.List;
import java.util.UUID;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;

public class UserRepositoryTest {

    private UserRepository userRepository;

    private TestEntityManager testEntityManager;

    void testGetByFirstName() {
        testEntityManager.persist(stubUser("john", "Wick"));
        testEntityManager.persist(stubUser("Robert", "McCall"));
        testEntityManager.persist(stubUser("John", "Rambo"));

        List<User> users = userRepository.getByFirstNameIgnoreCaseOrderByFirstNameAscLastNameAsc("John");

        assertEquals(2, users.size());
        assertEquals("John", users.get(0).getFirstName());
        assertEquals("Rambo", users.get(0).getLastName());
        assertEquals("john", users.get(1).getFirstName());
        assertEquals("Wick", users.get(1).getLastName());

    private User stubUser(String firstName, String lastName) {
        return new User(UUID.randomUUID().toString(), firstName, lastName);

There are a couple of things to note about the above test.

  • To test any repositories, the test class must be annotated with @DataJpaTest
  • Use @DirtiestContext when you want to clear up the H2 database content. This annotation makes the test a bit slower
  • To persist any test data, use TestEntityManager and avoid using the save method of the repository under the test


In this article, we covered how to test Spring Data Repositories with H2 in-memory database. Spring framework has out-of-the-box support for the H2 database, and with just adding H2 dependency to the project, you can start writing tests.

However, the ease of setup comes with some drawbacks. H2 does not support vendor-specific features of SQL databases. For instance, a query that uses MySQL STRAIGHT_JOIN is not testable with H2. Additionally, in-memory database integration tests are still not fully mimicking the production environment. To bridge this gap, if it’s needed, the Testcontainers library can be a good substitute. We cover using Testcontainers to test Spring Data Repositories in the upcoming article.

Inline/featured images credits