Generating JOOQ sources using Flyway and Testcontainers

The typical microservices I am working on are Kotlin based. They use Gradle as a build tool, Postgres for the database layer, Flyway for migration management, and JOOQ for database access. JOOQ generates Java or Kotlin class definitions from the database schema, which provides type safety and avoids hard coded strings in the application logic. The challenge is to make all of this work together. We want to make sure that the JOOQ sources are always in sync with the newest database schema defined by the Flyway migrations, and we are using Gradle for this. In this blog post, I describe my journey through the different solutions and the one that I am currently pretty happy with.

The general approach

There are Gradle plugins for both Flyway and JOOQ, and all of the solutions that I am covering use those. The Flyway plugin takes a JDBC URL and applies the migrations against the specified database. Similarly, the JOOQ plugin takes a JDBC URL (and other configuration options) and generates JOOQ sources for the specified database. By using the same database for both Flyway and JOOQ and making sure that the Flyway plugin always runs before the JOOQ plugin, we solve our problem of keeping the JOOQ sources in sync with the database schema. The relevant section of our build.gradle.kts would look something like this.

flyway {
  url = "<some jdbc url>"
}

jooq {
  configurations {
    create("main") {
      jooqConfiguration.apply {
        generator.database.inputSchema = "public"
        jdbc.apply {
          url = "<some jdbc url>"
        }
      }
    }
  }
}

tasks.named("generateJooq") {
  dependsOn("flywayMigrate")
}

But one question remains: which database do we provide to the plugins, or in other words, what do we set the JDBC URL to?

Sidenote on the classpath

The above snippet is only one part of the build file. Another important part is defining the used packages, and in which classpath they should be visible. In our case, we have three different classpaths to consider: the classpath of the application, the classpath of the build script (which will be used for example by the Flyway plugin), and the classpath of the JOOQ generator. The latter is necessary because the JOOQ plugin spans the code generator in a separate JVM (which creates some headache, as we will see below).

dependencies {
  implementation(kotlin("stdlib"))
  implementation("org.jooq:jooq:3.20.3")
  implementation("org.flywaydb:flyway-core:11.8.0")
  implementation("org.flywaydb:flyway-database-postgresql:11.8.0")
  implementation("org.postgresql:postgresql:42.7.5")

  jooqGenerator("org.postgresql:postgresql:42.7.5")
  jooqGenerator("org.testcontainers:postgresql:1.21.0")
  jooqGenerator("org.slf4j:slf4j-simple:2.0.17")
}

buildscript {
  dependencies {
    classpath("org.testcontainers:postgresql:1.21.0")
    classpath("org.postgresql:postgresql:42.7.5")
    classpath("org.flywaydb:flyway-database-postgresql:10.22.0")
  }
}

With the implementation directives in dependencies, we define the classpath of the application; with the classpath directives in the buildscript.dependencies the classpath for the build script; and with the jooqGenerator directive in dependencies the classpath of the JOOQ generator. We see the postgresql dependency three times: once each for the main application, for the Flyway plugin, and for the JOOQ generator. We also see the slf4j-simple dependency for the JOOQ generator, which ensures that we actually see the logs produced by the generator. Otherwise, the logs are hidden. This took me a bit to figure out, since the documentation on this is not really explicit.

With this sidenote out of the way, back to choosing a JDBC URL.

Static Postgres instance

A solution that I sometimes see is to externalize the problem. The JDBC URL is set to jdbc:postgresql://localhost:12345/servicename, and it is then up to whoever runs the build to ensure that a database is running at the specified port, for example, by running a Postgres Docker container.

This is by far my least favorite solution. I think a build script should be self-contained. Anyone should be able to run it, without any additional setup. Having to ensure that a database is provided goes against this principle. It also makes it very easy to end up in non-reproducible builds, for example, if the provided database is in an inconsistent state. Another problem is the hard-coded port. It could happen that a user has a different service running on that port, which means that they either have to change the build file locally, or move the other service to a different port. There are various ways trying to work around those issues, but so far I haven’t seen one that really fixed all of them.

Testcontainers

In Testcontainers in Spring Boot I describe why I am a big fan of testcontainers, in particular of their JDBC support. It allows specifying a JDBC URL like jdbc:tc:postgresql:17:///databasename, which will automatically bring up a Postgres container when the URL is accessed. This sounds like the perfect solution for our problem: simply provide the same testcontainer URL for both Flyway and JOOQ.

flyway {
  url = "jdbc:tc:postgresql:17:///databasename"
}

jooq {
  configurations {
    create("main") {
      jooqConfiguration.apply {
        generator.database.inputSchema = "public"
        jdbc.apply {
          driver = "org.testcontainers.jdbc.ContainerDatabaseDriver"
          url = "jdbc:tc:postgresql:17:///databasename"
        }
      }
    }
  }
}

tasks.named("generateJooq") {
  dependsOn("flywayMigrate")
}

(Note that we have to specify the driver explicitly for JOOQ but not for Flyway. Properly explaining why that’s the case probably deserves its own blog post.)

At first, this seems to work. Running jooqGenerate does not produce any errors. But a second look reveals that the directory with generated sources remains empty. This is because the JOOQ plugin starts a new JVM process for the source generation. The way that the testcontainer magic works means that Flyway and JOOQ will end up actually using two different databases. After all, testcontainers are meant to provide isolation, and reusing the same container in two different JVMs would break this. So in our case, JOOQ sees a completely empty database, and therefore produces no source files.

There is a way to avoid this. Testcontainers can be marked as reusable, which allows both JVMs to access the same database. We can do this through the JDBC URL with jdbc:tc:postgresql:17:///databasename?TC_REUSABLE=true. There are two caveats though. The first is that this feature is opt-in. You have to enable this on your machine by either setting the environment variable TESTCONTAINERS_REUSE_ENABLE=true, or by adding a property to ~/.testcontainers.properties. This again goes against the principle that build scripts should be self-contained and not require additional setup. The other caveat is that by using this flag, the Postgres container will run even after the build script finished, and will be used if the build script is run again. This again leads to non-reproducibility problems.

Starting testcontainer manually

The solution to those problems is to start and stop the Postgres container manually. But this creates a new problem: we have to know the JDBC URL of this instance so that we can configure JOOQ and Flyway to use it. One way to do this would be to expose the instance on a predefined port. Then we know the JDBC URL and can use it explicitly in the build file. But we have already discussed above that this could lead to port conflicts, so it would be preferable to avoid it. Instead, we can configure Flyway and JOOQ after we start the Postgres container (on a dynamic port).

Here is how this looks like (you can find the full example at https://github.com/sgrj/gradle-jooq-flyway).

jooq {
  configurations {
    create("main") {
      jooqConfiguration.apply {
        generator.database.inputSchema = "public"
      }
    }
  }
}

tasks.named<JooqGenerate>("generateJooq") {
  doFirst {
    val dbContainer by project.extra(
      PostgreSQLContainer("postgres:17").also { it.start() }
    )

    Flyway
      .configure()
      .locations("filesystem:$projectDir/src/main/resources/db/migration")
      .dataSource(dbContainer.jdbcUrl, dbContainer.username, dbContainer.password)
      .load()
      .migrate()

    jooq {
      configurations {
        getByName("main") {
          jooqConfiguration.jdbc.apply {
            url = dbContainer.jdbcUrl
            username = dbContainer.username
            password = dbContainer.password
          }
        }
      }
    }
  }

  inputs.files(fileTree("src/main/resources/db/migration"))
  allInputsDeclared = true

  finalizedBy("stopDbContainer")
}

tasks.register("stopDbContainer") {
  doLast {
    if (project.extra.has("dbContainer")) {
      val dbContainer: PostgreSQLContainer<*> by project.extra
      dbContainer.stop()
    }
  }
}

Note that in the main jooq block, we don’t specify a database at all.

When we run generateJooq, we want the Postgres container to be brought up automatically. We ensure this with the doFirst block in the generateJooq task. This block is run before the actual task starts. In it, we start a fresh Postgres container. The dbContainer variable contains the JDBC URL as well as username and password. We use them to manually run Flyway (we don’t use the flywayMigrate task), and afterwards, change the JOOQ configuration to also use the same database.

After the generateJooq task finished, we stop the Postgres container. In a first iteration, I did this in a doLast block inside the jooqGenerate task. But the doLast block is only executed when jooqGenerate was successful. If there are any problems with a migration or the source generation, the doLast block is not executed and the container keeps running. Instead, we create a new task and use the finalizedBy directive, which always runs the given task. Since we do the stopping in a separate task, we need to make the container available to this task. That’s what the project.extra is for.

Finally, we define the input files for jooqGenerate. This ensures that jooqGenerate is only run when those files change (so when we add a new migration), or when the output files are deleted or changed.

Conclusion

It took me several iterations to arrive at this solution. So far, I’m pretty happy with it. It is self-contained and independent of the environment it is run in, as long as it supports running test containers.

—Written by Sebastian Jambor. Follow me on Mastodon @crepels@mastodon.social for updates on new blog posts.