No my experience is the inverse. The type of library you describe is nice for the basic queries but once you start needing CTE, subquery, postgres json query, etc. it just because easier to manage it all in SQL directly.
I'm using JOOQ. Not saying that JOOQ is the greatest library, but all of what you just mentioned works in there without problem. Including CTEs and json stuff.
With a library such as SQLx, you can never really factor anything out. Or at least it's very hard and you lose the actual typesafety. I've been there and done that with doobie [https://typelevel.org/doobie/] which is basically the same in green.
