Database Integration Tests with Spring Boot and Testcontainers

1. Overview

With Spring Data JPA, you can easily create database queries and test them using the built-in H2 database.

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

In this guide, we will show how to use Testcontainers for integration testing with Spring Data JPA and PostgreSQL database.

In the previous article, we created several database queries, using mainly the @Query annotation , which we are testing now.

2. Configuration

To use the PostgreSQL database in our tests, we need to add the Testcontainers dependency of only tests and the PostgreSQL driver to our pom.xml file :

org.testcontainerspostgresql1.10.6testorg.postgresqlpostgresql42.2.5

We will also create the application.properties file in the testing resources directory , in which we will set Spring to use the required driver class, as well as create and delete the database schema each time the test runs:

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

3. 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 the 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 {
        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 @ClassRulefrom JUnit to configure the database container before executing the test methods . We also created a static inner class that implements ApplicationContextInitializer . Finally, we applied the annotation @ContextConfigurationto our test class with the initializing 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 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 in a tuned 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@example.com", 1));
    userRepository.save(new User("SAMPLE1", "email2@example.com", 1));
    userRepository.save(new User("SAMPLE", "email3@example.com", 1));
    userRepository.save(new User("SAMPLE3", "email4@example.com", 1));
    userRepository.flush();
}

In the above scenario, 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 ran the same tests using the built-in H2 database, both would be successful, but PostgreSQL does not accept aliases in the 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 used Testcontainers to identify a problem with a native query that would otherwise be detected only after going to the production database . It should also be noted that using JPQL queries is generally safer, since Spring translates them correctly depending on the database provider used.

4. Shared database instance

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

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

public class BaeldungPostgresqlContainer extends PostgreSQLContainer {
    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, we enable the JVM to handle the completion of the container on its own. We also implement a simple singleton, in which only the first test starts the container, and each subsequent test uses an existing instance. In the start () method, we use System # setProperty to save the connection parameters to 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 applied the @ClassRule annotation to the field with the container definition. Thus, the DataSource connection parameters are populated with the correct values ​​before creating the Spring context.

Now we can implement several tests using the same database instance by simply setting the field with the @ClassRule annotation created using our utility class BaeldungPostgresqlContainer .

5. Conclusion

In this article, we showed test methods on a production database using Testcontainers.

We also examined examples of using a single test using the ApplicationContextInitializer mechanism from Spring, as well as implementing a class for reusing a database instance.

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

As always, the full code used in this article is available on GitHub .