Engineering

Nevedomskii Andrei

Jul 13, 2023

Type safe data access with jOOQ and Kotlin

Many services at Wolt rely on jOOQ for data access. It is an ORM library that sticks close to SQL, allowing you to write fast and efficient queries while providing a nice, type safe API for that. Let's see how we can build a convenient and type safe data access layer with jOOQ and Kotlin.

The setup

In this article we will continue developing the monitoring service we started building before in «How to build a good API with Kotlin». We will start with declaring the DB schema, configuring jOOQ code generation and then will implement a repository to save and fetch monitoring events.

Also, to keep things simple for the time being, we will avoid using any dependency injection frameworks.

Database schema

Since jOOQ is a database first library, first thing we need to do is declare the schema we'd like to have. Usually, it's also a good idea to have some way to version your database changes. The 2 most popular tools to achieve that are Flyway and Liquibase. We will use Flyway here, as it's pretty straightforward to use. With it we can use a plain .sql file with a list of DDL statements as a migration script.

Our SQL database engine of choice at Wolt is PostgreSQL, so that's what we're going to use here.

The final model of ShmonitoringEvent we ended up with last time looks like this (see the full code on GitHub):


1
2
3
4
5
6
7
8
9
10
11
12
13
data class ShmonitoringEventRequest<out T : ServiceStatus>(
    val timestamp: LocalDateTime,
    val hostName: HostName,
    val serviceName: ServiceName,
    val owningTeamName: TeamName,
    val status: T,
)

data class ShmonitoringEventResponse<out T : ServiceStatus>(
    val base: ShmonitoringEventRequest<T>,
    val receivedTimestamp: LocalDateTime,
    val id: EventId
)

For the sake of simplicity, we will ignore status field for now, and focus on other fields.

According to the models above, here's what our table definition might look like:


1
2
3
4
5
6
7
8
9
CREATE TABLE events
(
    id                 UUID PRIMARY KEY,
    host_name          TEXT      NOT NULL,
    service_name       TEXT      NOT NULL,
    owning_team_name   TEXT      NOT NULL,
    "timestamp"        TIMESTAMP NOT NULL,
    received_timestamp TIMESTAMP NOT NULL
);

Nice and simple! All right, now let's generate jOOQ classes for that table. While we can generate UUID in the database using gen_random_uuid() method, I will not use it here to make the example a bit more universal.

jOOQ code generation

To generate jOOQ classes for our new table we can use the jOOQ Gradle plugin that I coincidentally maintain.

The setup is pretty easy there, here's what we need to add to the project's buildscript:


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
import dev.monosoul.jooq.RecommendedVersions.FLYWAY_VERSION
import dev.monosoul.jooq.RecommendedVersions.JOOQ_VERSION

plugins {
    // Add the plugin to the buildscript.
    id("dev.monosoul.jooq-docker") version "3.0.22"
}

dependencies {
    // Add runtime dependencies on jooq-kotlin and flyway-core modules.
    // We use the same jOOQ and Flyway versions as the ones used by the plugin.
    implementation("org.jooq:jooq-kotlin:$JOOQ_VERSION")
    implementation("org.flywaydb:flyway-core:$FLYWAY_VERSION")

    // Add PostgreSQL JDBC driver to use it both in runtime and for code generation.
    "org.postgresql:postgresql:42.6.0"
        .also(::implementation)
        .also(::jooqCodegen)
}

tasks {
    generateJooqClasses {
        // Configure the package name where generated jOOQ classes will be placed.
        basePackageName.set("dev.monosoul.shmonitoring.generated")
        usingJavaConfig {
            // Instruct jOOQ to generate classes using Kotlin generator.
            withName("org.jooq.codegen.KotlinGenerator")
            generate.apply {
                // Instruct jOOQ to generate record classes with non null field accessors, 
                // since all columns in our table are non-nullable anyway.
                withKotlinNotNullRecordAttributes(true)
            }
        }
    }
}

You can read more about Kotlin generator in jOOQ here.

Note that the plugin uses PostgreSQL 14.4 alpine docker image by default for jOOQ classes generation. If you want to use another image, you can customize it like that:

custom DB image
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
import dev.monosoul.jooq.RecommendedVersions.FLYWAY_VERSION
import dev.monosoul.jooq.RecommendedVersions.JOOQ_VERSION

plugins {
    id("dev.monosoul.jooq-docker") version "3.0.22"
}

dependencies {
    implementation("org.jooq:jooq-kotlin:$JOOQ_VERSION")
    implementation("org.flywaydb:flyway-core:$FLYWAY_VERSION")

    "org.postgresql:postgresql:42.6.0"
        .also(::implementation)
        .also(::jooqCodegen)
}

tasks {
    generateJooqClasses {
        withContainer {
            image {
                name = "postgres:14.4-alpine"
            }
        }
        basePackageName.set("dev.monosoul.shmonitoring.generated")
        usingJavaConfig {
            withName("org.jooq.codegen.KotlinGenerator")
            generate.apply {
                withKotlinNotNullRecordAttributes(true)
            }
        }
    }
}

After doing that we can generate jOOQ classes with generateJooqClasses Gradle task, like this:

1./gradlew generateJooqClasses

Now if we check the generated EventsRecord class, here's what we'll see (note that I omitted some parts here):


1
2
3
4
5
6
7
8
9
10
11
12
class EventsRecord : UpdatableRecordImpl<EventsRecord>(Events.EVENTS), Record6<UUID?, String?, String?, String?, LocalDateTime?, LocalDateTime?> {

    var id: UUID
    var hostName: String
    var serviceName: String
    var owningTeamName: String
    var timestamp: LocalDateTime
    var receivedTimestamp: LocalDateTime

   ...

}

Implementing repository

Now let's implement a repository with a single method to save an instance of ShmonitoringEventRequest to the database:


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
import dev.monosoul.shmonitoring.generated.tables.records.EventsRecord
import dev.monosoul.shmonitoring.generated.tables.references.EVENTS
import org.jooq.DSLContext
import java.time.Clock
import java.time.LocalDateTime
import java.util.UUID

class EventsRepository(
    private val db: DSLContext,
    private val clock: Clock = Clock.systemUTC(),
    private val generateId: () -> UUID = UUID::randomUUID,
) {
    fun save(event: ShmonitoringEventRequest<ServiceStatus>) {
        db.insertInto(EVENTS)
            .set(event.toRecord())
            .execute()
    }

    private fun ShmonitoringEventRequest<*>.toRecord() = EventsRecord(
        generateId(),
        hostName.value, // String value
        serviceName.value, // String value
        owningTeamName.value, // String value
        timestamp,
        LocalDateTime.now(clock),
    )
}

This repository is nice and simple. It has 3 dependencies:

jOOQ's DSLContext - the main entry point into running queries.
java.time.Clock instance to make testing easier (so that we can pass a fixed clock or a mock in the tests and easily assert the LocalDateTime instance generated on the line 25).
A function to generate an ID, mostly for the same reason as with the clock instance.

Now, if we look at the lines 21-23 you might notice an issue there similar to the one we discussed in «How to build a good API with Kotlin» article: since EventsRecord class takes multiple String arguments, there is an ambiguity in what exactly those strings represent. It is too easy to confuse the values there.

Of course we can add arguments names there, but that's not the most error prone solution. We can do better!

Harnessing the power of jOOQ

With jOOQ you can define your own data types to use in the generated classes and map them using converters and forced data types. Here's the best way to do that (IMHO) in Kotlin.

Adding converters

Converters in jOOQ provide a way to convert between "raw" types (or database column types, typically primitives like Int, Long, String etc.) and "forced" types (any other type you'd like to map a column type to).


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
import org.jooq.Converter
import kotlin.reflect.KClass

object JooqConverters {

    // We build a map of forced types to converters using Kotlin's builder function.
    private val classToConverter = buildMap {
        converterOf(::EventId, EventId::value)
        converterOf(::HostName, HostName::value)
        converterOf(::ServiceName, ServiceName::value)
        converterOf(::TeamName, TeamName::value)
    }

    // Since the map contains converters for many types and it has converters there with erased types (Converter<*, *>),
    // we need to add a method where we will cast the converter we're getting to the type we need.
    // This method does just that.
    @Suppress("UNCHECKED_CAST")
    fun <From, To> get(type: KClass<*>) = classToConverter[type] as Converter<From, To>

    // Inline method with reified types to simplify calls to get a converter instance.
    inline fun <From, reified To> get() = get<From, To>(To::class)

    // Extension function on a map of String to jOOQ's Converter.
    private inline fun <reified From, reified To> ConvertersMap.converterOf(
        // Function to convert an instance of the "raw" class to an instance of the type we want to force.
        // In our case we will just pass a constructor reference there, like we do on line 8.
        noinline fromConverter: (From) -> To,
        // Function to convert an instance of the forced type to an instance of the "raw" class.
        // In our case we will pass a getter reference there, like we do on the line 8.
        noinline toConverter: (To) -> From,
    ) {
        // We create an instance of jOOQ's converter using the reified types and the converter functions.
        this[To::class] = Converter.of(
            From::class.java,
            To::class.java,
            { it?.let(fromConverter) }, // we also make sure to properly handle nullable values to avoid NPEs
            { it?.let(toConverter) },
        )
    }
}

// Type alias for mutable map of class to converter, this is to make subsequent declarations a bit shorter.
private typealias ConvertersMap = MutableMap<KClass<*>, Converter<*, *>>

To learn more about inline functions and reified types, check out this official Kotlin docs page.

Okay, we have the converters. Now what's next?

Declaring forced types

Now we need to declare the forced types in the jOOQ's codegen config. To do that we will need to adjust the codegen task configuration we added earlier:


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
import org.jooq.meta.jaxb.ForcedType

tasks.generateJooqClasses {
    basePackageName.set("dev.monosoul.shmonitoring.generated")
    usingJavaConfig {
        withName("org.jooq.codegen.KotlinGenerator")
        generate.apply {
            withKotlinNotNullRecordAttributes(true)
            database.apply {
                withForcedTypes(
                    ForcedType()
                        // the class to use as a forced type
                        .withUserType("dev.monosoul.shmonitoring.model.EventId")
                        // the column data type to match
                        .withIncludeTypes("uuid")
                        // how to match the column we'd like to apply the forced type to (it's a regular expression)
                        .withIncludeExpression(".*\\.events\\.id")
                        // the converter to map the type
                        .withConverter("dev.monosoul.shmonitoring.persistence.JooqConverters.get()"),
                    ForcedType()
                        .withUserType("dev.monosoul.shmonitoring.model.HostName")
                        .withIncludeTypes("text")
                        .withIncludeExpression(".*\\.events\\.host_name")
                        .withConverter("dev.monosoul.shmonitoring.persistence.JooqConverters.get()"),
                    ...
                    /**
                     * same repeated for ServiceName and TeamName
                     */
                )
            }
        }
    }
}

As you can see, all forced types use the same method to get a converter. This is thanks to the inline method with reified types we added before.

If we check the generated EventsRecord class now, here's what we'll see (note that I omitted some parts here):


1
2
3
4
5
6
7
8
9
10
11
12
class EventsRecord : UpdatableRecordImpl<EventsRecord>(Events.EVENTS), Record6<EventId?, HostName?, ServiceName?, TeamName?, LocalDateTime?, LocalDateTime?> {

    var id: EventId
    var hostName: HostName
    var serviceName: ServiceName
    var owningTeamName: TeamName
    var timestamp: LocalDateTime
    var receivedTimestamp: LocalDateTime

   ...

}

Our value classes are used there.

But that's quite a lengthy configuration. If there are many types we want to map, it would make our buildscript convoluted and hard to read and maintain.

Shorter declaration

One way to improve that situation is by declaring an inline function like that:


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
import org.jooq.meta.jaxb.ForcedType

tasks.generateJooqClasses {
    basePackageName.set("dev.monosoul.shmonitoring.generated")
    usingJavaConfig {
        withName("org.jooq.codegen.KotlinGenerator")
        generate.apply {
            withKotlinNotNullRecordAttributes(true)
            database.apply {
                fun forcedType(modelName: String, type: String, columnName: String) = ForcedType()
                    .withUserType("dev.monosoul.shmonitoring.model.$modelName")
                    .withIncludeTypes(type)
                    .withIncludeExpression(".*\\.events\\.$columnName")
                    .withConverter("dev.monosoul.shmonitoring.persistence.JooqConverters.get()")

                withForcedTypes(
                    forcedType("EventId", "uuid", "id"),
                    forcedType("HostName", "text", "host_name"),
                    forcedType("ServiceName", "text", "service_name"),
                    forcedType("TeamName", "text", "owning_team_name"),
                )
            }
        }
    }
}

It looks a bit better this way. But still it could make the buildscript look like spaghetti when there are many types we want to force. Here's another thing we can do.

Externalized codegen configuration

The plugin for generating jOOQ classes I mentioned before supports externalized codegen configuration in the form of XML. Yeah, yeah, I know what you think: "Hey, XML is old and ugly, and nobody likes it". But I firmly believe in using the right tool for the job.

The thing is, jOOQ actually uses XML for codegen configuration. The lambda you pass to withJavaConfig method actually uses java bindings provided by jOOQ for it's own XML configuration. So you kind of use XML even when you think you don't use it. Didn't see that coming, huh? 🙂

Let's see how the configuration would look like using XML:

jooq.xml
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
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.18.0.xsd">
    <generator>
        <name>org.jooq.codegen.KotlinGenerator</name>
        <generate>
            <kotlinNotNullRecordAttributes>true</kotlinNotNullRecordAttributes>
        </generate>
        <database>
            <schemata>
                <schema>
                    <inputSchema>public</inputSchema>
                    <outputSchemaToDefault>true</outputSchemaToDefault>
                </schema>
            </schemata>
            <forcedTypes>
                <forcedType>
                    <userType>dev.monosoul.shmonitoring.model.EventId</userType>
                    <includeTypes>uuid</includeTypes>
                    <includeExpression>.*\.events\.id</includeExpression>
                    <converter>dev.monosoul.shmonitoring.persistence.JooqConverters.get()</converter>
                </forcedType>
                <forcedType>
                    <userType>dev.monosoul.shmonitoring.model.HostName</userType>
                    <includeTypes>text</includeTypes>
                    <includeExpression>.*\.events\.host_name</includeExpression>
                    <converter>dev.monosoul.shmonitoring.persistence.JooqConverters.get()</converter>
                </forcedType>
                <forcedType>
                    <userType>dev.monosoul.shmonitoring.model.ServiceName</userType>
                    <includeTypes>text</includeTypes>
                    <includeExpression>.*\.events\.service_name</includeExpression>
                    <converter>dev.monosoul.shmonitoring.persistence.JooqConverters.get()</converter>
                </forcedType>
                <forcedType>
                    <userType>dev.monosoul.shmonitoring.model.TeamName</userType>
                    <includeTypes>text</includeTypes>
                    <includeExpression>.*\.events\.owning_team_name</includeExpression>
                    <converter>dev.monosoul.shmonitoring.persistence.JooqConverters.get()</converter>
                </forcedType>
            </forcedTypes>
        </database>
    </generator>
</configuration>

While generateJooqClasses task configuration will look like this:


1
2
3
4
tasks.generateJooqClasses {
    basePackageName.set("dev.monosoul.shmonitoring.generated")
    usingXmlConfig(project.layout.projectDirectory.file("src/main/resources/db/jooq.xml"))
}

Note that passing file path to usingXmlConfig method is optional as long as your config is located at src/main/resources/db/jooq.xml. If that's the case, then you can just invoke the method without any arguments.

Now you might argue that the XML config actually has more lines than what it was in the buildscript. But I think this way of configuration has a few advantages:

Less convoluted buildscript, it's easier to maintain.
The codegen config for your data access classes located near the migration scripts, and I believe this sort of localization makes more sense.

Moreover, notice how we specify the configuration schema on line 2, thanks to that IDEs like IntelliJ IDEA will provide you code suggestions and highlighting.

Persisting sealed classes

Previously I said we'll skip saving service status for the time being to keep things simple. Now it's time to make things complicated again! 😄

First of all, let's recall how the service status model looks like (note that I'm omitting the annotations here):


1
2
3
4
5
6
7
8
9
10
11
sealed class ServiceStatus {

    data class Up(
        val upTime: Duration,
        val numberOfProcesses: NumberOfProcesses,
    ) : ServiceStatus()

    data class Warning(val message: WarningMessage) : ServiceStatus()

    object Down : ServiceStatus()
}

So, what are the options we have if we want to persist that model?

One option could be to add nullable columns to the table. But that's going to be as bad as
having nullable fields in your models.
Another option is to have a separate table for each status type and a composite reference in the events table, where the reference will consist of status id and status type. But that would be quite painful to maintain and work with.
One more option is to use JSONB type in PostgresSQL. This way we can take advantage of the things we learned about serializing sealed classes before. It will be simple to maintain and simple to query. Let's try this option!

But first we'll need to add a new column to the events table:


1
2
3
4
5
6
7
8
9
10
CREATE TABLE events
(
    id                 UUID PRIMARY KEY,
    host_name          TEXT      NOT NULL,
    service_name       TEXT      NOT NULL,
    owning_team_name   TEXT      NOT NULL,
    "timestamp"        TIMESTAMP NOT NULL,
    received_timestamp TIMESTAMP NOT NULL,
    service_status     JSONB     NOT NULL
);

On the 9th line we added a new column of JSONB type.

Adding a converter

Next we need to add a new converter to our JooqConverters object:


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
import com.fasterxml.jackson.databind.SerializationFeature.WRITE_DATES_AS_TIMESTAMPS
import com.fasterxml.jackson.databind.SerializationFeature.WRITE_DURATIONS_AS_TIMESTAMPS
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule
import com.fasterxml.jackson.module.kotlin.jsonMapper
import com.fasterxml.jackson.module.kotlin.kotlinModule
import com.fasterxml.jackson.module.kotlin.readValue
import org.jooq.Converter
import org.jooq.JSONB
import kotlin.reflect.KClass

object JooqConverters {

    // Jackson object mapper instance we will use for (de)serialization
    private val JACKSON_OBJECT_MAPPER = jsonMapper {
        addModule(kotlinModule())
        addModule(JavaTimeModule())
        disable(WRITE_DURATIONS_AS_TIMESTAMPS)
        disable(WRITE_DATES_AS_TIMESTAMPS)
    }

    private val classToConverter = buildMap {
        converterOf(::EventId, EventId::value)
        converterOf(::HostName, HostName::value)
        converterOf(::ServiceName, ServiceName::value)
        converterOf(::TeamName, TeamName::value)
        converterOfJsonbTo<ServiceStatus>() // conveter for ServiceStatus class
    }

    // Function to add a jOOQ converter for the type using the object mapper we added above.
    private inline fun <reified To> ConvertersMap.converterOfJsonbTo() =
        converterOf<JSONB, To>(
            fromConverter = { JACKSON_OBJECT_MAPPER.readValue(it.data()) },
            toConverter = { JSONB.valueOf(JACKSON_OBJECT_MAPPER.writeValueAsString(it)) },
        )

    @Suppress("UNCHECKED_CAST")
    fun <From, To> get(type: KClass<*>) = classToConverter[type] as Converter<From, To>

    inline fun <From, reified To> get() = get<From, To>(To::class)

    private inline fun <reified From, reified To> ConvertersMap.converterOf(
        noinline fromConverter: (From) -> To,
        noinline toConverter: (To) -> From,
    ) {
        this[To::class] = Converter.of(
            From::class.java,
            To::class.java,
            { it?.let(fromConverter) },
            { it?.let(toConverter) },
        )
    }
}

private typealias ConvertersMap = MutableMap<KClass<*>, Converter<*, *>>

We also need to add that converter to the list of forced types in the codegen config (jooq.xml):


1
2
3
4
5
6
7
8
9
10
11
...
<forcedTypes>
    ...
    <forcedType>
        <userType>dev.monosoul.shmonitoring.model.ServiceStatus</userType>
        <includeTypes>jsonb</includeTypes>
        <includeExpression>.*\.events\.service_status</includeExpression>
        <converter>dev.monosoul.shmonitoring.persistence.JooqConverters.get()</converter>
    </forcedType>
</forcedTypes>
...

Now if we run generateJooqClasses Gradle task again and check generated EventsRecord class, we'll see it has a new field:


1
2
3
4
5
6
7
8
9
10
11
12
13
class EventsRecord : UpdatableRecordImpl<EventsRecord>(Events.EVENTS), Record7<EventId?, HostName?, ServiceName?, TeamName?, LocalDateTime?, LocalDateTime?, ServiceStatus?> {

    var id: EventId
    var hostName: HostName
    var serviceName: ServiceName
    var owningTeamName: TeamName
    var timestamp: LocalDateTime
    var receivedTimestamp: LocalDateTime
    var serviceStatus: ServiceStatus

   ...

}

Voila! Now we have a hassle-free way to store our polymorphic service status in PostgresSQL, with all (de)serialization handled by jOOQ.

Updating repository implementation

Now that we have all the changes necessary to persist the data in a type safe manner, it's time to update the repository implementation we have:


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
import dev.monosoul.shmonitoring.generated.tables.records.EventsRecord
import dev.monosoul.shmonitoring.generated.tables.references.EVENTS
import org.jooq.DSLContext
import java.time.Clock
import java.time.LocalDateTime
import java.util.UUID

class EventsRepository(
    private val db: DSLContext,
    private val clock: Clock = Clock.systemUTC(),
    // the function to generate ID now returns an instance of EventId
    private val generateId: () -> EventId = { EventId(UUID.randomUUID()) },
) {
    fun save(event: ShmonitoringEventRequest<ServiceStatus>) {
        db.insertInto(EVENTS)
            .set(event.toRecord())
            .execute()
    }

    private fun ShmonitoringEventRequest<*>.toRecord() = EventsRecord(
        generateId(),
        // we don't extract values from the value classes anymore when passing them to EventsRecord
        hostName,
        serviceName,
        owningTeamName,
        timestamp,
        LocalDateTime.now(clock),
        // we now also pass the service status as we can properly persist it
        status,
    )
}

Let's also implement a method to find events satisfying a given filter, similar to the one we have in the service implementation.

Fetching events

First, let's refresh our memory for what the filter model looks like:


1
2
3
4
5
data class ShmonitoringEventFilter(
    val hostName: HostName? = null,
    val serviceName: ServiceName? = null,
    val owningTeamName: TeamName? = null,
)

Pretty simple, right? And we would like our find method implementation to fetch all events matching all the values provided with such filter. Here's what it will look like:


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
import dev.monosoul.shmonitoring.generated.tables.records.EventsRecord
import dev.monosoul.shmonitoring.generated.tables.references.EVENTS
import org.jooq.DSLContext
import org.jooq.impl.DSL

class EventsRepository(
    private val db: DSLContext,
    ...
) {

    fun find(filter: ShmonitoringEventFilter): List<ShmonitoringEventResponse<ServiceStatus>> =
        db.selectFrom(EVENTS)
            // We use the extension functions declared below to build a where condition and fetch the records.
            .where(filter.toCondition())
            .fetch { it.toResponse() }

    // We declare an extension function on ShmonitoringEventFilter to build a condition out of it.
    // We build a list of non-null elements only, where the elements are equality conditions for host name, service name and team name columns. 
    // Filter fields having null values will produce null elements here, so they won't be added to the list of conditions.
    // Then we build a single condition out of that list, joining the elements using logical operator AND .
    private fun ShmonitoringEventFilter.toCondition() = DSL.and(
        listOfNotNull(
            hostName?.let(EVENTS.HOST_NAME::eq),
            serviceName?.let(EVENTS.SERVICE_NAME::eq),
            owningTeamName?.let(EVENTS.OWNING_TEAM_NAME::eq),
        )
    )

    // We declare another extension function, this time on EventsRecord  to map it to ShmonitoringEventResponse.
    // Nothing fancy here, just constructing an instance.
    private fun EventsRecord.toResponse() = ShmonitoringEventResponse(
        base = ShmonitoringEventRequest(timestamp, hostName, serviceName, owningTeamName, serviceStatus),
        receivedTimestamp = receivedTimestamp,
        id = id,
    )
    
    ...
}

What's cool about it, is that not only records are type safe now, but also the table classes generated by jOOQ. Which means, that all DSL calls are also type safe for us now!

So if I accidentally make a typo in the function where we build condition, I'll get a compilation error:

type safe data access with jooq and kotlin-compilation error

Great stuff!

Trying it out

At this point we have all the pieces together to try our repository. We can do that with the following snippet:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import java.time.Duration
import java.time.LocalDateTime
import java.time.temporal.ChronoUnit.MICROS


val request = ShmonitoringEventRequest(
    LocalDateTime.now().truncatedTo(MICROS),
    HostName("DeathStar1"),
    ServiceName("Laser-beam"),
    TeamName("Imperial troops"),
    ServiceStatus.Up(
        upTime = Duration.ofMillis(1000),
        numberOfProcesses = NumberOfProcesses(2),
    )
)

repository.save(request)

val response = repository.find(
    ShmonitoringEventFilter(hostName = request.hostName, serviceName = request.serviceName)
).first()

println(response)
println(response.base == request)

Notice how on the 7th line we truncate the time instance to microseconds. This is because JVM has higher time precision than PostgreSQL. Without it the call on the line number 24 might return false. That code will produce an output like this:


1
2
3
4
5
6
7
8
9
ShmonitoringEventResponse(
   base=ShmonitoringEventRequest(timestamp=2023-06-23T14:36:13.759662, 
hostName=HostName(value=DeathStar1), serviceName=ServiceName(value=Laser-beam), 
owningTeamName=TeamName(value=Imperial troops), status=Up(upTime=PT1S, 
numberOfProcesses=NumberOfProcesses(value=2))), 
   receivedTimestamp=2023-06-23T12:36:13.895766, 
   id=EventId(value=c8cb2058-e45f-4c8b-8738-bb50ebee4ba2)
)
true

So we were able to successfully save and fetch an event, while using type safe models and DSL jOOQ provides. Amazing!

Summary

jOOQ provides a great tooling for developers to build queries in a type safe and convenient manner. And with Kotlin it becomes even more convenient, thanks to inline functions with reified types, extensions functions, null safety and other things.

This is a first part of the article, where I covered how you can take advantage of the jOOQ Gradle plugin and forced types to access your data in a truly type safe manner. In the second part I will cover a few caveats to keep in mind when writing queries using forced types and how to query JSON columns with jOOQ, so stay tuned!

The final code is available here: https://github.com/monosoul/shmonitoring/tree/main/type-safe-with-jooq