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

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.

CodePudding user response：

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:

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.
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.
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.

All of these methods would solve your problem, probably.