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.
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.