[The_Colonel]

Starting with raw SQL is fun. But at some point you find out you need some caching here, then there, then you have a bunch of custom disconnected caches having bugs with invalidation. Then you need lazy loading and fetch graphs. Step by step you'll build your own (shitty) ORM.

Same thing for people claiming they don't need any frameworks.

[qaq]

caching is orthogonal to using or not using ORM. You might opt to have caching with or without ORM in a consistent manner. You can also opt to add read replicas fronted by say pgcat in Postgres case without having separate caching layer.

[The_Colonel]

I guess this is a point where terminology matters. If you work with SQL database in an OOP language, you pretty much always do some object-relational mapping, no matter if you have a big framework or just raw SQL connection.

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

[stackskipton]

As SRE who dealt with more caching errors then I care to. Alot of caching comes down to YAGNI.

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.

[lcnPylGDnU4H9OF]

> Redis vs RDBMS call is very unlikely to matter in your standard CRUD App

To any juniors reading: cache the response payload (or parts of it), not the results of database queries.

[gilbetron]

There are plenty of libraries/packages for SQL that do all of that for you, too. The choice isn't between a sophisticated ORM and just throwing SQL text at a socket. The fundamental assumption of ORMs is broken, but much of the tooling works well and exists in non-ORM places.

[wesselbindt]

> you find out you need some caching here, then there

Forgive my ignorance, but how do ORMs help with adding caching? Or are you implying they obviate or reduce the need for caching?

[The_Colonel]

JPA implementations have "managed entities", sometimes called session or 1st level cache which is making sure that every entity is loaded at max. one time within a transaction. Like e.g. checking user/user permissions is something which typically has to be done in several places in course of a single request - you don't want to keep loading them for every check, you don't want to keep passing them across 20 layers, so some form of caching is needed. JPA implementations do it for you automatically (assuming you're fine with transaction-scoped cache) since this is such a core concept to how JPA works (the fact it's also a cache is kind of secondary consequence). JPA implementations typically provide more advanced caching capabilities, caching query results, distributed cache (with proper invalidation) etc.

[wesselbindt]

Ah yep, of course! Thanks!

[ivan_gammel]

They do caching themselves so that some of your queries via ORM won’t hit the actual db.

[capitainenemo]

So, given the main issue with ORM is the object/relational part... (https://web.archive.org/web/20160301022121/http://www.revisi...)

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.

[The_Colonel]

Integrating cache into connection pools brings little added value since connection pools don't have enough context/information to manage the cache intelligently. You'd have to do all the hard work (like invalidation) yourself anyway.

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.

[capitainenemo]

I guess I was thinking more of frequently run queries against infrequently modified data or where stale data doesn't matter so much. The sort of things that are ideal cache targets. You'd think you could tag queries like that. Sort of the things that a CDN caches but more granular. Sure if it's stuff that's frequently changed, an ORM could reason about it just like, well, the database does, but then you're back into all the bad things about running your shadow database with a badly fitting model, and you'd be better off just ensuring all or part of the database ran closer to your app with replication, say, in memory on same server.

[ivan_gammel]

That would be a result set layer, not a connection pool. Could make sense if you worked with rows, but if you use ORM, why mapping cached row again and again? ORMs cache hydrated objects, which seems to be more efficient.

[capitainenemo]

Yeah, it was more to see if there were any benefits to an ORM that could be used without the, well "ORM" part :)