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.
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 