Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic.
But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible.
For example instead of:
SELECT country, COUNT(*) as count
FROM users
GROUP BY country
WHERE organization = ?
[note that it matches SQL, not the language's collections function's names.]
As you see, that's also nice because now you can actually use variables easy - and even use pure rust to decide dynamically on things.
You can further increase typesafety if you want by doing things like `.from(table("users"))` and running extra checks on that table() part, similar to what the lib probably does. Also, sometimes you might have to make a compromise on your syntax and things like `"organization".=(yourVariable)` might have to be slightly rewritten.
Still, I think that people will rather end up with a library like I described, unless the SQL is very basic/static.
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.
I've been using SQL from backend languages for years, and I totally disagree. Using raw SQL is the only way for me. It's much easier to develop, tune, and debug in a SQL IDE. There's no need to translate back and forth between SQL and the language-specific DSL. With SQLx, I get all the type safety I really need.
Dynamically constructing queries is awkward, but most of mine ha very limited dynamic variation.
I get where you are coming from. But it's very easy to generate the SQL from the code. Then I take that, tune it in my SQL IDE against the DB and then adjust the code. Since the code is basically a 1:1 mapping to SQL (just with slightly different syntax) there isn't really a problem with that.
Once you have any kind of dynamic stuff (like a dynamic filter) you don't have any 100% pure SQL anymore anyways. If you don't have that, okay, this lib will be more convenient.
But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible.
For example instead of:
That should be [note that it matches SQL, not the language's collections function's names.]As you see, that's also nice because now you can actually use variables easy - and even use pure rust to decide dynamically on things.
You can further increase typesafety if you want by doing things like `.from(table("users"))` and running extra checks on that table() part, similar to what the lib probably does. Also, sometimes you might have to make a compromise on your syntax and things like `"organization".=(yourVariable)` might have to be slightly rewritten.
Still, I think that people will rather end up with a library like I described, unless the SQL is very basic/static.