[0] https://www.postgresql.org/docs/current/sql-expressions.html...
But composing strings together to forge a SQL query (outside the inputs) is generally a terrible, no-good, awful job. E.G. optionally adding filters to a query.
Largely because the SQL language itself does not compose well, due to its general inconsistency in just about every expression, and it’s awkward/unnecessary strictness in things as simple as SELECT/FROM/WHERE order and evaluation order
However, outside of the .NET world, there is no common ORM that I would use between languages and between projects. Learning an ORM repeatedly is much, much worse than learning SQL once, especially considering the low quality of some of them (I'm thinking Sequelize here, but I'm sure there are others).
For 99% of cases, I don't do string appending; I create different SQL files for different situations and select the file at runtime depending on the situation. 10 SQL files? Sure, I don't care. It's better than the hours of going through JavaScript code at 3AM and trying to understand how to do some random feature that may or may not be implemented in the ORM layer.
Incidentally, I am curious, how do you keep stuff like Views and Stored Procedures version controlled?
However, I don’t touch any of the “ORM” features other than certain conveniences like .Include() for trivially handling trivial joins, and most of the code ends up with an almost 1:1 translation to the actual SQL statement.
Same with SQLAlchemy Core in python, though the expression API is distinctly less pleasant than LINQ.
Anything that doesn’t fit cleanly in LINQ usually ends up as a View or procedure in the DB anyways (except for DELETE statements, which EF is uniquely awful with; requiring the rows to be fetched first before turning around and actually deleting them)