Don't want to use DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD with TestContainers

Question

I am doing an integration test with testcontainers and spring-boot and I am having an issue while initializing the scripts. I have 2 scripts: schema.sql and data.sql.

When I use DirtiesContext.ClassMode.AFTER_EACH_TEST_METHODit works fine, but it is not a good idea to rerun a new container after each test. Of course that make the tests very slow.

At the other hand when I use DirtiesContext.ClassMode.AFTER_CLASS I have this exception:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [sql/mariadb/schema.sql]: DROP TABLE IF EXISTS client; nested exception is java.sql.SQLIntegrityConstraintViolationException: (conn=4) Cannot delete or update a parent row: a foreign key constraint fails at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282) ~[spring-jdbc-5.3.13.jar:5.3.13] at ... Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=4) Cannot delete or update a parent row: a foreign key constraint fails ... Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Cannot delete or update a parent row: a foreign key constraint fails

The base class:

@Testcontainers
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("it")
@Sql({"/sql/mariadb/schema.sql", "/sql/mariadb/data.sql"})
@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_CLASS)
public abstract class BaseIntegrationTest implements WithAssertions {

    @Container
    protected static MariaDBContainer<?> CONTAINER = new MariaDBContainer<>("mariadb:10.6.5");

    @Autowired
    protected ObjectMapper mapper;

    @Autowired
    protected WebTestClient webTestClient;
}

The integration test:

class ClientControllerITest extends BaseIntegrationTest {

    @Test
    void integrationTest_For_FindAll() {
        webTestClient.get()
                .uri(ApplicationDataFactory.API_V1 + "/clients")
                .exchange()
                .expectStatus().isOk()
                .expectBody(Success.class)
                .consumeWith(result -> {
                    assertThat(Objects.requireNonNull(result.getResponseBody()).getData()).isNotEmpty();
                });
    }

    @Test
    void integrationTest_For_FindById() {
        webTestClient.get()
                .uri(ApplicationDataFactory.API_V1 + "/clients/{ID}", CLIENT_ID)
                .exchange()
                .expectStatus().isOk()
                .expectBody(Success.class)
                .consumeWith(result -> {
                    var clients = mapper.convertValue(Objects.requireNonNull(result.getResponseBody()).getData(),
                            new TypeReference<List<ClientDto>>() {
                            });
                    var foundClient = clients.get(0);
                    assertAll(
                            () -> assertThat(foundClient.getId()).isEqualTo(CLIENT_ID),
                            () -> assertThat(foundClient.getFirstName()).isEqualTo(CLIENT_FIRST_NAME),
                            () -> assertThat(foundClient.getLastName()).isEqualTo(CLIENT_LAST_NAME),
                            () -> assertThat(foundClient.getTelephone()).isEqualTo(CLIENT_TELEPHONE),
                            () -> assertThat(foundClient.getGender()).isEqualTo(CLIENT_GENDER_MALE.name())
                    );
                });
    }

    @Test
    void integrationTest_For_Create() {
        var newClient = createNewClientDto();

        webTestClient.post()
                .uri(ApplicationDataFactory.API_V1 + "/clients")
                .accept(MediaType.APPLICATION_JSON)
                .bodyValue(newClient)
                .exchange()
                .expectStatus().isOk()
                .expectBody(Success.class)
                .consumeWith(result -> {
                    var clients = mapper.convertValue(Objects.requireNonNull(result.getResponseBody()).getData(),
                            new TypeReference<List<ClientDto>>() {
                            });
                    var createdClient = clients.get(0);
                    assertAll(
                            () -> assertThat(createdClient.getId()).isEqualTo(newClient.getId()),
                            () -> assertThat(createdClient.getFirstName()).isEqualTo(newClient.getFirstName()),
                            () -> assertThat(createdClient.getLastName()).isEqualTo(newClient.getLastName()),
                            () -> assertThat(createdClient.getTelephone()).isEqualTo(newClient.getTelephone()),
                            () -> assertThat(createdClient.getGender()).isEqualTo(newClient.getGender())
                    );
                });
    }

    @Test
    void integrationTest_For_Update() {
        var clientToUpdate = createNewClientDto();
        clientToUpdate.setFirstName(CLIENT_EDITED_FIRST_NAME);

        webTestClient.put()
                .uri(ApplicationDataFactory.API_V1 + "/clients")
                .accept(MediaType.APPLICATION_JSON)
                .bodyValue(clientToUpdate)
                .exchange()
                .expectStatus().isOk()
                .expectBody(Success.class)
                .consumeWith(result -> {
                    var clients = mapper.convertValue(Objects.requireNonNull(result.getResponseBody()).getData(),
                            new TypeReference<List<ClientDto>>() {
                            });
                    var updatedClient = clients.get(0);
                    assertAll(
                            () -> assertThat(updatedClient.getId()).isEqualTo(clientToUpdate.getId()),
                            () -> assertThat(updatedClient.getFirstName()).isEqualTo(clientToUpdate.getFirstName()),
                            () -> assertThat(updatedClient.getLastName()).isEqualTo(clientToUpdate.getLastName()),
                            () -> assertThat(updatedClient.getTelephone()).isEqualTo(clientToUpdate.getTelephone()),
                            () -> assertThat(updatedClient.getGender()).isEqualTo(clientToUpdate.getGender())
                    );

                });
    }

    @Test
    void integrationTest_For_Delete() {
        webTestClient.delete()
                .uri(ApplicationDataFactory.API_V1 + "/clients/{ID}", CLIENT_ID)
                .exchange()
                 .expectStatus().isOk();
    }
}

schema.sql:

DROP TABLE IF EXISTS `client`;
CREATE TABLE `client` (
  `id` bigint(20) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `telephone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

data.sql

INSERT INTO client (id, first_name, last_name, gender, telephone) VALUES(1, 'XXX', 'XXX', 'MALE', 'XXX-XXX-XXXX');
INSERT INTO client (id, first_name, last_name, gender, telephone) VALUES(2, 'XXX', 'XXX', 'MALE', 'XXX-XXX-XXXX');

I am missing something? An advice will be very welcomed.

Answer 1

The @Sql will be executed, per default, BEFORE_TEST_METHOD. So this is run before each test method. In other words, before any test is run, that SQL is executed. But of course, by re-using the same database for multiple tests, this can run into an error, if the sql script isn't "idempotent", in other words, if the sql script cannot be applied safely twice to the same database.

To make it work, there are multiple possibilities, for example:

1. adding one for the AFTER_TEST_METHOD to cleanup. This then would basically remove all the stuff that was added by this test (including the things added by the @Sql before). This way, your db is "cleaned up" after each test run and every run can apply the same sql script again safely.

2. Or make it safe to be executed multiple times. This depends on the scripts, but if you can write the SQL in a way that allows you to run it twice without error, this would also work.

3. Without using @Sql, you could also configure a DatabasePopulator bean in your test config. This way, your SQL code would only run ONCE, when the whole application context is created.

4. You could also try using @Transactional on your test, which would make Spring wrap a transaction around your test execution and roll it back afterwards. Unfortunately I have currently no clue how nice this plays with @Sql, might work, might not, but I would give it a 75% chance of working. And of course, it has some implications (nothing is actually finally written to the DB and if you are using transactions inside your code, there might be complications).

All of these methods would solve your problem, probably.

Answer 2

The answer are placed in @TestContainer annotation docs:

      // will be shared between test methods
      @Container
      private static final MySQLContainer MY_SQL_CONTAINER = new MySQLContainer();
 
      // will be started before and stopped after each test method
      @Container
      private PostgreSQLContainer postgresqlContainer = new PostgreSQLContainer()
              .withDatabaseName("foo")
              .withUsername("foo")
              .withPassword("secret");

Answer 3

Encountered the same problem, I made it work placing following annotations over test methods

@Test
@Transactional
@Rollback //auto rollback test transaction
void test() {...}