Opinionated Query Definition

100 views
Skip to first unread message

Fabio Trabattoni

unread,
May 18, 2024, 12:41:11 PM5/18/24
to jOOQ User Group

Hello everyone,

I hope this message finds you well. I’d like to share an idea for a potential feature request, inspired by a brief discussion on SO with Lukas.

INTRO When starting a Java Spring project, developers often use JPA for data mapping because it allows for quick and straightforward setup. However, as the project grows, they start encountering performance issues like the infamous N+1 problem et similia.

At this stage, devs make one of the two things happen:

  1. Refactoring JPA Mappings: They push themselves to restructure mappings and use more efficient querying mechanisms with JPA.
  2. Switching to Alternatives: They opt for alternatives like JOOQ or QueryDSL to handle their queries.

Unfortunately, most of the times ,both approaches result in code that is hard to read, inconsistent, and difficult to maintain. At least in my experience.

THE IDEA Here’s the idea: a strongly opinionated way of defining queries that starts from the basic thought steps when dealing with data querying in Java. The focus would be on defining a query plan with filter criteria and specifying what to load and how it should be loaded, possibly even in a recursive fashion.

  1. Specify Filters: A straightforward mechanism to define and apply filters to the data set.
  2. Specify What to Fetch: A clear way to determine which fields to retrieve.
  3. Recursive Query Plan: The ability to define a query plan that can handle recursive fetching and recursive criteria, ensuring that related data is queried efficiently.

At a glance it could look something like this: https://github.com/thestroke82/leanquery/blob/master/src/main/java/org/frappa/leanquery/controller/CustomerController.java I've worked with a custom solution very much like this in the past and observed it performing its job remarkably well. The strongest advantage of such an approach is that as the codebase grows, readability remains practically constant, significantly improving maintainability. CONCLUSION The ideas are now out in the open, both in words and code (see the GitHub link above). I’m eager to hear your thoughts and advice, and I hope we can work together to evolve this concept into a formal feature request for JOOQ.

One final request: Please refrain from comments that patronize or dismiss the idea with statements like "JPA already does that" or "X does that too." Instead, I’m looking for constructive feedback and suggestions on how we can make this feature a valuable addition to the JOOQ ecosystem.

Thank you for your time and consideration. I look forward to your feedback and collaboration!

Lukas Eder

unread,
May 20, 2024, 6:14:05 AM5/20/24
to jooq...@googlegroups.com
Thanks for your message.

For the record of others following this discussion, it's a follow-up to this Stack Overflow question:

Fetch plans like these aren't a new idea. JPA does indeed already explore such functionality via entity graphs. I've seen similar things in .NET's LLBLGen, and numerous other environments. Spring Data and their whole idea of DDD tries to model database interaction via a set of aggregates and roots, which aren't too different from fetch plans / entity graphs. In a way, GraphQL does this as well. Your quick prototype isn't so different. The most revolutionary attempt so far IMO is Oracle 23ai's JSON-relational duality views concept:

The idea is always similar:

- You have a root
- From this root, you want to optionally navigate a tree of related data

It's not "patronising" to look into prior art, because we can learn from others' successes and mistakes, so I'm not quite sure why you'd like others to refrain from making comparisons. If you will, "constructive feedback" is only ever possible if it is informed, and in order to be informed, one needs to combine the experience of others' with additional ideas.

Why doesn't jOOQ have this yet? jOOQ's primary objective is to model the SQL language. In SQL, we write queries against the underlying relational data model, which is typically but not necessarily normalised at least in 3NF. This is a fundamental part of jOOQ's philosophy, and thus any DDD-style root/aggregate traversal does seem foreign to jOOQ (and to SQL). The impedance mismatch between DDD and SQL is non-negligible. To this day, I'm not 100% sure whether this is because DDD is fundamentally flawed, or existing attempts at implementing DDD principles are fundamentally flawed, in that they seem to completely dismiss the idea of an underlying relational data model (e.g. by being "data store agnostic"). The way these flaws manifest, in my opinion, is that the existing frameworks are extremely limited in the way they allow for non-entity querying. Everything is always fetched entirely (all attributes, all rows), and hardly any projection, aggregation, computation, and other type of ordinary SQL operation is possible. JPA's entity graphs can hardly do anything (and a beginner will not easily grasp whether they should choose entity graphs, criteria query, jpql, ordinary entity navigation, or just plain SQL!). GraphQL is perhaps the most powerful here, but even there, you can't go far beyond projecting pre-defined column expressions and writing simple filters on them.

jOOQ embraces querying, and there's nothing missing technically on a low level from jOOQ anymore since the introduction of MULTISET, nested ROW, etc. to implement arbitrary root/aggregate based views on top of jOOQ:

Some third parties have long ago started embracing this paradigm and built GraphQL (or similar) implementations on top of jOOQ and its MULTISET, e.g. hasura does this:

See also:

Obviously, your criticism here is for MULTISET to be "too complex," and thus queries becoming "not maintainable." And indeed, like many other things SQL, its usage can become repetitive after the 100th query. I don't think this disqualifies jOOQ, it's possible to build user-opinionated libraries on top of jOOQ to remove some of the repetitiveness, but obviously hard to judge without concrete examples. For jOOQ it is *much* more important to get the low level operators right, *first*, and then, if reasonable, build convenience on top of them. The philosophy behind this is: It's worse to say "this is not possible, yet" than to say "this is not convenient, yet"

For example, with MULTISET having shipped in 3.15, to-many join path relations, join path correlation, have been very powerful new features in jOOQ 3.19, which make even more sense now:


These building blocks are so powerful (and hardly seen elsewhere!) that I'm positive to be able to tackle even more steps in the near future. A lesser-known feature of jOOQ 3.19 is an improved implementation of inline derived tables, which allow you to create a derived table from any jOOQ Table expression by appending where() (which is only convenience, not a new operator):


There has also been an idea in the past to allow for other relational operators on arbitrary table expressions, such as Table.select() but it was rejected because the time wasn't ripe for such an operator yet, at the time:


Another rejected feature request for convenience over nesting MULTISET or ROW operators is this one:


These rejections just show that for a game changing feature to be implemented, the time must be ripe, and it just hasn't been yet. I still believe that there's an extremely low hanging but also equally well hidden fruit to be picked here, where by only adding 1-2 convenience methods on top of existing query operators, what you have in mind here is possible to implement in jOOQ in an entirely query based way, without building new "query languages" on top of jOOQ. But it's very hard to discover the exact API that will be the game changer, and not the disappointment like many other attempts at tackling fetching trees of relational data by making compromises on the power that the SQL language would otherwise allow. I believe that Oracle 23ai's JSON-relational duality views are the closest to a thorough solution that unifies both worlds (and even allows for writing back!) The only flaws that I can see so far is that they require:

1. Completely static views. This is a big flaw IMO - in order to change anything in the resulting tree structure, the view has to be changed, for every consumer of the view! With jOOQ, this is all arbitrarily dynamic.
2. The use of JSON in the projection, when this could be done with ORDBMS capabilities of Oracle. Oracle has the standard SQL MULTISET operator as well, though I understand that it was probably far easier to implement and reaches a bigger target audience from the start.

But it is a very well-designed approach nonetheless and it illustrates where jOOQ will go in the future.

So, to summarise:

1. jOOQ will continue to add more convenience for nesting data structures in queries with even more focus on path traversal and projections / selections / correlations based on paths
2. jOOQ will not offer an alternative, "opinionated" API on top of jOOQ that solves only root/aggregate based entity traversal (and thus not repeat the mistake of countless ORMs of separating querying from entity interactions, leading to confusion and limitation). Any jOOQ solution will be fully integrated into the entirety of the query language in a way that would even be idiomatic for SQL itself.

An example of 2) is the DAO API, which I regret so much. It is such a terrible bikeshed, being the only really opinionated thing in jOOQ. Sure, users like it, and it can't be removed again for this reason. But it is not well-designed. It was a "quick win," that will never implement the features users ultimately desire. Jakarta Data is making the same mistake right now by copying Spring's Repository and thus opening up endless bikesheds instead of truly innovating, at least in my opinion.

In order to respond to your criticism, jOOQ's approach is the *only* way to result in code:

- That is not hard to read
- That is consistent
- That is easy to maintain

Users can build opinionated APIs on top of jOOQ if they think its approach is not convenient enough, though, incidentally, well-designed convenience within the jOOQ query system may lead to satisfactory results for not-extremely-opinionated users nonetheless.


--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/99bbd0c3-e5af-4ee7-a897-7ff869098288n%40googlegroups.com.

Bernd Huber

unread,
May 20, 2024, 7:38:58 AM5/20/24
to jOOQ User Group
Hello Fabio,

i just want to share my similar use-case, just to give a perspective to a different way to solve dynamic querying.

- 1. a common Query-Model
- 2. a Mapper that maps the Query-Model to Dynamic Jooq
- 3. a Repository for each use-case that resolves data with help of MULTISET, Pagination, Filtering etc.

The common Query-Model (FkQuery). 

The mapper that maps the Query-Model to Dynamic Jooq:

The Repository:

---

I think my solution is not recursive or as strong in regards to dynamic flexibility, but it does also provide a dynamic way to interact with the Repository
to filter specific data in a Table-Relationship (Main-Table, Subtables). Its not as strongly typesafe though, because the query-model can also contain invalid selectors,
because it can also come directly from frontends for example.

The Repository here is providing a static (always the same) Select Statements on multiple tables. In your solution you would dynamically build a different
Select, so your way to do this would be more performant and more specific, while this approach is more uniform but less dynamic.

I found your approach interesting, because a clean way to define "Fragments" and dynamically use them is surely helpful.

Bernd Huber

unread,
May 20, 2024, 7:54:28 AM5/20/24
to jOOQ User Group

> An example of 2) is the DAO API, which I regret so much. It is such a terrible bikeshed, being the only really opinionated thing in jOOQ. Sure, users like it, and it can't be removed again for this reason. But it is not well-designed. It was a "quick win," that will never implement the features users ultimately desire. Jakarta Data is making the same mistake right now by copying Spring's Repository and thus opening up endless bikesheds instead of truly innovating, at least in my opinion.

in my opinion, the DAO API is a very helpful template to start with creating an own DAO Abstraction, but i would not use it directly as it is, but copy it and change it to ones own needs. Its still better to have it then to not have it.
lukas...@gmail.com schrieb am Montag, 20. Mai 2024 um 10:14:05 UTC:

fabio trabattoni

unread,
May 20, 2024, 10:49:51 AM5/20/24
to jOOQ User Group
Mine is a smaller attempt. I didn't want to build a general query engine, I just wanted something that made sense inside a specific domain. The primary goal is to force the devs to act in a well-defined pattern.

The general apporach is very similar though:
1) Create a simple grammar in the form of OOP classes
2) Instrument an engine that translates 1) for the lower levels

fabio trabattoni

unread,
May 20, 2024, 10:49:57 AM5/20/24
to jOOQ User Group
Thank you for your thoughtful response, Lukas. I'd like to clarify a few points:

I don't consider my idea revolutionary, nor do I aim to build the next big framework from it. It was a simple attempt to solve a small problem.

While my idea resembles GraphQL to some extent, GraphQL is far more ambitious and comes with an "all or nothing" constraint. Additionally, I'm not a fan of the "querying by JSON example" approach, but that's just a personal preference. All I wanted is something that makes sense inside the domain: there are books and there are rentals, what do you want to load? And how?

I wasn't aware of Oracle 23c AI, so I'll look into it as soon as possible.

I did not intend to undermine or criticize jOOQ. I use it in every project, all the time. As you mentioned, jOOQ is designed to mitigate the impedance mismatch problem at large, not the simple traversal/filter issue I proposed. It only makes sense that you decided not to include this discussion as a new feature in jOOQ.

My critique of MULTISET was poorly phrased. MULTISET is a fantastic feature in jOOQ, possibly one of the best. However, it’s not a "pattern" in and of itself. Like any flexible tool, it can get very ugly very quickly if used by inexperienced developers. My idea was meant to act as a "cage" to enforce a strongly defined approach for developers.

In my opinion, and I don't want to play the Martin Fowler here, the impedance mismatch has won, database side. Almost every OOP framework/architecture that accesses databases is generally designed around inserts, updates, and selects. For instance, the classic layered architecture is driven by the DAO/repository layer, which often results in a procedural approach rather than an OOP one. Phrases like "composition is favored over inheritance" seem to have been coined only to somehow relieve us from the burden of this heart-felt truth. One of the reasons for the recent success of Hexagonal/Onion architectures, to name another example, is that they strive to bring us back to the fundamentals of OOP, don't they?

Ultimately, as you suggested, the best approach might be to build an opinionated API on top of jOOQ, similar to what I tried to achieve in my demo application.

Thanks again, and I apologize if I came across as aggressive or critical.

Lukas Eder

unread,
May 20, 2024, 11:57:02 AM5/20/24
to jooq...@googlegroups.com
On Mon, May 20, 2024 at 4:49 PM fabio trabattoni <fabio.tr...@gmail.com> wrote:
Thank you for your thoughtful response, Lukas. I'd like to clarify a few points:

I don't consider my idea revolutionary, nor do I aim to build the next big framework from it. It was a simple attempt to solve a small problem.

While my idea resembles GraphQL to some extent, GraphQL is far more ambitious and comes with an "all or nothing" constraint. Additionally, I'm not a fan of the "querying by JSON example" approach, but that's just a personal preference. All I wanted is something that makes sense inside the domain: there are books and there are rentals, what do you want to load? And how?

I wasn't aware of Oracle 23c AI, so I'll look into it as soon as possible.

I did not intend to undermine or criticize jOOQ. I use it in every project, all the time. As you mentioned, jOOQ is designed to mitigate the impedance mismatch problem at large, not the simple traversal/filter issue I proposed. It only makes sense that you decided not to include this discussion as a new feature in jOOQ.

That wasn't my impression, I just wanted to make sure it's understood what can possibly belong in jOOQ and what cannot. I mentioned the DAO because it has a very long history of feature requests where users wanted "just" one more feature inside of the DAO. Every time, the answer was: Don't use the DAO for this, write a query, often to the disappointment of said users. And that kind of shows it's a mistake for a library like jOOQ to engage in such opinionated utilities, even if they are perceived as "useful templates" as Bernd called it.
 
My critique of MULTISET was poorly phrased. MULTISET is a fantastic feature in jOOQ, possibly one of the best. However, it’s not a "pattern" in and of itself. Like any flexible tool, it can get very ugly very quickly if used by inexperienced developers. My idea was meant to act as a "cage" to enforce a strongly defined approach for developers.

But jOOQ doesn't want to enforce cages. jOOQ is a collection of operators and statements, none of which have any weight or opinion attached to them. jOOQ isn't opinionated about almost anything.
Don't care about 1NF? jOOQ doesn't care, it can work with your database. Do everything in 6NF? jOOQ doesn't care, it can work with your database.

What users want to do, is up to users, of course.

In my opinion, and I don't want to play the Martin Fowler here, the impedance mismatch has won, database side. Almost every OOP framework/architecture that accesses databases is generally designed around inserts, updates, and selects. For instance, the classic layered architecture is driven by the DAO/repository layer, which often results in a procedural approach rather than an OOP one. Phrases like "composition is favored over inheritance" seem to have been coined only to somehow relieve us from the burden of this heart-felt truth. One of the reasons for the recent success of Hexagonal/Onion architectures, to name another example, is that they strive to bring us back to the fundamentals of OOP, don't they?

I wouldn't know. Luckily, I've been out of these loops for ages 😅  I've always found software architecture to be art for art's sake...

Ultimately, as you suggested, the best approach might be to build an opinionated API on top of jOOQ, similar to what I tried to achieve in my demo application.

 It can obviously work for you, and I'll be very happy to discuss any missing operator or missing convenience that helps you build this API more to your liking.

Thanks again, and I apologize if I came across as aggressive or critical.

Not at all!

Lukas Eder

unread,
May 24, 2024, 10:09:43 AM5/24/24
to jOOQ User Group
There has also been an idea in the past to allow for other relational operators on arbitrary table expressions, such as Table.select() but it was rejected because the time wasn't ripe for such an operator yet, at the time:


For the record, I'll revive that feature request. Given how useful and powerful inline derived tables have been in jOOQ 3.19's implementation of policies...

... I'll definitely look into adding more power to this area again for 3.20 or another future release
Reply all
Reply to author
Forward
0 new messages