[nijave]

Imo they work well for dynamic query composition.

Say you want to combine a few sets with dynamic where clauses then intersect a couple other sets. For example, you have a "products" API that lets the user pick from a bunch of different filters

It's pretty easy to composite all that together with a decent ORM

Also migration management and having a programmatic DB schema to object schema mapping is very convenient

I do tend to see a lot of bastardized queries, though from treating ORM objects like they're native in memory objects (N+1, using programming functions where SQL equiv would have been more efficient, pulling data back only to dump it into the where clause of another query)

[nicwolff]

You don't need an ORM for any of that, just a query builder. In Python e.g. you can use SQLAlchemy Core instead of the SQLAlchemy ORM.

[jon_richards]

The main value I get from sqlalchemy is parsing the result into useful structures. Getting the two models out of a join, prefetching related objects, etc. Though I much prefer rust diesel’s approach of no lazy queries (prefetches returned as list[tuple[object, list[related_object]], though diesel had other issues for me). My policy with sqlachemy is to unwrap all results to that if I’m passing/returning them. No relationship access outside of the function making the query.