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:

Introduction to Database Migrations with Spring Boot and Flyway

If you are working with Spring Boot there are several ways to initialize the database.

You could create a schema.sql, add it to the classpath, and set spring.datasource.initialization-mode=true. Spring will execute the SQL script on startup.

Or you could use the Hibernate built-in functionality and set spring.jpa.hibernate.ddl-auto to create or update.

But both approaches are very limited and if you have to deal with an evolving database you may look for something more sophisticated.
That’s where database migration tools fill the gap.

Database migration tools

In the Java environment, there are two open-source projects available: Flyway and Liquibase. The difference between these two is that Flyway uses SQL for the migrations and Liquibase supports additional abstractions in XML, YAML, or JSON.

Both tools are very well integrated with Spring Boot. You simply have to add the right dependency in your pom.xml or Gradle build file.
Read more about why you should care about database migrations here.

Flyway with Spring Boot

In my example, I use Flyway with Spring Boot. To use it simply add this dependency and Spring Boot will auto-configure Flyway right away.

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Now you’re ready to start adding your migration SQL files to your classpath in the path classpath:db/migration

Naming Conventions

To have Flyway execute your SQL scripts you have to follow some naming conventions.

  • Prefix (V, U or R)V = Versioned migrations that are executed only once
  • U = Undo migrations (only supported in the commercial version)
  • R = Repeatable migrations. Scripts that could run with every execution
  • Version
  • Separator __
  • Description
  • Suffix: (.sql)

Example: V1__Init.sql

Example Migration

V1__Init.sql could look like

CREATE TABLE address (
    id integer NOT NULL,
    city character varying(255),
    state character varying(255),
    street character varying(255),
    zip character varying(255),
    created_date timestamp,
    last_modified_date timestamp
);

How it Works

Flyway uses a table to store the version of the database. The default name of this table is flyway_schema_history and the content looks like this:

As you can see Flyway stores the actual version and is, therefore, able to know which migrations scripts have to be executed.

Further Reading

That was just a brief introduction. Flyway has way more to offer and you will find all you need to know in the official documentation on flywaydb.org