Is there a way to generate jooq code from flyway migrations?

Question

I have a kotlin spring boot application which is using Jooq. My database is postresql and I have flyway migrations configured for it. The problem is that when I am in my dev environment there is no problem generating code for jooq, but when I try to deploy my app to my server (with github actions and docker compose) I cant build the project, because Jooq is trying to connect to database (which is not started yet) to generate necessary code. My gradle build looks like this:

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile
import org.jooq.meta.jaxb.ForcedType
import org.jooq.meta.jaxb.Logging
import org.jooq.meta.jaxb.Property

val environment = System.getenv()

plugins {
    id("org.springframework.boot") version "2.7.5"
    id("io.spring.dependency-management") version "1.0.15.RELEASE"
    kotlin("jvm") version "1.8.0"
    kotlin("plugin.spring") version "1.8.0"

    id("nu.studer.jooq") version "7.0"
}

group = "osu.salat23"
version = "ALPHA"
java.sourceCompatibility = JavaVersion.VERSION_17

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")
    implementation("org.springframework.boot:spring-boot-starter-security")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-actuator")


    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.flywaydb:flyway-core")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.6.4")
    implementation("com.squareup.okhttp3:okhttp:4.10.0")
    implementation("com.github.yvasyliev:java-vk-bots-longpoll-api:3.5.3")
    implementation("org.telegram:telegrambots-spring-boot-starter:6.3.0")
    implementation("com.microsoft.playwright:driver-bundle:1.28.1")
    implementation("com.microsoft.playwright:playwright:1.28.1")

    runtimeOnly("org.postgresql:postgresql")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.security:spring-security-test")
    testImplementation("com.squareup.okhttp3:mockwebserver:4.10.0")
    testImplementation("junit:junit:4.13.2")

    jooqGenerator("org.postgresql:postgresql:42.5.1")
    jooqGenerator("jakarta.xml.bind:jakarta.xml.bind-api:3.0.1")
}
configurations {
    all {
        exclude("org.apache.logging.log4j", "log4j-slf4j-impl")
    }
}


tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "17"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

jooq {
    version.set("3.16.0")  // default (can be omitted)
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)  // default (can be omitted)

    configurations {
        create("main") {  // name of the jOOQ configuration
            generateSchemaSourceOnCompilation.set(false)  // default (can be omitted)

            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = environment["DATABASE_URL"] ?: "jdbc:postgresql://localhost:5432/postgres"
                    user = environment["DATABASE_USER"] ?: "postgres"
                    password = environment["DATABASE_PASSWORD"] ?: "postgres"
                    properties.add(Property().apply {
                        key = "ssl"
                        value = "false"
                    })
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        forcedTypes.addAll(listOf(
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "JSONB?"
                            },
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "INET"
                            }
                        ))
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "osu.salat23.circler"
                        directory = "src/generated-src/jooq/main"  // default (can be omitted)
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

Is there a way to generate code not from database but rather from my flyway migrations? I have tried to follow official documentation but there is so little information and also no gladle kotlin dsl code examples, so I am asking it here.

Answer 1

I solved my problem the following way: Because I am using github actions for my CI CD pipelines, I added a postgres service (because I am using vendor specific features) to my gh actions config:

jobs: 
   Deploy: 
     runs-on: ubuntu-latest 
     services: 
       postgres: 
         image: postgres:14.0 
         env: 
           POSTGRES_DB: postgres 
           POSTGRES_USER: postgres 
           POSTGRES_PASSWORD: postgres 
         ports: 
           - 5432:5432 
         options: >- 
           --health-cmd pg_isready 
           --health-interval 10s 
           --health-timeout 5s 
           --health-retries 5 
     steps: 
       - uses: actions/checkout@v3 
       - uses: actions/setup-java@v3 
         with: 
           distribution: adopt 
           java-version: 18 
       - name: Setup gradle 
         uses: gradle/gradle-build-action@v2 
       - name: Execute gradle build 
         run: ./gradlew clean && ./gradlew build && ./gradlew bootJar

The CI database credentials are also written in my gradle jooq config. By doing this, I can build my jar inside actions runner and then send it by using SCP to my server through ssh. Flyway generates database and jooq then generates code. Pretty simple yet good setup (at least for me)

Before that, I wanted to setup test containers, but decided that is was too big of a hustle for me.

If anybody will run into the same issue - I can send my gradle config if needed for better clarity.

Here is my gradle file for jooq compatibility:

import nu.studer.gradle.jooq.JooqExtension
import nu.studer.gradle.jooq.JooqGenerate
import org.flywaydb.gradle.task.FlywayMigrateTask
import org.jetbrains.kotlin.gradle.tasks.KotlinCompile
import org.jooq.meta.jaxb.ForcedType
import org.jooq.meta.jaxb.Logging
import org.jooq.meta.jaxb.Property

val environment = System.getenv()

plugins {
    id("org.springframework.boot") version "2.7.5"
    id("io.spring.dependency-management") version "1.0.15.RELEASE"
    kotlin("jvm") version "1.8.0"
    kotlin("plugin.spring") version "1.8.0"
    id("org.flywaydb.flyway") version "9.8.1"
    id("nu.studer.jooq") version "7.0"
}

group = "osu.salat23"
version = "ALPHA"
java.sourceCompatibility = JavaVersion.VERSION_17

repositories {
    mavenCentral()
    maven(url="https://jitpack.io")
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")
    implementation("org.springframework.boot:spring-boot-starter-security")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-actuator")
    implementation("io.micrometer:micrometer-registry-prometheus")

    implementation("com.github.cvb941:kotlin-parallel-operations:1.5.0")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.flywaydb:flyway-core")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.6.4")
    implementation("com.squareup.okhttp3:okhttp:4.10.0")
    implementation("com.github.yvasyliev:java-vk-bots-longpoll-api:3.5.3")
    implementation("org.telegram:telegrambots-spring-boot-starter:6.3.0")
    implementation("com.microsoft.playwright:driver-bundle:1.28.1")
    implementation("com.microsoft.playwright:playwright:1.28.1")

    runtimeOnly("org.postgresql:postgresql")
    implementation("com.github.holgerbrandl:jsonbuilder:-SNAPSHOT")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.security:spring-security-test")
    testImplementation("com.squareup.okhttp3:mockwebserver:4.10.0")
    testImplementation("junit:junit:4.13.2")

    jooqGenerator("org.postgresql:postgresql:42.5.1")
    jooqGenerator("jakarta.xml.bind:jakarta.xml.bind-api:3.0.1")
}
configurations {
    all {
        exclude("org.apache.logging.log4j", "log4j-slf4j-impl")
    }
}


tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "17"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

tasks.withType<JooqGenerate> {
    dependsOn("flywayMigrate")
}

flyway {
    url = "jdbc:postgresql://localhost:5432/postgres"
    user = "postgres"
    password = "postgres"
    schemas = arrayOf("public")
    locations = arrayOf("filesystem:${project.projectDir}/src/main/resources/db/migration")
}

jooq {
    version.set("3.16.0")  // default (can be omitted)
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)  // default (can be omitted)

    configurations {
        create("main") {  // name of the jOOQ configuration
            generateSchemaSourceOnCompilation.set(true)  // default (can be omitted)

            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = "jdbc:postgresql://localhost:5432/postgres"
                    user = "postgres"
                    password = "postgres"
                    properties.add(Property().apply {
                        key = "ssl"
                        value = "false"
                    })
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        forcedTypes.addAll(listOf(
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "JSONB?"
                            },
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "INET"
                            }
                        ))
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "osu.salat23.circler"
                        directory = "generated/generated-src/jooq/main"  // default (can be omitted)
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

Answer 2

You could consider intentionally committing the jOOQ generated code to your repo instead of re-generating it every time as part your build process.

Basic idea is, when you intend to make a DB change, presumably you'll be in your dev environment: run the DB migrations, then generate the jooq code, change your project to compile with the new code, commit.

Answer 3

There are 2 ways to do this:

• If you think you're not using anything RDBMS vendor specific, then jOOQ can emulate (simple) Flyway migrations (much like any SQL script based migrations and database setups, including dumps) using the DDLDatabase
• If you're using RDBMS vendor specific things, or more advanced Flyway features, which can't be emulated simply (e.g. repeatable migrations), then ideally, you just set up a build that runs an actual Flyway migration prior to code generation, e.g. in testcontainers

The article above uses Maven, but it should work the same way with Gradle, in principle.