[9rx]

> I’ve never seen an ORM used in the wild where you could actually avoid thinking about tables and columns and rows pretty frequently.

There is a compelling argument that absent the n+1 problem, it could be a leak-free abstraction. And, in practice, SQLite doesn't suffer from the n+1 problem... But that is definitely true in a lot of cases, particularly where the n+1 problem is unavoidable.

> If it’s a one-off you’re essentially writing a bespoke shorthand wrapper for some custom SQL.

While the original comment also conflated query building and ORM, I am not sure that we should. The intersection of query building and ORM was historically referred to as the active record pattern. I suspect the ActiveRecord library, from Rails, calling itself an ORM is where things started to become confused. Regardless, whatever you want to call the data mapping and query building, they are undeniably distinct operations.

It may be fair to say that query builders are no more than an indirection. That said, I posit that they only exist as an abstraction to model composability on top of SQL. If SQL naturally composed through basic string concatenation, I expect the use of query builders would quickly disappear.

[necovek]

But SQL is "naturally composable" using string concatenation. I've seen one too many of

  tables = ["order", "product"]
  join_on = ["order.product_id = product.id"]
  filters = []

as the base of the query, with all of these being concatenated together.

But instead of this, an ORM usually provides you with a syntax (that will pass syntax checks and have highlighting) that matches the language, which is all nice and good because dealing with arbitrary strings does suck.

I've seen ORMs being used for query composition way before Rails even existed: I believe Hibernate had HQL with their first release in 2001, just like SQLObject did in 2002. I am sure neither of those "invented" the pattern.

Note that fetching objects using an ORM library, filtering and such is what I also consider query composition (it just happens behind the scenes).

[9rx]

> But SQL is "naturally composable" using string concatenation.

It is not. Not even in a simple case. Consider:

    base = "SELECT * FROM table LIMIT 10"
    cond1 = "WHERE foo = 1"
    cond2 = "WHERE bar = 2"

base + cond1 + cond2 or any similar combination will not produce a valid query. It could if SQL had some thought put into it. There are many languages that have no problem with such things. But that irrational fear of moving beyond the 1970s when it comes to SQL...

The only realistic way to assemble queries is to prepare an AST-like structure to figure out where the pieces fit, and then write that out to a final query string. In practice, that means either first parsing the partial queries into that structure (hard) or providing a somewhat SQL looking API in the host language that builds the structure (easy). Unsurprisingly, most people choose what is easy.

> I am sure neither of those "invented" the pattern.

None of these invented the pattern. But the invention point is irrelevant anyway. You must have misread something?