How to setup jOOQ with Flyway and Gradle

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 that can handle migrations for you.

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

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:

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. By default you get 2 source sets: main and test. Let's add a new one called flyway.

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:

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 covering the integration of the generator with Gradle. Unfortunately, this redirects to a third-party plugin that does not support KotlinScript.

A bit of googling led me to another third-party plugin that is build with KotlinScript in mind.

Let's configure the plugin for our projet:

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:

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. You can find the whole build.gradle.kts file here.

Comments

[Kevin Davin]

Sadly, the jooq plugin isn't compatible with jooq 3.13.x.

Version 3.13.x is the default version in Spring 2.3, so new projects, for now, can't use this.

I've made a PR about that, waiting for approval: github.com/rohanprabhu/kotlin-dsl-...

[Gauthier]

I'm using it with jOOQ 3.13.1 without any issue

[Kevin Davin]

The PR has been merged, so now this is working.

[Gauthier]

I see that, but it was working for me before with spring boot 2.2

[Kevin Davin]

Yeah, but spring boot 2.2 use the same jooq version used in the plugin by default.

[Thomas Broyer]

FWIW, the way I do the jOOQ codegen in my projects is to "just" use a JavaExec task and put the config in an XML file:

val flywayConf by lazy {
    Properties().apply {
        file("src/main/resources/db/flyway.conf").reader().use { load(it) }
    }
}

val dbUrl = findProperty("db.url") as? String ?: "jdbc:postgresql:///foo"
val dbUser = findProperty("db.user") as? String ?: "foo"
val dbPassword = findProperty("db.password") as? String ?: "foo"
val dbSchema = flywayConf.getProperty("flyway.schemas")

flyway {
    url = dbUrl
    user = dbUser
    password = dbPassword
    schemas = arrayOf(dbSchema)
}

val jooqCodegen by configurations.creating {
    isVisible = false
    isCanBeResolved = true
    isCanBeConsumed = false
}

dependencies {
    api(platform(project(":platform"))
    api("org.jooq:jooq")
    api("org.postgresql:postgresql")
    api("com.google.guava:guava")

    jooqCodegen(platform(project(":platform")))
    jooqCodegen("org.jooq:jooq-codegen")
    jooqCodegen("org.postgresql:postgresql")
}

val jooqOutputDir = file("src/main/jooq")

tasks {
    register<JavaExec>("jooq") {
        val jooqConfigFile = file("src/jooq-codegen.xml")

        dependsOn("flywayMigrate")
        finalizedBy("spotlessJavaApply")

        inputs.dir("src/main/resources/db/migration").withPathSensitivity(PathSensitivity.RELATIVE)
        inputs.file(jooqConfigFile).withPathSensitivity(PathSensitivity.NONE)
        outputs.dir(jooqOutputDir)

        doFirst {
            project.delete(jooqOutputDir)
        }

        classpath = jooqCodegen
        main = "org.jooq.codegen.GenerationTool"
        systemProperties = mapOf(
            "db.url" to dbUrl,
            "db.user" to dbUser,
            "db.password" to dbPassword,
            "db.schema" to dbSchema,
            "outputdir" to jooqOutputDir.path
        )
        args(jooqConfigFile)
    }
}
sourceSets {
    main {
        java {
            srcDir(jooqOutputDir)
        }
    }
}
idea {
    module {
        generatedSourceDirs.add(jooqOutputDir)
    }
}

(note: we run the codegen against a Postgresql database, so we do it manually and commit the generated files, hence the jooqOutputDir being in src/main, and running Spotless as a finalizer task to reformat the code; also we only use SQL migrations, hence the inputs only listing src/main/resources/db/migration)

[Etienne Studer]

Interesting blog post. Thanks for writing it up.

The plugin used in this post is a "weak" fork of the original plugin. The original plugin is richer in functionality, leverages recent Gradle features much more deeply, and is stronger in implementation and internal testing:
github.com/etiennestuder/gradle-jo...

Since version 5.x, the gradle-jooq-plugin has support for Kotlin:
github.com/etiennestuder/gradle-jo...

There is also a small example in its repo on how to combine it with Flyway:
github.com/etiennestuder/gradle-jo...

There is also a small example in its repo on how to combine it with Spring Boot:
github.com/etiennestuder/gradle-jo...

I'm writing this just for completion, not to critize.