DB Integration Tests with Spring Boot and Testcontainers

1. Overview

Spring Data JPA provides an easy way to create database queries and test them with an embedded H2 database.

But in some cases, testing on a real database is much more profitable, especially if we use provider-dependent queries.

In this tutorial, we’ll demonstrate how to use Testcontainers for integration testing with Spring Data JPA and the PostgreSQL database.

In our previous tutorial, we created some database queries using mainly the @Query annotation, which we’ll now test.

2. Configuration

To use the PostgreSQL database in our tests, we have to add the Testcontainers dependency with test scope:

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

Let’s also create an application.properties file under the test resources directory in which we instruct Spring to use the proper driver class and to create the scheme at each test run:

spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.jpa.hibernate.ddl-auto=create

3. Single Test Usage

To start using the PostgreSQL instance in a single test class, we have to create a container definition first and then use its parameters to establish a connection:

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
@ActiveProfiles("tc")
@ContextConfiguration(initializers = {UserRepositoryTCLiveTest.Initializer.class})
public class UserRepositoryTCLiveTest extends UserRepositoryCommon {

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

    @Test
    @Transactional
    public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationNative_ThenModifyMatchingUsers() {
        userRepository.save(new User("SAMPLE", LocalDate.now(), USER_EMAIL, ACTIVE_STATUS));
        userRepository.save(new User("SAMPLE1", LocalDate.now(), USER_EMAIL2, ACTIVE_STATUS));
        userRepository.save(new User("SAMPLE", LocalDate.now(), USER_EMAIL3, ACTIVE_STATUS));
        userRepository.save(new User("SAMPLE3", LocalDate.now(), USER_EMAIL4, ACTIVE_STATUS));
        userRepository.flush();

        int updatedUsersSize = userRepository.updateUserSetStatusForNameNativePostgres(INACTIVE_STATUS, "SAMPLE");

        assertThat(updatedUsersSize).isEqualTo(2);
    }

    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 above example, we used @ClassRule from JUnit to set up a database container before executing test methods. We also created a static inner class that implements ApplicationContextInitializer. As the last step, we applied the @ContextConfiguration annotation to our test class with the initializer class as a parameter.

By performing these three actions, we can set connection properties before the Spring context is published.

Let’s now use two UPDATE queries from the previous article:

@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 them with the configured 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 above scenario, the first test ends with success but the second throws InvalidDataAccessResourceUsageException with the message:

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

If we’d run the same tests using the H2 embedded database, both tests would complete successfully, but PostgreSQL does not accept aliases in the SET clause. We can quickly fix the query 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 complete successfully. In this example, we used Testcontainers to identify a problem with the native query which otherwise would be revealed after switching to a real database on production. We should also notice that using JPQL queries is safer in general because Spring translates them properly depending on the database provider used.

3.1. One Database per Test with Configuration

So far, we’ve used JUnit 4 rules to spin up a database instance before running all tests inside a test class. Eventually, this approach will create a database instance before each test class and tear it down after running all tests in each class.

This approach creates maximum isolation between the test instances. Also, the overhead of launching a database multiple times can make tests slow.

In addition to the JUnit 4 rules approach, we can modify the JDBC URL and instruct the Testcontainers to create a database instance per test class. This approach will work without requiring us to write some infrastructural code in our tests.

For instance, in order to rewrite the above example, all we have to do is to add this to our application.properties:

spring.datasource.url=jdbc:tc:postgresql:11.1:///integration-tests-db

The “tc:” will make Testcontainers instantiate database instances without any code change. So, our test class would be as simple as:

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class UserRepositoryTCJdbcLiveTest extends UserRepositoryCommon {

    @Test
    @Transactional
    public void givenUsersInDB_WhenUpdateStatusForNameModifyingQueryAnnotationNative_ThenModifyMatchingUsers() {
        // same as above
    }
}

If we’re going to have one database instance per test class, this approach is the preferred one.

4. Shared Database Instance

In the previous paragraph, we described how to use Testcontainers in a single test. In a real case scenario, we’d like to reuse the same database container in multiple tests because of the relatively long startup time.

Let’s now create a common class for database container creation by extending 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
    }
}

By leaving the stop() method empty, we allow the JVM to handle the container shutdown. We also implement a simple singleton pattern, in which only the first test triggers container startup, and each subsequent test uses the existing instance. In the start() method we use System#setProperty to set connection parameters as environment variables.

We can now put them in our application.properties file:

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

Let’s now use our utility class in the test definition:

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
@ActiveProfiles({"tc", "tc-auto"})
public class UserRepositoryTCAutoLiveTest extends UserRepositoryCommon {

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

    //tests
}

As in previous examples, we applied the @ClassRule annotation to a field holding the container definition. This way, the DataSource connection properties are populated with correct values before Spring context creation.

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

5. Conclusion

In this article, we illustrated ways to perform tests on a real database instance using Testcontainers.

We looked at examples of single test usage, using the ApplicationContextInitializer mechanism from Spring, as well as implementing a class for reusable database instantiation.

We also showed how Testcontainers could help in identifying compatibility problems across multiple database providers, especially for native queries.