4 ms·

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 gr

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

by ris 2y ago

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.