[setr]

The problem with ORMs are

1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result

2. They leave awkward holes in their abstraction, leading to psychotic behaviors like N+1 and implicit type coercions to helpfully break your indexes silently

3. They make simple queries simple, and hard queries absolutely revolting

4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking

5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways

6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB

The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset

[nodamage]

Which ORMs are you basing this comment on exactly?

(1) and (3) are not really problems with an ORM that gets out of your way and lets you drop down to raw SQL when necessary, but still helps you hydrate result rows back to objects (and still provides the associated features I mentioned previously).

(2) and (5) can be interpreted as "your ORM does not absolve you from knowing SQL".

I've never personally run into a situation where doing (4) or (6) were desirable.

> The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset

If you work on projects where a full featured ORM can be replaced by a simple query builder then cool, but the rest of the feature set is really useful for the projects I work on so why would I toss them out?

[BlackFly]

I agree completely, the features the parent is mentioning like "identity mapping, unit of work, and change tracking/events" are exactly the things I don't want out of the ORM because that is the leaky abstraction I don't want to constantly be working with and around.

If it was just a query builder we could have a conversation about the benefits of that vs sql and when one beats another. But it is all kinds of other features that are implicitly activated and then conspire to ruin your day when you were trying to solve some other problem. ORMs bring too much baggage by default. So now you have to talk about its relative merits compared to just writing SQL and the merits of always having these other features activated. Which other features? You need to read your full ORM manual because they really vary from one to another.

[nodamage]

Your comment comes across like saying "why would I use an impact driver when my screwdriver does everything I need?"

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.

[bb88]

I have list of issues with SQL. Not composable. Unable to detect query errors at compile time because the schema is only loosely coupled to the code base. And as you yourself point out, SQL is not standardized, which is also terrible and leads to things like Oracle vendor lock in.

And frankly this list hasn't changed in 30 or maybe 40 years now.

And DBA's were so notoriously egregious that Martin Fowler made his "NoDBA" blog post over a decade ago now. And the movement to NoSQL definitely made things worse.

I wish the SQL community would stop treating ORM's like the vietnam paper did 20 years ago, and embrace them for what they are, as a stepping stone, and maybe as a useful tool to help people understand SQL itself.

[simondotau]

> as a stepping stone, and maybe as a useful tool to help people understand SQL itself.

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.

[nodamage]

I mean there are plenty of projects that don't fit this description, where the database is just a persistence layer for your objects and the database should be subservient to application code.

[watwut]

I find the 1, 2 and 3 to be complete non issues. As in, they dont exist as issues for hibernate. If you feel like particulat query is oh so difficult, you can always use sql for that one part.

4 is in the "like so what" category.

5 - it optimizes alright for average case. You have to optimize in edge cases, but then again, you have to optimize edge cases with pure sql too.

6 - no I dont want much inheritance in db whether i am using pure sql or orm.

[mexicocitinluez]

> 1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result

EF Core is provider-specific and also exposes provider-specific functionality.

> 4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking

This just isn't true for EF Core. https://learn.microsoft.com/en-us/ef/core/performance/effici...

> 5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways

Not universally true either. You only have to review complex queries. If you're making claims about a specific ORM it would be good to mention it as it's not universal.

> 6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB

Then don't? Since when is inheritance required for ORM-usage?

I have found a lot of the anti-ORM critiques come from either using a crappy ORM or having not used a good one in the last 5 years.

[cpursley]

Best to avoid OOP jungles in general.