Coming Up for Air

Quarkus Dev Services, jOOQ, Flyway, and Testcontainers: A Full Example

Wednesday, April 20, 2022 |

I have written a few posts about using Quarkus with Testcontainers, Flyway, and jOOQ. Since posting those, I’ve learned some new tricks that have changed how I integrate the various tools. In this post, I’d like to share a complete example that shows how use Quarkus, Quarkus Dev Services, Testcontainers, and Flyway together for a zero (ish) local config setup.

Introduction

To state things more clearly, the project developed here will have the following features:

  • No need for a locally-installed database

  • A Flyway-managed database schema

  • Maven-driven jOOQ code generation

  • A database instance using Quarkus Dev Services for use when running the Quarkus app in dev and test mode

  • Testcontainers-based testing

Buckle up. This is going to be a long one. :)

Basic Project Setup

Let’s start with the Maven setup. I won’t show the entire pom here. For that, please see the GitHub repo.

We’ll start by setting up dependencyManagement to make handling Quarkus dependencies easier:

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

<properties>
    <version.build-helper>3.2.0</version.build-helper>
    <version.compiler-plugin>3.8.1</version.compiler-plugin>
    <version.flyway>8.4.1</version.flyway>
    <version.jooq>3.16.6</version.jooq>
    <version.junit-jupiter>5.8.2</version.junit-jupiter>
    <version.pgsql-jdbc>42.3.3</version.pgsql-jdbc>
    <version.quarkus>2.8.1.Final</version.quarkus>
    <version.rest-assured>5.0.1</version.rest-assured>
    <version.surefire>2.22.2</version.surefire>
    <version.testcontainers>1.17.1</version.testcontainers>
</properties>

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-bom</artifactId>
            <version>${version.quarkus}</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

Next, let’s add the dependencies for the various tools we’ll be using:

 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
51
52
53
54
55
56
57
58
59
60
61

<dependencies>
    <!-- Quarkus -->
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-flyway</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-resteasy-jackson</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-agroal</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-jdbc-postgresql</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-arc</artifactId>
    </dependency>
    <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-junit5</artifactId>
        <scope>test</scope>
    </dependency>
    <!-- Quarkus -->
    <!-- jOOQ -->
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq</artifactId>
        <version>${version.jooq}</version>
    </dependency>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-meta</artifactId>
        <version>${version.jooq}</version>
    </dependency>
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen</artifactId>
        <version>${version.jooq}</version>
    </dependency>
    <!-- jOOQ -->
    <!-- JUnit -->
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>${version.junit-jupiter}</version>
        <scope>test</scope>
    </dependency>
    <!-- RestAssured -->
    <dependency>
        <groupId>io.rest-assured</groupId>
        <artifactId>rest-assured</artifactId>
        <version>${version.rest-assured}</version>
        <scope>test</scope>
    </dependency>
    <!-- RestAssured -->
</dependencies>

There will be more changes, but we’ll add those in the following sections.

Flyway Setup

Flyway, as you may already know, is a tool that we can use to manage database changes more easily. What we will do then, is set up our project to use Flyway to manage our database for both production and test usage. We’ll start by setting up the input files. We’ll put those in src/main/resources/db/migration (as required by the quarkus-flyway extension), starting with the base schema file, V0001_schema.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE TABLE authors
(
    id         INT PRIMARY KEY,
    last_name  TEXT,
    first_name TEXT
);

CREATE TABLE books
(
    id             INT PRIMARY KEY,
    title          TEXT,
    description    TEXT,
    published_year INT,
    author_id      INT NULL REFERENCES authors (id)
);

When Flyway runs, it will check the database to see if this file has already been processed. It does so by checking a metadata table it creates for just this purpose. If the database is persistent (as it would be in production), this file will be skipped. We’ll see, though, that our database will be ephemeral, so it will always be run. That, though, is simply a 'feature' of this demo. :)

I also like to have a file that loads dummy data so that I have something to test with, as well as to work with in dev mode while I’m working on the application (which I move/remove when getting ready for production, of course). To do that, I use a repeatable migration. Our example here is src/main/resources/db/migration/R__dummy_data.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DELETE FROM books;
DELETE FROM authors;
INSERT INTO authors (id, last_name, first_name)
VALUES (1, 'Tolkien', 'J.R.R.'),
       (2, 'Lewis', 'C.S'),
       (3, 'Sanderson', 'Brandon'),
       (4, 'Tom', 'Clancy');

INSERT INTO books (id, title, description, published_year, author_id)
VALUES
       -- Source: https://www.tolkiensociety.org/actorModel/books-by-tolkien/
       (1, 'Sir Gawain & The Green Knight', 'A modern translation of the Middle English romance from the stories of King Arthur.', 1925, 1),
       (2, 'The Hobbit: or There and Back Again', 'The bedtime story for his children famously begun on the blank page of an exam script that tells the tale of Bilbo Baggins and the dwarves in their quest to take back the Lonely Mountain from Smaug the dragon.', 1937, 1),
-- ...
;

It is our responsibility to make sure that repeatable migrations can, indeed, be repeated, so we delete everything from our two example tables. That’s overkill for our demo, but I find it a good general practice.

Now we need to wire Flyway into our application. Fortunately, Quarkus has built-in support for this, so we simply need to add a property to enable it. We add this in src/main/resources/application.properties:

1

quarkus.flyway.migrate-at-start=true

Quarkus will use the defined datasource for running Flyway, which we’ll configure now.

Quarkus Dev Services

Quoting from the documentation, "Quarkus supports the automatic provisioning of unconfigured services in development and test mode." Specifically, we’re interested in the database at this point. Quoting again from the docs, "The database Dev Services will be enabled when a reactive or JDBC datasource extension is present in the application, and the database URL has not been configured.".

So to enable Dev Services, we need to not configure a datasource, but we still need a datasource in production. Fortunately, Quarkus has us covered there as well. We can qualify our configuration properties for various modes. That leads us to a configuration that might look like this:

1
2
3
4
5
6

quarkus.datasource.db-kind=postgresql
quarkus.datasource.devservices.port=54321

%prod.quarkus.datasource.jdbc.url=${DATABASE_URL:jdbc:postgresql://localhost:5432/fullexample}
%prod.quarkus.datasource.username=${DATABASE_USER:steeplesoft}
%prod.quarkus.datasource.password=${DATABASE_PASS:steeplesoft}

We need to tell Quarkus what kind of database we want, so we set quarkus.datasource.db-kind. We also don’t want to conflict with any possibly running PostgreSQL instance on a given machine, so we set the port to something less likely to conflict. It would be nice to be able to randomize this, but I’m not sure how. If you do, hit me up on Twitter. :)

The second set of properties are prefixed with %prod. When running in dev or test mode, these properties will be ignored, but will be applied (minus the prefix) when the application is run in production.

The magic here is somewhat implicit. When we start the app with mvn quarkus:dev, since we have the quarkus-agroal extension included in our build, Quarkus will start up a PostgreSQL instance, as well as setting up a DataSource, ready for injection. Or use with Flyway. When we start the server in dev mode, we should see entries like the following from standard out:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[io.qua.dat.dep.dev.DevServicesDatasourceProcessor] (build-30) Dev Services for the default datasource (postgresql) started.
[org.fly.cor.int.lic.VersionPrinter] (Quarkus Main Thread) Flyway Community Edition 8.5.8 by Redgate
[org.fly.cor.int.lic.VersionPrinter] (Quarkus Main Thread) See what's new here: https://flywaydb.org/documentation/learnmore/releaseNotes#8.5.8
[org.fly.cor.int.lic.VersionPrinter] (Quarkus Main Thread)
[org.fly.cor.int.dat.bas.BaseDatabaseType] (Quarkus Main Thread) Database: jdbc:postgresql://localhost:54321/default (PostgreSQL 14.2)
[org.fly.cor.int.sch.JdbcTableSchemaHistory] (Quarkus Main Thread) Creating Schema History table "public"."flyway_schema_history" ...
[org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Current version of schema "public": << Empty Schema >>
[org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Migrating schema "public" to version "0001 - schema"
[org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Migrating schema "public" with repeatable migration "dummy data"
[org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Successfully applied 2 migrations to schema "public", now at version v0001 (execution time 00:00.091s)

Of course, the app doesn’t do anything yet, as we haven’t created any REST endpoints, but it does run, and we do have a database, which is pretty cool. Let’s take a look now at how we can integrate jOOQ so we can more easily access this database.

jOOQ Setup

jOOQ, among other things, will allow us to write type-safe queries. For certain use cases, it’s a great alternative to (or supplement for!) things like JPA. To get started, we need to integrate the code generation tool into our build. We’re going to do this in a way that only generates the code only if it’s missing, and we’ll add the generated output to source control to make things faster in CI and other developers machines. Let’s start with the build.

Let’s define some properties, and the profile for the code gen:

  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
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

<properties>
    <jooq.outputdir>src/main/jooq</jooq.outputdir>
</properties>

<profiles>
    <profile>
        <id>codegen</id>
        <activation>
            <file>
                <missing>${jooq.outputdir}</missing>
            </file>
        </activation>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.codehaus.gmaven</groupId>
                    <artifactId>groovy-maven-plugin</artifactId>
                    <version>2.1.1</version>
                    <executions>
                        <execution>
                            <id>startdb</id>
                            <phase>generate-sources</phase>
                            <goals>
                                <goal>execute</goal>
                            </goals>
                            <configuration>
                                <source>
                                    db = new org.testcontainers.containers.PostgreSQLContainer("postgres:latest")
                                            .withUsername("${flyway.user}")
                                            .withDatabaseName("${flyway.user}")
                                            .withPassword("${flyway.password}")
                                    db.start()
                                    project.properties.setProperty('flyway.url', db.getJdbcUrl())
                                </source>
                            </configuration>
                        </execution>
                    </executions>
                    <dependencies>
                        <dependency>
                            <groupId>org.testcontainers</groupId>
                            <artifactId>postgresql</artifactId>
                            <version>${version.testcontainers}</version>
                        </dependency>
                    </dependencies>
                </plugin>
                <plugin>
                    <groupId>org.flywaydb</groupId>
                    <artifactId>flyway-maven-plugin</artifactId>
                    <version>${version.flyway}</version>
                    <executions>
                        <execution>
                            <phase>generate-sources</phase>
                            <goals>
                                <goal>migrate</goal>
                            </goals>
                        </execution>
                    </executions>
                    <dependencies>
                        <dependency>
                            <groupId>org.postgresql</groupId>
                            <artifactId>postgresql</artifactId>
                            <version>${version.pgsql-jdbc}</version>
                        </dependency>
                    </dependencies>
                    <configuration>
                        <locations>
                            <location>filesystem:src/main/resources/db/migration</location>
                        </locations>
                    </configuration>
                </plugin>
                <plugin>
                    <groupId>org.jooq</groupId>
                    <artifactId>jooq-codegen-maven</artifactId>
                    <version>${version.jooq}</version>
                    <executions>
                        <execution>
                            <phase>generate-sources</phase>
                            <goals>
                                <goal>generate</goal>
                            </goals>
                        </execution>
                    </executions>
                    <configuration>
                        <jdbc>
                            <url>${flyway.url}</url>
                            <user>${flyway.user}</user>
                            <password>${flyway.password}</password>
                            <schema>public</schema>
                        </jdbc>
                        <generator>
                            <database>
                                <name>org.jooq.meta.postgres.PostgresDatabase</name>
                                <includes>.*</includes>
                                <inputSchema>public</inputSchema>
                                <outputSchema>public</outputSchema>
                            </database>
                            <target>
                                <packageName>com.steeplesoft.fullexample.jooq</packageName>
                                <directory>${jooq.outputdir}</directory>
                            </target>
                        </generator>
                    </configuration>
                    <dependencies>
                        <dependency>
                            <groupId>org.postgresql</groupId>
                            <artifactId>postgresql</artifactId>
                            <version>${version.pgsql-jdbc}</version>
                        </dependency>
                    </dependencies>
                </plugin>
            </plugins>
        </build>
    </profile>
</profiles>

If you want more details on what all’s going on here, take a moment to (re)visit my post detailing it here. In short, though:

  • Using the Testcontainers API, we start a containerized database and grab the resulting URL

  • Using the Flyway Maven plugin, we run our migrations using the files defined above against this database

  • Finally, we point the jOOQ codegen Maven plugin at this newly populated database to generate the artifacts we’re after.

There is one more step: telling Maven where to find the generated classes so we can use them. To do that, we’ll use the build-helper-maven-plugin:

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

<plugins>
    <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>build-helper-maven-plugin</artifactId>
        <version>${version.build-helper}</version>
        <executions>
            <execution>
                <phase>generate-sources</phase>
                <goals>
                    <goal>add-source</goal>
                </goals>
                <configuration>
                    <sources>
                        <source>${jooq.outputdir}</source>
                    </sources>
                </configuration>
            </execution>
        </executions>
    </plugin>
</plugins>

Now Maven, as well as any IDE that bases its project information on the Maven POM, will be able to see these classes. We can git add src/main/jooq to add these to git, and, when there are changes to the schema, we simply execute something like this:

1
2
3

$ rm -rf src/main/jooq
$ mvn generate-sources
$ git add src/main/jooq

That’s probably a bit heavy-handed, but it should work. :) If you have a more elegant solution, again, find me on Twitter. :)

Writing the first endpoint

We now have a database instance started for us, we have the schema being created and dummy data add automatically, and we have the jOOQ classes we need to more safely access the data, so let’s write a very simple endpoint to show off our hard work.

First, we need the DSLContext, so let’s create a CDI Producer:

 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

public final class DslContextProducer {
    @Inject
    protected DataSource dataSource;

    @Produces
    @RequestScoped
    public DSLContext getDslContext() {
        try {
            return DSL.using(getConfiguration());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private Configuration getConfiguration() {
        return new DefaultConfiguration()
                .set(dataSource)
                .set(new Settings()
                        .withExecuteLogging(true)
                        .withRenderFormatted(true)
                        .withRenderCatalog(false)
                        .withRenderSchema(false)
                        .withMaxRows(Integer.MAX_VALUE)
                        .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED)
                        .withRenderNameCase(RenderNameCase.LOWER_IF_UNQUOTED)
                );
    }}

This is a pretty simple CDI producer:

  • We’re injecting the DataSource that Quarkus provides us. It will either be one for the Dev Services database in dev or test mode, or the "real" one in production mode.

  • We pass that DataSource to jOOQ via the Configuration object.

  • Bob’s your uncle.

The REST endpoint could look like this:

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

import static com.steeplesoft.fullexample.jooq.tables.Authors.AUTHORS;

import java.util.List;
import javax.inject.Inject;
import javax.ws.rs.GET;
import javax.ws.rs.Path;

import org.jooq.DSLContext;

@Path("/authors")
public class AuthorsResource {
    @Inject
    DSLContext context;

    @GET
    public List<AuthorDTO> getAuthors() {
        return context.select()
                .from(AUTHORS)
                .fetchInto(AuthorDTO.class);
    }
}

Note that it’s generally not a good idea to access the database in the REST layer, but I’ve done so here to keep things simple, and the usage of jOOQ here is very simple as well, so I won’t spend too much time on it.

With this overly simple REST endpoint in place, we’ve demonstrated Quarkus Dev Services, Flyway, and jOOQ. Let’s finish with testing this with Testcontainers.

Testing with Testcontainers

In a previous post, I discussed setting up a test using Testcontainers, but I did so using Kotlin. In another post, I did the same thing, but I booted the database from Maven using the groovy-maven-plugin. While those work, there’s an even better way: let Quarkus do it for us.

Way back up the page a bit, we saw how the quarkus-agroal extension will create a containerized database instance for when run in dev and test mode. As it turns out, we’re running in test mode here, so Quarkus will create and start the database for us and create the DataSource for us. This is injected normally as it was above, with no changes to the classes under test. All we have to do, then, is write the test.

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

import static io.restassured.RestAssured.when;

import io.quarkus.test.common.http.TestHTTPEndpoint;
import io.quarkus.test.junit.QuarkusTest;
import org.junit.jupiter.api.Test;

@QuarkusTest
@TestHTTPEndpoint(AuthorsResource.class)
public class AuthorsResourceTest {
    @Test
    public void testEndpoint() {
        when().get("/")
                .then()
                .log()
                .body()
                .statusCode(200);
    }
}

This is, admittedly, kind of a dumb test, but it does demonstrate the use the Quarkus test framework, which handles starting and stopping the server for us, allowing us to easily write unit tests against it. The test is full @Inject-able as well. If we wanted to inject the DataSource or the DSLContext, we would simply add the injection point like we would in production code:

1
2
3
4
5
6
7

@Inject
DSLContext context;

@Test
public void testContextInjection() {
    Assertions.assertNotNull(context);
}

Note that it does take a while for the tests to start running. That’s because Dev Services is spinning up the database instance, which is not a cheap operation. The start of our application is quite quick, however, once the DB is ready.

It’s also worth noting that, in our tests, we’re not actually interacting with Testcontainers directly. Quarkus is doing that on our behalf. If you edit the logging configuration to allow it, you’ll see output like this:

1
2
3
4
5
6
7

[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running com.steeplesoft.fullexample.AuthorsResourceTest
[org.jbo.threads] (main) JBoss Threads version 3.4.2.Final
[org.tes.doc.DockerClientProviderStrategy] (build-30) Loaded org.testcontainers.dockerclient.UnixSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
[com.git.doc.zer.sha.org.apa.hc.cli.htt.imp.cla.HttpRequestRetryExec] (ducttape-0) Recoverable I/O exception (java.io.IOException) caught when processing request to {}->unix://localhost:2375

The only place we use Testcontainers directly is in our Flyway/codegen configuration above, but it’s still there, and it’s still awesome. :)

Conclusion

In this project, we show a complete — if simple — integration of several important technologies which will allow use to write REST endpoints quickly and easily, and we need to worry (too much) about the local environment, whether it’s our machine, a coworkers, or even, say, a Github Action. If the machine has Java and Maven installed (and git and docker, of course), it’s simply a matter of cloning the project and issuing mvn clean install to build, test, and package the application.

In case you missed the link above, you can find this complete example here.

Hopefully, you’ll find this helpful. If you have problems, or suggestions on how to prove it, find me on Twitter and let’s talk.

Search

    Quotes

    Sample quote

    Quote source

    About

    My name is Jason Lee. I am a software developer living in the middle of Oklahoma. I’ve been a professional developer since 1997, using a variety of languages, including Java, Javascript, PHP, Python, Delphi, and even a bit of C#. I currently work for Red Hat on the WildFly/EAP team, where, among other things, I maintain integrations for some MicroProfile specs, OpenTelemetry, Micrometer, Jakarta Faces, and Bean Validation. (Full resume here. LinkedIn profile)

    I am the president of the Oklahoma City JUG, and an occasional speaker at the JUG and a variety of technical conferences.

    On the personal side, I’m active in my church, and enjoy bass guitar, running, fishing, and a variety of martial arts. I’m also married to a beautiful woman, and have two boys, who, thankfully, look like their mother.

    My Links

    Publications

    © Copyright Jason Lee, 2005-2023. All rights reserved.