How to use jOOQ with Testcontainers and, Flyway

When using a database the question is how to manage the versions of the schema and how to use the database in testing. Container technologies became a defacto standard. Why not use containers for development and your database?

Testcontainers is a Java library that supports JUnit tests, providing lightweight, throwaway instances of common databases, Selenium web browsers, or anything else that can run in a Docker container.
(Source: www.testcontainers.org)

Sounds perfect, right? And what about database versioning?

Flyway. Version control for your database. Robust schema evolution across all environments. With ease, pleasure, and plain SQL. (Source: flywaydb.org)

Awesome! Now let’s have a look at how we can use these two technologies in combination with jOOQ to generate the Java code and test the application. The example project uses MariaDB but would work with any database. You’ll find the source code on GitHub: github.com/simasch/jooq-mariadb

The Build

The example uses Maven as the build tool. And the process is as follows

  1. Start MariaDB
    A Testcontainer with MariaDB is started using the Groovy plugin
  2. Execute Flyway migrations
    Flyway migrations are applied to the MariaDB
  3. Generate jOOQ Code
    jOOQ generates code from the MariaDB objects
  4. Compile
    Code and generated classes are compiled
  5. Run Tests
    For each test, Testcontainers starts a MariaDB container

Bellow, you can see how the Maven plugins are configured.

<!-- Start Testcontainer -->
<plugin>
    <groupId>org.codehaus.gmaven</groupId>
    <artifactId>groovy-maven-plugin</artifactId>
    <version>2.1.1</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <source>
                    db = new org.testcontainers.containers.MariaDBContainer("mariadb:10.7.3")
                            .withUsername("${db.username}")
                            .withDatabaseName("${db.database}")
                            .withPassword("${db.password}")
                    db.start()
                    project.properties.setProperty('db.url', db.getJdbcUrl())
                </source>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mariadb</artifactId>
            <version>${testcontainers.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>${mariadb.version}</version>
        </dependency>
    </dependencies>
</plugin>
<!-- Migrate schema -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.14.0</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
            <configuration>
                <url>${db.url}</url>
                <user>${db.username}</user>
                <password>${db.password}</password>
                <locations>
                    <location>filesystem:src/main/resources/db/migration</location>
                </locations>
            </configuration>
        </execution>
    </executions>
</plugin>
<!-- Generate jOOQ code -->
<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbc>
            <driver>${db.driver}</driver>
            <url>${db.url}</url>
            <user>${db.username}</user>
            <password>${db.password}</password>
        </jdbc>
        <generator>
            <database>
                <inputSchema>sakila</inputSchema>
                <forcedTypes>
                    <forcedType>
                        <name>BOOLEAN</name>
                        <includeTypes>(?i:TINYINT\(1\))</includeTypes>
                    </forcedType>
                </forcedTypes>
            </database>
            <target>
                <packageName>ch.martinelli.sakila</packageName>
            </target>
            <generate>
                <pojos>true</pojos>
                <pojosToString>false</pojosToString>
                <daos>true</daos>
            </generate>
        </generator>
    </configuration>
</plugin>

That’s it. Easy, isn’t it?

If you are new to jOOQ don’t miss my introduction video: