Jooq: Support Kotlin coroutines as DSLContext extensions

[lukaseder]

It should be possible to add some Kotlin extensions directly in the jOOQ core library and ship them to users integrating with Kotlin, without affecting Java users. For example, we could support coroutines as DSLContext extensions or ResultQuery extensions

A proof of concept has been made here:
https://github.com/marshallpierce/jOOQ/commit/72b0b3b0127afc758094016e124b0f7d1da98cd4

As suggested on the user group:
https://groups.google.com/d/msg/jooq-user/NS5anAYmIgI/FlV3H6gvAwAJ

[marshallpierce]

I think this shouldn't even be too nasty from a dependency perspective -- simply mark the kotlin stdlib and coroutine dependencies as optional in maven parlance, and users who want to use them will already have those things on their classpath anyway.

[lukaseder]

We have a new module starting from jOOQ 3.14: jooq-kotlin (https://github.com/jOOQ/jOOQ/issues/6256). Let's see what can be done in there.

[lukaseder]

Looking into this now. It seems that the main purpose of the suggested approach is to replace our call to blocking(() -> { ... }), which invokes ForkJoinPool::managedBlock by Kotlin's suspend syntax. The content that is inside of the transactionResult method here: https://github.com/marshallpierce/jOOQ/commit/72b0b3b0127afc758094016e124b0f7d1da98cd4#diff-588eae5d26e55b16e058f61c3a1ce658 is the same as that of DefaultDSLContext.

At least, that seems to be it for these DSLContext.transaction() methods. But wouldn't the expectation here be for any database interaction to be "suspending", even without a transaction? Surely, leveraging this language feature for jOOQ's transaction API is better than nothing, but I'd say that a lot of people upvoting this issue would expect more.

[lukaseder]

I don't want to rush anything here (as with reactive support, too: #6298). There seems to be a significant investment, making all sorts of API suspend-enabled for Kotlin users, and I don't fully understand what this means yet.

[marshallpierce]

My initial desire was to be able to call suspend functions inside a closure passed to transactionResult (and ideally propagate coroutine context from the caller as well).

Use case:

• in a transaction, query some data
• call some other thing (which suspends) with that data
• update data with the response from the suspending call
• commit

I agree that probably any closure-flavored API like that would benefit from taking a suspend closure, transaction or not.

[lukaseder]

I agree that probably any closure-flavored API like that would benefit from taking a suspend closure, transaction or not.

Exactly. I think this is an all-or-nothing discussion. Supporting coroutines only in a few areas will not be enough. It's never ending, too. Because coroutines are not the same thing as asynchronous calls (e.g. using CompletionStage), which are again not the same thing as reactive Publisher types. For example, we should adapt DAO types to support the existing async/reactive APIs currently supported by jOOQ: https://github.com/jOOQ/jOOQ/issues/5916. Does that mean that we also need a SuspendingDAO, for Kotlin?

I personally have high hopes that Loom will clean this up for all JVM languages. These things shouldn't be solved on an API level. What's the take on Loom in the kotlin ecosystem?

[marshallpierce]

Re: Loom, I couldn't speak for the whole Kotlin community, but at least year's JCrete nobody seemed very optimistic. Even if it was shipping today, I at least would still prefer coroutines: APIs like coroutine context and scoped concurrency make them (to my mind) a better eventual goal than "threads, but more lightweight". We use coroutine context to propagate things like "current request id" across fanned out coroutines, something that requires clumsy and error prone manual work to do with threads and threadlocals.

[lukaseder]

Re: Loom, I couldn't speak for the whole Kotlin community, but at least year's JCrete nobody seemed very optimistic

I personally think that Loom lacks the marketing and hype that other asynchronous and/or reactive APIs received for reasons I still don't fully understand.

Even if it was shipping today, I at least would still prefer coroutines: APIs like coroutine context and scoped concurrency make them (to my mind) a better eventual goal than "threads, but more lightweight"

I think this part of Loom is the most widely misunderstood. Loom is much more than "just" about lightweight threads. What it means specifically for the entire ecosystem and adoption is still ... looming, because it develops much slower than API based approaches, and there are no "success stories" yet. (See above comment on marketing and hype).

However, the idea that asynchronous models, reactive models, and suspending models would all be possible on existing APIs (at least, that's the promise of Loom) seems very interesting, because the ultimate show-stopper for any other approach that is the currently blocking JDBC API could be re-used, and with it, an entire ecosystem of proven technology. At least Oracle has been betting on this, and thus has abandoned ADBA.

What I meant by my previous doubts is that by using any of the other API alternatives, jOOQ has kept offering a wishy-washy solution to integrating jOOQ in the programming models (e.g. async via CompletionStage or reactive via Publisher) without being able to truly leverage the model on the implementation level. At the same time, 95% of the jOOQ API is blocking while only a few API elements pretend to be non-blocking. This is just not a good solution for jOOQ. When maintaining jOOQ, this incompleteness of vision just to be able to superficially check some boxes has been quite the source of frustration.

Add to that yet another approach, which are Kotlin co-routines, which attempt to solve the problems again on an API level, on a per-function basis. What is being requested here is again to be able to check some box on a superficial level, because in order to truly offer Kotlin coroutine support, I'm afraid the entirety of jOOQ's API would have to be offered in a variety of "red functions" flavours (see: https://journal.stuffwithstuff.com/2015/02/01/what-color-is-your-function)

Having said so, if a user is happy accepting suspending Kotlin functions to fulfil programming model needs, without really getting the benefit throughout the stack, including within the database, then it would be much easier to just wrap jOOQ API calls (the query building) in some suspend services. If this is really "just" about the transaction API in jOOQ, then that is a part that can be very easily replaced by a few lines of custom code, or a Kotlin native transaction management library that leverages suspend.

We use coroutine context to propagate things like "current request id" across fanned out coroutines, something that requires clumsy and error prone manual work to do with threads and threadlocals.

I'm not sure what passing around context has to do with the thread model, though? For example, with the existing jOOQ transaction API, it would be possible to implement suspension in a jOOQ TransactionProvider, and pass on any context from the surrounding DSLContext to the TransactionCallable's Configuration object:

// Using Java syntax...

// Here, we're in some thread/fiber/execution context 1
ctx1.transactionResult(ctx2 -> {
  // Here, we're transparently in some thread/fiber/execution context 2
  ctx2.dsl().select(...).fetch();
});

We're not offering this out of the box yet, but again, it should be possible to achieve using SPIs. It's not easy to do (it's probably easier to use a third party library for suspending transaction support), but I'm trying to argue against the various API based solutions to these asynchronous execution models, which ripple through the entire stack, making existing tools like jOOQ or JDBC hard to use.

[lukaseder]

Related topic: https://github.com/jOOQ/jOOQ/issues/6298

[lukaseder]

For the record, great slide from a great talk

https://www.youtube.com/watch?v=23HjZBOIshY

[marshallpierce]

I think we might be talking past each other... I'm all in favor of Oracle (and Ron, who is very capable) pressing forward on Loom. It's just not all that relevant to me right now, even if it may end up being the bees knees for when it ships.

In projects I touch, I don't have a need for jOOQ (or JDBC) to be all nonblocking under the hood because with typical small DB connection pool pool sizes, it's no burden to maintain a similarly small thread pool to handle blocking i/o on those connections. While it would be nice, I suppose, to avoid that, it's not really an issue in the same way that HTTP clients and servers really benefit from multiplexing many connections onto a few threads: it is more typical to have 1000 (or 100,000) HTTP connections slowly trickling data back and forth than it is to do the same with a database, at least in my experience.

I don't follow how using suspending code in a TransactionalCallable could be addressed via SPI since TransactionalCallable's run() isn't suspending... what am I missing?

[lukaseder]

It's just not all that relevant to me right now

I understand you'd like a quick win. My point is, jOOQ likely won't offer one for the reasons I've mentioned.

Here's an example of an extremely simple, alternative transaction manager that can be used with jOOQ: https://github.com/witoldsz/ultm

Here's another example of someone doing similar things for jOOQ/ZIO:
https://github.com/Shastick/zio-jooq

If the problem this feature request is trying to solve is isolated out of jOOQ and into a dedicated library like the above, then it would be really simple to implement. Adding support for Kotlin suspend functions inside of jOOQ is much more design work, which I find, at this point premature. I'm not saying it can't be done, and I'm not saying it shouldn't be done. I'm saying that these things should be addressed (if at all) in a much broader context of addressing alternative execution models in general, which include reactive models (#6298).

While this seems like a simple thing for you (and it probably is), it is absolutely not for jOOQ.

I don't follow how using suspending code in a TransactionalCallable could be addressed via SPI since TransactionalCallable's run() isn't suspending... what am I missing?

I was not discussing a Kotlin specific way of doing this. I was trying to hint at how this could be achieved with a Loom style suspension...

[marshallpierce]

I totally understand your reluctance to sprinkle suspend all over jOOQ. :) I think zio-jooq is basically the equivalent of withContext(Dispatcher.IO) { ... } for Kotlin coroutines (koroutines?), so that much at least is done. I suppose that brings us back to copy and pasting transactionResult as a Kotlin extension function that takes a suspend function type? 🤷‍♂️

[PedroSena]

I totally understand your reluctance to sprinkle suspend all over jOOQ. :) I think zio-jooq is basically the equivalent of withContext(Dispatcher.IO) { ... } for Kotlin coroutines (koroutines?), so that much at least is done. I suppose that brings us back to copy and pasting transactionResult as a Kotlin extension function that takes a suspend function type? man_shrugging

@marshallpierce Do you mind sharing the workaround you are using ? I'm on the same situation and not sure I fully understood what you are suggesting

[mschorsch]

@PedroSena The workaround is as follows:

1. In your projekt create a Kotlin file DSLContextExtensions.kt in a package named org.jooq.impl . The package is important because some functionality can only accessed from the package org.jooq.impl.
2. Paste the fowllowing code from https://github.com/marshallpierce/jOOQ/commit/72b0b3b0127afc758094016e124b0f7d1da98cd4#diff-588eae5d26e55b16e058f61c3a1ce658 in DSLContextExtensions :

package org.jooq.impl

import org.jooq.Configuration
import org.jooq.DSLContext
import org.jooq.exception.DataAccessException

suspend fun <T> DSLContext.transactionResult(block: suspend (Configuration) -> T): T =
        transactionResult(block, configuration())

private suspend fun <T> transactionResult(block: suspend (Configuration) -> T, config: Configuration): T {
    val ctx = DefaultTransactionContext(config.derive())
    val provider = ctx.configuration().transactionProvider()
    val listeners = TransactionListeners(ctx.configuration())
    var committed = false

    val result: T

    try {
        try {
            listeners.beginStart(ctx)
            provider.begin(ctx)
        } finally {
            listeners.beginEnd(ctx)
        }

        result = block(ctx.configuration())

        try {
            listeners.commitStart(ctx)
            provider.commit(ctx)
            committed = true
        } finally {
            listeners.commitEnd(ctx)
        }
    }
    // [#6608] [#7167] Errors are no longer handled differently
    catch (cause: Throwable) {

        // [#8413] Avoid rollback logic if commit was successful (exception in commitEnd())
        if (!committed) {
            if (cause is Exception)
                ctx.cause(cause)
            else
                ctx.causeThrowable(cause)

            listeners.rollbackStart(ctx)
            try {
                provider.rollback(ctx)
            }

            // [#3718] Use reflection to support also JDBC 4.0
            catch (suppress: Exception) {

                cause.addSuppressed(suppress)

            }
            listeners.rollbackEnd(ctx)
        }

        // [#6608] [#7167] Errors are no longer handled differently
        when (cause) {
            is RuntimeException, is Error -> throw cause
            else -> throw DataAccessException(
                    if (committed) "Exception after commit" else "Rollback caused",
                    cause
            )
        }
    }

    return result
}

1. Now you can use transactionResult in your code!

[PedroSena]

Perfect, thanks @mschorsch