[bendiksolheim]

There is a tradeoff here, as usual.

The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.

[jnsie]

But any ORM I have ever used supports raw queries. So, for boilerplate (which is often a significant portion of queries) the ORM does the heavy lifting, and for everything else you have the power to write the SQL yourself. This is something commonly buried in ORM discussions and I never understand why.

[mgkimsal]

I think it comes from experiences of coming in to codebases and seeing that people are using the ORM for everything, even when it's obviously suboptimal and raw SQL would be better.

Compromise, best tool for the job, nuance, etc - all tend to get tossed out when people make tech decisions. Someone using an ORM may not even be aware that raw SQL is possible.

[bendiksolheim]

I wouldn‘t say its buried, at least not in the discussions I have been in, but the problem is that even with custom queries you are still in this weird place between fully managed ORM queries and raw SQL queries. How does the result map to data structures? How does it handle colliding column names in joins? Does the ORM handle over complete control when using custom SQL? There are tons of questions like these that are not uniformly answered by different ORMs.

I am not saying that ORMs never work. They sure do in a lot of cases, and some ORMs more than others. But an ORM is not a silver bullet – far from it. There are gotchas, and you need to invest time in understanding how they work. Just as you need to understand how SQL works.

[jordanab]

It's not just 'simple CRUD apps' vs 'advanced apps', I find it's also language/ecosystem specific. I've worked on Java and .NET projects (typed languages) where ORM's are a fact of life, and actually make it a lot easier to navigate & refactor your codebase. I've also worked on Python and Node.js projects, where ORM's often did not significantly improve the developer workflow (navigation, refactoring etc.) because of the lack of strict types in these languages, and ORM's and their usage were therefore a point of daily team discussion.

[ggregoire]

Exactly this.

In Python/Node: I'd never use an ORM for handling 5 tables. I'm just fine writing/maintaining my 5-10 queries in plain SQL. Centralize all the queries in a single file and that's it.

In Java/C#: Do I even have the choice? Even for 1 table it's probably easier to let the ORM do its job than fighting the typing system.

[pmontra]

This and the infamous question "how do I write this complex SQL query in ${ORM}?"

I ended up using raw queries in many of the projects I worked on for more than 2 or 3 years. It's more cost effective than trying to find the right combination of ORM statements.

[arcturus17]

Yes, but as I say in the sibling comment, could this not be the best of both worlds?

[pmontra]

Almost. The best of both worlds would be an ORM that can understand the SQL query and populate its objects/structs/whatever with the result. I have to manually process the results now.

If I had an ORM like that maybe I would write all SELECTs directly in SQL.

What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.

[arcturus17]

For complex data models, I find the ORM provides me with a lot of help in the form of IDE hints. I find the ORM schemas also provide documenting/referential value with less code than the raw SQL equivalent.

For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.

[ggregoire]

> When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution.

Perfect solution to what problem?

[Scarbutt]

Yeah, I'll will say plain SQL is even simpler here.