Spring Boot & Flyway - clear database between integration tests

To make sure that tests are repeatable and isolated, it is a good practice to ensure that they always start with a clean state. In unit testing this could be mocking dependencies, or setting certain properties on objects. In integration testing it often means bringing the database to a well known state - usually erasing all the tables, and inserting the data that the integration tests expect.

In this short tutorial you'll see how to clean a database between integration tests. The recipe assumes that you use: relational database (like MySQL or PostgreSQL), Spring Boot, Flyway - for database migrations, JUnit 5.

Even if you don't use exactly these technologies, overall idea is quite generic, so I am sure you can adjust it to your needs.

You will also learn the following:

• why it is important to clear the database in integration tests
• how to clear the database using JdbcTemplate, Spring Data repositories and Flyway
• how to create custom JUnit 5 extension
• how to create JUnit 5 meta-annotations

Sample test ​

Typically, each application has at least few "end to end" integration tests - that execute HTTP requests and interact with a real database. No mocks, no stubs - these tests are meant to ensure that all pieces of the application work together.

In the code below, there are two tests:

• one executes a GET request and expects an empty array - to verify the behavior when there are no Person entities saved
• the second one verifies that POST request saves person in the database.

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class AppTests {
    
    @Autowired
    private TestRestTemplate restTemplate;

    @Test
    void returnsEmptyArrayWhenNoPersonSaved() {
        ResponseEntity<List<Person>> response = restTemplate.exchange("/person", HttpMethod.GET, null,
                new ParameterizedTypeReference<>() { });
        assertThat(response.getBody()).isEmpty();

    }

    @Test
    void savesPerson() {
        ResponseEntity<Void> createResponse = restTemplate.postForEntity("/person", new PersonDTO("john"), Void.class);
        assertThat(createResponse.getStatusCode().is2xxSuccessful()).isTrue();

        ResponseEntity<List<Person>> listResponse = restTemplate.exchange("/person", HttpMethod.GET, null,
                new ParameterizedTypeReference<>() { });
        assertThat(listResponse.getBody()).hasSize(1);
    }
}

These tests work well when they are executed either one by one, or in the correct order. If savesPerson runs first, then the database will already have Person entries and returnsEmptyArrayWhenNoPersonSaved test will fail.

Let's fix it by making sure that each test starts with a clean state.

JUnit 5 @BeforeEach ​

With JUnit 5, we can execute a piece of code before each test with @BeforeEach annotation:

@BeforeEach
void clearDatabase() {
    // ...
}

Since it is common to clean database tables in tests, Spring offers an utility JdbcTestUtils#deleteFromTables.

@BeforeEach
void clearDatabase(@Autowired JdbcTemplate jdbcTemplate) {
    JdbcTestUtils.deleteFromTables(jdbcTemplate, "person");
}

Alternatively, instead of using JdbcTestUtils, we can inject the repository class - assuming the repository class has deleteAll method:

@BeforeEach
void clearDatabase(@Autowired PersonRepository personRepository) {
    personRepository.deleteAll();
}

Out of these two options, I would choose JdbcTestUtils as it is not bound to a particular table like repository classes, but in fact both of these approaches come with drawbacks:

• sequences are not deleted
• if there are INSERT statements as a part of the database setup, they will be lost
• the list of tables to clean has to be kept up to date whenever we add new entities.

To summarize, it requires certain amount of maintenance work.

Flyway#clean() ​

Flyway comes with a handy method that solves all the issues mentioned above - Flyway#clean(). When used together with Flyway#migrate(), it will erase the database, and run all database migrations bringing the database to pristine state as it would be after the first run.

Spring Boot autoconfigures Flyway bean, so it can be injected into @BeforeEach method:

@BeforeEach
void clearDatabase(@Autowired Flyway flyway) {
    flyway.clean();
    flyway.migrate();
}

When we re-run the test, it will fail with a message like:

org.flywaydb.core.api.FlywayException: Unable to execute clean as it has been disabled with the 'flyway.cleanDisabled' property.

flyway.clean() method is disabled by default, because when used in production it would literally erase the database. It needs to be explicitly enabled for tests through setting spring.flyway-clean-disabled to false, either in:

• application.properties file
• properties field in @SpringBootTest annotation
• properties field in @TestPropertySource annotation

@SpringBootTest(
        webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, 
        properties = "spring.flyway.clean-disabled=false"
)

The price for this approach is that Flyway migrations get executed for each test - this takes some time. Usually it should not be an issue, but just keep in mind that your test suite may take some seconds more to execute - depending on how many database migration scripts are there. If this is a problem, take a look at Squashing DB migrations using Testcontainers by Mike Kowalski.

Considering clearing database should happen for each integration test, and in real-life project you will have multiple integration test classes, to avoid repetition this method would need to be moved either to a parent class for each integration test (although I don't recommend that), or even better - to a custom JUnit extension.

@ClearDatabase JUnit Extension ​

Instead of clearing the database in @BeforeEach method, we can move this functionality to a JUnit extension:

import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.extension.BeforeEachCallback;
import org.junit.jupiter.api.extension.ExtensionContext;

import org.springframework.test.context.junit.jupiter.SpringExtension;

public class ClearDatabaseExtension implements BeforeEachCallback {
    @Override public void beforeEach(ExtensionContext extensionContext) throws Exception {
        Flyway flyway = SpringExtension.getApplicationContext(extensionContext)
                .getBean(Flyway.class);
        flyway.clean();
        flyway.migrate();
    }
}

Thanks to SpringExtension#getApplicationContext, we are able to access any bean from the Spring Boot application context. In this case, we expect a single bean of type Flyway - which is the default for Spring Boot & Flyway integration, but if you have multiple datasources and multiple Flyway beans, you may need to adjust this extension to your needs.

Now any class that needs clearing database, can use @ExtendWith(ClearDatabaseExtension.class):

@SpringBootTest(
        webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT,
        properties = "spring.flyway.clean-disabled=false")
@ExtendWith(ClearDatabaseExtension.class)
class AppTests {

    @Test
    void returnsEmptyArrayWhenNoPersonSaved() {
        // ...
    }

    @Test
    void savesPerson() {
        // ...
    }
}

While we removed the need to use @BeforeEach method in each test class, some remains - we must remember to enable Flyway cleaning, and to use @ExtendWith. This can be solved by creating a custom JUnit meta annotation that will do both of these.

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import org.junit.jupiter.api.extension.ExtendWith;

import org.springframework.test.context.TestPropertySource;

@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@ExtendWith(ClearDatabaseExtension.class)
@TestPropertySource(properties = "spring.flyway.clean-disabled=false")
public @interface ClearDatabase {
}

Now the test class can be simplified to:

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ClearDatabase
class AppTests {

    @Test
    void returnsEmptyArrayWhenNoPersonSaved() {
        // ...
    }

    @Test
    void savesPerson() {
        // ...
    }
}

What's next ​

Let me know in the comments if you find it useful!

Simplifying test setup can be taken even further with implementing custom @IntegrationTest annotation, but let's leave it for the next article.