Coming Up for Air

Hands-free Flyway and jOOQ

Thursday, May 14, 2020 |

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<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 websites.

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
<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.

Search

    Quotes

    Sample quote

    Quote source

    About

    My name is Jason Lee. I am a software developer living in the middle of Oklahoma. I’ve been a professional developer since 1997, using a variety of languages, including Java, Javascript, PHP, Python, Delphi, and even a bit of C#. I currently work for Red Hat on the WildFly/EAP team, where, among other things, I maintain integrations for some MicroProfile specs, OpenTelemetry, Micrometer, Jakarta Faces, and Bean Validation. (Full resume here. LinkedIn profile)

    I am the president of the Oklahoma City JUG, and an occasional speaker at the JUG and a variety of technical conferences.

    On the personal side, I’m active in my church, and enjoy bass guitar, running, fishing, and a variety of martial arts. I’m also married to a beautiful woman, and have two boys, who, thankfully, look like their mother.

    My Links

    Publications