I think it's bc of how popular ORM's are. You can get pretty far with an ORM without knowing any SQL, but you can inadvertently do some really dumb things if you don't understand how SQL works.
SQL was the first language I ever learned, so perhaps my perspective is skewed, but it seems like it would be pretty hard to use an ORM without gaining an intuition into how SQL works.
What is a trap I see often among ORM users is them running into the n+1 problem. However, that's actually a consequence of database implementations not being theoretically pure. If you had an ideal SQL database then their approach would be technically correct. They aren't wrong to think that way.
It's just that we don't have ideal SQL databases, so we have to resort to hacks to make things work in the real world. Why we need to break from the theoretical model and use those hacks can be difficult to understand if you aren't familiar with the implementation at a lower level.
"Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy." (Ted Neward)
Remember an ORM is an object-relational mapper not a query builder. The "simple stuff" is repetitive, extremely common, straight forward, and boring -- exactly the sort of thing you want to automate.
> Yeah, but when you add/change a new property to your most used class you don't have to change 500 odd SQL statements, it just works.
ORM doesn't have anything to do with SQL statements. ORM only operates on the results those SQL statements produce.
You're probably thinking of the active record pattern, which combines ORM with query building into an interesting monstrosity. Indeed, query builders produce SQL statements and can save you from changing 500 odd raw SQL queries.
A lot of ORMs create SQL statements too, they have an integrated query builder. And functionality for updating, deleting and even creating/modifying schemas (migrations).
But as always: use the right tool for the job. A hammer is a great tool, but not suitable for removing screws (most of the time).
> A lot of ORMs create SQL statements too, they have an integrated query builder.
These are technically active record libraries. One of the popular ones is literally known as ActiveRecord, but the whole suite of them are implementations of the active record pattern.
ORM is simply the process of converting relations (i.e. sets of tuples, i.e. rows and columns) into structured objects and back again. This layer of concern is independent of query generation.
ORMs are not only great for simple stuff. They are great for a lot of complicated queries too. But as always, you need to know what you’re doing. An ORM is not a magic layer, that solves all of your problems.
I’m mostly using entity framework core, which allows you to do quite complex queries, which can be really easy to read and maintain. But if you’re careless, you can create quite slow queries, that’s clear. But you can easily create slow queries with plain SQL too.
Yes, I’ve found that it’s possible to write hundreds of lines of ORM code to do some very complex and sophisticated processing, that could be done in a dozen lines of plain SQL.
The ORMs I’ve used also needed every entity from the database to be duplicated in the host language, which is a horrific amount of duplication in any non trivial project. Not just the entities but also the relationships. And then they need to be kept in sync.
And then they have these awful and subtle caching issues that mean if you fall back to SQL then you take a massive performance hit. Or if you run multiple servers against the same database you need another layer of complex caching infrastructure. Or if you update a table outside of the application you can get inconsistent results.
The whole thing is a nightmare of unnecessary complexity apparently intended to hide developers from the much lower complexity of databases.
ORMs are an inefficient, leaky and slow abstraction layer on top of a fast and complete abstraction layer.
I’ll never willingly use an ORM again.
Edited to add: the whole fragile contraption is done in an effort to move slices of the database into the memory context of a remote application. To what end? So we can update some counter and push the whole lot back out? It literally makes no sense.
There are two ways to achieve a single source of truth:
1. Generate ORM entities from the database (usually there is a too for that)
2. Generate the database from ORM entities, and make changes via generated/manual migrations
You are supposed not to edit the generated side, just re-generate it on a change of the model.
I don't think it's the syntax of SQL that causes problems (and which ORMs try to replace)... it's the complex underlying logic necessary to accurately join multiple tables, set where and having conditions, and aggregating records, and knowing to which dataset each should apply.
Countless "replacements" for SQL have come around to make data accessible by non-expert, but regardless if your using a BI tool, reporting platform, ORM, whatever, pretty soon you're going to need to understand and express the logic, and that's when you really appreciate SQL's directness and terseness.
Every single org I've found that says "we don't use ORMs because they're slow" ends up constructing their own bespoke, wheel-reinventing "orm" or sticks all their application logic in sprocs, which are nightmares for imperative logic.
