Jooq: Implement a DriverProvider SPI abstracting over JDBC to allow for using non-blocking, async drivers with jOOQ

[lukaseder]

Currently, jOOQ is strictly tied to JDBC throughout its internals and existing SPIs, like ExecuteListener, ConnectionProvider, and others.

But that's not a hard requirement. We could implement a new DriverProvider SPI, which allows for implementing all the driver binding glue code that is required by jOOQ, e.g.:

• A wrapper of a connection / session
• A wrapper for transactions
• A wrapper for prepared statements
• A wrapper for bind variable bindings
• A wrapper for result sets
• A wrapper for procedures and in / out parameters
• More...

Once this SPI is in place, we can support third party non-blocking drivers other than JDBC drivers e.g. for PostgreSQL and SQL Server, which can then make truly non-blocking calls through methods like ResultQuery.fetchAsync(). These include:

• r2dbc
• vert.x
• direct driver support, such as e.g. https://github.com/alaisi/postgres-async-driver

[mkurz]

This would be really great! I guess this is related to #7605?

Another pointer here would be jasync-sql/jasync-sql (which is a port of the deprecated, Scala based mauricio/postgresql-async to Kotlin).

[lukaseder]

@mkurz Thanks for the pointer. Yes that other issue is a duplicate of this one.

I'm really going to focus on supporting ADBA and/or R2DBC, as these will quite likely involve into standard APIs, unlike these many PostgreSQL-specific proprietary APIs

[davinkevin]

I think this issue will be my favorite of all Github ! I dream of being able to use JOOQ in a reactor env without relying on JDBC and Thread Pool.

[lukaseder]

@davinkevin Of course, you can already do that today, writing just a few lines of glue code and calling Query.getSQL() and Query.getBindValues()...

[lukaseder]

Work on ADBA has been stopped: https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2019-September/000529.html

[adam-arold]

Is there a plan for an R2DBC variant for jOOQ? I'd love to use jOOQ on my current project but this limitation is a bottleneck.

[lukaseder]

@adam-arold : Yes there is a plan. But as always, there is no guarantee as to when this will be tackled. You can already use jOOQ today with R2DBC by extracting jOOQ-generated SQL using Query.getSQL().

[aguibert]

@lukaseder will this also be able to cover the Vertx Reactive SQL Client?
https://github.com/eclipse-vertx/vertx-sql-client

It is basically an alternative to R2DBC, and will be the reactive DB access API in Vertx (obviously) but also Quarkus. Almost all of the big DBs have drivers for Vertx reactive: PostgreSQL, MySQL, Microsoft SQL Server, and IBM DB2.

[adam-arold]

This would be great. I'm stuck with something else right now because I can't go reactive with jOOQ. :(

[lukaseder]

@aguibert will this also be able to cover the Vertx Reactive SQL Client?

Would definitely be reasonable to think of it. Also, do look into this existing work: https://github.com/jklingsporn/vertx-jooq

@adam-arold This would be great. I'm stuck with something else right now because I can't go reactive with jOOQ. :(

Well, you aren't "stuck". You can extract the SQL and execute it elsewhere... This issue here is mostly about moving the plumbing from client code into jOOQ

[adam-arold]

I was doing the code generation as well. I don't know how I could do that with no driver.

[lukaseder]

I was doing the code generation as well. I don't know how I could do that with no driver.

I don't think I understand. Why do you need a reactive driver for code generation?

[adam-arold]

Because it connects to the database. Or are you suggesting that I should use 2 drivers? Do you have a code example for this moving the plumbing from client code into jOOQ?

[lukaseder]

Or are you suggesting that I should use 2 drivers?

I mean, the code generator works effortlessly, out of the box, via JDBC. Why make this hard artificially by expecting it to work with non-JDBC drivers to the extent of "being stuck"? It does not have anything to do with the runtime database interfacing model that you're trying to achieve via some asynchronous and / or reactive driver... If you're using Maven, that's just 5 additional lines of Maven configuration, and it works.

We're not ready for such third party drivers. And it is really not a simple thing to do in the runtime library. Adding support for this in the code generator is very low priority, because I see zero benefits from doing code generation in a reactive way.

Do you have a code example for this moving the plumbing from client code into jOOQ?

The "plumbing" or "boiler plate code" looks similar to what needs to be done to use jOOQ on a JPA EntityManager: https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-native

If you want to use vertx, someone has already implemented some integration facilities to make this easier: https://github.com/jklingsporn/vertx-jooq

I cannot provide you with an example for moving this logic into jOOQ. But I can outline it. Look at all the SPIs that jOOQ supports, such as org.jooq.ExecuteListener or org.jooq.Binding. Look at all the auxiliary API such as ResultQuery.fetch() and all the other types of execute() and fetch() methods. All of these need to be touched, adapted, documented, and some new ones added in order to be able to abstract over JDBC, as the assumption that the underlying drivers implement JDBC are everywhere.

This is a reasonable assumption for an API like jOOQ (or Hibernate) to make, because JDBC is ubiquitous, and these reactive drivers are a "fad". I have very high hopes that Loom will provide an 80/20 solution to people looking for non-blocking database interactions without changing the entire stack and adding requirements like these to third party libraries like jOOQ, because abstracting over JDBC is very hard.

An example, think of calling stored procedures (implemented in org.jooq.impl.AbstractRoutine). The sequential order of operations (in the default case) is:

• Initialise ExecuteListener lifecycle
• Obtain a Connection from the ConnectionProvider SPI (which needs to be reworked, too). This implicitly governs things like third party transaction management, connection pooling, etc. Perhaps it's easy to refactor into "non-blocking", but I'm not convinced it is. This is currently a blocking operation, and it no longer should be.
• Render the SQL string. This will not work the same way either, because some third party drivers have decided not to follow the JDBC specifications here, especially with respect to bind variable syntax or escape sequences. I've tried my luck here on the R2DBC mailing list, without success: https://groups.google.com/forum/#!msg/r2dbc/z6FcAdr441w/l5fSQQyyCAAJ. I think a big part of JDBC's success is to standardise (like ODBC) on things like bind parameter markers. There's nothing to gain if every third party API exposes their own marker syntax. Sure, nothing that jOOQ couldn't handle, but it's a lot of work. We'd first implement support for R2DBC and vertx's marker syntax, but then would need additional SPIs to abstract over this syntax for arbitrary third party drivers. And that's just bind markers. What about the JDBC { call ... } escape sequence? Should we investigate how to translate that to each vendor specific syntax for each dialect? We could, of course (and we already did for Oracle and PostgreSQL, for other reasons) but it's more work.
• Now it's time to prepare the call. This is currently a blocking operation but it no longer should be. Notice that currently, preparing the call is one operation and...
• Binding parameters is another. In fact, currently binding IN, IN / OUT, and OUT parameters is one operation as JDBC doesn't make any difference, but depending on how much of a vendor specific wire protocol leaks into a third party driver, this may not necessarily be the case. I can perfectly well see the case for some driver to think it's better to first register all OUT parameters, and only then bind all IN values. JDBC abstracts over these things, because it can easily buffer the relevant wire protocol instructions for these registrations and bindings. Can R2DBC, vertx, etc. abstract over this as well, or will the burden be upon jOOQ?
• Now, while I don't see much blocking being done with registering OUT parameters at this point, IN parameters are a different beast. They could be seen as a stream being sent to the server, in a reactive way. I can think of quite a few problems arising from this change of paradigm, especially when the stream contains nested streams (LOBs).
• Execution time! The execution itself is a simple message sent to the server, and the results will then be expected to be pushed by the server, instead of pulled. Let's look at that.
• In SQL Server, the order of fetching OUT parameters and fetching result sets from stored procedure calls is relevant, even in JDBC (the abstraction didn't work here). In other RDBMS, I haven't found any such relevant ordering, there may be some exceptions where only one thing can be done per procedure (Firebird / Jaybird). Make no mistake, there is not just "one result set" to be fetched. In fact, there's a stream sent by the server consisting of 1) result sets, 2) update counts, 3) exceptions / warnings / messages. In the case of R2DBC, I made sure this was being considered. I haven't checked the status quo of the 1.0 API to verify if what jOOQ does here for SQL Server (and MySQL) is really possible: https://groups.google.com/d/msg/r2dbc/QZpTpQtj1HA/VT0ob00WCQAJ
• Finally, the OUT parameters are fetched, and they can again be of all sorts of stream-y types.

We're "done" with stored procedure calls (at least the default case. there are a few exceptions with Oracle and PostgreSQL). We're only missing:

• Result set fetching statements
• Updating statements (which may as well be fetching statements, because of triggers, see above)
• Single statement / multi bind value set batches
• Multi statement batches

And obviously, I have forgotten a few edge cases. As you can see, this doesn't sound like something that is not doable, but it does sound like something that is extremely complex to implement correctly:

• For all dialects
• For various execution models, including R2DBC, vertx, kotlin coroutines (#9335), and others
• Without introducing regressions in the blocking case, which is probably still 95% of jOOQ's target audience

Compare that to the relatively easy "plumbing" people currently have to implement at the client side, and you see my reluctance to go forward with this, when at the horizon, there is Loom who promises most of the non-blocking goodies (including reactiveness) on top of JDBC.

[lukaseder]

Or are you suggesting that I should use 2 drivers?

Note, if adding a second driver is a kind of hassle that I currently do not see in your wording, there is always the possibility of feeding SQL scripts to the jOOQ code generator, without it needing to connect to any database: https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/

[adam-arold]

Thanks for the outline @lukaseder I'm gonna try this! You're right, it adds no benefit to add the reactive driver for the codegen.

[lukaseder]

A related topic is support for Kotlin coroutines: https://github.com/jOOQ/jOOQ/issues/9335

[adam-arold]

Something I'm also using! 👍

[lukaseder]

An update on this matter.

I've been nerd sniped into writing a quick prototype for a DSLContext.parsingConnectionFactory(), an R2DBC backed reactive version of the DSLContext.parsingConnection():
https://groups.google.com/g/jooq-user/c/O7Wgmo1KiOE

Which is a kick-ass (but less known) feature where jOOQ proxies JDBC to translate legacy application SQL from X to Y (mostly Oracle to PostgreSQL):
https://www.jooq.org/doc/latest/manual/sql-execution/parsing-connection

So, why not also proxy R2DBC?

This was supremely easy to do, and it motivated me to give an alternative ResultQuery.subscribe(Subscriber) implementation that is truly non-blocking and R2DBC backed a go. A quick draft also works right away. So, let's do this for jOOQ 3.15! I've created a new issue that you may follow, which wraps up all the actual tasks:
https://github.com/jOOQ/jOOQ/issues/11700

That new issue doesn't go for the almost impossible goal of writing a "driver provider SPI" like this one, offering to support for arbitrary backing driver SPIs to jOOQ. It will simply add R2DBC support, given that even Oracle now implemented a driver:
https://github.com/oracle/oracle-r2dbc

It would be nice to be able to integrate with vert.x out of the box as well, but as the saying goes: "perfect is the enemy of good". Perhaps, someone could implement a vert.x / R2DBC bridge to make this work as well (I wonder if such a thing is possible, @jklingsporn?). In the meantime, there are several popular third party plugins for vertx, including the most popular one here: https://github.com/jklingsporn/vertx-jooq

I'm closing this issue here in favour of #11700.

[mkurz]

Great news, thanks Lukas!

[jklingsporn]

Hi Lukas,
I'd love to help with the integration of the vertx support. As I am not aware of all the jOOQ-internals, here are is an uncompleted list of things that need to be considered:

• Support for the different driver implementations (postgres, mysql, db2). What I mean here is that even though they share a common interface they are different modules.
• Consider support of different return types. For now, if you call fetch on a query it executes the query using blocking JDBC, if you call fetchAsync it returns a CompletionStage. Vertx _primary_ async coordination unit io.vertx.core.Future can be wrapped from/into a CompletionStage but for the sake of interoperability with a vertx-project, the new module should return a io.vertx.core.Future. However there are more supported APIs: RxJava2, ReactiveStreams (Akka, etc) and Mutiny (Quarkus). As far as I know, vertx itself generates such APIs based on the io.vertx.core.Future-implementation. For the sake of completeness I also mention that there also exists an API that is of type void and always expects a handler as a last argument. The handler then deals with the async result.
• Translation of vertx-types to jooq-types
• Transaction handling
• Stored procedure handling
• Batch processing
• Cursor handling / result streaming

[lukaseder]

Thanks for your offer @jklingsporn. Question no 1 before anything else: Why doesn't Vertx not support, get involved with R2DBC? Since it can already expose itself via reactive-streams, it should be possible to wrap Vertx in an R2DBC ConnectionFactory, I think?

[jklingsporn]

I guess you are right. Quick search gave me this. I will try to find out more.

[lukaseder]

Thanks for looking up that issue. I've added a confirming comment there, linking again to https://github.com/jOOQ/jOOQ/issues/11700

The point of https://github.com/jOOQ/jOOQ/issues/11700 is really to be able to avoid too much unnecessary abstraction. JDBC is a wondeful API for tools like jOOQ. Loom will solve most issues reactive approaches try to solve as well without moving away from JDBC.

While I think jOOQ can add some value immediately by integrating with R2DBC, I don't want to:

• Support more than R2DBC
• Implement this feature here, which adds yet another layer of abstraction

So, if folks at RH could work together with folks at Pivotal, that would be great.

(Sorry, that meme was obligatory here 😁)