A problem that I haven’t seen a good solution to is that most (all?) ORMs are also caches. And once you step outside the object box, cache invalidation is incredibly hard. Taking an example from this post:
await postRepository
.createQueryBuilder()
.update(Post)
.set({ status: 'archived' })
.where("authorId IN (SELECT id FROM author WHERE company = :company)", { company: 'Hooli' })
.execute();
What happens to all the pre-existing Post objects when you do this?
(Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)
ActiveRecord approach. Objects aren't kept in cache calling update like that won't update other object in memory. Each time you call select you get a different object.
Hibernate approach. Object are kept in cache and running update will update existing objects in memory because each select returns the same object.
The only one I gave experience with is SqlAlchemy (1.x). You certainly can do a query builder update and get current subsequent data out of ORM objects, but it’s really easy to mess up, at least in my experience.
Coincidentally I had to solve this problem at work a few weeks ago. I don't know how (or if) other ORMs deal with this, but for TypeORM it was relatively painless to implement optimistic concurrency[0] along with transactions at function-scope level which all but eliminates this issue.
Also, most ORMs make it hard or ignore the possibility of using database transactions, checkpoints, partial or full rollbacks, etc. Which might be fine for trivial queries, low write load and unimportant data. But for anything moderately important or complex, transactions are essential.
They do? Every ORM I've used supports explicit transactions and rollbacks. EF, NHibernate, GORM, SQLAlchemy. What major ORMs deny you this functionality?
It isn't that there is no such thing as "a way to open/close transactions". It is that transactions usually break all the supposed ORM benefits like composability. Often, you cannot even method-chain transactions like foo.search().begin().insert().insert().commit(), you have to do it in multiple lines. Even worse, rollback handling is often an exception, breaking control flow of your application.
And most only support basic transactions, not checkpoints.
(Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)