Search code examples
jdbcquarkusvert.xjooq

Using JOOQ with Reactive SQL Client in Quarkus

I want to use the JOOQ DSL in Quarkus to build my SQL (and hopefully execute them).
Therefore I added the following Quarkus JOOQ extension.

Since I want to use the reactive PG SQL Client in my project, I'm asking myself if e.g. the fetch() method of JOOQ will block the thread ? Is it compatible with the reactive vertx client under the hood or does it use a blocking one ? Looks like the latter one since it doesn't return a future or anything like that.

In that case I propably should only use JOOQ for creating the SQL string.

Solution

  • Which parts of the jOOQ API can be used reactively

    jOOQ's ResultQuery<R> extends Publisher<R>, so you can just place a jOOQ query in any reactive stream implementation. There are 3 main Publisher subtypes in jOOQ:

    And starting with jOOQ 3.17, there will also be a way to create transactional Publisher types.

    With this in mind, in the reactive world, you will never need to call any of the traditional jOOQ blocking execution methods. You'll always implicitly execute jOOQ queries via some reactive streams integration.

    Avoiding calls to blocking API

    Starting with jOOQ 3.17, all the blocking API (e.g. ResultQuery.fetch()) will be annotated as org.jetbrains.annotations.Blocking, so you get IDE support to warn you that you're about to do something that might not make sense in your non-blocking context.

    Backing implementation

    For any of this to work, you need to provide jOOQ with an R2DBC connection. R2DBC is an SPI that enables interoperability between client libraries like jOOQ and R2DBC drivers, like r2dbc-postgres. Just like JDBC, it works as an SPI, not strictly an API. Besides, it integrates also directly with the reactive streams SPI, which has been integrated in the JDK 9 via the Flow API.

    There might be future work to support alternative non-blocking drivers in the future, however R2DBC seemed to be the most interoperable choice at the time the reactive support was added, and I do hope that the Vert.x and R2DBC teams will find ways to cooperate more tightly in the future. The Vert.x SQL client, for example, does not implement the reactive streams SPI directly, Red Hat does not seem too interested (yet) in moving forward with this issue here: https://github.com/eclipse-vertx/vertx-sql-client/issues/249

    So, for now, this means that you have to either:

    • Use jOOQ with R2DBC, which is what jOOQ supports (not sure if Quarkus will support R2DBC, though I don't see any reason why it shouldn't)
    • Use jOOQ to generate SQL only and run the SQL with Vert.x (you'll lose a lot of type safety and convenience, as well as access to advanced features like MULTISET, which relies on jOOQ executing your query)

    A side note on reactive execution

    Of course, it's always important to think about whether you really need to go reactive. In my personal experience, this is mostly a matter of programming style, not actual performance and/or load requirements. Sticking with the blocking paradigm and JDBC will greatly simplify your every day work, and I doubt you'll notice a measurable difference in production.