Table of Contents

Working with jOOQ and Flyway using Testcontainers

This guide shows how to create a Micronaut application with jOOQ support

Authors: Sergio del Amo

Micronaut Version: 4.6.3

1. About this Guide

In this guide, you will learn how to

  • Create a Micronaut 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

  • Simplify testing with Micronaut Test Resources

2. What you will need

To complete this guide, you will need the following:

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. What we are going to achieve in this guide

We are going to create a Micronaut project using jOOQ together with PostgreSQL. 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 Micronaut testing support and Testcontainers PostgreSQL module.

5. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide \
    --features=flyway,jooq,postgres,testcontainers,assertj \
    --build=maven \
    --lang=java
If you don’t specify the --build argument, Gradle with the Kotlin DSL is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.
If you don’t specify the --test argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

If you use Micronaut Launch, select Micronaut Application as application type and add flyway, jooq, postgres, testcontainers, and assertj features.

If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Diff

Setup IntelliJ IDEA to develop Micronaut Applications.

6. Getting Started

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.

7. Configuration

7.1. Data Source Configuration

testcontainers/src/main/resources/application.properties
datasources.default.db-type=postgres
datasources.default.driver-class-name=org.postgresql.Driver

Please note that the configuration does not specify the JDBC URL, username, or password. We will supply them dynamically in our tests or via Test Resources.

7.2. Flyway Configuration

We need a way to create the database schema. For that, we use Micronaut integration with Flyway.

Flyway automates schema changes, significantly simplifying schema management tasks, such as migrating, rolling back, and reproducing in multiple environments.

testcontainers/src/main/resources/application.properties
flyway.datasources.default.enabled=true
1 Enable Flyway for the default datasource.
Configuring multiple data sources is as simple as enabling Flyway for each one. You can also specify directories that will be used for migrating each data source. Review the Micronaut Flyway documentation for additional details.

Flyway migration will be automatically triggered before your Micronaut application starts. Flyway will read migration commands in the resources/db/migration/ directory, execute them if necessary, and verify that the configured data source is consistent with them.

8. 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:

testcontainers/src/main/resources/db/migration/V1__create_tables.sql
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.

9. Configure jOOQ code generation using Maven plugin

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 ...
    <properties>
...
        <testcontainers-jooq-codegen-maven-plugin.version>0.0.2</testcontainers-jooq-codegen-maven-plugin.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>42.6.0</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.3-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>example.micronaut.jooq</packageName>
                                        <directory>target/generated-sources/jooq</directory>
                                    </target>
                                </generator>
                            </jooq>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
            ...
        </plugins>
    </build>
</project>

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:15.3-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.

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 package then you can find the generated code under the target/generated-sources/jooq directory.

10. 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:

testcontainers/src/main/java/example/micronaut/domain/User.java
package example.micronaut.domain;

public record User(
        Long id,
        String name,
        String email
) {}
testcontainers/src/main/java/example/micronaut/domain/Post.java
package example.micronaut.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
) {}
testcontainers/src/main/java/example/micronaut/domain/Comment.java
package example.micronaut.domain;

import java.time.LocalDateTime;

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

11. Implementing basic database operations using jOOQ

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

testcontainers/src/main/java/example/micronaut/domain/UserRepository.java
package example.micronaut.domain;

import static example.micronaut.jooq.tables.Users.USERS;
import static org.jooq.Records.mapping;

import java.time.LocalDateTime;
import java.util.Optional;

import jakarta.inject.Singleton;
import org.jooq.DSLContext;

@Singleton (1)
class UserRepository {

    private final DSLContext dsl;

    UserRepository(DSLContext dsl) { (2)
        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));
    }

    public void deleteUser(Long id) {
        dsl.deleteFrom(USERS).where(USERS.ID.eq(id)).execute();
    }
}
1 Use jakarta.inject.Singleton to designate a class as a singleton.
2 Use constructor injection to inject a bean of type org.jooq.DSLContext. Micronaut SQL jOOQ integration registers DSLContext singletons so that you can inject them into your application.

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.

12. Tests

13. Testcontainers Dependencies

The generated application contains the following Testcontainers dependencies:

pom.xml
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers</artifactId>
    <scope>test</scope>
</dependency>
pom.xml
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

13.1. Test Seed Data

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

testcontainers/src/test/resources/db/testmigration/V99__test_data.sql
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);

Load the test seed data by adding the following configuration in src/test/resources/application-test.properties.

testcontainers/src/test/resources/application-test.properties
flyway.datasources.default.locations[0]=classpath:db/migration
flyway.datasources.default.locations[1]=classpath:db/testmigration

13.2. AbstractTest class

Create an abstract class to simplify the dynamic configuration of the database connection

testcontainers/src/test/java/example/micronaut/domain/AbstractTest.java
package example.micronaut.domain;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.test.support.TestPropertyProvider;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;

import java.util.Map;

public abstract class AbstractTest implements TestPropertyProvider { (1)

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

    @Override
    public @NonNull Map<String, String> getProperties() { (1)
        if (!postgres.isRunning()) {
            postgres.start();
        }
        return Map.of("datasources.default.url", postgres.getJdbcUrl(),
                "datasources.default.username", postgres.getUsername(),
                "datasources.default.password", postgres.getPassword());
    }
}
1 When you need dynamic properties definition, implement the TestPropertyProvider interface. Override the method .getProperties() and return the properties you want to expose to the application.
2 Shared containers are defined as static fields in a top level test class and have to be annotated with @Container.

13.3. Write repository test

In order to test the repository, we need to have a running Postgres database instance. We will extend from AbstractTest to easily start a Postgres database and write the tests as follows:

testcontainers/src/test/java/example/micronaut/domain/UserRepositoryTest.java
package example.micronaut.domain;

import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.testcontainers.junit.jupiter.Testcontainers;
import static org.assertj.core.api.Assertions.assertThat;

@MicronautTest(startApplication = false) (1)
@TestInstance(TestInstance.Lifecycle.PER_CLASS) (2)
@Testcontainers(disabledWithoutDocker = true) (3)
class UserRepositoryTest extends AbstractTest {

    @Inject
    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");

        repository.deleteUser(savedUser.id());
    }

    @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");
    }


}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context. This test does not need the embedded server. Set startApplication to false to avoid starting it.
2 Classes that implement TestPropertyProvider must use this annotation to create a single class instance for all tests (not necessary in Spock tests).
3 Disable test if Docker not present.

The tests invoke the UserRepository methods and verified the expected return values.

14. 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.

testcontainers/src/main/java/example/micronaut/domain/PostRepository.java
package example.micronaut.domain;

import static example.micronaut.jooq.Tables.COMMENTS;
import static example.micronaut.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 jakarta.inject.Singleton;
import org.jooq.DSLContext;

@Singleton (1)
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));
    }
}
1 Use jakarta.inject.Singleton to designate a class as a singleton.

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.

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:

testcontainers/src/test/java/example/micronaut/domain/PostRepositoryTest.java
package example.micronaut.domain;

import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.testcontainers.junit.jupiter.Testcontainers;
import java.util.Optional;
import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertTrue;

@MicronautTest(startApplication = false) (1)
@TestInstance(TestInstance.Lifecycle.PER_CLASS) (2)
@Testcontainers(disabledWithoutDocker = true) (3)
class PostRepositoryTest extends AbstractTest {

    @Test
    void shouldGetPostById(PostRepository repository) {
        Optional<Post> postOptional = repository.getPostById(1L);
        assertTrue(postOptional.isPresent());
        Post post = postOptional.get();

        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);
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context. This test does not need the embedded server. Set startApplication to false to avoid starting it.
2 Classes that implement TestPropertyProvider must use this annotation to create a single class instance for all tests (not necessary in Spock tests).
3 Disable test if Docker not present.

15. Testing the Application

To run the 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.

16. Testing with Test Resources

We are going to simplify testing with Micronaut Test Resources:

Micronaut Test Resources adds support for managing external resources which are required during development or testing.

16.1. Removing Testcontainers Dependencies

Remove the Testcontainers dependencies from your build files.

16.2. Configure Test Resources

Integerate your Maven Build with Test Resources

You can enable test resources support simply by setting the property micronaut.test.resources.enabled (either in your POM or via the command-line).

16.3. Test Resources PostgreSQL

When the application is started locally — either under test or by running the application — the resolution of the properties datasources.default.url, datasources.default.username, and datasources.default.password is detected. The Test Resources service starts a local PostgreSQL docker container and injects the required properties.

16.4. Simpler Test with Test Resources

Thanks to Test Resources, we can simplify the test, no need for an abstract class, as follows:

testresources/src/test/java/example/micronaut/domain/UserRepositoryTest.java
package example.micronaut.domain;

import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;
import static org.assertj.core.api.Assertions.assertThat;

@MicronautTest(startApplication = false) (1)
class UserRepositoryTest {

    @Inject
    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");

        repository.deleteUser(savedUser.id());
    }

    @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");
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. By default, each @Test method will be wrapped in a transaction that will be rolled back when the test finishes. This behaviour is is changed by setting transaction to false.

If you run the test, you will see a PostgreSQL container being started by Test Resources through integration with Testcontainers to provide throwaway containers for testing.

16.5. Micronaut Test Resources Goals

Micronaut Test Resources aims to achieve the following goals:

  • zero-configuration: without adding any configuration, test resources should be spawned and the application configured to use them. Configuration is only required for advanced use cases.

  • classpath isolation: use of test resources shouldn’t leak into your application classpath, nor your test classpath

  • compatible with GraalVM native: if you build a native binary, or run tests in native mode, test resources should be available

  • easy to use: the Micronaut build plugins for Gradle and Maven should handle the complexity of figuring out the dependencies for you

  • extensible: you can implement your own test resources, in case the built-in ones do not cover your use case

  • technology agnostic: while lots of test resources use Testcontainers under the hood, you can use any other technology to create resources

17. 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.

18. Next Steps

Learn more about:

19. Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

20. License

All guides are released with an Apache license 2.0 license for the code and a Creative Commons Attribution 4.0 license for the writing and media (images…​).