DEV Community

How to setup jOOQ with Flyway and Gradle

Gauthier on June 11, 2020

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

Read full post

Kevin Davin • Jun 13 '20 • Edited

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 • Jun 24 '20

I'm using it with jOOQ 3.13.1 without any issue

Kevin Davin • Jun 24 '20

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

Gauthier • Jun 24 '20

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

Kevin Davin • Jun 24 '20

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

Thomas Broyer • Jun 11 '20

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 • Apr 6 '21

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.