Fully Automate Your jOOQ File Generation
How to use Gradle, Docker, and Flyway to automate jOOQ file generation without your actual database
Java Object Oriented Querying, aka jOOQ, is a handy tool to write SQL queries for your application in a type-safe and convenient way via a Java/Kotlin DSL (domain-specific language). The only downside compared to writing SQL queries by hand or using ORM frameworks like Hibernate in combination with JPA is that to achieve type safety, the classes you use have to be generated with the jOOQ CLI tool.
The file generation can be done in multiple ways, but the most reliable way is to let jOOQ connect to your database and read out all the information needed to create the corresponding classes. Unfortunately, this reveals two issues:
- jOOQ needs an actual database. This can complicate fast iterations while developing, as you’ll need to maintain a local database constantly.
- When connecting to a production or dev environment database, you can only generate classes based on the current version of the database, and this won’t include changes done in your local environment.
Luckily, it is possible to automate this process by including the jOOQ file generation in the build process of your JVM application as long as you avoid previously mentioned issues.
As an example project, we will use a JVM web application written in Kotlin with Spring Boot as the web framework. Gradle will be the build tool; however, everything in this article can be achieved with Maven as well.
PostgreSQL is the chosen database for our example. The most common way to initialize and configure the database tables is with the help of a migration tool. For our project, we will use Flyway. However, the same concept would work with Liquibase.
Our project folder looks like this:
It contains a folder with all our database migration scripts that Flyway will use to apply the changes to our PostgreSQL database.
Additionally, our project contains a Docker Compose file compose.yaml. It contains the definition of a PostgreSQL container, and it’s mostly used to allow local development.
When we want to start developing locally and creating our database, we would have to apply the migration either during the startup of our application (which I would not recommend if the plan is to scale the service later on) or by hand before starting it.
We will add a Flyway docker container to the compose file to make our life a bit easier. Via the compose configuration, we mount our migration script folder as a volume and configure compose to execute the Flyway container after the PostgreSQL container has started. With this trick, we can automatically apply database changes whenever we run the docker-compose config.
# FILE: compose.yaml
version: '3'
services:
postgres:
image: 'postgres:15'
container_name: "postgres"
environment:
- 'POSTGRES_DB=postgres'
- 'POSTGRES_PASSWORD=postgres'
- 'POSTGRES_USER=postgres'
ports:
- '5432:5432'
flyway:
# Use Docker image containing Flyway CLI
image: flyway/flyway:9.22.1
container_name: "flyway-migration"
# Execute migration command with input parameters on container startup
command: -locations=filesystem:/flyway/migration -user=postgres -password=postgres -url="jdbc:postgresql://postgres:5432/postgres" -connectRetries=5 migrate
# Copy migration script folder into container
volumes:
- ./db/migration:/flyway/migration
# Wait for Postgres container to start
depends_on:
- postgresImportant: Make sure the defined ports for the PostgreSQL container in the
compose.yamlare free and not blocked by other running applications or containers. Alternatively, you can update them to different ports. When doing so, make sure to also update the command in the flyway service config.
With the help of this script, we now have an easy way to start up the database with all migrations applied.
Tip: If you are using Spring Boot, you can use the module “spring-boot-docker-compose” as a development dependency to automatically run the docker compose file when starting your application.
We could run the jOOQ file generation tool against this running database to create correct DSL classes. However, the goal is to automate this process completely. To achieve this, we can enhance the Gradle build file of our project with two additional plugins. Both or similar plugins are also available for Maven.
- Docker Plugin: Allows us to run docker-compose commands on a previously configured file in the project
- jOOQ Plugin: Allows us to run jOOQ file generation CLI as a Gradle task, including all necessary configuration.
// FILE: build.gradle.kt
plugins {
// Addtional plugins
id("nu.studer.jooq") version "8.2"
id("com.avast.gradle.docker-compose") version "0.17.5"
// ... other plugins
}
// ... other configurations
// Configure Docker plugin
dockerCompose {
useComposeFiles = listOf("compose.yaml")
projectNamePrefix = "jooq-sample"
}
// Configure jOOQ plugin
jooq {
// use jOOQ version defined in Spring Boot
version = dependencyManagement.importedProperties["jooq.version"]
edition = JooqEdition.OSS
configurations {
create("main") { // name of the jOOQ configuration
jooqConfiguration.apply {
// configure database connection
jdbc.apply {
driver = "org.postgresql.Driver"
url = "jdbc:postgresql://localhost:5432/postgres"
user = "postgres"
password = "postgres"
}
generator.apply {
// Generate Kotlin classes isntead of Java
name = "org.jooq.codegen.KotlinGenerator"
database.apply {
name = "org.jooq.meta.postgres.PostgresDatabase"
inputSchema = "public"
}
// Target package of the generated code
target.apply {
packageName = "me.jonastm.jooqsample.adapter.out.persistence.entities"
directory = "build/generated-src/jooq/main" // default (can be omitted)
}
strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
}
}
}
}
}Once the plugins are correctly configured, we can combine their functionalities by creating a custom Gradle task (I named it “generateDatabaseTypes”) that will do the following:
- Run
docker compose up. This will start the PostgreSQL database and apply migrations - Execute jOOQ file generation
- Run
docker compose downto remove the containers again
// FILE: build.gradle.kt
// ... previous configurations
tasks.register("generateDatabaseTypes") {
// Execute the needed Gradle tasks
dependsOn("composeUp")
dependsOn("generateJooq")
dependsOn("composeDown")
// Ensure correct execution order of the tasks
tasks.findByName("generateJooq")?.shouldRunAfter("composeUp")
tasks.findByName("composeDown")?.shouldRunAfter("generateJooq")
}In the project folder, we can execute this task on the command line via ./gradle generateDatabaseTypes. We will see that a build folder will be created containing, among other files, all of the jOOQ classes for our custom-tailored SQL DSL.
This makes updating jOOQ DSL class files much more convenient as we have to execute a single Gradle task. Additionally, add the execution of this task in a potential CI/CD pipeline, making sure all required files are available before trying to build the application.
Important: When using this task in CI/CD pipeline or even locally, make sure you have docker (with compose) running and available to the current OS user. Otherwise, the task will fail.
If you want to avoid even running the Gradle task, you can go one step further and add the task to the default Gradle build task. However, this might unnecessarily increase build times during application development.
// FILE: build.gradle.kt
// ...
tasks.named("build") {
dependsOn("generateDatabaseTypes")
}This concludes my guide on how to automate jOOQ file generation. As mentioned, the example shows how to do it with Gradle, Kotlin, PostgreSQL, and Flyway. However, the same concept can be applied to projects with other setups, e.g., Maven, Java, MySQL, and Liquibase.
If you are interested in Java, Kotlin, and cloud development, consider following me on Medium for more articles. A complete example project can be found in the following GitHub repository:
