How to use postgres database when generate jooq by LiquibaseDatabase

Question

There is pretty good guide how to use code generation from liquibase However by default it uses h2 database to create schema from migrations and then generate records. How to specify another database for liquibase migrations (for example, postgres)?

My solution (build.gradle.kts):

plugins {
    java
    id("nu.studer.jooq") version "7.1.1"
    id("org.liquibase.gradle") version "2.1.1"
}

java {
    sourceCompatibility = JavaVersion.VERSION_18
    targetCompatibility = JavaVersion.VERSION_18
}

dependencies {
    jooqGenerator("org.postgresql:postgresql:42.3.2")
    jooqGenerator("org.slf4j:slf4j-jdk14:1.7.30")
    jooqGenerator("org.testcontainers:postgresql:1.17.1")

    liquibaseRuntime(files("src/main/resources"))
    liquibaseRuntime("org.testcontainers:postgresql:1.17.1")
    liquibaseRuntime("org.postgresql:postgresql:42.3.2")
    liquibaseRuntime("org.liquibase:liquibase-core")
    liquibaseRuntime("info.picocli:picocli:4.6.1")
    liquibaseRuntime("ch.qos.logback:logback-core:1.2.3")
    liquibaseRuntime(sourceSets.getByName("main").compileClasspath)
    liquibaseRuntime(sourceSets.getByName("main").output)
}

liquibase {
    activities.register("main") {
        this.arguments = mapOf(
            "logLevel" to "info",
            "changeLogFile" to "db/migration/changelog-master.xml",
            "url" to "jdbc:tc:postgresql:9.6.8:///postgres?TC_DAEMON=true",
            "username" to "user",
            "password" to "password",
            "driver" to "org.testcontainers.jdbc.ContainerDatabaseDriver"
        )
    }
    runList = "main"
}

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

jooq {
    version.set("3.16.6")
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)

    configurations {
        create("main") {
            generateSchemaSourceOnCompilation.set(true)

            jooqConfiguration.apply {
                logging = org.jooq.meta.jaxb.Logging.WARN
                jdbc.apply {
                    driver = "org.testcontainers.jdbc.ContainerDatabaseDriver"
                    url = "jdbc:tc:postgresql:9.6.8:///postgres?TC_DAEMON=true"
                    user = "user"
                    password = "password"
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "com.example"
                        directory = "build/generated-src/jooq/main"
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

tasks.named<nu.studer.gradle.jooq.JooqGenerate>("generateJooq") {
    (launcher::set)(javaToolchains.launcherFor {
        languageVersion.set(JavaLanguageVersion.of(18))
    })
}

Expectations:

after running code gen ./gradlew generateJooq first starts postgres testcontainer and liquibase apply migrations, after that starts jooq code generation

Reality:

after running code gen starts postgres testcontainer and liquibase apply migrations and then starts new testcontainer and jooq tries generate records

Is there any solution to specify one postgres container for migrations and code gen?

Answer 1

Solution:
(build.gradle.kts)

plugins {
    java
    idea
    id("nu.studer.jooq")
    id("org.liquibase.gradle")
}

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.testcontainers:postgresql:1.17.1")
    }
}

dependencies {
    jooqGenerator("org.jooq:jooq-meta-extensions-liquibase")
    jooqGenerator("org.postgresql:postgresql:42.3.2")
    jooqGenerator("org.slf4j:slf4j-jdk14:1.7.30")
    jooqGenerator("org.testcontainers:postgresql:1.17.1")
    jooqGenerator(files("src/main/resources"))

    liquibaseRuntime(files("src/main/resources"))
    liquibaseRuntime(sourceSets.getByName("main").compileClasspath)
    liquibaseRuntime(sourceSets.getByName("main").output)
    liquibaseRuntime("org.liquibase:liquibase-core")
    liquibaseRuntime("org.postgresql:postgresql")
    liquibaseRuntime("info.picocli:picocli:4.6.1")
    liquibaseRuntime("ch.qos.logback:logback-core:1.2.3")
}

// starting container
var postgres: JdbcDatabaseContainer<*> = PostgreSQLContainerProvider().newInstance("14-alpine")
postgres.start()

val containerStop by tasks.registering {
    if (postgres.isRunning) {
        println("STOPPING DATABASE CONTAINER")
        postgres.stop()
    }
}

tasks.named("update") {
    doLast {
        tasks.named("generateJooq").get()
    }
}

tasks.withType<JooqGenerate> {
    doLast {
        containerStop.get()
    }
}

liquibase {
    activities.register("main") {
        this.arguments = mapOf(
            "logLevel" to "info",
            "classpath" to "${projectDir}/",
            "changeLogFile" to "db/migration/changelog-master.xml",
            "url" to postgres.jdbcUrl,
            "username" to postgres.username,
            "password" to postgres.password,
            "driver" to "org.postgresql.Driver"
        )
    }
    runList = "main"
}

jooq {
    version.set(jooqVersion)
    edition.set(JooqEdition.OSS)

    configurations {
        create("main") {
            generateSchemaSourceOnCompilation.set(true)

            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = postgres.jdbcUrl
                    user = postgres.username
                    password = postgres.password
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        excludes = "databasechangelog|databasechangeloglock"
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "my.package.jooq"
                        directory = "build/generated-src/jooq/main"
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

./gradlew clean update build

Answer 2

On the official jOOQ blog, they wrote up a guide for Using Testcontainers to Generate jOOQ Code

Pointing the jOOQ code generator towards a live database used to be a bit of a challenge, which is why jOOQ offers alternative, connection-free code generation modes, including:

• The JPADatabase, if you have a pre-existing JPA entity based meta model.
• The XMLDatabase, if you have some form of XML version of your schema, which you can XSL transform to jOOQ’s format
• The LiquibaseDatabase, which simulates a Liquibase database migration and uses the simulated database output as a source for meta information of the code generator

But all of the above have the same limitation. You can’t really use many vendor-specific features, such as advanced stored procedures, data types, etc.

The article continues, and describes how you can use jOOQ as a Modern Approach Using Testcontainers, which may help with understanding the proper procedures for using jOOQ's LiquibaseDatabase command.

Here is some additional documentation:

• jOOQ: LiquibaseDatabase: Code generation from Liquibase XML, YAML, JSON files
• Liquibase:Using Liquibase with PostgreSQL