jOOQ+Liquibase+docker

186 views
Skip to first unread message

Pasha Finkelshtein

unread,
Feb 22, 2024, 2:41:37 AMFeb 22
to jOOQ User Group
Hi everybody!

I wanna do the following during my Gradle build:

  1. Launch docker (test?) container with postgres
  2. Run my Liquibase migrations on it
  3. Regenerate jOOQ code from the database
  4. Bring the container down
However, I didn't find a working way to launch a (test)container during the build. Did anybody succeed in doing this?

Best,
Pasha

Lukas Eder

unread,
Feb 22, 2024, 2:46:04 AMFeb 22
to jooq...@googlegroups.com
Hi Pasha,

I haven't tried yet, but it seems that Task.doFirst() and Task.doLast() could help here?

E.g.

tasks.named("jooqCodegen") {
    doFirst {
        // Start testcontainers
    }
    doLast {
        // Stop testcontainers
    }
}

You might add some additional logic to coordinate liquibase with jooqCodegen

I hope this helps

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3422abd2-8312-4fc4-8476-82b6bf3b25ebn%40googlegroups.com.

Pasha Finkelshtein

unread,
Feb 22, 2024, 3:44:59 AMFeb 22
to jooq...@googlegroups.com
Certainly it should work, but I can't start test containers within the Gradle build :(
doLast should not be even needed — Ryuk will take care of shutting the container down
Also, we need to run Liquibase migrations, so I think bringing container up should be a separate task too


facebook
twitter
linkedin
instagram

Pasha Finkelshteyn

Developer Advocate for Data Engineering

JetBrains




asm...@jetbrains.com
https://linktr.ee/asm0dey

Find out more



You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/WYKlwguBHwE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5wcrgPJj9vKNS4Pr33E-0xhnTDKAn7kWwir-ma7XM1eA%40mail.gmail.com.

Lukas Eder

unread,
Feb 22, 2024, 3:58:42 AMFeb 22
to jooq...@googlegroups.com
Well, surely testscontainers folks will be able to help with testcontainers specific issues?

Pasha Finkelshtein

unread,
Feb 22, 2024, 4:01:19 AMFeb 22
to jooq...@googlegroups.com
I tried, no luck yet :)

But I believe it's just something nobody did - launch a container for build, not for tests.
Also, LiquibaseDatabase won't work for me, because I use `CALL` in my migrations, which creates a lot of entities in the DB...

Simon Martinelli

unread,
Feb 22, 2024, 4:04:43 AMFeb 22
to jooq...@googlegroups.com
Hi Pasha,

I do this all the time, but with Maven.
Siva from the Testcontainers team even created a Maven Plugin:

Unfortunately, I don’t know Gradle to help you.

— Simon

Lukas Eder

unread,
Feb 22, 2024, 4:11:03 AMFeb 22
to jooq...@googlegroups.com
Maybe, if you ask more specific questions? It's hard to see what exactly didn't work on your end, and I doubt that:

- Nobody does this
- It doesn't work in general

I recommend doing this all the time for Maven and Flyway:

And I'm sure it'll work for Gradle and Liquibase too. I have tasks open to do this myself for jOOQ's MCVE and demo projects, e.g.

But didn't get around to it yet.

Per Lundberg

unread,
Feb 22, 2024, 4:14:27 AMFeb 22
to jooq...@googlegroups.com
Hi Pasha,

Not _exactly_ identical to your use case, but what we do in our Gradle build is to launch a test container for our code generation, then run the migrations (with Flyway), then shut the container down. This works for us, but we've put the whole "launch test container" and run codegen etc. in a dedicated Java class instead of managing it purely via Gradle.

I can share some more details as needed, but you could first look at this which shows what our Gradle task looks like for this: https://groups.google.com/g/jooq-user/c/PxxcBaB8gxA/m/hxDKw3SvAgAJ

Best regards,
Per Lundberg

From: jooq...@googlegroups.com <jooq...@googlegroups.com> on behalf of Pasha Finkelshtein <pavel.fin...@gmail.com>
Sent: Thursday, February 22, 2024 11:01
To: jooq...@googlegroups.com <jooq...@googlegroups.com>
Subject: Re: jOOQ+Liquibase+docker
 

Pasha Finkelshtein

unread,
Feb 22, 2024, 4:19:04 AMFeb 22
to jooq...@googlegroups.com
This obviously SHOULD work, but it doesn't. And I was hoping to get a concrete example to understand what I'm doing wrong.

It seems to me that test containers just don't work during the gradle build, but maybe I'm wrong. And since nobody in the Testcontainers slack answer I thought that maybe here somebody has a working example.

Sorry for bothering you

Lukas Eder

unread,
Feb 22, 2024, 4:31:34 AMFeb 22
to jooq...@googlegroups.com
Well, what did you try?

Pasha Finkelshtein

unread,
Feb 22, 2024, 5:08:36 AMFeb 22
to jOOQ User Group
This is what I have:

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

}
....

val testContainer = org.testcontainers.containers.PostgreSQLContainer(DockerImageName.parse("paradedb/paradedb:latest").asCompatibleSubstituteFor("postgres"))

val startContainer by tasks.registering {
    testContainer.start()
}

Hypothetically, it should to the trick - when I call gradlew startContainer or when Liquibase's update tsk depends on it and I call update - it should spin up the container and stop it only when the gradle is done.
Or, for example, if I will put jooqCodegen.finalizedBy(stopContainer) - the same should happen. However, I got the following error in the very beginning:

Can't instantiate a strategy from org.testcontainers.dockerclient.UnixSocketClientProviderStrategy (ClassNotFoundException). This probably means that cached configuration refers to a client provider class that is not available in this version of Testcontainers. Other strategies will be tried instead.
Could not find a valid Docker environment. Please check configuration. Attempted configurations were:
As no valid configuration was found, execution cannot continue.


I am not sure what is the issue here. Obviously, testcontainers work on my machine; I have native Docker installed. I have a dependency on testcontainers on my script's classpath. At this point I do not know what I am doing wrong :(

HOMERMAN

unread,
Feb 22, 2024, 6:36:29 AMFeb 22
to jOOQ User Group
I do this exact thing in one of my Gradle builds. Runnig this task with -Penvironment=test will:
  1. Create a Postgres Docker container
  2. Initialize it with your Liquibase changelogs
  3. Generate your jOOQ artifacts, and 
  4. Shut down the container afterwards
You can see if(environment == "test") then Docker + Liquibase operations are invoked.

There's some hard-coded concepts (like environment names, Docker image version, etc), but you should be able to adapt it to your needs.

###################################

import liquibase.Liquibase
import liquibase.database.DatabaseFactory
import liquibase.database.jvm.JdbcConnection
import liquibase.resource.DirectoryResourceAccessor
import java.sql.DriverManager

tasks.register("jooqGenerate") {
  group = "jooq"
  description = "Runs the jOOQ code generator against the schema defined in the Liquibase changelogs"

  doLast {
    val environment = findProperty("environment")?.toString() ?: "local"

    var postgresContainer: PostgreSQLContainer<Nothing>? = null
    val schemaName: String
    val driverClassName: String
    val jdbcUrl: String
    val username: String
    val password: String

    if(environment == "test") {
      postgresContainer = PostgreSQLContainer<Nothing>("postgres:16.1").apply { start() }

      schemaName = "public"

      driverClassName = postgresContainer!!.driverClassName
      jdbcUrl = postgresContainer.jdbcUrl
      username = postgresContainer.username
      password = postgresContainer.password

      Class.forName(driverClassName)

      DriverManager.getConnection(jdbcUrl, username, password)
        .use { connection ->
          connection.autoCommit = true

          connection.createStatement().use { statement ->
            statement.executeUpdate("create schema if not exists $schemaName")
          }

          connection.schema = schemaName

          JdbcConnection(connection).use { liquibaseJdbcConnection ->
            val liquibaseDatabase = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(liquibaseJdbcConnection)

            val liquibase = Liquibase("main.yaml", DirectoryResourceAccessor(File("...")), liquibaseDatabase)
            liquibase.update("")
            liquibase.forceReleaseLocks()
          }
        }

    } else {
      schemaName = if(environment == "local") "public" else environment

      driverClassName = findProperty(...)!!.toString()
      jdbcUrl = findProperty(...)!!.toString()
      username = findProperty(...)!!.toString()
      password = findProperty(...)!!.toString()
    }

    GenerationTool.generate(
      Configuration()
        .withJdbc(
          Jdbc()
            .withDriver(org.postgresql.Driver::class.java.name)
            .withUrl(jdbcUrl)
            .withUsername(username)
            .withPassword(password)
        )
        .withGenerator(
          Generator()
            .withName(...)
            .withStrategy(...)
            .withDatabase(...)
            .withGenerate(...)
            .withTarget(...)
        )
    )

    postgresContainer?.stop()

Per Lundberg

unread,
Feb 22, 2024, 6:36:29 AMFeb 22
to jOOQ User Group
Hi,

This seems to me like these dependencies are being added to the wrong classpath somehow. Are you sure they should go to the buildscript dependencies?

Here's what we have, but as mentioned, we have our codegen running somewhat differently (in a separate Gradle configuration/source set). Also note that we are using jOOQ Pro in our case; IIRC the artifact names have to be edited if you're using jOOQ Open Source (but the Gradle plugin probably takes care of the jOOQ stuff anyway in your case).

dependencies {
    // for running DB-based codegen in isolated Docker container
    codegenImplementation( "org.testcontainers:testcontainers:${testcontainersVersion}" ) {
        exclude group: 'javax.xml.bind', module: 'jaxb-api'
    }

    codegenImplementation( "org.testcontainers:postgresql:${testcontainersVersion}" ) {
        exclude group: 'javax.xml.bind', module: 'jaxb-api'
    }

    codegenImplementation "org.postgresql:postgresql:${postgresqlJdbcVersion}"
    codegenImplementation "${jooqGroupId}:jooq:${jooqVersion}"
    codegenImplementation "${jooqGroupId}:jooq-codegen:${jooqVersion}"
}

Best regards,
Per

Sent: Thursday, February 22, 2024 12:08
To: jOOQ User Group <jooq...@googlegroups.com>
Subject: Re: jOOQ+Liquibase+docker
 
--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

Pasha Finkelshtein

unread,
Feb 22, 2024, 8:03:22 AMFeb 22
to jOOQ User Group
Thank Per,

I'm absolutely not sure. My way of thinking was that both liquibase and jooq need to have a running container. So it can't be neither jooqRuntime, nor liquibaseRuntime, so it should be like "gradleRuntime", but there is no such thing :)

How do you start the container?

Best,
Pasha

Message has been deleted

Pasha Finkelshtein

unread,
Feb 22, 2024, 8:06:49 AMFeb 22
to jOOQ User Group
Thanks HOMERMAN,

Where does the dependency on PostgreSQLContainer come from? I don't see neither imports nor dependency declaration...

Best,
Pasha

Per Lundberg

unread,
Feb 22, 2024, 8:17:28 AMFeb 22
to jooq...@googlegroups.com
Hi,

Yeah, understanding the source sets (or whatever the concept is properly called, not sure I have the right naming myself) can be challenging. This can be a bit of help: https://www.baeldung.com/gradle-source-sets

Our code for starting the container is pretty much like this:

        DockerImageName imageName = DockerImageName.parse( "timescale/timescaledb:2.14.2-pg13-oss" ).asCompatibleSubstituteFor( "postgres" );

        try ( JdbcDatabaseContainer<?> container = new PostgreSQLContainer<>( imageName ) ) {
            container.start();

            // Apply migrations, run jOOQ codegen
        }

Best regards,
Per

Sent: Thursday, February 22, 2024 15:03

Pasha Finkelshtein

unread,
Feb 22, 2024, 8:21:45 AMFeb 22
to jooq...@googlegroups.com
Thanks per,

Could you please show how you add a dependency on testcontainers to your script? 



facebook
twitter
linkedin
instagram

Pasha Finkelshteyn

Developer Advocate for Data Engineering

JetBrains




asm...@jetbrains.com
https://linktr.ee/asm0dey

Find out more


You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/WYKlwguBHwE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/VI0PR08MB10448C60863DA23CDCE8EF908EA562%40VI0PR08MB10448.eurprd08.prod.outlook.com.

HOMERMAN

unread,
Feb 22, 2024, 8:24:36 AMFeb 22
to jOOQ User Group
@Pasha

Sorry - I copy/pasted/genericized before I shared, and I think inadvertently snipped the import out along the way:

import org.testcontainers.containers.PostgreSQLContainer

Pasha Finkelshtein

unread,
Feb 22, 2024, 8:31:27 AMFeb 22
to jOOQ User Group
Thanks HOMERMAN,

But where does this import come from? Where is the dependency declared?
Is it in the usual dependencies section? With which scope?

HOMERMAN

unread,
Feb 22, 2024, 8:40:10 AMFeb 22
to jOOQ User Group
It's on the buildscript's classpath. So from the very top of my build script it looks like this:

import liquibase.Liquibase
import liquibase.database.DatabaseFactory
import liquibase.database.jvm.JdbcConnection
import liquibase.resource.DirectoryResourceAccessor
import org.jooq.codegen.DefaultGeneratorStrategy
import org.jooq.codegen.GenerationTool
import org.jooq.meta.jaxb.Configuration
import org.jooq.meta.jaxb.Database
import org.jooq.meta.jaxb.Generate
import org.jooq.meta.jaxb.Generator
import org.jooq.meta.jaxb.Jdbc
import org.jooq.meta.jaxb.Strategy
import org.jooq.meta.jaxb.Target
import org.jooq.meta.postgres.PostgresDatabase
import org.testcontainers.containers.PostgreSQLContainer
import java.sql.DriverManager

buildscript {
   repositories {
     mavenCentral()
   }

  dependencies {
    classpath("org.testcontainers:testcontainers:${rootProject.extra["testcontainersBomVersion"]}")
    classpath("org.testcontainers:junit-jupiter:${rootProject.extra["testcontainersBomVersion"]}")
    classpath("org.testcontainers:postgresql:${rootProject.extra["testcontainersBomVersion"]}")
    classpath("org.liquibase:liquibase-core:${rootProject.extra["liquibaseVersion"]}")
    classpath("org.jooq:jooq:${rootProject.extra["jooqVersion"]}")
    classpath("org.jooq:jooq-meta:${rootProject.extra["jooqVersion"]}")
    classpath("org.jooq:jooq-codegen:${rootProject.extra["jooqVersion"]}")
    classpath("org.postgresql:postgresql:${rootProject.extra["postgresVersion"]}")
  }
}

tasks.register("jooqGenerate") {
   ...
}

Pasha Finkelshtein

unread,
Feb 22, 2024, 8:44:48 AMFeb 22
to jOOQ User Group
Now I have a relatively full picture, thank you very much!

Pasha Finkelshtein

unread,
Feb 23, 2024, 2:24:24 AMFeb 23
to jOOQ User Group
Hi @Lukas,

After some experimenting, I think that I hit an issue with the jOOQ Gradle plugin, or maybe it's lack of my understanding.

Thq jooq block requires jdbc URL to be known in advance, in the configuration phase. But in a perfect world, I need the docker to be run only in the execution phase.

For example, this code works:

val start by tasks.registering(Container::class) {
    println("START CONTAINER")
    container = parade
//    doLast {
        start()
//    }
}

abstract class Container : DefaultTask() {
    @get:Input
    abstract val container: Property<PostgreSQLContainer<*>>

    fun start() {
        container.get().start()
    }

    fun stop() {
        container.get().stop()
    }
}


But if I uncomment ths "doLast" line, I get an error

* What went wrong:
Mapped port can only be obtained after the container is started

I think that the jdbc block should be lazy and read its parameters only during execution. It might be true for other blocks too — hypothetically other tasks can augment reality with parts later used by jOOQ too.

Lukas Eder

unread,
Feb 23, 2024, 4:38:35 AMFeb 23
to jooq...@googlegroups.com
Hi Pasha,

Yes, see this issue for lazy configuration evaluation:

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

Pasha Finkelshtein

unread,
Feb 23, 2024, 5:00:47 AMFeb 23
to jooq...@googlegroups.com
Beautiful!

Thanks everyone for the help!

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/WYKlwguBHwE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6JqDRmNgH92zdja4pGquGXQApH86uXT2k4w8fAs1CNeg%40mail.gmail.com.

Lukas Eder

unread,
Mar 1, 2024, 8:24:39 AMMar 1
to jOOQ User Group
Task configuration is now lazy with the next nightly builds of 3.20.0 and 3.19.6:

I'll probably make another set of patch releases next week.

Lukas Eder

unread,
Mar 8, 2024, 12:24:56 PMMar 8
to jooq...@googlegroups.com
3.19.6 has been released and the gradle plugin configuration should now be lazy.

Let me know if this fixes the remaining problems for you.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f1e39661-1e4d-43af-b2ea-6156b8ba478bn%40googlegroups.com.

Kevin Jones

unread,
Mar 14, 2024, 12:30:17 PMMar 14
to jooq...@googlegroups.com
If I update to JOOQ 3.19.6 my IDE stops working properly

I have a multi-module Gradle project with the generated code in one module that's referenced by two others. My code is Kotlin. I've been running JOOQ 3.19.2 and everything is OK. if I upgrade to 3.19.5 then everything is still OK  however going to 3.19.6 causes the IDE (IntelliJ IDEA) to break. 

In the IDE I get hundreds of "Unresolved Reference" errors, one for each of the types that I generate with JOOQ.

I've tried deleting the generated code and re-generating it but I still get the same issue.

Nothing else has changed, just the update from 3.19.5 to 3.19.6

The project still builds and runs so it's only within the IDE that I'm seeing the issues,

I've tried invalidating IDEA's caches but still have the same issue.


Kevin 

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.


--
Kevin Jones
KnowledgeSpike

Lukas Eder

unread,
Mar 14, 2024, 12:45:03 PMMar 14
to jooq...@googlegroups.com
Hi Kevin,

Thanks for your message.

How can this be reproduced, exactly?

Thanks,
Lukas

Kevin Jones

unread,
Mar 14, 2024, 1:11:25 PMMar 14
to jooq...@googlegroups.com
Yeah, I was wondering that :)

I've created this project (it's a cut down version of what I'm using)


If you open this in the IDE then the current version of JOOQ is set to 3.19.5 and everything is loaded properly.

If you open the file "kotlin/com/knowledgespike/progressive/database/TeamRecords.kt" then you should see no errors

If you go into the gradle directory and open the libs.versions.toml file, 
    change the version to 3.19.6
    reload the gradle project
    open the file "kotlin/com/knowledgespike/progressive/database/TeamRecords.kt" then you should see the errors

If you have Idea's project window open, and expand the 'app_shared' project so that 'generated/java' is visible, then you can see the issue.

As you switch from JOOQ version 3.19.5 to 3.19.6 the generated code stops being a sourceSet, if you then switch back to 3.19.5 it becomes a sourceSet again

image.png

image.png

My JOOQ plugin configuration is in the build.gradle.kts file in the 'app_shared' project.

Hope that makes sense,

Kevin



--
Kevin Jones
KnowledgeSpike

Lukas Eder

unread,
Mar 14, 2024, 2:49:14 PMMar 14
to jooq...@googlegroups.com
Hi Kevin,

Thanks for the example project. I can reproduce the issue with your instructions. Will look into it tomorrow.

Pasha Finkelshtein

unread,
Mar 14, 2024, 3:01:29 PMMar 14
to jOOQ User Group
It seems to me that if the build and everything else work correctly from the command line, then it's a bug in IDEA and it should be reported. 
Thank you in advance!

Lukas Eder

unread,
Mar 15, 2024, 3:59:07 AMMar 15
to jooq...@googlegroups.com
It does seem like something worth reporting to IntelliJ, though I recall how slippery the implementation of a lazy configuration was, so it won't hurt to investigate if the 3.19.6 plugin now does something "stupid." The current logic to register the code generation output directory with the source sets is this, in CodegenPlugin:

task.doFirst(t -> {
    SourceSetContainer source = project
        .getExtensions()
        .findByType(SourceSetContainer.class);

    if (source != null) {
        source.configureEach(sourceSet -> {
            if (sourceSet.getName().equals("main"))
                sourceSet.getJava().srcDir(task.getOutputDirectory());
        });
    }
});

Gradle has been an arcane mystery to me over these past months, so it may well be that I'm still missing something "obvious" about when to do what.

Bernd Huber

unread,
Mar 17, 2024, 1:29:37 PMMar 17