Glen Mazza's Weblog

For a Spring Boot application accessing a Flyway-managed MySQL database, I updated its integration tests from using in-memory HSQLDB to Testcontainers' MySQL module. This was both to have testing be done on a database more closely matching deployment environments and also to have various tables pre-populated with standard data provided by our Flyway migration scripts. I'm happy to report that the cost of these benefits was only a slight increase in test running time (perhaps 10-15% more time), and that despite there being 170 Flyway migrations at that time of the conversion.

It is best to use Testcontainers when starting to develop the application, so any hiccups found in a Flyway migration file can be fixed before that migration file becomes final. Switching to testcontainers after-the-fact uncovered problems with some of our migration scripts requiring additional configuration of the MySQL testcontainer. The main problems were unnecessary explicit specification of the schema name in the scripts, and a suboptimal definition of a table that required explicit_defaults_for_timestamp to be configured in MySQL. This configuration was in our deployment my.cnf files but not in the default one used by the MySQL testcontainer.

Solving the first issue involved explicit specification of the username, password, and database name when creating the Testcontainers instance. Initializing the MySQL container just once for all integration tests is sufficient for our particular application, so TC's Manual container lifecycle control which uses Java configuration was used:

@SpringBootTest
@ActiveProfiles("itest")
public class MyAppIntegrationTest {

    private static final MySQLContainer MY_SQL_CONTAINER;

    static {
        MY_SQL_CONTAINER = new MySQLContainer("mysql:5.7")
                .withUsername("myappUser")
                .withPassword("myappPass")
                .withDatabaseName("myapp");
        MY_SQL_CONTAINER.start();
    }

    @DynamicPropertySource
    public static void containersProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.username", MY_SQL_CONTAINER::getUsername);
        registry.add("spring.datasource.password", MY_SQL_CONTAINER::getPassword);
        registry.add("spring.datasource.url", MY_SQL_CONTAINER::getJdbcUrl);
    }
}

The containersProperties call dynamically populates the given Spring boot config values with those provided from the MySQL testcontainer. Note the spring.datasource.url will be a "regular" MySQL URL, i.e., Spring Boot and Flyway are unaware that the database is a Testcontainers-provided one.

When doing Java-based instantiation of the MySQL instance as above, I've found adding the standard property file configuration of the MySQL testcontainer to be unnecessary and best avoided -- doing so appeared to create a second, unused, container instance, slowing down builds. However, if you choose to use properties-only Java configuration (perhaps wishing to instantiate and initialize MySQL testcontainers more frequently during the integration tests), configuration similar to the below should work. Note the "myappdatabase", "myuser" and "mypass" values given in the url property are used to tell Testcontainers the values to set when creating the database and the user. In turn, whatever values placed here should go into the standard Spring username and password properties as shown:

spring.flyway.enabled=true
spring.datasource.url=jdbc:tc:mysql:5.7://localhost/myappdatabase?user=myuser&password=mypass
spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.username=myuser
spring.datasource.password=mypass

Fixing the second issue involved using a different my.cnf for the MySQL testcontainer. To accomplish that I copied the mysql-default-conf/my.cnf directory and file from the org.testcontainers.mysql:1.17.6 library (easily viewable from IntelliJ IDEA) and pasted it as src/itest/resources/mysql-default-conf/my.cnf in the Spring Boot application. From the latter location I added my needed change.

Notes:

• The Gradle dependencies used to activate the MySQL testcontainer in the integration test environment:

  configurations {
      integrationTestImplementation.extendsFrom testImplementation
      integrationTestRuntimeOnly.extendsFrom testRuntimeOnly
  }
  
  dependencies {
      integrationTestImplementation 'org.springframework.boot:spring-boot-starter-test'
      integrationTestImplementation 'org.testcontainers:mysql'
      integrationTestImplementation 'org.testcontainers:junit-jupiter'
  }
  

• As the migrations are kept in the main/resources folder, in the standard db/migration subdirectory, I thought I could perhaps just create overriding migrations in the corresponding itest/resources subdirectory for the few migrations with problems, and that Flyway would use the overrides over the ones in the main/resources folder. That did not work, though, Flyway halts with a complaint about duplicate filenames between the two folder locations.

Further resources:

• One-Stop Guide to Database Migration with Flyway and Spring Boot - Article from Reflectoring.io covering Spring Boot and Flyway together (not Testcontainers-specific).
• Execute Flyway Database Migrations on Startup - SpringBoot docs