Background

In the past implementation, we had to start a Postgres server, supply a script to initialize the tables in database, and had to connect to the database to generate jOOQ code. The flow is not ideal because it creates a dependency to the database. One of the solutions is to generate the jooQ code from a different container. I use two tools to solve this: Testcontainers and Flyway.

Testcontainers is a framework to create a throwaway instance of Docker container, and Flyway is a library to migrate database. We will create a Postgres container using Testcontainers and Flyway to initialize the tables needed. jooQ will then generate the code from the Testcontainers instance. We don’t have to supply our database credentials during build process anymore if using this method, thus removing the dependency to the Postgres server.

Implementation

The new flow to generate jooQ code is:

  1. Create a Postgres container using Testcontainers.
  2. Initialize tables using Flyway.
  3. jOOQ will connect to the Testcontainers instance and generate code from there.

To run Testcontainers, we use groovy-maven-plugin plugin to execute a Groovy script.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<plugin>
  <groupId>org.codehaus.gmaven</groupId>
  <artifactId>groovy-maven-plugin</artifactId>
  <version>2.1.1</version>
  <executions>
    <execution>
      <phase>generate-sources</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <source>
          db = new org.testcontainers.containers.PostgreSQLContainer(
              "postgres:15-alpine")
              .withUsername("${db.username}")
              .withDatabaseName("postgres")
              .withPassword("${db.password}");
          db.start();
          project.properties.setProperty('db.url', db.getJdbcUrl());
          project.properties.setProperty('testcontainer.containerid', db.getContainerId());
          project.properties.setProperty('testcontainer.imageName', db.getDockerImageName());
        </source>
      </configuration>
    </execution>
    <execution>
      <id>testcontainer-stop</id>
      <phase>test</phase>
      <goals>
        <goal>execute</goal>
      </goals>
      <configuration>
        <source>
          containerId = "${testcontainer.containerid}"
          imageName = "${testcontainer.imageName}"
          println("Stopping testcontainer $containerId - $imageName")
          org.testcontainers.utility.ResourceReaper
              .instance()
              .stopAndRemoveContainer(containerId, imageName);
        </source>
      </configuration>
    </execution>
  </executions>
  <dependencies>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>postgresql</artifactId>
      <version>1.18.0</version>
    </dependency>
  </dependencies>
</plugin>

In the code snippet above, the plugin does two things:

  • In generate-sources phase, the plugin starts a new PostgreSQL container with the image tag postgres:15-alpine. It also creates a database named postgres, with username and password provided by the project. At the end of the script, it sets several project properties: db.url, testcontainer.containerid, and testcontainer.imageName.
  • During test phase, the project stops the instance and remove the container.

The plugin depends on org.testcontainers:postgresql package to run.

To execute SQL script, we use flyway-maven-plugin plugin:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

<plugin>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-maven-plugin</artifactId>
  <version>9.22.0</version>
  <executions>
    <execution>
      <phase>generate-sources</phase>
      <goals>
        <goal>migrate</goal>
      </goals>
      <configuration>
        <url>${db.url}</url>
        <user>${db.username}</user>
        <password>${db.password}</password>
        <locations>
          <location>filesystem:src/main/resources/db/migration</location>
        </locations>
      </configuration>
    </execution>
  </executions>
</plugin>

This plugin uses db.url,db,username, and db.password properties as a part of the configuration. The SQL scripts are stored in src/man/resources/db/migration. I named my script file V1_0__init_tables.sql to fulfill Flyway’s naming convention.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

CREATE TABLE IF NOT EXISTS users (
  id BIGINT NOT NULL PRIMARY KEY,
  username TEXT,
  email TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY(START WITH 200000000) ,
  user_id BIGINT REFERENCES users(id),
  name TEXT,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  type TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
x);

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  category_id BIGINT REFERENCES categories(id),
  amount BIGINT,
  transaction_date DATE NOT NULL DEFAULT CURRENT_DATE,
  note TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The script creates three tables: users, categories, transactions.

The last step is to generate the code. We can run command mvn clean install without having to provide additional parameters.

Using All in One Library

There is a library that can do all the above processes: testcontainers-jooq-codegen-maven-plugin. It can create a DB server using a Testcontainer instance, run Flyway migration script, and generate the jooQ code. We can remove the dependencies to groovy-maven-plugin, flyway-maven-plugin, and jooq-codegen-maven if we use this library.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

<plugin>
  <groupId>org.testcontainers</groupId>
  <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
  <version>${testcontainers-jooq-codegen-maven-plugin.version}</version>
  <dependencies>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>postgresql</artifactId>
      <version>${testcontainers.version}</version>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>${postgresql.version}</version>
    </dependency>
  </dependencies>
  <executions>
    <execution>
      <id>generate-jooq-sources</id>
      <goals>
        <goal>generate</goal>
      </goals>
      <phase>generate-sources</phase>
      <configuration>
        <database>
          <type>POSTGRES</type>
          <containerImage>postgres:15-alpine</containerImage>
        </database>
        <flyway>
          <locations>
            filesystem:src/main/resources/db/migration
          </locations>
        </flyway>
        <jooq>
          <generator>
            <database>
              <includes>.*</includes>
              <inputSchema>public</inputSchema>
            </database>
            <target>
              <packageName>com.tabunganku.jooq.model</packageName>
              <directory>target/generated-sources/jooq</directory>
            </target>
          </generator>
        </jooq>
      </configuration>
    </execution>
  </executions>
</plugin>

This plugin needs dependency to Testcontainer’s Postgres library and Postgres JDBC driver. Under database section, set the type to POSTGRES because we use Postgres database and postgres:15-alpine as the container image for mimicking the existing environment. For Flyway configuration, set the directory to src/main/resources/db/migration. For jooQ section, set the target package name to com.tabunganku.jooq.model and the target directory target/generated-sources/jooq. Run mvn clean install to generate the jOOQ code and build the project.

Which One is Better ?

I prefer testcontainers-jooq-codegen-maven-plugin because it allows me to integrate quicker and without any hassle. Using multiple libraries enables a lot of customization, but the project does not require it at the moment.

Additional Resources

  • Testcontainers: link
  • Flyway: link
  • Using Testcontainers to Generate jOOQ Code: link
  • testcontainers-jooq-codegen-maven-plugin: link
  • Pull Request for First Method: PR
  • Pull Request for Second Method: PR