3 ms·

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 exampl

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.

ris 2y ago
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.
- Diggsey 2y ago
  That has not been my experience. SQL is a fully fledged programming language so there's very little that you can't express this way. What are you doing that requires dynamically building a query?
  - kelnos 2y ago
    > That has not been my experience 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...
    - jon_richards 2y ago
      Isn’t the standard just to set the unchanged column to itself?
    - Diggsey 2y ago
      In this case you just set the column value to the existing value...
  - LinXitoW 2y ago
    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.
  - weiznich 2y ago
    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.
63stack 2y ago
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)

ris 2y ago

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.

Diggsey 2y ago
That has not been my experience. SQL is a fully fledged programming language so there's very little that you can't express this way. What are you doing that requires dynamically building a query?
- kelnos 2y ago
  > That has not been my experience 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...
  - jon_richards 2y ago
    Isn’t the standard just to set the unchanged column to itself?
  - Diggsey 2y ago
    In this case you just set the column value to the existing value...
- LinXitoW 2y ago
  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.
- weiznich 2y ago
  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.

63stack 2y ago

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)