The issue with the abstractions over SQL is that while they fix some problems they always introduce a bunch of new problems so in the end SQL is still preferable. I have yet to see an example where that is not the case.
The fundamental issue is that they have to generate SQL at the end of the day, so there’s a hard limit on how much you can really change. I don’t know why every database treats SQL as the only API, even Postgres. Even the extension systems, which have the opportunity to hook directly into DB internals (and has no standardization to bother meeting) end up with SQL as the API to do actual db operations.
> Even the extension systems, which have the opportunity to hook directly into DB internals (and has no standardization to bother meeting) end up with SQL as the API to do actual db operations.
FWIW, nothing forces an extension to do so. I'm pretty sure there are several that do DML using lower level primitives.
For me query builders are the quintessential example. Not ORMs, just thin layers that allow you build up a query in pieces.
If you have cases where you might need to conditionally join, or have any reasonably complex filtering based on input, building up a plain SQL statement using string interpolation at the call site gets very messy very quickly.
I have used many query builders and experienced both the upsides and the painful downsides. The by far best one I have used has been JOOQ (Lukas Eder is a genius who really understands SQL well) but even that often causes more pain than it helps. Compared to the issues caused by them I in most cases prefer string interpolation of SQL.