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?

Yep, and each has their own bespoke and often inconsistent API. All to eventually emit plain ole BEGIN/COMMIT/ROLLBACK.

JPA is at least a spec rather than just yet another single isolated implementation. Unfortunately it's not a good spec.

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.

I think most ORMs support transactions at this stage!

There are two approaches.

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.

SqlAlchemy does permit both.

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.

I meant if you use SqlAlchemy Core you get out mostly ActiveRecord behavior. If you use SqlAlchemy ORM you get latter behavior.

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.

[0]https://en.wikipedia.org/wiki/Optimistic_concurrency_control

Additionally it's strange to argue that ORMs can be efficient and only give examples that use fragments of sql given to the ORM.

anything in the above query can be written without a single piece of raw sql.