[Charon77]

Agreed. Simple CRUD is something that only shows up in the beginning of the project, everyone was told to use ORM for that purpose, business grow, and you had awkward requirements that require complex ORM features which might exist but requires deep dive into ORM library's corner case, or just straight not possible and makes you bang your head wishing you'd write SQL instead where it would have been obvious what to write.

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.

[dsego]

But SQL is low level, eg. you can't dynamically pass in filters and construct statements without knowing what the query will be ahead of time. ORMs have query builders that allow you do dynamically construct SQL statement based on parameters, they allow avoiding N+1 queries by doing joins in memory and much more. It's just not possible with vanilla SQL unless you concatenate strings or have multiple versions of the same query for every situation. A good ORM is an abstraction layer above that gives you more powerful tools, it's like comparing high level OOP code vs machine code.

[simondotau]

Query builders are a doddle to write, extremely trivial to debug, and generally far easier overall than having to shoehorn data into a structure that your ORM likes.

The problem is not that ORMs fail to expose every feature of a particular SQL database. The problem is that they encourage you to model your data in a way that is convenient for the ORM, rather than in a way that is correct for the domain.

Any sufficiently powerful ORM eventually has to provide escape hatches into SQL. At that point, the abstraction has failed: the ORM is no longer helping you understand the database, it is getting out of the way so you can use the database properly.

An ORM is a straitjacket. It pushes you toward sub-optimal structures, and those structures deny you access to the most powerful aspects of SQL: relational modelling, constraints, joins, aggregation, views, transactions, and set-based operations.

[dsego]

It seems like you are throwing away the baby with the bathwater. I don't think providing 90% of the structure you need is a failed abstraction. And it just doesn't follow that it is pushing you towards sub-optimal structures, not sure where this conclusion comes from. All ORMs I've seen have ways to describe relations between models, even polymorphic types, aggregates, eager loading (to avoid N+1) etc.

[ElectricalUnion]

> I don't think providing 90% of the structure you need is a failed abstraction.

It is, when the "10%" is the actual hot queries that your system will use the most?

Code right now "is so cheap". You can provide your favourite LLM with your database schema, and some domain comments, and ask it a query to fetch/update data, and it will generate somewhat sane queries for you. You can then inspect those queries yourself, send them to another LLM or human to review and, when they look OK, ship it.

And when it comes time to debug it, you have, you know, an actual query, not some pseudo-query in a custom DSL. No need to implement runtime telemetry just to try to figure out if the ORM actually made the query you thought it was supposed to do.

[nodamage]

Even if you replace ORM generated queries with hand generated queries or LLM generated queries you're still missing a huge chunk of functionality provided by an ORM.

For my projects I would say that the majority of the value an ORM delivers occurs after the query has returned from the database.

But for some reason everyone focuses on query generation as if it were the only feature of an ORM.

[Charon77]

"after the query has returned from the database"

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?