Coming Up for Air

Hands-free Flyway and Jooq

Recently, I started working on a new project and I wanted to give Jooq a go. I also wanted to integrate Flyway: I wanted Jooq to generate its various classes based off the database schema, and I want to Flyway to create that schema. That’s all easy enough, but I’m resisting, right now, committing the generated classes to source control (to avoid the churn and additional maintenance), so how do I make that happen with as little work as possible? How do I make it work in a CI environment? Thanks to Maven, the answer is lots and lots of XML. :) Let’s take a look…​

Adding the dependencies

To add Jooq and Flyway to a project, you need these dependencies:

<properties>
    <jooq.version>3.13.1</jooq.version>
    <flyway.version>6.4.2</flyway.version>
</properties>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>${jooq.version}</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>${jooq.version}</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <version>${jooq.version}</version>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>${flyway.version}</version>
</dependency>

This will enable the use of the Jooq libraries in your code, as well as for runtime Flyway migrations. The mechanics of both of those are outside the scope of this post, so, if you need help there, please see the respective project web sites.

Setting up build-time Flyway

The next step is setting up the build to run the Jooq generator. For there to be anything to generate, we need Flyway to generate the schema. For this project, I’m using H2 for tests, so I’m going to configure Maven and Flyway create an H2 database:

<properties>
    <flyway.version>6.4.2</flyway.version>
    <flyway.url>jdbc:h2:file:${project.build.directory}/testdb</flyway.url>
    <flyway.user>sa</flyway.user>
    <flyway.password>sa</flyway.password>
    <h2.version>1.4.200</h2.version>
</properties>
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>${flyway.version}</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <locations>
            <location>filesystem:src/main/resources/db/migration</location>
        </locations>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
        </dependency>
    </dependencies>
</plugin>

This tells Flyway to create a database called testdb in the build directory, then generate the schema using the production migrate files in the source directory.

Generate the Jooq classes

With the schema prepared, we can generate sources:

<properties>
    <jooq.outputdir>target/generated-sources/jooq</jooq.outputdir>
</properties>
<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${jooq.version}</version>

    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
        </dependency>
    </dependencies>

    <configuration>
        <jdbc>
            <url>${flyway.url}</url>
            <user>${flyway.user}</user>
            <password>${flyway.password}</password>
        </jdbc>
        <generator>
            <database>
                <includes>.*</includes>
                <excludes>
                    Flyway.*
                    | All.*
                    | SchemaVersion.*
                </excludes>
                <inputSchema>PUBLIC</inputSchema>
                <outputSchema>public</outputSchema>
                <properties>
                    <property>
                        <key>dialect</key>
                        <value>H2</value>
                    </property>
                </properties>
            </database>
            <target>
                <packageName>com.example.backend.models.jooq</packageName>
                <directory>${jooq.outputdir}</directory>
            </target>
        </generator>
    </configuration>
</plugin>

Now, when we run mvn compile, Flyway creates an H2 database, and builds the schema, then Jooq generates all of its files in target/generated-sources/jooq.

Adding generated classes to the build

All of that’s pretty cool, until…​ you try to use those classes in your project. Neither Maven nor your IDE will be able to see them just yet. There’s one more large block of XML we need to add:

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>build-helper-maven-plugin</artifactId>
    <version>3.1.0</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>add-source</goal>
            </goals>
            <configuration>
                <sources>
                    <source>${jooq.outputdir}</source>
                </sources>
            </configuration>
        </execution>
    </executions>
</plugin>

Using the Build Helper plugin, we add Jooq’s output directory to the build, and we’re in business.

Closing note

One last note: if you make changes to the Flyway migrate file, you’ll need to execute a mvn clean to remove the test database and any Flyway checksum caches. If you don’t, your build will fail.

All of this does add a bit to the build process, but, for me, so far it’s justified. As I make changes to the schema, my Jooq classes are automatically recreated, and the test database is brought up to date.

tags: Maven Flyway Jooq

Quotes

Sample quote

Quote source