A now defunct site discussing why ORM is a poor map.

Admittedly, this doesn't end up being great, but it seems hard to solve this well in other ways, as much as I wish I could write SQL and get types for free.

Every time I tried to do a project without an ORM, using only raw SQL, I inevitably ran into:

- serialization/deserialization boilerplate. Like, having to manually map values returned by the DB library to object (or named tuple, or structure) properties

- poor code reuse, having multiple very similar queries that have just one small difference

- extra pain in changing DB schema. Adding a field requires to go and manually edit many queries

Anti-ORM crowd never gives a good answer to these issues.

Instead, they push strawman attacks like "oh, you only use ORM, because you can't write raw SQL". I can absolutely assure you that this is not the case. Every time I use an ORM (SQLAlchemy mostly, the one mentioned in the article) I am 100% sure what SQL do I want it to produce and what SQL will a particular ORM invocation produce.

Can the OP expand on why this is? Just curious.

For example, the ASP.NET Razor syntax allows HTML and C# code to be interspersed surprisingly freely:

    <ul>
    @foreach (var user in Model.Users)
    {
      <li>@user.Name</li>
    }
    </ul>

Just like how Cargo, NuGet, NPM, etc... can import packages and/or how you can cross-reference projects in build systems, web apps should be able to reference a database schema project directly, importing the SQL definitions without any explicit "mapping". If the SQL changes, the type changes, and the build system picks that up automatically without any additional manual steps.

.NET with EF Core is almost there, and I've seen some half-hearted attempts in various languages over the years, but it's like the industry has an allergy to the concept.

Ur/Web is probably the closest to the idealised concept, and I think that's what I read years ago that put the dream in my mind: https://dl.acm.org/cms/attachment/feb131ab-37e1-4638-be17-ab...

There is nothing that an ORM can do to help with this sort of problem without reaching for the obvious escape hatch of arbitrary command text execution. The ability to map the tables to objects in my programming environment is a distracting clown show for this specific problem. What really matters is understanding the provider and its techniques for bulk loading records. No ORM will ever be able to touch these provider capabilities on their "happy" paths. At best you'll wind up using the ORM and a bunch of provider-specific SQL anyways.

ORMs for schema management is a stronger argument, but only in cases where the codebase/service has complete ownership over each respective database. Any kind of heterogenous workload says that ORM for schema management is a potential nightmare unless you do something like create a project that is only for migrating the schema, at which point I'd argue you could just maintain a source controlled folder of sql/shell scripts.

So I think the ORM debate could be over

postgresql is a beast

NoSQL for operational data storage is more efficient and cost effective.

ORMs were a regression test that exposed unnecessary complexity.

That's important. Because now days it's trivial for LLMs to translate ORM to SQL and vice-versa with ~100% accuracy. I haven't written any raw SQL (only Active Record) in about two years, and the odd time I blunder with AR and create an n+1 I find out about it via error tracking (e.g. Sentry) a few minutes later and fix it. No biggie.

There's also an additional layer of protection in that using AI on the codebase can spot SQL blunders incidentally (i.e. you ask about X, and the AI does X but also says "Not asked, but flagging for your attention: problem with SQL on line 256 etc.."

Relational databases are incredibly flexible even if you have a NoSQL mindset, you can do data modelling like that in Postgres too with jsonb data types.

I would challenge you to look at event driven architectures, CQRS, event sourcing, and how to implement and leverage read models.

It will expand your architecture toolkit.

ORMs that try to paper over all the differences fail miserably. They become super complicated and generally produce crap SQL.

ORMs also tend to oversimplify database design. They are just tables with primary keys, right? Who needs indices? Who needs to think about collation? God forbid anyone mentions physical organisation of the data!

Having said this, I do use a very small subset of SQLAlchemy (the bits I understand) in data pipelines.

I make use of table-valued db functions (IMO the most underrated feature of relational DBs) to define virtual relations/tables. I implement a set of CRUD db functions per entity. Then, on the app side, I define (or generate) DTO types representing these virtual relations. Finally, I use a custom ORM I wrote myself, which defines a general and consistent storage API, to talk to the db functions, using the DTO types.

The advantages of this approach are numerous, some include:

- I have full control of the SQL that goes into constructing the virtual table, I can leverage all the goodness of SQL here. I can even define multiple virtual relations per physical table, or read-only relations, etc, all by implementing the appropriate sets of CRUD db functions

- On the ORM side, I have all the goodness of static typing, a consistent API for all CRUD methods, a full fluent query DSL, etc

- Since, unlike tables or views, db functions can be passed arguments, i am able to layey all kinds of goodness on top of the basic CRUD actions, like audit info passing, custom upsert strategies, some level of record-based authorization, etc

But this architecture does require you to know and write SQL. IMO the value of ORMs do not lie in avoiding SQL; it lies in the capability to express consistent SQL at a higher level of abstraction, but you still need to understand your SQL.

PS: I still exported the table before deploying this fyi.

I stopped using ORMs around 2008 because they made the easy problems easier and the hard problems harder. I wanted to just write SQL and exploit all the power the DBMS has to offer instead of fighting with an abstraction layer, so I created Pyranid in 2015 and keep it actively updated.

We have lots of experience with ORMs based on dynamic languages (i.e. Objective-C and Ruby) and if not careful, you can indeed go sideways pretty quickly.

Recently, we've been using https://ash-hq.org. It tries to solve the same problems as an ORM, but using a pure functional language (Elixir). You are using structs instead of objects, so it can feel very close to using raw dictionaries/hashes. It also makes it super easy to drop down to raw sql, while maintaining that struct interface at the top.

While it does take some getting used to (especially coming from a dynamic, OO language), I'm liking this alternative a lot!

I haven’t personally had third-party ORM frameworks succinctly encourage synchronization and help me build against long-term divergence of data models across the stack. “Make it easy to do the right thing and hard to do the wrong thing” still leaves a lot of room for ‘gotchas’ as apps evolve over time.

Finally, I don’t understand the aversion in learning even a bit of sql. As topics go, it’s a very good (maybe even the best, if I were being provocative) effort:payoff ratio. Not sure I’d call myself a sql expert, but am always pleasantly surprised how much functionality is within reach by knowing even the very basics of sql.

For me I find it's an excellent step up from a plain SQL query builder (with an API such as `select(Foo).join(bar)`) as it lets me both effortlessly perform projections (one can write `(\e -> (e.foo, e.bar) <$> someQuery` to take a query producing rows of `E` and turn it into rows of 2-tuples built from two projected fields.

I wrote a bit about my Rust rewrite here: https://bensimms.moe/postgres-lateral-makes-quite-a-good-dsl...

Implying I use an ORM because I don't know SQL... I've reverse engineered embedded databases and written directly to the .dat files on production systems that deal with HIPAA data. I'm pretty sure I know SQL better than most people on HN. I still prefer an ORM.

Why? Because with my ORM, I can code gen faster than you can vibe code. I can build on top of the abstraction layer. The data model in the ORM is the M in MVC. The backend could be a SQL database, a file system, a REST service, that part is irrelevant. The M is the same, regardless of the backing store. View and Controller code still works.

I find most people who are anti-ORM are kinda junior and trying to flex their power to write SQL scripts as if it is impressive. That's why there's always this weird implying that ORM users don't know SQL.

These are simply tools. The only wrong opinion is to believe that there’s a strict superiority of one over another. However, the content of this and other blogs can help people make informed decisions on when to reach for each tool.

The generator takes a single specification and produces everything needed for the server, client, and databases (SQLite, Oracle, in-memory, etc.) to stay perfectly in sync.

It has worked really well for us and has been a huge productivity boost.

The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.

- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?

- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!

Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.

- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.

- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.

I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".

- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.

Disclaimer I am sure I won't change anyone's minds :-)

Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.

I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)

[1] https://joist-orm.io/

[2] https://joist-orm.io/modeling/why-entities/

> ...(although things like Postgres’ hstore can help)...

Back when this blog post was written, this advice would have been reasonable. Today, I don't know anyone reaching for hstore since the more featureful json support was added.

I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.

ORMs are just a layer of abstraction. Like any abstraction, they make some tradeoffs that can get you into some sticky situations like inefficient queries mentioned in the article.

But, if you understand the tradeoffs, you can use them for what they're good for (standardization & simplification & in-codebase schema definitions & so on) and usually drop down to SQL whenever there's a particularly necessary case.

We do programmers always need a library?

Program the damn thing.

Can anyone that has used ActiveRecord share their opinion?

Even when using other languages, I just pine for LINQ/EF Core. It's truly the best ORM in my opinion. Also, even if one does not want to use the LINQ or the Query syntax (I forgot what it was called), the ability to execute SQL is also still a game changer.

By your definition PDO would qualify https://www.php.net/manual/en/class.pdorow.php

Here's a full report for you https://gist.github.com/hparadiz/a1fe30e88dbbe070878a7ea4f72...

Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.

But this gets to the heart of what I was saying. I'll grant you that ORMs save a little bit of boiler plate up front (but not much - ORMs have plenty of their own boiler plate, just instead of a universally understood language like SQL they have it in their own custom config JSON/yaml/XML), but that is where I spend a teeny fraction of my time coding. Writing "boilerplate" SQL for a decently large project (say 50-100 object types) takes me maybe an extra day in coding time. I have wasted multiples of that time trying to track down a single weird ORM bug, or poorly performing query. Plus, spending that time up front to write my queries is always the least stressful time of the project. What is most stressful is when my site is finally getting a big traffic push, but then something causes the DB to crater and the leaky abstraction of the ORM makes it ten times harder to debug.

But that's just me

Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?

I get the first part, but not the second.

Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.

> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.

Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.

There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.

Totally agree. Views as a data API is the best way to take advantage of the facilities that the database itself offers and guarantees enforces consistency across disparate clients.

Ah yes, the famous database integration anti-pattern.

> but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.

[citation needed]

The fact that you have being practising "database integration" won't suddenly disappear just because you used a ORM. In fact I expect even worse database integration from your average ORM user, as people that uses ORM blindly often don't care (to their own detriment) about "silly issues" like data provenance or persistence mechanical sympathy.

At some point I expect the DBAs of such database integration nightmares will have to start handling stuff like column-level security and row-level security to prevent naive users from shooting themselves in the foot.

I am not using ON DELETE CASCADE to be clever - the referenced data is genuinely required.

I think ORMs mostly exist because most programming languages tend to lack an elegant way to write SQL and interact with results. Somewhat ironically, the much-maligned CFML (aka ColdFusion) got this right decades ago. It made SQL string building trivial, and it provided a native data type for tabular query results.

No other language I'm aware of has this, and it's the missing piece in many modern ecosystems. They do not need an ORM. They need better ergonomics for interacting with databases: a clean way to compose queries, execute them, and work with the result as structured relational data rather than shoehorning it into application objects.

Relational databases can represent graphs, and graphs naturally have relations, but in your OO language you can make choices about how to traverse an object graph based on external state, and such traversal is incremental and dynamic. Relational databases can have recursive queries, and these can be used to traverse graphs, but the shape of the query has to be known up front. Recursive queries can be dynamic over database state, but not over arbitrary external state. Even assuming some incredibly deeply integrated super-ORM, it's easy to imagine how programs that operate on graphs _and_ can be automatically mapped to an efficient set of relational queries are a limited subset.

This is the fundamental object-relational mismatch. You can use escape hatches, or you can contort your code, but every time you do this, you have to accept that you're no longer "mapping" in the transparent sense that ORMs were supposed to provide.

I think probably the easiest way to get an intuitive sense for the problem is to consider a simple object graph model:

    User {
        name
        friends: List<User>
        posts: List<Post>
    }

    user.friends[0].friends[0].posts

Now, how do you create an object that is backed by a relational database while still transparently letting you perform object-graph traversals such as the one above? It's easy to see how `User` would need to be an object with a `name` field. Since the data is recursive, you probably don't want to eagerly load all friends and posts, so you'd have proxy objects that make additional queries when you access them.

It's easy to see how this leads to the classic N+1 style issue. You have your user, you load their friends. Maybe your proxy object is smart enough to only load only their first friend. You end up making a bunch of additional queries after the first one to load the user. Especially when your database is on a disk and large, or accessed over the network, you can see how this quickly gets out of hand.

In the object/graph model, the relationships are _internalised_. They're represented _within_ the object. But in the relational model, relationships are external. To "map" from one to the other efficiently, you can't just represent things as objects with some glue, because you keep running into these "look ahead" issues. When you access user.friends or even user.friends[0], your mapper has no way to know what you're going to ask for next.

Of course, one way to solve this would be with deeper integration or a DSL. Let's say you had a query language which can represent the above query, and then you analyse this query to try to map it efficiently to a relational query. Sounds like we've solved the problem? Well kind of, yes. Except we're no longer mapping the object model to the relational model. A given query leaves you with dead objects, you've just delayed the problem while leaking abstractions. You can add proxies to those but you're now back to square one except you've maybe improved performance a little bit.

If you don't actually need those features then obviously an ORM will offer less value to you. That doesn't mean ORMs aren't useful tools, they just aren't useful for the problems you work on.

I tend to work on projects where those features are useful and if the ORM didn't provide them out of the box then I would need to build them myself. In other words using a query builder alone does not adequately solve the problems I need to solve.

TLDR: Tooling pressure is a design pressure.

Not much can beat stored procs when it is dealing with multi-step heave volume stuff. But I don't miss not having to do hacks for logging and debugging compared to the flexibility offered by non-db side.

For pretty much everything else, the poor ability to log and debug makes them a headache to manage. I

Usually it's due to one of these:

- The domain deals with a lot of things that are not in the database.

- The domain is one of many and deals with just a fraction of what is in the database.

- The domain deals with things stored in several databases.

- The database was designed in the 90s and the domain is new.

- It's not my database so I can't change it.

(Even for greenfield systems I don't think it's generally desirable that the database matches the domain model.)

    User {
        name
        friends: List<Friend>
        posts: List<Post>
    }

I now do:

    user = get_mapped_user()
    for post in user.friends[0].friends[0].posts {
        ...
    }

What happens when I access `.friends`?

Does it give me an empty list, because I didn't ask for it?

I am not aware of anything that calls itself an ORM which merely does:

    user: User = map_from_relational_to_user(query_user())

ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database. The main distinction between them being in the availability and first-class nature of the escape hatches when this operation inevitably becomes slow.

But that is not what ORMs are. They teach bad habits that make SQL harder to understand, not easier, because the power of SQL depends on good data modelling.

Perhaps the worst habit is treating the database as subservient to the application code. This assumption comes naturally to many programmers. In most programming contexts, file formats, wire protocols, and internal representations are defined by the code that consumes them. That's fine in some cases.

But in a data-centric application, the relationship should be reversed. Before writing a single line of application code, you should understand the domain model and design a schema that represents it well. The database is not just a persistence layer for objects. It is the system of record, and its structure should reflect the shape, constraints, and relationships of the real-world data. Everything else should be built to conform to it.

But a close second is that it encourages composition in situations where duplication is the right choice. Having your sql query spread across 7 files makes tracking down bugs and performance issues (and fixing them) incredibly difficult.

Obsessively cramming tabular data into objects is often unnecessary, and it bloats the code downstream of the database query. It then encourages the bad habit of performing data manipulation in code rather than directly in the database.

"Fetch related objects" is a code smell. If any related data was needed, your original query should have already fetched it.

In my experience, these are typically best turned into views (or materialized views), because they represent some fundamental relationship or property within the data that’s useful to be able to quickly reference or query directly against. KPI aggregates, for example.

Without an ORM you have to write a bit more boilerplate code to interact with the database. But by taking advantage of the power of your database engine, you could potentially avoid writing huge amounts of data manipulation logic. In my experience, an ORM is more of a code amplifier than a code simplifier.

DTOs are one of the big code smells of a code base that does little but will be full of boilerplate. As soon as you see an automapper or a folder of DTOs you know you're in for some serious pain.

On the plus side you also know you can reduce the codebase by about 75%.

Even if you think that maintaining your domain model is easier in SQL (it’s not, for most full-stack engineers), the extra capabilities you get from an ActiveRecord framework such as full-stack admin pages, free migrations, etc. win overall.

I can believe that the gap is closing with the “api for your Postgres” frameworks but really, try reaching your frontend developers sql and see if they have a better time than learning Django/Rails.

The specific danger of CRUD is that all operations are expressible in it. If your system is CRUD, everything goes. A developer who doesn't understand the system's design might be inclined to assume an application is "just CRUD" and add all sorts of misfeatures to it that violate otherwise constrained states. They will turn the application into CRUD.

All it takes for an application to go from carefully modeled to CRUD is for people to believe it already was just CRUD.

The only good thing about ORM is the type safety, but I find rust's sqlx or java's jooq to be hitting the sweet spot.

That said it’s still my most frequent cause of crashes, however I think mostly it’s just because this is simply a hard problem that SQLite just isn’t cut out for (although it did take Apple until macOS 27 to supply a codable decorator grrr).

Ideally databases could evolve to fit OR mapping more closely, which incidentally is what Arrow and Parquet have done to an extent.

I mean think about it, creating a single row is CRUD, retrieving a row by ID is crud. Retrieving all the rows that belong to a user with pagination is crud, updating a specific row by ID is CRUD, deleting a specific row by ID is CRUD.

You have a settings page? CRUD

User profile? CRUD

Application form with more than 120 input fields, complex tables, split across multiple pages? CRUD

Heck I have a version of that where you get to nest multiple sub application forms into a single application with absurd amounts of nesting and it is still CRUD.

Most cases that aren't CRUD tend to be niche cases.

I mean think about it. An extended search feature that has hundreds of options is still CRUD and tends to work much better with an ORM since the query builder dynamically builds the SQL query for you instead of messing around with a static SQL query with a massive amount of feature flags.

The cases where you don't have CRUD are the rare cases. Things like reporting, batch jobs that process multiple rows at once or reconciliation that tries to find the differences between two databases.

Maybe it's not clear, but the arbitrarily complex application logic obviously is not written in SQL so even if the application is more complex than a straw man CRUD example doesn't mean that the database sees something more complicated than row creation, retrieval, updating and deletion.

...and Django was like this 15 years ago when I first started using it. The core design hasn't changed, it just sounds like most other ORMs don't really know what they're doing.

What does "bare bones ORM" mean? That seems like saying "bare bones sort", but like sort it seems to me like it is either something that happens or something that doesn't happen. You either map objects and relations or you don't. Are you imagining that there is some way to partially map relations and objects but somehow not go all the way? I admittedly cannot picture what that would look like. What would the purpose be?

> SQL is a query language for relational databases.

No. SQL is not for relational databases. This is most obviously observed by the fact that SQL is centred around tables instead of relations. That naming isn't just a marketing gimmick. Tables are technically different from relations. Codd, inventor of the relational model, spent a lot of time writing about why SQL isn't relational if you want a more in-depth technical explanation, but suffice to say that ORMs and SQL are not directly compatible. Although obviously they can work together if you layer in additional functionality. You can make any data shape work with another if you provide some kind of mapping between them.

Most of those are not necessary for 90% of use cases

I'm not taking the piss either

All most people really need to know is table CRUD, row CRUD, and a bit about indices.

For anything more advanced you'll need a DBA, but IMO you unless you are scaling like crazy you will not need much more than that for SQL knowledge. It's really, really not that complex for most use cases

> I claim nothing about "bare bones", so, again, you must clarify what you mean by it before I can do anything with it.

I shouldn't need to spell this out, but I am not insinuating anything negative or positive about your approach by calling the description you gave initially as "bare bones". I am simply using "bare bones" as a shorthand to refer to your definition of ORM.

You are claiming that implementing an ORM doesn't require query building, I am claiming that it does. I am calling your proposed ORM that doesn't require query building "bare bones", just as a shorthand to avoid saying "ORM that doesn't do any query building". I hope this makes things clear for you.

> That's up to the implementation to figure out. ORM isn't a specific algorithm. Is that the source of your confusion?

We are talking about _an_ ORM. As I clearly stated in my original comment, and as is being described in TFA. _An_ ORM is a tool which presents a "relational" database's (e.g. PostgreSQL, SQLite, MariaDB, ...) contents as an object graph. With the _external_ relationships (as in the relational model) modelled as _internal_ relationships (as in object graphs).

I am not talking about abstract "object <-> relational mapping" in a vacuum, I am talking about the specific thing that people mean when in 2026 they say "I am using an ORM".

> Whereas SQL does not.

SQL centres around bags of tuples, so you are indeed right that it's not quite truly relational, as was Codd, as I acknowledged. But that doesn't make any impact on what people mean when they say "ORM" in modern day programming parlance. So please stop appealing to an irrelevant definition.

> Relational databases are natively relational, not just able to represent relations.

What do you think this even means? The fact that SQL has bags of tuples just means that if you want it to follow a relational model, you must merely ensure that you enforce these bags to be sets. At this point the remaining complaints by Codd don't have an impact on the fact that, insofar as you don't hit those specific niche limitations, you are faithfully representing a relational model at least in your data.

Relationships in the relational model are not internal, they are external. This is one of the reasons for the OR mismatch. It's counterintuitive that object graphs represent relations internally, and relational models do so externally, at least when phrased like that, but it's inherently true.

The fact that graphs represent relationships also doesn't make graph databases or object databases relational. I would have assumed this was clear from what I wrote so far, but as I already established, I suspect you're not actually reading half of what I am writing, so it maybe isn't so surprising that you think I am ignorant of these things.

If I only read select paragraphs of what Codd wrote, I might also think that he was ignorant of his own model, but instead I kept reading what he wrote until I was confident in my understanding of it. I suggest you do the same if you wish to have a productive conversation on any topic online.

I have a lot of fighting against Spring / Hibernate for this. It doesn't let me do cartesian product because the query builder thinks it needs a cartesian product, I can't select only some of the columns without making a whole new data structure while fighting the ORM that thinks one table = one class, can't query from a joined table.

I don't have any issue with mapping using these query builders like jooq which lets you use generated class from the live db for simple use case and give you other mechanism for querying weird joins or aggregates.

What sort of perks of ORM after the query has returned do you find to be helpful?