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’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.