How to setup jOOQ with Flyway and Gradle
Tags: tutorial, jooq, gradle, kotlin
Published: 2020-06-11 03:29:57

jOOQ is an extremely powerful tool that puts your database first:

> jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.

I recently switched from a traditional ORM to jOOQ, and I am very happy with the result (that's a story for another post).

My project is using Gradle, with buildscripts written in KotlinScript, and I use Flyway to perform database migrations. The database I use is H2.

While adding jOOQ to the mix, I had to review my build process in order for all the pieces to integrate properly, and this is what I'm going to show you in this post.

# The big idea

jOOQ generates code from an existing database. Great. But do we have a database to generate the code from ?

In my case, I didn't. There is no single instance of the database to work with, either in dev or in prod. My database is the result of multiple Flyway migrations applied in sequence.

In order for my build process to always have the latest jOOQ DSL, I would need to be able to:

1. Run all the Flyway migrations to create an empty database (we only need the database structure to generate the DSL, not its data).
2. Run the jOOQ code generator from this database.

That particular build task would need to be run before compiling my code.

# Setting up Flyway

The first thing we need is a way to generate an empty database from our Flyway migrations. I didn't needed that before, since Spring Boot would automatically launch Flyway at startup and apply all the migrations if necessary.

Flyway has an [official Gradle plugin](https://flywaydb.org/documentation/gradle/) that can handle migrations for you.

In my `build.gradle.kts` I define some properties for my empty target database:

```kotlin
val jooqDb = mapOf(
  "url" to "jdbc:h2:${project.buildDir}/generated/flyway/h2",
  "schema" to "PUBLIC",
  "user" to "sa",
  "password" to ""
)
```

We can then configure the Flyway plugin:

```kotlin
flyway {
  url = jooqDb["url"]
  user = jooqDb["user"]
  password = jooqDb["password"]
  schemas = arrayOf(jooqDb["schema"])
  locations = arrayOf("classpath:db/migration")
}
```

Flyway can use 2 types of migrations: _SQL_ and _Java_. SQL migrations are easy, as you just need to point to the files, but Java migrations need to be compiled first. That's why the `locations` in the configuration above points to a `classpath` location.

**We also don't want to couple our Java migrations compilation to our codebase compilation**, that would prevent us from updating the jOOQ DSL while we are working on some code that may not compile yet.

To do so, we need to separate the Flyway migrations from the rest of our codebase. Gradle has a very handy feature for that: [Source Sets](https://docs.gradle.org/current/userguide/building_java_projects.html#sec:java_source_sets). By default you get 2 source sets: `main` and `test`. Let's add a new one called `flyway`.

```kotlin
sourceSets {
  //add a flyway sourceSet
  val flyway by creating {
    compileClasspath += sourceSets.main.get().compileClasspath
    runtimeClasspath += sourceSets.main.get().runtimeClasspath
  }
  //main sourceSet depends on the output of flyway sourceSet
  main {
    output.dir(flyway.output)
  }
}
```

The `flyway` source set will inherit the compile and runtime classpath of the `main` source set. In addition, the `main` source set will depend on the `flyway` source set, meaning that Gradle will always make sure that the `flyway` tasks runs before `main` if needed. This will also ensure that the output of the `flyway` source set is bundled in the `jar` file produced by Spring Boot.

We need to move our Flyway migrations files to:

- SQL migrations: `src/flyway/resources/db/migration`
- Java migrations: `src/flyway/kotlin/db/migration`

With the new source set we get a new task called `flywayClasses`, which will compile the classes for the `flyway` source set (i.e. our Java migrations).

In order to include the Java migrations when Flyway will run, we need to run `flywayClasses` before we run `flywayMigrate`. In Gradle terms, we need to add a dependency between the 2 tasks:

```kotlin
val migrationDirs = listOf(
  "$projectDir/src/flyway/resources/db/migration",
  "$projectDir/src/flyway/kotlin/db/migration"
)
tasks.flywayMigrate {
  dependsOn("flywayClasses")
  migrationDirs.forEach { inputs.dir(it) }
  outputs.dir("${project.buildDir}/generated/flyway")
  doFirst { delete(outputs.files) }
}
```

In addition, we define 3 extra steps for the `flywayMigrate` task:

- we will define Task Inputs and Outputs, so that our task can be run only if the inputs/outputs have changed:
  - we add all the migration directories as inputs: `migrationDirs.forEach { inputs.dir(it) }`
  - we add the output directory as a task output: `outputs.dir("${project.buildDir}/generated/flyway")`
- lastly, we will delete the output directory content before running the task `doFirst { delete(outputs.files) }`. If not for this, the resulting H2 database would remain in-between runs of `flywayMigrate`. Normally this is not an issue, as you can run Flyway on a database with existing content, and no migrations would be applied. However, when you are developing, you may change an existing migration you are working on (the last one only, never change previous migrations!), in which case running `flywayMigrate` would fail because the migration hash would not match. By deleting the output first, we get rid of this problem, and make development iterations easier.

Running `flywayMigrate` will now generate an empty H2 database with all our migrations into `build/generated/flyway`!

# Setting up jOOQ code generation

First we need a way to integrate jOOQ with Gradle. The official documentation has [a section](https://www.jooq.org/doc/latest/manual/code-generation/codegen-gradle/) covering the integration of the generator with Gradle. Unfortunately, this redirects to a [third-party plugin](https://github.com/etiennestuder/gradle-jooq-plugin) that does not support KotlinScript.

A bit of googling led me to [another third-party plugin](https://github.com/rohanprabhu/kotlin-dsl-gradle-jooq-plugin) that is build with KotlinScript in mind.

Let's configure the plugin for our projet:

```kotlin
jooqGenerator {
  jooqVersion = "3.13.1"
  configuration("primary", project.sourceSets.getByName("main")) {
    databaseSources = migrationDirs

    configuration = jooqCodegenConfiguration {
      jdbc {
        username = jooqDb["user"]
        password = jooqDb["password"]
        driver = "org.h2.Driver"
        url = jooqDb["url"]
      }

      generator {
        target {
          packageName = "org.gotson.komga.jooq"
          directory = "${project.buildDir}/generated/jooq/primary"
        }

        database {
          name = "org.jooq.meta.h2.H2Database"
          inputSchema = jooqDb["schema"]
        }
      }
    }
  }
}
```

The plugin integrates well in the Gradle lifecycle and will monitor the `databaseSources` to decide if the task is up to date or not. It will also register itself as a dependency for the `compileJava` and `compileKotlin` tasks, so your DSL will always be up to date when you compile your code.

We provide some extra configuration:

- to connect to the database (`jdbc` block)
- to generate the jOOQ DSL (`generator` block)

That's fine, but we need to make sure our Flyway tasks run before the jOOQ code generation. Again, the Gradle task dependencies help us here:

```kotlin
val `jooq-codegen-primary` by project.tasks
`jooq-codegen-primary`.dependsOn("flywayMigrate")
```

# Summary

We are all set, everything is nicely configured thanks to the Gradle lifecycle, all the tasks depend on what's needed, and will run only if something changes, making your builds go faster.

When you are working on new database features, you can easily run `jooq-codegen-primary` if you need to update your jOOQ DSL after some changes to your Flyway migrations. When you will compile your code, even after a `clean`, Flyway will run first, creating an empty database, and jOOQ will run after to generate your DSL. Finally your code will be compiled using that DSL.

The code excerpts are taken from my open-source project [Komga](https://github.com/gotson/komga). You can find the whole `build.gradle.kts` file [here](https://github.com/gotson/komga/blob/v0.39.0/komga/build.gradle.kts).