The problem is that easy queries are easy, who cares about orm, and hard ones are too hard for orm. I haven't been able to see the benefits in either easy or hard projects.

you nailed it: In simple cases the abstraction doesn't help enough to justify including it and in complex ones it actually makes things much more complicated because it just doesn't work right.

Ultimately it's useless for both.

I definitely agree there is no perfect solution for every use case. What I think is a mistake, however, is using a technology that makes stuff easier when you are small and have little load, but then become absolute burdens once you become successful and need to scale. I've worked on many a project where the ORM became the primary piece of "tech debt" that was hindering productivity.

I contrast that with technologies that are easy to use when you're small, but then let you layer additional pieces on later when you need to scale, without needing to redo everything.

I guess I mean it's never going to be answered because there is no short answer. It's like asking: what's the best programming language?

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.

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.

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.

Static type safety is one difference.

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

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