The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQL as written. That is a very powerful property for an ad-hoc query language because it means that the user describes what they want, and the database itself takes care of the best way to get it to them. For programmers though, who usually want something predictable and repeatable to happen, it is not always ideal.

ORMs try to make SQL seem more programmer-oriented by adding an extra layer of indirection over SQL. Unfortunately, what they actually tend to do is to add an additional black box enclosing SQL's black box, and that usually makes everything worse because it is now almost impossible to reason about how the database is actually going to execute a particular query.

There doesn't seem to be an ideal solution to this problem. I think it is why so many databases tend to tweak SQL into something that better fits their implementation detail, and we end up with the explosion of almost-SQL languages that the author is complaining about. Personally, the least-bad solution I have found is the one you mention in your post: write your own SQL abstraction layer every time. At least that way, you can poke your screwdriver into the box and reason about what is going on in there.