How to use jOOQ with Testcontainers and, Flyway

blog-post-img
By Simon Martinelli
29.04.2022

Struggling to manage database schema versions and testing in your Java application? This guide shows you how to streamline the process using jOOQ, Testcontainers, and Flyway. I’ll explore how to streamline development workflows by automating schema migrations, generating Java code from your database, and running tests in isolated Docker containers.

The Challenge of Database Schema Management

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?

The Solution: Leveraging jOOQ, Testcontainers, and Flyway

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.

jOOQ Code Generation with Maven

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>

Conclusion: Streamlining Database Management

That’s it. Easy, isn’t it? Ready to experience the benefits of jOOQ, Testcontainers, and Flyway for yourself? You’ll find the source code on GitHub: github.com/simasch/jooq-mariadb

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

Related Posts

Simon Martinelli
Follow Me
Latest posts by Simon Martinelli (see all)