Nissan jooq

This is a short story about how I set up the jooq generator with gradle and postgis. By and large, it wasn't really necessary in my PAT project, but I wanted to repeat the stack I work with, but from scratch.

To begin with, I moved everything related to the database to the library. Here are the gradle file, migrations and generated bug classes. The gradle file looks something like this.

plugins {
    val kotlinVersion = "1.9.20"
    id("java")
    id("org.flywaydb.flyway") version "9.10.2"
    id("nu.studer.jooq") version ("6.0.1")
    kotlin("jvm") version kotlinVersion
}

group = "ru.mapgramm"
version = "0.0.1-SNAPSHOT"

repositories {
    mavenCentral()
}

dependencies {
    jooqGenerator("org.postgresql:postgresql")
    implementation("org.jooq:jooq")
    implementation("org.postgis:postgis-jdbc")
    compileOnly("org.postgresql:postgresql")
    jooqGenerator("org.postgresql:postgresql")
}

At first I decided to just roll out migrations locally and run the generator on a locally running database.

Actually, let's start setting up the generator. I find the setting with a quick search on the Internet, adapt it to Kotlin Gradle and launch it.

jooq {
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)
    configurations {
        create("main") {
            jooqConfiguration.apply {
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = <jdbc_url>
                    user = <username>
                    password = <password>
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = false
                        isFluentSetters = false
                        isJavaBeansGettersAndSetters = false
                    }
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                    }
                    target.apply {
                        packageName = "ru.keykeeper.core.api.jooq.generated"
                        directory = "$projectDir/src/main/java/"
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

As a result, we get a huge number of classes. They appeared because of the postgis plugin and it is not yet clear how to get rid of them. Although they do not really interfere.

Let's try to write some kind of query. For example, here is a query to get all groups with a join to posts and a filter by zoom

fun findAllByZoom(zoom: Int, dsl: DSLContext = jooq): List<PostGroupDto> {
    val postGroupRecords = dsl
        .select(*POST_GROUP_VIEW.fields())
        .select(*POST.fields())
        .from(POST_GROUP_VIEW)
        .join(POST_TO_GROUP).on(POST_TO_GROUP.GROUP_ID.eq(POST_GROUP_VIEW.ID))
        .join(POST).on(POST_TO_GROUP.POST_ID.eq(POST.ID))
        .where(POST_GROUP_VIEW.ZOOM.eq(zoom))
        .fetchGroups(
            { r -> r.into(PostGroupViewRecord::class.java) },
            { r -> r.into(PostRecord::class.java) },
        )

    return postGroupRecords.entries.map { entry ->
        entry.key.toDto(entry.value.map { it.toDto() })
    }
}

In general it works and the fact that these are all Java classes does not interfere at all, but there are some other problems.

Inserting rows is not allowed here.

I can't save date. And jsonb. And geometry. And in general, something is wrong with saving, if it is not a string or a number. It looks like this:

org.postgresql.util.PSQLException: ERROR: column "payload" is of type jsonb but expression is of type character varying

And this is what I spend an indecent amount of time on. During this time, I found how to generate Kotlin classes and how to generate them normally during assembly, but more on that later. And the solution to the problem was a simple Soviet…

@Bean
fun configuration(connectionProvider: DataSourceConnectionProvider): DefaultConfiguration {
    val jooqConfiguration = DefaultConfiguration()
    jooqConfiguration.set(connectionProvider)
 -> jooqConfiguration.setSQLDialect(SQLDialect.POSTGRES);
    return jooqConfiguration
}

… setSQLDialect(SQLDialect.POSTGRES)as it turns out, you shouldn't forget about the settings of the beetle itself either.

@Configuration
class JooqConfiguration {
    @Bean(value = ["jdbcDSLContext"])
    fun jdbcDSLContext(configuration: DefaultConfiguration): DSLContext {
        return DefaultDSLContext(configuration)
    }

    @Bean
    fun connectionProvider(dataSource: DataSource): DataSourceConnectionProvider {
        return DataSourceConnectionProvider(
            TransactionAwareDataSourceProxy(dataSource),
        )
    }

    @Bean
    fun configuration(connectionProvider: DataSourceConnectionProvider): DefaultConfiguration {
        val jooqConfiguration = DefaultConfiguration()
        jooqConfiguration.set(connectionProvider)
        jooqConfiguration.setSQLDialect(SQLDialect.POSTGRES);
        return jooqConfiguration
    }
}

Generate correctly

While I was looking for a solution to the problem above, I found a way to successfully generate beetle classes with flyway and testcontainers. I imagined it roughly like this, but someone else came up with it for me, so let's use it.

First, I made a class for myself with a postgis container, since I couldn't find one in the test containers. Plus, I configured the flyway migration to this container. Plus, I set these properties in the generator.

abstract class PostgresService : BuildService<BuildServiceParameters.None>, AutoCloseable {
    private var image = org.testcontainers.utility.DockerImageName.parse("postgis/postgis:12-3.0")
        .asCompatibleSubstituteFor("postgres")
    private val container = org.testcontainers.containers.PostgreSQLContainer(image)
    init { container.start() }
    override fun close() = container.stop()
    fun getContainer() = container
}

val dbContainerProvider: Provider<PostgresService> = project.gradle.sharedServices
    .registerIfAbsent("postgres", PostgresService::class) {}

flyway {
    val dbContainer = dbContainerProvider.get().getContainer()
    url = dbContainer.jdbcUrl
    user = dbContainer.username
    password = dbContainer.password
    locations = arrayOf("classpath:db/migration")
    sqlMigrationPrefix = "V"
}

jooq {
  val dbContainer = dbContainerProvider.get().getContainer()
  ...
              jdbc.apply {
                  driver = "org.postgresql.Driver"
                  url = dbContainer.jdbcUrl
                  user = dbContainer.username
                  password = dbContainer.password
              }
  ...
}

And here you need to set the dependency of the task for generating classes on the database migration.

val generateJooq by project.tasks
generateJooq.dependsOn("flywayMigrate")

Very important title

generator.apply {
  name = "org.jooq.codegen.KotlinGenerator"
}

Hidden text

To generate Kotlin classes you need to change the value name generator and restart generation. It's obvious in general. But I said I'd show you how I did it.

Since the paragraph is short, here's a meme

Postgis problem

There is one last problem left, I can't write queries with postgis functions using jooqDsl, but I need to!

For the jooq generator, you can configure bindings so that the specified kotlin types are used for the types in the database. For example, here are the settings for jsonb and date-time

...
database.apply {
  ...
  withForcedTypes(
      org.jooq.meta.jaxb.ForcedType()
          .withName(org.jooq.impl.SQLDataType.INSTANT.typeName)
          .withIncludeTypes("(?i:TIMESTAMP\\ (WITH|WITHOUT)\\ TIME\\ ZONE)"),
      org.jooq.meta.jaxb.ForcedType()
          .withName(org.jooq.impl.SQLDataType.JSONB.typeName)
          .withIncludeTypes("jsonb"),
  )
}
...

So, you can write such bindings yourself for your types. But it turns out to be quite large and I would not like to do it for all postgis classes. And as it turned out, there is a project where this was done for me.

implementation("io.github.oshai:jooq-postgis-spatial:1.0")

Now we add binding to the generator settings:

...
database.apply {
    ...
    withCustomTypes(
        org.jooq.meta.jaxb.CustomType()
            .withName("Geometry")
            .withBinding("net.dmitry.jooq.postgis.spatial.binding.JTSGeometryBinding")
            .withType("com.vividsolutions.jts.geom.Geometry")
    )
    withForcedTypes(
    ...
        org.jooq.meta.jaxb.ForcedType()
            .withName("Geometry")
            .withIncludeTypes("(geometry|GEOMETRY)"),
    )
}
...

And here's the strange thing with them, the function withCustomTypes – is outdated and should only be used withForcedTypes. But when I set everything up again, it stopped working. I am happy with the above option, but if you know what's wrong with it, I'd be glad to hear some advice)

withForcedTypes(
  ...
  org.jooq.meta.jaxb.ForcedType()
      .withName("Geometry")
      .withTypes("com.vividsolutions.jts.geom.Geometry")
      .withBinding("net.dmitry.jooq.postgis.spatial.binding.JTSGeometryBinding")
      .withIncludeTypes("(geometry|GEOMETRY)")
)

Well, I'll immediately try to make a request with new functions

dsl
.selectFrom(POST_GROUP)
.where(
    POST_GROUP.ZOOM.eq(zoom)
        .and(
            stContains(
                geom1 = JTS.getDefaultGeomFactory().createPolygon(
                    arrayOf(
                        Coordinate(bounds.upLeft.x, bounds.upLeft.y),
                        Coordinate(bounds.upRight.x, bounds.upRight.y),
                        Coordinate(bounds.downRight.x, bounds.downRight.y),
                        Coordinate(bounds.downLeft.x, bounds.downLeft.y),
                        Coordinate(bounds.upLeft.x, bounds.upLeft.y),
                    )
                ),
                geom2 = POST_GROUP.POINT,
            )
    )
)
.fetch(mapper)

Summary

In general, everything worked out, there is a generator, there is a DSL and all this is successfully used. Although for PET projects the difference is small, the beetle is much more convenient for me to use.
By the way, here it is applicationfor whom I did all this
Subscribe to cartI sometimes post something on the topic there.

That's all.