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