Working with jOOQ and Flyway using Testcontainers

In this guide, you will learn how to

Create a Spring Boot application with jOOQ support
Generate jOOQ code using Testcontainers, Flyway and Maven Plugin
Implement basic database operations using jOOQ
Implement logic to load complex object graph using jOOQ
Test the jOOQ persistence layer using Testcontainers

Prerequisites

Java 17+
Your favorite IDE (Intellij IDEA, Eclipse, NetBeans, VS Code)
A Docker environment supported by Testcontainers https://www.testcontainers.org/supported_docker_environment/

What we are going to achieve in this guide

We are going to create a Spring Boot project using jOOQ together with Postgres. We will create our database tables by using Flyway Database migrations. We will configure testcontainers-jooq-codegen-maven-plugin to generate jOOQ code using Testcontainers and Flyway migration scripts.

We will implement our persistence layer repositories using jOOQ to manage Users, Posts, and Comments. Then we will test the repositories using Spring Boot testing support and Testcontainers Postgres module.

Getting Started

Create a new Spring Boot project from Spring Initializr by selecting Maven as build tool and add the starters JOOQ Access Layer, Flyway Migration, Spring Boot DevTools, PostgreSQL Driver and Testcontainers.

jOOQ (jOOQ Object Oriented Querying) is a popular open-source library that provides a fluent API for building typesafe SQL queries.

In order to leverage the benefits of TypeSafe DSL provided by jOOQ, we need to generate Java code from our database tables, views, and other objects, which will allow us to interact with the database using a fluent and intuitive API.

To learn more about how jOOQ code-generator will help you, please read Why You Should Use jOOQ With Code Generation.

In production-grade applications, it is highly recommended to use a database migration tool such as Flyway or Liquibase to apply any changes to the database.

So, the usual process of building and testing the application by generating jOOQ java code from the database is:

Create an instance of database using Testcontainers
Apply Flyway or Liquibase database migrations
Run jOOQ code-generator to generate Java code from the database objects.
Run integration tests

The jOOQ code generation can be automated as part of the Maven build process using the testcontainers-jooq-codegen-maven-plugin.

You can also use the official jOOQ code generator maven plugin by using jooq-codegen-maven, groovy-maven-plugin and flyway-maven-plugin together as described in https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/.

With jOOQ, the database comes first. So, let’s start with creating our database structure using Flyway migration scripts.

Create Flyway database migration scripts

In our sample application, we have users, posts and comments tables. Let’s create our first migration script following the Flyway naming convention.

Create src/main/resources/db/migration/V1__create_tables.sql file as follows:

create table users
(
    id         bigserial not null,
    name       varchar   not null,
    email      varchar   not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id),
    constraint user_email_unique unique (email)
);

create table posts
(
    id         bigserial                    not null,
    title      varchar                      not null,
    content    varchar                      not null,
    created_by bigint references users (id) not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id)
);

create table comments
(
    id         bigserial                    not null,
    name       varchar                      not null,
    content    varchar                      not null,
    post_id    bigint references posts (id) not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id)
);

ALTER SEQUENCE users_id_seq RESTART WITH 101;
ALTER SEQUENCE posts_id_seq RESTART WITH 101;
ALTER SEQUENCE comments_id_seq RESTART WITH 101;

Note that at the end of the SQL script, we have set our database sequence values to start with 101 so that we can insert some sample data along with primary key values for testing.

Configure jOOQ code generation using Maven plugin

Configure the testcontainers-jooq-codegen-maven-plugin in pom.xml as follows:

<properties>
    <testcontainers.version>1.19.3</testcontainers.version>
    <testcontainers-jooq-codegen-maven-plugin.version>0.0.3</testcontainers-jooq-codegen-maven-plugin.version>
    <jooq.version>3.18.3</jooq.version>
    <postgresql.version>42.6.0</postgresql.version>
</properties>

<build>
    <plugins>
        <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:16-alpine</containerImage>
                        </database>
                        <flyway>
                            <locations>
                                filesystem:src/main/resources/db/migration
                            </locations>
                        </flyway>
                        <jooq>
                            <generator>
                                <database>
                                    <includes>.*</includes>
                                    <excludes>flyway_schema_history</excludes>
                                    <inputSchema>public</inputSchema>
                                </database>
                                <target>
                                    <packageName>com.testcontainers.demo.jooq</packageName>
                                    <directory>target/generated-sources/jooq</directory>
                                </target>
                            </generator>
                        </jooq>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

Let’s understand the plugin configuration.

As we are using PostgreSQL database, we have configured the postgres JDBC driver and Testcontainers postgresql libraries as dependencies of the plugin.
Under <configuration>/<database> section, we have configured the type of the database, POSTGRES, that we want to use it for our code generation, and specified the Docker image name, postgres:16-alpine, which will be used to create the database instance.
Under <configuration>/<flyway> section we have specified the location of Flyway migration scripts path.
We have also configured the packageName and target location for the generated code. You can configure all the configuration options supported by the official jooq-code-generator plugin.

We have explicitly configured the versions of jOOQ, Testcontainers, Postgresql driver in the <properties> section. But with Spring Boot 3.1.0 version, these property values are already defined in the parent pom.xml. So, unless you want to override these versions, you don’t need to explicitly configure these library versions in the <properties> section.

The plugin uses Testcontainers to spin up an instance of PostgreSQL container, apply Flyway migrations and then generate the java code using jOOQ code generation tool.

With this configuration in place, now if you run ./mvnw clean package then you can find the generated code under the target/generated-sources/jooq directory.

Create model classes

We may want to create our own model classes to represent the data structures that we want to return for various use-cases. Imagine we are building a REST API, and we may want to return responses with only a subset of column values from our tables.

So, let’s create User, Post and Comment classes as follows:

package com.testcontainers.demo.domain;

public record User(Long id, String name, String email) {}

package com.testcontainers.demo.domain;

import java.time.LocalDateTime;
import java.util.List;

public record Post(
  Long id,
  String title,
  String content,
  User createdBy,
  List<Comment> comments,
  LocalDateTime createdAt,
  LocalDateTime updatedAt
) {}

package com.testcontainers.demo.domain;

import java.time.LocalDateTime;

public record Comment(
  Long id,
  String name,
  String content,
  LocalDateTime createdAt,
  LocalDateTime updatedAt
) {}

Implementing basic database operations using jOOQ

Let’s implement methods to create a new user and get user by email using jOOQ as follows:

package com.testcontainers.demo.domain;

import static com.testcontainers.demo.jooq.tables.Users.USERS;
import static org.jooq.Records.mapping;

import java.time.LocalDateTime;
import java.util.Optional;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

@Repository
class UserRepository {

  private final DSLContext dsl;

  UserRepository(DSLContext dsl) {
    this.dsl = dsl;
  }

  public User createUser(User user) {
    return this.dsl.insertInto(USERS)
      .set(USERS.NAME, user.name())
      .set(USERS.EMAIL, user.email())
      .set(USERS.CREATED_AT, LocalDateTime.now())
      .returningResult(USERS.ID, USERS.NAME, USERS.EMAIL)
      .fetchOne(mapping(User::new));
  }

  public Optional<User> getUserByEmail(String email) {
    return this.dsl.select(USERS.ID, USERS.NAME, USERS.EMAIL)
      .from(USERS)
      .where(USERS.EMAIL.equalIgnoreCase(email))
      .fetchOptional(mapping(User::new));
  }
}

You can see jOOQ DSL looks very similar to SQL but written in Java. By using jOOQ generated code, we can keep our code in-sync with the database structure and also benefit from the type safety.

For example, the where condition where(USERS.EMAIL.equalIgnoreCase(email)) expects a String for the email value. If you try to pass any non-string value like where(USERS.EMAIL.equalIgnoreCase(123)) then it will give you a compiler error preventing you from making mistakes at the compilation time itself rather than at runtime.

Spring Boot provides multiple ways to write tests based on the scope of the "unit" you are testing. If you want to test only the repository, then you can use the test slice annotations like @JdbcTest, @DataJpaTest, @JooqTest, etc, whereas if you want to write an integration test by loading the entire application context, then you can use @SpringBootTest annotation.

Before writing the tests, let’s create an SQL script to set up the test data by creating src/test/resources/test-data.sql file as follows:

DELETE FROM comments;
DELETE FROM posts;
DELETE FROM users;

INSERT INTO users(id, name, email) VALUES
(1, 'Siva', 'siva@gmail.com'),
(2, 'Oleg', 'oleg@gmail.com');

INSERT INTO posts(id, title, content, created_by, created_at) VALUES
(1, 'Post 1 Title', 'Post 1 content', 1, CURRENT_TIMESTAMP),
(2, 'Post 2 Title', 'Post 2 content', 2, CURRENT_TIMESTAMP);

INSERT INTO comments(id, name, content, post_id, created_at) VALUES
(1, 'Ron', 'Comment 1', 1, CURRENT_TIMESTAMP),
(2, 'James', 'Comment 2', 1, CURRENT_TIMESTAMP),
(3, 'Robert', 'Comment 3', 2, CURRENT_TIMESTAMP);

Write repository test using @JooqTest slice annotation

By using @JooqTest, SpringBoot loads only the persistence layer components and auto-configures jOOQ’s DSLContext.

In order to test the repository, we need to have a running Postgres database instance. We will use Testcontainers special JDBC URL to easily start a Postgres database and write the tests as follows:

package com.testcontainers.demo.domain;

import static org.assertj.core.api.Assertions.assertThat;

import org.jooq.DSLContext;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jooq.JooqTest;
import org.springframework.test.context.jdbc.Sql;

@JooqTest(
  properties = {
    "spring.test.database.replace=none",
    "spring.datasource.url=jdbc:tc:postgresql:16-alpine:///db",
  }
)
@Sql("/test-data.sql")
class UserRepositoryJooqTest {

  @Autowired
  DSLContext dsl;

  UserRepository repository;

  @BeforeEach
  void setUp() {
    this.repository = new UserRepository(dsl);
  }

  @Test
  void shouldCreateUserSuccessfully() {
    User user = new User(null, "John", "john@gmail.com");

    User savedUser = repository.createUser(user);

    assertThat(savedUser.id()).isNotNull();
    assertThat(savedUser.name()).isEqualTo("John");
    assertThat(savedUser.email()).isEqualTo("john@gmail.com");
  }

  @Test
  void shouldGetUserByEmail() {
    User user = repository.getUserByEmail("siva@gmail.com").orElseThrow();

    assertThat(user.id()).isEqualTo(1L);
    assertThat(user.name()).isEqualTo("Siva");
    assertThat(user.email()).isEqualTo("siva@gmail.com");
  }
}

Let’s understand what is going on in this test:

We have used @JooqTest slice test annotation to only load persistence layer components and auto-configure DSLContext.
We have used the Testcontainers special JDBC URL as spring.datasource.url property value which will automatically spin up a PostgreSQL container and configure Spring context to use it as DataSource.
As we have flyway-core dependency added, Spring Boot will automatically execute the Flyway migrations placed in src/main/resources/db/migration directory.
We have injected jOOQ’s DSLContext and instantiated UserRepository using JUnit’s @BeforeEach callback method.
Finally, our tests invoke the UserRepository methods and verified the expected return values.

Write Integration Test using @SpringBootTest

We can write integration tests using @SpringBootTest annotation which will load the entire application context. While you can use the Testcontainers special JDBC url with @SpringBootTest too, let us see how we can use Testcontainers with ServiceConnection support introduced in Spring Boot 3.1.0.

Learn more about the Testcontainers support introduced in Spring Boot 3.1.0 at Spring Boot Application Testing and Development with Testcontainers

package com.testcontainers.demo.domain;

import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.test.context.jdbc.Sql;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

@SpringBootTest
@Sql("/test-data.sql")
@Testcontainers
class UserRepositoryTest {

  @Container
  @ServiceConnection
  static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>(
    "postgres:16-alpine"
  );

  @Autowired
  UserRepository repository;

  @Test
  void shouldCreateUserSuccessfully() {
    User user = new User(null, "John", "john@gmail.com");

    User savedUser = repository.createUser(user);

    assertThat(savedUser.id()).isNotNull();
    assertThat(savedUser.name()).isEqualTo("John");
    assertThat(savedUser.email()).isEqualTo("john@gmail.com");
  }

  @Test
  void shouldGetUserByEmail() {
    User user = repository.getUserByEmail("siva@gmail.com").orElseThrow();

    assertThat(user.id()).isEqualTo(1L);
    assertThat(user.name()).isEqualTo("Siva");
    assertThat(user.email()).isEqualTo("siva@gmail.com");
  }
}

Let’s understand what is going on here:

We have used @SpringBootTest annotation which loads the entire application context because of which we are able to directly inject the UserRepository bean.
We have used Testcontainers JUnit 5 Extension annotations @Testcontainers and @Container to spin up a PostgreSQL container and used @ServiceConnection to auto-configure the datasource properties.
We have initialized the test data using @Sql("/test-data.sql")
The tests are similar to the tests we have written using @JooqTest slice annotation in the previous section.

Fetching complex object trees using jOOQ

So far we have seen using jOOQ to perform very basic database operations. But jOOQ shines when it comes to querying the database with complex queries, stored procedures, etc.

In our database model, we have Many-To-One relationship from Post-to-User, One-To-Many relationship from Post-to-Comment.

Let us see how we can get a Post for a given postId along with the created user and its comments using jOOQ powerful MULTISET feature using a single query.

package com.testcontainers.demo.domain;

import static com.testcontainers.demo.jooq.Tables.COMMENTS;
import static com.testcontainers.demo.jooq.tables.Posts.POSTS;
import static org.jooq.Records.mapping;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.row;
import static org.jooq.impl.DSL.select;

import java.util.Optional;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

@Repository
class PostRepository {

  private final DSLContext dsl;

  PostRepository(DSLContext dsl) {
    this.dsl = dsl;
  }

  public Optional<Post> getPostById(Long id) {
    return this.dsl.select(
        POSTS.ID,
        POSTS.TITLE,
        POSTS.CONTENT,
        row(POSTS.users().ID, POSTS.users().NAME, POSTS.users().EMAIL)
          .mapping(User::new)
          .as("createdBy"),
        multiset(
          select(
            COMMENTS.ID,
            COMMENTS.NAME,
            COMMENTS.CONTENT,
            COMMENTS.CREATED_AT,
            COMMENTS.UPDATED_AT
          )
            .from(COMMENTS)
            .where(POSTS.ID.eq(COMMENTS.POST_ID))
        )
          .as("comments")
          .convertFrom(r -> r.map(mapping(Comment::new))),
        POSTS.CREATED_AT,
        POSTS.UPDATED_AT
      )
      .from(POSTS)
      .where(POSTS.ID.eq(id))
      .fetchOptional(mapping(Post::new));
  }
}

We have used jOOQ’s Nested records support for loading the ManyToOne association of Post-to-User and MULTISET feature for loading OneToMany association of Post-to-Comments.

To learn more about jOOQ’s MULTISET feature, please refer https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/

From jOOQ 3.19, fetching the associations using MULTISET is further simplified using implicit path correlations feature.

We can write integration test for PostRepository as follows:

package com.testcontainers.demo.domain;

import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;

@SpringBootTest(
  properties = {
    "spring.test.database.replace=none",
    "spring.datasource.url=jdbc:tc:postgresql:16-alpine:///db",
  }
)
@Sql("/test-data.sql")
class PostRepositoryTest {

  @Autowired
  PostRepository repository;

  @Test
  void shouldGetPostById() {
    Post post = repository.getPostById(1L).orElseThrow();

    assertThat(post.id()).isEqualTo(1L);
    assertThat(post.title()).isEqualTo("Post 1 Title");
    assertThat(post.content()).isEqualTo("Post 1 content");
    assertThat(post.createdBy().id()).isEqualTo(1L);
    assertThat(post.createdBy().name()).isEqualTo("Siva");
    assertThat(post.createdBy().email()).isEqualTo("siva@gmail.com");
    assertThat(post.comments()).hasSize(2);
  }
}

Run tests

./mvnw test

You should see all tests PASS. You can also notice that after tests are executed, the containers are stopped and removed automatically.

Summary

The Testcontainers library helped us to generate java code from the database using jOOQ code generator tool, and we are able to write tests by using the same type of database, PostgreSQL, that we use in production as opposed to using mocks or in-memory databases.

As we are always generating the code from the current state of the database, we can ensure that our code is in sync with the database changes, and we are free to do any code refactoring and still ensure that the application is working as expected.

To learn more about Testcontainers visit http://testcontainers.com

Prerequisites

What we are going to achieve in this guide

Getting Started

Create Flyway database migration scripts

Configure jOOQ code generation using Maven plugin

Create model classes

Implementing basic database operations using jOOQ

Write repository test using @JooqTest slice annotation

Write Integration Test using @SpringBootTest

Fetching complex object trees using jOOQ

Run tests

Summary

Further Reading