It depends on what you're writing. I've seen enough projects writing raw SQL because of aversion to ORMs being bogged down in reinventing a lot of what ORMs offer. Like with other choices it is too often a premature optimization (for perf or DX) and a sign of prioritizing a sense of craftsmanship at the expense of the deliverables and the sanity of other team members.
Same thing for people claiming they don't need any frameworks.
It became clear that each developer who'd worked on the code had written their own helpers to avoid direct SQL. It took a fair bit of persuading leadership, but the first task ended up being doing a huge reactor of everything SQL. Unsurprisingly enough, lots of bugs got squashed that way.
Forgive my ignorance, but how do ORMs help with adding caching? Or are you implying they obviate or reduce the need for caching?
Why is caching not a feature in DB connection pools? I mean, most databases have it on their side, why not have it as an option for the same query sets prior to hitting the db, with configurable invalidations? Or is it, and I've just never thought to look for it.
Example: if you execute "UPDATE orders SET x = 5 WHERE id = 10", the connection pool has no idea what entries to invalidate. ORM knows that since it tracks all managed entities, understands their structure, identity.
But this is not what people usually call as ORMs. All the "bad kind of ORM" (JPA impls, Entity Framework, SQLAlchemy, Doctrine, Active Record...) have some concept of an entity session which is tracking the entities being processed. To me, this is a central feature of an ORM, one of its major benefits. It is, incidentally, also serving as a transaction-scoped cache.
I won't of course dispute that you can have caching on other levels as well (which may perform differently, for different use cases).
To his point: It's very hard to beat decades of RDBMS research and improvements
Your RDBMS internal caching will likely get you extremely far and speed difference of Redis vs RDBMS call is very unlikely to matter in your standard CRUD App.
1. Mapping result rows back to objects, especially from joins where you will get back multiple rows per "object" that need to be collated.
2. Automatic handling of many-to-many relationships so you don't have to track which ids to add/remove from the join table yourself.
3. Identity mapping so if you query for the same object in different parts of your UI you always get the same underlying instance back.
4. Unit of work tracking so if you modify two properties of one object and one property of another the correct SQL is issued to only update those three particular columns.
5. Object change events so if you fetch a list of objects to display in the UI and some other part of your UI (or a background thread) add/updates/deletes an object, your list is automatically updated.
6. And finally in cases where your SQL is dynamic having a query builder is way cleaner than concatenating strings together.
For those who are against ORMs I am curious how you deal with these problems instead.
To any juniors reading: cache the response payload (or parts of it), not the results of database queries.
Statically-typed queries written in a mini-DSL within your application language seems like such a joy!
But then the configuration is a hassle. The DbContext lifecycles are a hassle. The DbContext winds up a big ball of mutable state that often gets passed all up and down your call stack, reducing the ability to reason about much of the code locally. Was this instance initialized with or without change tracking? How many and what changes have been applied? Were these navigation properties lazily or eagerly loaded?
And it promises to keep your domain persistence ignorant with its fluent configuration syntax. But then you have compromise on that here, then compromise in it there.
Pretty soon, you realize that you started out building a project for domain X or domain Y, only to realize that you're trying to shoehorn domain X/Y behavior into your Entity Framework app.
As far as I know this is a very oldschool view on how to treat dbs, but I still think this is the only correct one.
I hate ORMs with a passion - they're just a potential source of bugs and performance issues, coming from either bugs in the engine, devs not understanding SQL, devs misjudging what query will get generated, leaky abstractions etc.
It's big enough of an ask to understand SQL itself, it's the height of folly to think you can understand SQL when it's being generated by some Rube-Goldberg SQL generator, especially if said generator advertises that you don't need to know SQL to use it.
Also, infrastructure guys and DBA types tend not to like ORMs. But they are not the ones trying to manage the complexity in the business process. They just see our queries are not optimal, and it is everything to them.
SQLAlchemy is the real deal, but it's more difficult and people prefer easy.