FWIW, you very rarely need to make the query itself dynamic. After all, SQL alone is powerful enough to do conditionals.
For example:
SELECT * FROM example
WHERE ($1 IS NULL OR field1 = $1)
AND ($2 IS NULL OR field2 = $2)
...
When you supply the parameters to this prepared statement, the constant conditions should be optimized away by the query planner if you're using a decent database.
Those tricks cover a very small subset of dynamic queries, and if you don't give your developers a proper tool to do dynamic queries in, your code will start growing these funny string-formatting-based query-mangling functions that have god-knows-what escaping problems behind them.
Well, it has been mine. That trick you suggested works for the simple binary "either include this WHERE clause filter or don't" -- which, agreed, is something that comes up often enough -- but it's only in the simplest of CRUD apps where that's all I've needed.
A quick example: I have a REST endpoint that updates a resource. The JSON fields that map to the DB columns can be missing (leave the column value unchanged), null (set the column value to NULL), or have a value (set the new provided value). The list of assignments in the UPDATE needs to be dynamic; sometimes there might be only one column being updated, sometimes two, three, four five...
As this claim comes up quite often I have a set of examples I typically ask these people to provide. So far I've got so response, maybe you can point out how you would implement one or more of the following queries using `sqlx::query!`?
* A batch insert query for mysql
* A conditional where clause for postgresql that allows the user to dynamically specify the column + filter operation + values over e.g. a rest end point. Each column can appear zero, one or multiple times with different or even the operations.
* A IN expression with a list of values provided by a rust `Vec<_>` (so dynamically sized) for sqlite.
It's a fully fledged, horrible language. Anything beyond basic queries is unreadable, ESPECIALLY when it's done in plain strings in another language. There's not even a way to not have to repeat values EVERY SINGLE TIME (think variables or constants in every other language).
Oh, but what about <feature>? Well, is that SQL, or a frankensteined version of SQL, aka a "dialect"?
SQL is the JavaScript of databases, and we'll be better for it once we admit this.
Can you show an example where you have 4 possible fields to filter on, and then one more where the sorting field is dynamic? (Not the value, but the column)
For example:
When you supply the parameters to this prepared statement, the constant conditions should be optimized away by the query planner if you're using a decent database.