Support R2DBC

[VaughnVernon]

Implement support for R2DBC. This may be something such as jdbi-r2dbc. FYI:

https://r2dbc.io/
https://github.com/r2dbc

Also, we support Jdbi in the vlingo-symbio which is part of our reactive platform. Check it out. We'd love to get your team up to speed on our full platform.

https://github.com/vlingo/vlingo-symbio-jdbc/tree/master/src/main/java/io/vlingo/symbio/store/object/jdbc/jdbi

[stevenschlansker]

Hi Vaughn, thanks for the pointer. Reactive is very new and exciting but my understanding is that the current implementation of JDBC, and in fact the database server software itself, makes it a poor candidate for reactive concepts at the lower layers. The database server has worker processes that take queries and produce results, and each process will work on its query until completion. There's no ability to asynchronously submit 10 queries and await their results.

This means that as you implement JDBC reactive, you eventually find that you must implement an old style thread pool that holds your connections / handles, services queries, and then emits results into the reactive framework. This means that you can have some of the benefits of reactive, in particular you can write reactive code, but it seriously limits the scalability benefits that reactive attempts to achieve.

So we'd definitely welcome API integrations, but unless the state of the JDBC world has moved on since I last investigated, it'll be a somewhat limited wrapper that just hides the fact that the code underneath is still old school threaded code.

I appreciate the offer to get up to speed on your platform, but our library prides itself on being almost entirely standalone and not part of any platform or framework. So while we might look for inspiration and are always willing to collaborate towards shared goals, we're a fiercely independent project :)

[VaughnVernon]

Hey, Steven! Thanks for your comments. Currently we solve the reactive-over-JDBC by serializing requests within actors in our vlingo-symbio implementations. You can create as many JDBCObjectStoreActor as makes sense for your connection limitations, but the actors themselves access JDBC synchronously. It's more that the client of the ObjectStore implementations running on actors don't block while queries are being fulfilled.

I don't have a thorough understanding of R2DBC, but it is touted as being a full JDBC replacement and works completely asynchronously as a database driver. There are currently implementations for Postgres and MS SQL Server only, but probably will grow in time. I was wondering what it would be like for Jdbi to implement over R2DBC. I understand if this doesn't make sense for you now, but the reference is here in case it does at some future time. No stress :)

[stevenschlansker]

Interesting, they actually do provide their own drivers. So maybe there is some benefit to be had here!

I'm curious to explore this further: benchmarks, proof of concept code, etc. There seem to be some limitations: their r2dbc driver for postgres advertises that e.g. BLOB and CLOB types don't work yet, and there's no mention at all of stored procedures or CALL.

But at least my focus personally right now is elsewhere so this may need some community input and attention to move forward, as I don't have time right now to open an exploration here, and I don't imagine other core members are looking to jump on it either.

Looking forward to hearing more, even if just votes from other members of the community that this is a needed feature.

[mp911de]

Great to see this ticket.

I wanted to quickly drop two things to clarify the state of R2DBC:

• It's still a young initiative and things like BLOB/CLOB and Stored Procedure support are to be implemented before we go GA.
• Drivers are fully reactive and non-blocking meaning the whole I/O part is non-blocking. There's no offloading of blocking work into thread pools.

In any case, R2DBC aims for an SPI to be picked up by client implementations such as Spring Data R2DBC, R2DBC Client (a tiny wrapper on top of R2DBC SPI that lend some ideas from jdbi) and more to come.

[nebhale]

Just wanted to chime in and mention that my original prototype/demonstration client layer, r2dbc-client was designed based on Jdbi's APIs and ethos. If you do choose to investigate further, I think you'll find it familiar 😄.

[brianm]

I have seen folks expose Rx stuff out of JDBI ( @hgschmie for example ). I am not sure what "support" for R2DBC would mean.

Are you talking about implementing R2DBC on top of JDBI, having JDBi consume R2DBC Connections, swapping JDBI to focus on Rx interfaces, or... something else?

I think it would be "support R2DBC drivers to be used by JDBI, exposing the JDBI interfaces" ?

[VaughnVernon]

R2DBC is an async replacement for JDBC.

[mp911de]

@brianm R2DBC isn't JDBC on ThreadPools. R2DBC means two things:

1. R2DBC drivers are implementations using a non-blocking transport layer returning org.reactivestreams.Publisher types for each I/O-bound operation. They do not use JDBC drivers underneath but implement wire protocols from scratch.
2. R2DBC is a standardized (vendor-independent) SPI allowing to build client libraries on top. Driver implementations can be interchanged like it is done today for JDBC.

Are you talking about implementing R2DBC on top of JDBI, having JDBi consume R2DBC Connections

Yes, this is what it basically means. Having an R2DBC-specific JDBI module returning Project Reactor/RxJava2/Zerodep types.

[brianm]

I'm pretty sure I get what R2DBC is, and I think it is a Very Good Thing.

I am trying to figure out what "JDBI support" for it is!

[leaumar]

Maybe write a code sample of what you expect to do via JDBI's API?

[mp911de]

How about supporting the following for starters:

Jdbi jdbi = Jdbi.create("r2dbc:h2:mem:///test"); // (H2 in-memory database), obtain ConnectionFactory via ConnectionFactories.get(String url)

// Reactor style:
Flux<User> users = jdbi.withHandle(handle -> {
    
    return handle.execute("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
    
        .then(handle.execute("INSERT INTO user(id, name) VALUES (?0, ?1)", 0, "Alice"))
    
        .then(handle.createUpdate("INSERT INTO user(id, name) VALUES (?0, ?1)")
            .bind(0, 1)
            .bind(1, "Bob")
            .execute())
         .then(handle.createQuery("SELECT * FROM user ORDER BY name")
              .mapToBean(User.class).many());
});

// RxJava style
Flowable<User> users = jdbi.withHandle(handle -> { … });

The interface-based support could look like:

public interface UserDao {
    @SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
    Completable createTableRxJava();
    
    @SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
    Mono<Void> createTableProjectReactor();

    @SqlQuery("SELECT * FROM user ORDER BY name")
    @RegisterBeanMapper(User.class)
    Flowable<User> listUsersRxJava();
    
    @SqlQuery("SELECT * FROM user ORDER BY name")
    @RegisterBeanMapper(User.class)
    Flux<User> listUsersProjectReactor();
}

I'm not opinionated about RxJava vs. Project Reactor, therefore I tried to list variants using both APIs.

[qualidafial]

Could somebody clarify what exactly is being proposed here?

Is R2DBC a layer on top of JDBC, or its own thing? Because Jdbi is coupled to JDBC in the extreme.

[mp911de]

R2DBC is not a layer on top of JDBC. R2DBC is a non-blocking API to access SQL databases and it is its own thing (R2DBC drivers are typically written from scratch implementing vendor-specific wire-protocols using a non-blocking I/O layer). If you will, R2DBC is the reactive specification of how to integrate with SQL databases.

The proposal here would be having an API that looks and works like Jdbi but uses underneath R2DBC drivers. Instead of returning scalar objects, the API would return Reactive Streams types.

[stevenschlansker]

Thanks for the clarification. I expect the first step here would be to prototype a Jdbi fork or branch that serves minimally as a PoC showing how to implement the above examples on top of R2DBC. From there we would have to determine a path towards integration in parallel to existing JDBC support, or establish that it would be better as a hard fork. As Matthew points out above we are tightly coupled to JDBC currently and changing that is likely to both be a lot of hard work as well as potentially require breaking changes.

I am excited to have this on the project's long-term radar but I don't expect it to move quickly without significant community involvement.

[mp911de]

@stevenschlansker We've created initially r2dbc-client that was inspired by JDBI. What do you think about a pull request/PoC that introduces a r2dbc module (jdbi-r2dbc) as a contribution of R2DBC Client back into JDBI along the lines of:

ConnectionFactory cf = …;

Rdbi rdbi = new Rdbi(cf);

rdbi.inTransaction(handle ->
    handle.execute("INSERT INTO test VALUES ($1)", 100))

    .thenMany(rdbi.inTransaction(handle ->
        handle.select("SELECT value FROM test")
            .mapResult(result -> result.map((row, rowMetadata) -> row.get("value", Integer.class)))))

    .subscribe(System.out::println);

[stevenschlansker]

Very cool. Yes, if the work can be integrated in a way that is reasonably seamless, we'd happily incubate it in a module!

It would be nice if somehow the Rdbi api "came from" Jdbi -- in that when you do a jdbi.installPlugin(...).registerRowMapper(...) then any Rdbi instances that "come from" that Jdbi also get their configuration. It'd also be nice to be able to do things like @SqlUpdate CompletableFuture<ResultType> doSomeWork(...); or whatever the appropriate reactive type is.

[dzikoysk]

What's the current status of this issue? It kinda looks like everyone just gave up on this, right? 🤔

[stevenschlansker]

Hi @dzikoysk, I think this push is currently dormant. There's an attached PR which is an interesting first take, but the project maintainers are hesitant to sign up for an approach that doubles our API surface.

You may call me a dinosaur, but my personal feeling is that the reactive programming model is a lot of hype, and may not maintain popularity long-term. There's definitely some good things in there but my personal experience is that 90% of things that are done with reactive would be better done in the boring old simple thread style... especially with virtual threads on the horizon.

I would love to have support for reactive style programming but would prefer if we could find some way to have it dovetail nicely into the existing API, rather than functioning as a hard-fork.

(Sorry for the late response...)

[dzikoysk]

I kinda gave up on this, as crucial components required to bootstrap application in Java are developed by Java developers that have similar point of view as you, or such issues are just left unanswered indefinitely. 😅

I also could say that the bad application of reactive programming is not a problem caused by reactive programming, but by design choices etc. but I guess that's just pretty pointless to keep this discussion over and over. I personally recommend everyone that face such issues in the future to just forget about it or change technology like even language for more expressive technologies that specialize in its areas rather than trying to force existing ones to provide crippled support for integrations that are making both sides unsatisfied.

From the perspective of time I'd find it way less frustrating if issues like this would be just closed with a statement that it won't be supported. Keeping those discussions open and effectively giving "false hope" was just pretty much unhelpful.

[VaughnVernon]

If you look back at my first few comments, I didn't expect this to be done. I was hopeful. After nearly four years since submitting this issue/enhancement request I completely forgot about it. I did look into R2DBC to attempt my own support over a database after I requested this. I found it overly complicated due to its deep dependency on project Reactor and I considered Reactor a conflict with our own Reactive Streams implementation. It's not that you'd have to use Reactor (as far as I have been told), but all the existing implementations (as examples) seem to use it. While in principle it's disappointing that this didn't fly, I completely understand the reasons for you reluctance. As far as I am concerned you can close this issue. I would but since it is not my decision I think a product stakeholder should do so. Thanks again for Jdbi. I think it is the way that Java ORMish tools should work.

[anborg]

2023:

• Non-blocking paradigm is going from hype-to-reality. Saves $$, efficient resource utilization in application stack.
• Db vendors have created nonblocking db drivers (r2dbc) : postgres, h2, oracle, sqlserver,
• Frameworks like springboot / quarkus / micronaut, have enabled non-blocking in all layers. My only blocker is jdbi-blocking-access.

I searched for how to use r2dbc drivers in my jdbi project, and landed in this issue. I thought jdbi folks would keep this open! Meanwhile, I 've to put a pause to jdbi-code that I developed, delete it for now, and unfortunately-use-hibernate-with-r2dbc.

[VaughnVernon]

At this point the other angle is to use Jdbi with Java 21+ using virtual threads (Loom). When virtual threads block on I/O, the physical thread doesn't park with it. It's used by a different virtual thread(s) until the I/O is completed.

Going to Java 21+ might be more difficult if you aren't even on Java 8, and possibly even if you are. I haven't tried yet, but I think it obsoletes R2DBC when virtual threads are all grown up. And frankly, for various reasons I dislike that to use R2BDB you must depend on Reactor.

[mp911de]

It's a bit unfortunate that most of R2DBC drivers gravitate around Project Reactor instead of a different Reactive Streams implementation.

[stevenschlansker]

@anborg , the Jdbi project is not trying to be anti-reactive here, but we do want to make sure that accepting reactive compatibility does not completely upend the project. The issue with the first proposed approach is that was effectively a hard-fork of the Jdbi API which must be maintained indefinitely, and all new features must be written for both. The Jdbi project is very much alive but the number of maintainers is not large and none of us use reactive at all, so we would not understand how to build this well.

If we can design an approach that allows to mix in reactive features without needing to completely redesign Jdbi's API, we're still open to it, but I think it will be a lot of work.