In this note, I will analyze 2 points that I do not like about jOOQ.

jOOQ, Quarkus and reactivity

  • jOOQ supports reactivity via R2DBC
  • Quarkus supports reactivity via Vert.x

One of them has to give in. There is a special generator for c Vert.x and example – but it has not been updated for a year (slightly outdated). We need to try and figure out how it is.

A good question on this topic: https://stackoverflow.com/questions/72669626/using-jooq-with-reactive-sql-client-in-quarkus

In principle, many people think that non-active database queries are not a problem. I haven’t tested it myself, but I’ve been working for many years without it. It is clear that it will work, but I would really like reactivity.

jOOQ, Spring and Reactivity

It would seem: what can go wrong here?

It does not start automatically, the ConnectionFactory must be set manually:

@Configuration  
@EnableR2dbcRepositories  
class R2dbcConfig(  
  @Value("\${spring.r2dbc.url}") private val dsUrl: String,  
  @Value("\${spring.r2dbc.username}") private val dsUsername: String,  
  @Value("\${spring.r2dbc.password}") private val dsPassword: String,  
) : AbstractR2dbcConfiguration() {  
  
  override fun connectionFactory(): ConnectionFactory {  
    return ConnectionFactories.get(  
      ConnectionFactoryOptions  
        .parse(dsUrl)  
        .mutate()  
        .option(ConnectionFactoryOptions.USER, dsUsername)  
        .option(ConnectionFactoryOptions.PASSWORD, dsPassword)  
        .build(),  
      )  
  }  
}

Plus, it works reactively like this (via Flux.from):

fun getAllEmails(): Flow<String?> {
  return Flux.from(db.select(USERS.EMAIL).from(USERS))
    .asFlow()
    .map { it.value1() }
}

But not like that (via `FetchAsync’):

suspend fun getAll(): List<UsersRecord> {  
  return jdbcDb.fetchAsync(USERS).await()  
}

Because of this, you have to make 2 DSLContext:

@Primary  
@Bean  
fun dslContext(r2dbcConfig: R2dbcConfig): DSLContext {
  return DSL.using(r2dbcConfig.connectionFactory())
}
  
@Bean("jdbcDb")  
fun jdbcDslContext(dataSource: HikariDataSource): DSLContext {  
  return DSL.using(dataSource.connection)  
}

It is still unclear why FetchAsync does not work via R2DBC, but it can be written in this form (there is a little more code, but it works):

suspend fun byIdNullable(id: Long): UsersRecord? {  
  return Mono.from(  
    db.select(USERS).from(USERS).where(USERS.ID.eq(id)),  
  ).map { it.value1() }.awaitSingleOrNull()  
}

Since jOOQ is for complex cases, a little more code is quite acceptable.

jOOQ, Kotlin and null values

The developers of jOOQ claim that despite the not-null fields in the table, it is quite possible to write a query so that null returns (which is true).

  • only because of this, all fields are always nullable.
  • and in all cases, you need to manually, relatively speaking, write !!

As for me, it is still better to do it taking into account the range of the speakers, and specific cases can be considered separately. But there is no such thing right now - it’s inconvenient.

Conclusions

In this regard, in practice it makes sense to combine 2 access engines with Postgres: Spring Data Repositories (simple cases) & jOOQ (complex cases).

I would like to note separately that Flyway does not work with R2DBC and it needs JDBC settings (`spring.datasource’).