[kingdomcome50]

I disagree with this sentiment. The use-cases for an ORM are straight-forward. It's more like asking, "which tool is best for getting this nail into this piece of wood?"

What most people discover to be the greatest benefit of using an ORM is the "mapper" bit (converting tabulated data into an object graph and visa versa) and, to a lesser degree, change-tracking.

Somewhat ironically, the overwhelming majority of the time criticism of ORMs is directed at neither of the above, instead pointing to query performance.

You can have data mapping, you can have change-tracking, you can even have schema migrations without opting-in to the pain points many ORMs introduce because these are all somewhat orthogonal concerns.

At the end of the day there is very little to be saved between writing:

  users->where(u => u.name === "John")

and

  SELECT * FROM users WHERE [name] = 'John'

Often, as queries become more complex, the SQL is actually a shorter expression than whatever query DSL comes with the ORM.

[okbake]

A big benefit of the `users->where(...)` approach is being able to reuse and compose. An example would be conditionally adding a WHERE clause based on some parameters. Using the raw query approach you end up having to do some string concatenation versus managing the state of the some query builder object.

I think that the "query builders" though are just one piece of the ORM that you mention, alongside the change-tracking, data mapping, etc. Having a decent query builder that isn't abstracting away too much of the underlying sql (essentially just mapping 1-to-1) plus data mapping are the sweet spot for me personally.

[kingdomcome50]

You are correct.

Interestingly, it is this exact property (composition) that creates the most common problems when using an ORM.

Composition is often at odds with optimization.

[recursive]

Static type safety is one difference.

[kingdomcome50]

It'd be more accurate to say the illusion of type safety.

Under the hood many[0] ORMs simply construct a query similar to my example above and then convert result set of tabulated strings to the appropriate types (usually using reflection).

This means two things:

First, that the "type-safety" portions of an ORM are really located in the "mapping" code, so not really related to querying.

And second: you don't really have type safety. A database schema could change at any time and break the code even if static analysis seems to think it should work.

[0] Notable exceptions are languages that offer type providers (e.g. F#) but I digress

[recursive]

This particular illusion of safety actually provides some non-zero degree of safety. For instance, it prevents trivial typos in property names.