Support for one-to-one and one-to-many relationships

[murdos]

Spring Data JDBC supports one-to-one and one-to-many (either as Set, List or Map) relationships: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.entity-persistence.types

It would be really useful to also have this feature with R2DBC.

[mp911de]

This ticket is a duplicate of #352. Since this one is more focussed, we're closing #352.

[darichey]

Would love to see one-to-one support. For now, I think I have to modify my database schema.

[mp911de]

The reason we cannot provide the functionality yet is that object mapping is a synchronous process as we directly read from the response stream. Issuing sub-queries isn’t possible at that stage.

Other object mapping libraries work in a way, that they collect results and then issue queries for relation population. That correlates basically with collectList() and we’re back to all disadvantages of blocking database access including that auch an approach limits memory-wise consumption of large result sets.

Joins can help for the first level of nesting but cannot solve deeper nesting. We would require a graph-based approach to properly address relationships.

[mspiess]

Would it be possible if the type of the relation was Flux instead of a collection? Of course that would limit it to lazy loading, but it might be good enough as a compromise for the time being. I was assuming one-to-many here, but the approach would be the same for one-to-one with Mono.

[mp911de]

Modeling a domain with Mono's and Fluxes rather pollutes your domain with things that do not belong in there. Imagine an Order with Flux<OrderLines>. There's no chance to add another one. Having a ReactiveRelation<T> could be built, but honestly, adding reactive types to a domain model introduces a lot of complexity. We recommend rather a lookup of relations where it applies.

[thachhuynh]

So for now, can we use spring data jpa annotations instead?

[Bittuw]

@mp911de, parallel != reactive. We still can read relationship between entities. We have two ways:

1. Sequence of requests in reactive manner;
2. Create VERY BIG JOIN;

I am for the first option, because we can easy resolver circle dependencies between responses + if RDMBS provide real async, we already can request the next part of data from relationship entity (Which have already come). In the second we have to do complex analyze of answer from RDMBS which will be very ugly.

[wickedev]

What if introduce concept of CallAdapterFactory, in "retrofit" which is RESTful API client library. by introducing it, community can be handle wrapper type with adaptor. List(eagar, one to many), Set, Deferred, Observable, Flow, Reactive Streams, Mono, Flux whatever else. This way should not have to get hands dirty at the core library level.

Retrofit retrofit =  
  new Retrofit.Builder()
      .baseUrl(apiBaseUrl)
      .addConverterFactory(GsonConverterFactory.create())
      .addCallAdapterFactory(RxJavaCallAdapterFactory.create())
    .build();

public interface GistService {  
    // access Gists with default call adapter
    @GET("gists") 
    Call<List<Gist>> getGists(); // Retrofit Default

    // create new Gist with RxJava call adapter
    @POST("gists")
    Observable<ResponseBody> createGist(@Body Gist gist); // Handle by RxJavaCallAdapterFactory
}

The Node.js community also has a similar problem. For TypeORM, Eager relations use Array, Lazy relations use Promise.
https://github.com/typeorm/typeorm/blob/05259795f0ff4a0241647ccfb621f2a5f55b3d34/docs/eager-and-lazy-relations.md

[mp911de]

In contrast to HTTP, relations need to be fetched from the same connection to ensure transactional isolation. In addition, R2DBC is a streaming API which means that the entire SELECT result needs to be consumed from the server first, before we can issue additional queries. Spring Data R2DBC doesn't collect the results into a List first but converts and emits results as they are received.

[pmaedel]

Relationships with eager fetching is already possible when one writes their own queries and then uses R2dbcConverter, though that usecase could see improvement: #448

I guess with some documentation and examples, people could get by without builtin support quite comfortably.
I would furthermore argue, that the approach of defining the specific join query & model for the respectively needed relationship is more reasonable altogether then trying to find a one-size-fits-all solution as we know from JPA where the relationships and their fetch types are defined within a single model. This removes flexiblity to decide per use case whether a join is needed or not.

Just one short example of how many different patterns are possible and possibly desired to query for in an exclusive manner:

@Table("student") data class Student(val id: Long?, val name: String)
@Table("teacher") data class Teacher(val id: Long?, val name: String)
@Table("seminar") data class Seminar(val id: Long?, val teacher: Long, val student: Long, val name: String)

//possiblities: 
data class SeminarJoined(val seminar : Seminar, val students: List<Student>, val teacher: Teacher)
data class SeminarsOfTeacher(val teacher: Teacher, val seminars: List<Seminar>)
data class SeminarsOfStudent(val student: Student, val seminars: List<Seminar>)
data class TeachersOfStudent(val student: Student, val teachers: List<Teacher>)
data class StudentsOfTeacher(val teacher: Teacher, val students: List<Student>)

[jiangtj]

I hope Fluent API can support join()

[chefinDan]

It's been 6 months since someone from Spring has commented on this issue. Are there any updates or future plans to support JPA-like annotations?

[matiasah]

@mp911de I actually agree with the idea of having Monos and Fluxes on entity level relationships, even if this "pollutes" domain objects a bit, it's still a paradigm switch from sequential to reactive, it seems like a good approach and I don't think it should be discarded.

[jsunsoftware]

Any news from this ticket? Did work start or some estimations?

[hantsy]

It's been 6 months since someone from Spring has commented on this issue. Are there any updates or future plans to support JPA-like annotations?

Micronaut Data Jdbc/R2dbc supports such a feature, you can use JPA annotations to declare the relations directly.

Micronaut Data also supports JPA Specification for type-safe queries in Jdbc/R2dbc, even provides a Kotlin Coroutines variant. I've filed an issue for requesting supports in Spring Data, but rejected.

[Aleksander-Dorkov]

There is Hibernate Reactive now. So if Data-JPA uses Hibernate, can't this dependency use Hibernate Reactive? It supports all relational mappings except @ManyToMany witch has an easy workaround and @ElementCollections witch no body should use. You can use pretty much all of hibernates annotations in you domain model as well.

From the compatibility here I can see that it requires java 11.
https://hibernate.org/reactive/
So I guess since the spring team wants to support java 8 it's impossible? Will you implement this as a solution in spring-boot 3?

[EnvyIT]

Any news on this one ? As @Aleksander-D-92 emphasized there is Hibernate Reactive available. Would be great to get some feedback when Spring Data R2DBC is as mighty as its sync brothers.

[EnvyIT]

New year new luck - first of all, happy New Year 2023 everyone. Hope you are fine and ready for new challenges?!

Challenges like implementing this feature. May I ask you again if you have any updates on this issue for us ?

[amitojduggal]

Without this feature what could be the way to manually workaround the entity relationships?

[hantsy]

How Micronaut Data get one-to-many/many-to-many support in R2dbc/Jdbc?

Micronaut Data Jdbc and R2dbc has the same code style, but use different types.

[hschenke]

Vote up 👍

[wickedev]

@mp911de If we are considering supporting this feature, could you please provide some guidance on how to proceed, such as shape of the API? I am willing to contribute if possible.

[SemperEtAnte]

Still no progess in this feature? As for me r2dbc is kinda uncomfortable without relations. Yes it's async, but in big projects there will be too much additional code to proceed relations without ORM

[EnvyIT]

New year new luck - first of all, happy New Year 2024 everyone. Another year has past and we still have no answer if you plan to implement the feature or not. Any kind of react would be appreciated from the community and myself.

[amitojduggal]

Seems it isn't on the radar for this project's progress. The reason could simply be that one can jump ship to Spring Boot 3.2 with Virtual threads and explore similar performance as reactive without having to look for libraries with nonblocking io.

[SledgeHammer01]

Seems it isn't on the radar for this project's progress. The reason could simply be that one can jump ship to Spring Boot 3.2 with Virtual threads and explore similar performance as reactive without having to look for libraries with nonblocking io.

Unfortunately, without this feature, r2dbc is pretty useless. Thus webflux is pretty useless if you intend to use a relational database.

[wiiitek]

Seems it isn't on the radar for this project's progress. The reason could simply be that one can jump ship to Spring Boot 3.2 with Virtual threads and explore similar performance as reactive without having to look for libraries with nonblocking io.

Unfortunately, without this feature, r2dbc is pretty useless. Thus webflux is pretty useless if you intend to use a relational database.

Maybe someone could explain it to me: I might not understand it, because for me
many-to-many means, that we are retrieving a lot of rows at once:

1. let's imagine we have entityA with many-to-many association to entityB
2. we would like to retrieve some entityA rows
3. with all associated entityB rows
4. and all transitively associated entityA rows

in other words: all rows from entityA and entityB if there is any association between them.

For example: you have a blog post with tags, and want to also load all other blog posts with those tags.

I think it is possible to split it into separate repositories:

• blog post having one-to-many with tags
• tag having one-to-many with blog posts

Such separation makes it simpler and is enough for simple use cases (requirements).

For blog posts we probably don't even need tag entities, but instead find blog posts having at least one of the tags.

Can someone help me understand the use-case / requirements, where we really need many-to-many?

[SledgeHammer01]

Can someone help me understand the use-case / requirements, where we really need many-to-many?

Thread is about 1:1 and 1:M. Even 1:1 and 1:M are not supported. Yes, you can split M:M into 2 1:Ms, but you can't do either. r2dbc is only supporting 1:0 now which is basically useless for anything other then small POCs.

EDIT: Unless of course, you don't mind hand rolling all the SQL <-> POJO mappings Java code.

[pmaedel]

I pointed out years ago how one can easily use the ORM of R2DBC with custom queries to map rich relationships, and why this use case driven modeling is even preferable to a single source of truth model, as you get with JPA.

the comment:
#356 (comment)

code samples:
#448

[DavidTheProgrammer]

I pointed out years ago how one can easily use the ORM of R2DBC with custom queries to map rich relationships, and why this use case driven modeling is even preferable to a single source of truth model, as you get with JPA.

the comment: #356 (comment)

code samples: #448

I get what you're going for, but in your solution, you basically cannot use repositories for anything other than a simple query, and that's assuming you now have two instances of each class, one for the repositories without the nested classes and the other with the nested properties that you'll use with custom converters and template. The other option is to commit to one implementation or the other; we can't commit to repositories because we need mappings leaving only the handroll method in which case we're discarding the entire repository aspect and model that we've come to know and love from Spring Data. I argue might be a worse evil than Flux and Mono in entity classes.

However this comment does make me wonder how these mappings can essentially be done within the same connection for the sake of the transaction, does this mean we keep the connection alive until the request lifecycle is complete? How do we know it's complete? We can't prematurely close it because you might call the Flux or Mono much later than we anticipate and the data needs to be consistent. Do we keep the connection alive until the object reference is unreachable? This might lead to some hard-to-debug issues... Just thinking out loud.

[SledgeHammer01]

I pointed out years ago how one can easily use the ORM of R2DBC with custom queries to map rich relationships, and why this use case driven modeling is even preferable to a single source of truth model, as you get with JPA.

the comment: #356 (comment)

code samples: #448

No, you didn't. Your code sample just shows a trivial 1:1 mapping. Now, can you show us an EFFICIENT way to do 1:M where the root table returns 1000's of records and you need to support EFFICIENT pagination as well? You can't use join for that as it wouldn't be performant.

Your other requirements are 1: that your mapping code significantly outperforms Hibernate with virtual threads :). 2: your solution also has to be generic enough that I don't have to c&p 100's or 1000's of lines of code for every query, etc, etc. 3: what about efficient updates? :)

If you can't outperform Hibernate with virtual threads on more "real world" use cases, what's the point of even bothering with Webflux and r2dbc? Whole point of Webflux in general was to scale better. With VTs, its a hard sell nowadays.

Although as I mentioned in an earlier post, I'd probably still go Webflux if I was using a DB with better reactive support. For relational? No way.

[hantsy]

@mp911de I would like Spring Data R2dbc provide similar Aggregate template in Spring Data Jdbc, and add one-to-one and one-to-many embedded concept for the entity. It is a good match of the DDD entity concept.

Or add R2dbc to jmolecules, I can not find a R2dbc example here, https://github.com/xmolecules/jmolecules-examples/tree/main

[JoseLion]

@mp911de, is there any news regarding this feature? I'm asking because I recently published a package to Maven Central that helps to handle relationships in R2DBC, and it'd be great to have some feedback from you. The implementation does not go with the ideal solution of a graph-based approach. Instead, it uses .collectList() for mapping and leverages the Entity Callbacks API to ensure deep population/persistence. This should be good enough for now because users will generally do that if they manually implement a lookup when required.

The library is still in v1 so any feedback is more than welcome. It covers one-to-one, one-to-many, many-to-one, and many-to-many relationships. It even provides a solution to use entity projections in the relationships. I tried to cover as much ground as possible, but there's still space to improve, especially to make it work with all of Spring's features. I'm leaving the link to the homepage and the GitHub repository below also in case people are interested 🙂

Homepage: https://joselion.gitbook.io/spring-r2dbc-relationships/

GitHub: https://github.com/JoseLion/spring-r2dbc-relationships

[Aleksander-Dorkov]

Since this is never going to get resolved, I have to ask 2 questions.

1. If we use Spring Boot 3.2 or newer with Java 21+ with virtual threads enabled, does it have basically the exact same performance as WebFlux, because from my understanding the sole advantage of the whole reactive stack is the non blocking IO?
2. Are the db drivers reactive as well? Does spring have a way to know witch drivers to fetch, depending if we are using Java 21+ with virtual threads enabled or not, and does it even matter? Can the non-blocking driver work well with normal spring-data-jpa?

[hantsy]

1. Using Java 21 make the classic stack(Servlet/Spring Data JPA) getting better performance and with virtual treads.
2. If you want to use one-to-many/many-to-many freely in JPA and also want to use reactive stack, consider Hibernate Reactive(which use same API in JPA, but provides reactive features), check my example project:https://github.com/hantsy/spring-puzzles/tree/master/hibernate-reactive-mutiny, Unfortunately, Spring Data rejected Hibernate Reactive support: Hibernate Reactive reconsideration spring-data-jpa#2503

[hantsy]

In our real project, we uses jOOQ(which support R2dbc) to query the rich relations, such as one-to-one/one-to-many etc in one query. We just use the Spring Data R2dbc to execute insert/update/delete and simple query based on the derived convention.

Check my example project using R2dbc Repository and use jOOQ to extend the functionality in Custom Repository.

[mp911de]

Can the non-blocking driver work well with normal spring-data-jpa?

Spring Data JPA and JPA in general use blocking APIs to operate the driver. That being said, there's no way to benefit from non-blocking drivers using JPA. Virtual threads however, provide a non-blocking-like experience by switching threads that would otherwise wait for I/O to happen.

[hantsy]

Seems it isn't on the radar for this project's progress. The reason could simply be that one can jump ship to Spring Boot 3.2 with Virtual threads and explore similar performance as reactive without having to look for libraries with nonblocking io.

Unfortunately, without this feature, r2dbc is pretty useless. Thus webflux is pretty useless if you intend to use a relational database.

Use Hibernate Reactive with Spring or switch to Quarkus or Micronaut Data R2dbc, all have rich features as you expected.

[Topfgriff]

Did this repo move into Spring Data Relational?
If so maybe the issues should be reopened there to get the devs attention?

[mp911de]

Indeed, the repository has been merged into Spring Data Relational.

[Topfgriff]

I have reopened the issue on the new repository

[schauder]

Consider this issue replaced by spring-projects/spring-data-relational#1834 opened by @Topfgriff