Database Integration Tests With Spring Boot and Testcontainers

With Spring Data JPA, you can easily create database queries and test them with an embedded H2 database.

But sometimes, testing against a real database is much more useful, especially if we use queries tied to a specific database implementation.

In this tutorial, we'll show you how to use Testcontainers for integration testing with Spring Data JPA and a PostgreSQL database.

We will be testing JPQL and native SQL queries created using the @Query annotation in Spring Data JPA.

Configuration

In order to use a PostgreSQL database in our tests, we must add the test-only Testcontainers dependency and the PostgreSQL driver to our pom.xml file:

<dependency>
 <groupId>org.testcontainers</groupId>
 <artifactId>postgresql</artifactId>
 <version>1.10.6</version>
 <scope>test</scope>
</dependency>
<dependency>
 <groupId>org.postgresql</groupId>
 <artifactId>postgresql</artifactId>
 <version>42.2.5</version>
</dependency>

We will also create an application.properties file in the testing resources directory, in which we will tell Spring to use the desired driver class, as well as create and delete the database schema each time the test is run:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.hibernate.ddl-auto=create-drop

Unit Test

To start using a PostgreSQL instance in a single test class, you need to create a container definition and then use its parameters to establish a connection:

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(initializers = {UserRepositoryTCIntegrationTest.Initializer.class})
public class UserRepositoryTCIntegrationTest extends UserRepositoryCommonIntegrationTests {

 @ClassRule
 public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:11.1")
 .withDatabaseName("integration-tests-db")
 .withUsername("sa")
 .withPassword("sa");

 static class Initializer
 implements ApplicationContextInitializer<ConfigurableApplicationContext> {
 public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
 TestPropertyValues.of(
 "spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
 "spring.datasource.username=" + postgreSQLContainer.getUsername(),
 "spring.datasource.password=" + postgreSQLContainer.getPassword()
 ).applyTo(configurableApplicationContext.getEnvironment());
 }
 }
}

In the example above, we used @ClassRule from JUnit to set up the database container before executing the test methods. We also created a static inner class that implements the ApplicationContextInitializer. Finally, we have applied the @ContextConfiguration annotation to our test class with the initialization class as a parameter.

After completing these three steps, we can set the connection parameters before publishing the Spring context.

Now we use two UPDATE queries:

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status,
 @Param("name") String name);

@Modifying
@Query(value = "UPDATE Users u SET u.status = ? WHERE u.name = ?",
 nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

And test in the configured runtime environment:

@Test
@Transactional
public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationJPQL_ThenModifyMatchingUsers(){
 insertUsers();
 int updatedUsersSize = userRepository.updateUserSetStatusForName(0, "SAMPLE");
 assertThat(updatedUsersSize).isEqualTo(2);
}

@Test
@Transactional
public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationNative_ThenModifyMatchingUsers(){
 insertUsers();
 int updatedUsersSize = userRepository.updateUserSetStatusForNameNative(0, "SAMPLE");
 assertThat(updatedUsersSize).isEqualTo(2);
}

private void insertUsers() {
 userRepository.save(new User("SAMPLE", "[email protected]", 1));
 userRepository.save(new User("SAMPLE1", "[email protected]", 1));
 userRepository.save(new User("SAMPLE", "[email protected]", 1));
 userRepository.save(new User("SAMPLE3", "[email protected]", 1));
 userRepository.flush();
}

In the script above, the first test succeeds, and the second throws an InvalidDataAccessResourceUsageException with the message:

Caused by: org.postgresql.util.PSQLException: ERROR: column "u" of relation "users" does not exist

If we were to run the same tests using the embedded H2 database, both would succeed, but PostgreSQL does not accept aliases in a SET statement. We can quickly fix the request by removing the problematic alias:

@Modifying
@Query(value = "UPDATE Users u SET status = ? WHERE u.name = ?",
 nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

This time both tests passed successfully. In this example, we've used Testcontainers to detect a problem with a native query that would otherwise only be discovered after migrating to a production database. It should also be noted that using JPQL queries is generally safer because Spring translates them correctly depending on the database provider used.

Shared Database Instance

In the previous section, we described how to use Testcontainers in a unit test. In real cases, I would like to use the same database container in several tests due to the relatively long startup time.

Let's create a generic class for creating a database container by inheriting PostgreSQLContainer and overriding the start() and stop() methods:

public class BaeldungPostgresqlContainer extends PostgreSQLContainer<BaeldungPostgresqlContainer> {
 private static final String IMAGE_VERSION = "postgres:11.1";
 private static BaeldungPostgresqlContainer container;

 private BaeldungPostgresqlContainer() {
 super(IMAGE_VERSION);
 }

 public static BaeldungPostgresqlContainer getInstance() {
 if (container == null) {
 container = new BaeldungPostgresqlContainer();
 }
 return container;
 }

 @Override
 public void start() {
 super.start();
 System.setProperty("DB_URL", container.getJdbcUrl());
 System.setProperty("DB_USERNAME", container.getUsername());
 System.setProperty("DB_PASSWORD", container.getPassword());
 }

 @Override
 public void stop() {
 //do nothing, JVM handles shut down
 }
}

Leaving the stop() method empty allows the JVM to handle the termination of the container itself. 

We'll also implement a simple singleton where only the first test starts the container, and each subsequent test uses an existing instance. 

In the start() method, we use System#setProperty to store the connection settings in environment variables.

Now we can write them to the application.properties file:

spring.datasource.url=${DB_URL}
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}

Now we use our utility class in the test definition:

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserRepositoryTCAutoIntegrationTest {

 @ClassRule
 public static PostgreSQLContainer postgreSQLContainer = BaeldungPostgresqlContainer.getInstance();

 // tests
}

As in the previous examples, we have applied the @ClassRule annotation to the field with the container definition. This way, the DataSource connection parameters are populated with the correct values before the Spring context is created.

We can now implement multiple tests using the same database instance by simply defining a @ClassRule annotated field created with our BaeldungPostgresqlContainer utility class.

Conclusion

In this article, we have shown testing methods on a production database using Testcontainers.

We also looked at examples of using a unit test using the ApplicationContextInitializer mechanism from Spring, as well as class implementations for reusing a database instance.

We also showed how Testcontainers can help identify compatibility issues between multiple database vendors, especially for native queries.