[deadbabe]

Unless you are working with different types of database systems and don’t want to rewrite the same queries in different languages for different databases, there is no reason to use an ORM.

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