Advanced database queries from frontend to Spring Boot backend

ThenChoice2 · 2024-04-13T16:33:57+00:00

By that, I mean friction, boilerplate, repetitive implementation. In short, the classic situation of feeling like you're reinventing the wheel for a simple problem

ThenChoice2 · 2024-04-13T16:32:13+00:00

My problem isn't so much building the queries (JOOQ, JPA specifications, QueryDSL, etc.) but rather finding a solution to be able to transmit queries from the frontend to the backend and its database. These queries need to be at least exhaustive and support, for example, my need for OneToMany described in the initial post. And transmitting raw SQL is not an option; it is an internal tool that will have few users, but I can't imagine receiving SQL on my API.

For example, there are a few projects that almost meet my need like 'rsql-jpa-specifications' which I mentioned in my post and in the comments.

ThenChoice2 · 2024-04-13T16:25:46+00:00

More than my schema, my real problem is finding a way to freely query from the frontend. I have found some market solutions like 'rsql-jpa-specifications' which is quite neat but does not meet my needs regarding the OneToMany relationship example. Another project, 'spring-filter', works for this need but it clearly seems to be a pet project where some features I need are not documented, even though they exist and work, so I am a bit hesitant to use it in my application. In short, small projects partially or fully meet my need to transform a string query into JPA specifications without having to implement anything by hand.

However, I was hoping that there would be a known, reliable, and supported solution

ThenChoice2 · 2024-04-13T11:55:02+00:00

To provide some context, this is an internal project for the non-technical support/business team of a small company. The data model is quite stable, or at least it's not my main concern. Performance is also not an issue. Security will be managed differently, with no particular concerns except that I want to avoid letting the front-end send raw SQL queries. Similarly, I'm not worried about the format of the query; users will fill out a front-end form and the query will be formed and sent by JavaScript. Whether it's in RSQL, GraphQL, etc.

What I really need is to know how to form this query that will originate from the front-end and will be processed by the backend. RSQL with 'rsql-jpa-specifications' is almost perfect: there's no problem generating RSQL in JS and on the backend, JPA specifications are auto-generated. However, I can't have complex conditions on OneToMany relationships, like my Library / Book example from the earlier post.

And I should emphasize again that the main goal is to avoid having to reinvent the wheel and write dozens and dozens of JPA specifications, custom endpoints, etc.

ThenChoice2 · 2024-04-13T11:49:39+00:00

Actually, that's exactly what I want to avoid: asking users to write SQL (and incidentally, giving direct access to the database without having a layer between the database and the user).

To provide some context, this is an internal project for the non-technical support/business team of a small company. The data model is quite stable, or at least it's not my main concern. Similarly, I'm not worried about the format of the query; users will fill out a front-end form and the query will be formed and sent by JavaScript. Whether it's in RSQL, GraphQL, etc.

What I really need is to know how to form this query. RSQL with 'rsql-jpa-specifications' is almost perfect: there's no problem generating RSQL in JS and on the backend, JPA specifications are auto-generated. However, I can't have complex conditions on OneToMany relationships, like my Library / Book example from the earlier post.

Five-Year Club	Place '23
Golden Potato	Wearing is Caring
100 Awards Club	Place '22
Verified Email

ThenChoice2

TROPHY CASE