How to Use H2 Database in Spring Boot Test

Spring Boot Testing Test Containers

Spring Boot Testing Tutorial – Part 2, in this article we are going to discuss how to test our database layer in isolation, first by using Embedded H2 Database and then using Test Containers.

You can check Part 1 of this tutorial series, where we went through how to Unit Test Spring Boot Application using Junit 5 and Mockito.

Similar to Part 1, we are going to take the Reddit Clone Application as an example and we will write tests for the Database Layer Components.

If you are a visual learner like, you can checkout the video tutorial below:

Source Code

You can check out the source code of this tutorial here

Testing the Database layer using an embedded database

Spring Boot Test Framework by default provides us with an annotation called @DataJpaTest which will provide all the necessary configuration to test our database-related logic.

It also provides good out of the box support to embedded databases, in this section we are going to see how to use the H2 embedded database to test our Data Access Layer.

Adding H2 Database to Classpath

We can add H2 Database to our project's classpath by adding the below dependency to our pom.xml file

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

Configure H2 Database

Now let's configure the H2 Database related properties inside the application-test.properties file, this will create a Spring Profile called "test" and when activated, will provide the H2 related Database configuration to Spring's Datasource configuration.

application-test.properties

spring.datasource.driver-class-name=org.h2.Driver spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.username=sa spring.datasource.password= spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

PostRepository.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.model.Post; import com.programming.techie.springredditclone.model.Subreddit; import com.programming.techie.springredditclone.model.User; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Repository;  import java.util.List;  @Repository public interface PostRepository extends JpaRepository<Post, Long> {     List<Post> findAllBySubreddit(Subreddit subreddit);      List<Post> findByUser(User user); }

PostRepositoryTestEmbedded.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.model.User; 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.test.context.ActiveProfiles;  import java.time.Instant;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @ActiveProfiles("test") public class UserRepositoryTestEmbedded {      @Autowired     private UserRepository userRepository;      @Test     public void shouldSaveUser() {         User user = new User(null, "test user", "secret password", "user@email.com", Instant.now(), true);         User savedUser = userRepository.save(user);         assertThat(savedUser).usingRecursiveComparison().ignoringFields("userId").isEqualTo(user);     }  }

We are using @DataJpaTest which will provide all the necessary configuration needed to bootstrap JPA related classes in our Test.
Next, we are using the @ActiveProfiles annotation to activate the test profile, so that the H2 Database related properties are injected into our Test Context.
In our first test, we create a test which checks whether we are able to save a user to the database or not.
We create a dummy user and tried to save it into the repository by using the userRepository.save() method
We are asserting whether we received the user with similar properties or not by using assertThat().usingRecursiveComparison().isEqualTo() method chain.
As the userId field is auto-incremented, we have to ignore that field from the comparison, we can do that by adding the ignoringFields("userId") to the above-mentioned method chain.

Now if you try to run the tests, it should pass without any problems.

Inserting Test Data into the Database

To test the database logic, initially we need some data to work with, we can do that either by manually constructing the objects and saving them to the database using Java in the @BeforeEach section, like below:

@DataJpaTest @ActiveProfiles("test") public class UserRepositoryTestEmbedded {      @Autowired     private UserRepository userRepository;      @BeforeEach     public void setup() {         User user = new User(null, "test user", "secret password", "user@email.com", Instant.now(), true);         User savedUser = userRepository.save(user);     }      // Our Tests  }

Or if we have access to the database files, we can use the @Sql annotation provided by Spring Test Framework, to point to the script files which contains the SQL code to insert the values into the database tables.

You can create the database scripts inside a file called test-data.sql, make sure to store this file under the path src/main/test/resources folder.

test-data.sql

INSERT INTO user (`user_id`, `created`, `email`, `enabled`, `password`, `username`) VALUES (null , null , 'test@email.com', true, 's3cr3t', 'testuser_sql');

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.model.User; 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.test.context.ActiveProfiles; import org.springframework.test.context.jdbc.Sql;  import java.util.Optional;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @ActiveProfiles("test") public class UserRepositoryTestEmbedded {      @Autowired     private UserRepository userRepository;      @Test     public void shouldSaveUser() {         User user = new User(null, "test user", "secret password", "user@email.com", Instant.now(), true);         User savedUser = userRepository.save(user);         assertThat(savedUser).usingRecursiveComparison().ignoringFields("userId").isEqualTo(user);     }      @Test     @Sql("classpath:test-data.sql")     public void shouldSaveUsersThroughSqlFile() {         Optional<User> test = userRepository.findByUsername("testuser_sql");         assertThat(test).isNotEmpty();     }  }

Inside the shouldSaveUsersThroughSqlFile Test, as we are using the @Sql annotation to pre-populate the data, so all we have to do is check whether the data is inserted or not.

In this way, you can test your database related logic using Spring's @DataJpaTest annotation.

But in our actual Reddit Clone Application, we are using MySQL database as our main database, and when doing the database testing, we are using an embedded H2 database, due to this difference, there may be scenarios where our database logic may work at the time of local development but not when using the production database.

It is always advisable to test our logic with the same kind of database we are using in Production.

Testing Database Layer using TestContainers

To mitigate the above-mentioned problem, we have are going to use a Java Library called TestContainers.

Testcontainers is a Java library that supports JUnit tests, providing lightweight, throwaway instances of common databases, Selenium web browsers, or anything else that can run in a Docker container.

You need to have docker installed on your machine as a pre-requisite to use TestContainers

To install TestContainers library in our project, we have to add the below dependencies to our pom.xml

pom.xml

              <dependency>             <groupId>org.testcontainers</groupId>             <artifactId>mysql</artifactId>             <version>1.14.3</version>             <scope>test</scope>         </dependency>

As we are using a MySQL datbase, we added the mysql test container library.

Now it's time to write our first test using the TestContainers.

PostRepositoryTest.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.model.Post; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest; import org.testcontainers.containers.MySQLContainer; import org.testcontainers.junit.jupiter.Container; import org.testcontainers.junit.jupiter.Testcontainers;  import java.time.Instant;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @Testcontainers @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class PostRepositoryTest {      @Container     MySQLContainer mySQLContainer = (MySQLContainer) new MySQLContainer("mysql:latest")             .withDatabaseName("spring-reddit-test-db")             .withUsername("testuser")             .withPassword("pass");     @Autowired     private PostRepository postRepository;      @Test     public void shouldSavePost() {         Post expectedPostObject = new Post(null, "First Post", "http://url.site", "Test",                 0, null, Instant.now(), null);         Post actualPostObject = postRepository.save(expectedPostObject);         assertThat(actualPostObject).usingRecursiveComparison()                 .ignoringFields("postId").isEqualTo(expectedPostObject);     } }

So let's see what we are doing in the above test:

We have added the @TestContainers annotation to our Test, this is a Junit 5 annotation which will enable the testcontainer functionality in our tests.
As we are using the MySQL Database from TestContainers, we have to tell to spring test framework that it should not try to replace our database. We can do that by using the @AutoConfigureTestDatabase(replace=AutoConfigureTestDatabase.Replace.NONE annotation.
Inside the Test, we are initializing a MySQLContainer by providing the docker image name, and the required database information.
In the actual test method – shouldSavePost() we are trying to create a new Post Object and asserting whether it's saved correctly to the database or not.

Do not use "root" as the username

Be careful to not use the username as root when configuring the MySQLContainer, as the root username already exists in MySQL

Now if you try to run the above test, you should see the output like below:

            2020-10-21 22:16:55.913  INFO 26128 --- [           main] o.t.d.NpipeSocketClientProviderStrategy  : Accessing docker with local Npipe socket (npipe:////./pipe/docker_engine) 2020-10-21 22:16:55.914  INFO 26128 --- [           main] o.t.d.DockerClientProviderStrategy       : Found Docker environment with local Npipe socket (npipe:////./pipe/docker_engine) 2020-10-21 22:16:56.060  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : Docker host IP address is localhost 2020-10-21 22:16:56.101  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : Connected to docker:    Server Version: 19.03.13   API Version: 1.40   Operating System: Docker Desktop   Total Memory: 12746 MB 2020-10-21 22:16:57.501  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit 2020-10-21 22:16:57.501  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : Checking the system... 2020-10-21 22:16:57.502  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker server version should be at least 1.6.0 2020-10-21 22:16:58.534  INFO 26128 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker environment should have more than 2GB free disk space 2020-10-21 22:16:58.542  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Creating container for image: mysql:latest 2020-10-21 22:16:58.660  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Starting container with ID: ddcc016239f667041c5da03a3b888cb9ec75427a3172b167faf7179476c33414 2020-10-21 22:16:59.064  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Container mysql:latest is starting: ddcc016239f667041c5da03a3b888cb9ec75427a3172b167faf7179476c33414 2020-10-21 22:16:59.073  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Waiting for database connection to become available at jdbc:mysql://localhost:32821/spring-reddit-test-db using query 'SELECT 1' 2020-10-21 22:17:12.420  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Container is started (JDBC URL: jdbc:mysql://localhost:32821/spring-reddit-test-db) 2020-10-21 22:17:12.420  INFO 26128 --- [           main] 🐳 [mysql:latest]                        : Container mysql:latest started in PT16.8793605S Hibernate: insert into post (created_date, description, post_name, id, url, user_id, vote_count) values (?, ?, ?, ?, ?, ?, ?) 2020-10-21 22:17:12.501  INFO 26128 --- [           main] o.s.t.c.transaction.TransactionContext   : Rolled back transaction for test: [DefaultTestContext@475c9c31 testClass = PostRepositoryTest, testInstance = com.programming.techie.springredditclone.repository.PostRepositoryTest@4e07b95f, testMethod = shouldSavePost@PostRepositoryTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@5c86a017 testClass = PostRepositoryTest, locations = '{}', classes = '{class com.programming.techie.springredditclone.SpringRedditCloneApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTestContextBootstrapper=true}', contextCustomizers = set[[ImportsContextCustomizer@5c7bfdc1 key = [org.springframework.boot.autoconfigure.cache.CacheAutoConfiguration, org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration, org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration, org.springframework.boot.autoconfigure.liquibase.LiquibaseAutoConfiguration, org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration, org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration, org.springframework.boot.test.autoconfigure.jdbc.TestDatabaseAutoConfiguration, org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManagerAutoConfiguration]], org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@247d8ae, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@6c130c45, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.autoconfigure.OverrideAutoConfigurationContextCustomizerFactory$DisableAutoConfigurationContextCustomizer@4d02f94e, org.springframework.boot.test.autoconfigure.filter.TypeExcludeFiltersContextCustomizer@351584c0, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@ef997c7e, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@4d0f2471], contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map[[empty]]]

Test Results for PostRepositoryTest - Spring Boot Testing

And you can also see that our tests are passing ✔️✔️✔️

Let's write another test for the UserRepository.java class, this time we are going to name it as UserRepositoryTest.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.BaseTest; import com.programming.techie.springredditclone.model.User; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest; import org.testcontainers.containers.MySQLContainer; import org.testcontainers.junit.jupiter.Container; import org.testcontainers.junit.jupiter.Testcontainers;  import java.time.Instant;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @Testcontainers @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class UserRepositoryTest {      @Container     MySQLContainer mySQLContainer = (MySQLContainer) new MySQLContainer("mysql:latest")             .withDatabaseName("spring-reddit-test-db")             .withUsername("testuser")             .withPassword("pass");     @Autowired     private UserRepository userRepository;      @Test     public void shouldSavePost() {         User expectedUserObject = new User(123L, "test user", "secret password", "user@email.com", Instant.now(), true);         User actualUserObject = userRepository.save(expectedUserObject);         assertThat(actualUserObject).usingRecursiveComparison()                 .ignoringFields("userId").isEqualTo(expectedUserObject);     }  }

And now if you try to run both the tests together, you will observe that the MySQL TestContainer is starting up two times. This will increase our test execution time a lot, imagine running if we are running lots of tests in our project, it will take lots of time.

This is how the test execution report looks like for the above 2 tests(PostRepositoryTest.java and UserRepositoryTest.java)

Slow running tests due to TestContainers - Spring Boot Testing

You can observe that it took 30 seconds to execute 2 tests. We can try to improve this by configuring Test Containers to re-use the containers, instead of spinning them up on each test run.

Improving the performance of our Tests

We should always try to make the test feedback loop very short and make our tests run faster. Fortunately, it is not so complex to improve the performance of our tests, we just have to follow the below 2 points:

Remove the @TestContainers and @Container annotation from our test, this will force Junit 5 to re-start the container on every test and it will not allow us to re-use them.
Follow the singleton container approach as mentioned on the TestContainer official website

By using the singleton container approach, we just have to move the logic of initializing the containers to an Abstract class, and make our Tests extend this abstract class

BaseTest.java

package com.programming.techie.springredditclone;  import org.testcontainers.containers.MySQLContainer;  public class BaseTest {      static MySQLContainer mySQLContainer = (MySQLContainer) new MySQLContainer("mysql:latest")             .withDatabaseName("spring-reddit-test-db")             .withUsername("testuser")             .withPassword("pass")             .withReuse(true);      static {         mySQLContainer.start();     } }

You can observe that we added a new method .withReuse(true) to our container initialization code, and we are manually starting the container inside the static block, this makes sure that the mySQLContainer.start() is executed only once.

Now let's remove the initialization logic from our PostRepositoryTest.java and UserRepositoryTest.java and extend them from the BaseTest.java

PostRepositoryTest.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.BaseTest; import com.programming.techie.springredditclone.model.Post; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;  import java.time.Instant;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class PostRepositoryTest extends BaseTest {      @Autowired     private PostRepository postRepository;      @Test     public void shouldSavePost() {         Post expectedPostObject = new Post(null, "First Post", "http://url.site", "Test",                 0, null, Instant.now(), null);         Post actualPostObject = postRepository.save(expectedPostObject);         assertThat(actualPostObject).usingRecursiveComparison()                 .ignoringFields("postId").isEqualTo(expectedPostObject);     }  }

UserRepositoryTest.java

package com.programming.techie.springredditclone.repository;  import com.programming.techie.springredditclone.BaseTest; import com.programming.techie.springredditclone.model.User; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest; import org.testcontainers.containers.MySQLContainer;  import java.time.Instant;  import static org.assertj.core.api.Assertions.assertThat;  @DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class UserRepositoryTest extends BaseTest {      @Autowired     private UserRepository userRepository;      @Test     public void shouldSavePost() {         User expectedUserObject = new User(123L, "test user", "secret password", "user@email.com", Instant.now(), true);         User actualUserObject = userRepository.save(expectedUserObject);         assertThat(actualUserObject).usingRecursiveComparison()                 .ignoringFields("userId").isEqualTo(expectedUserObject);     }  }

Now if you try to run both these tests together, you can observe a warning message like below in your tests:

22:40:31.807 [main] WARN 🐳 [mysql:latest] – Reuse was requested but the environment does not support the reuse of containers
To enable reuse of containers, you must set 'testcontainers.reuse.enable=true' in a file located at C:\Users\<username>\.testcontainers.properties

To get around this warning, you have to change the .testcontainer.properties file inside your user home folder, and add the property testcontainers.reuse.enable=true

Once this is done, you can see that the tests which took 30s to execute will now only take 300 ms.

Here is the proof:

Improved performance in tests due to singleton container approach

Conclusion

We came to the end of this article, and I hope you learned something new by reading this article.

I will see you in the next part of the Spring Boot Testing Tutorial series, where we will see how to Test our Web Layer (REST APIs) using Spring MockMvc

Until then, happy coding 🙂

How to Use H2 Database in Spring Boot Test

Source: https://programmingtechie.com/2020/10/21/spring-boot-testing-tutorial-database-testing-with-test-containers/

Frodsham Thelly