4 ms·

Yeah, I am using sqlx and it is amazing. The only thing it cannot do is help you in the case when you actually need a query builder, e.g. when you need support

by jeltz 2y ago

Yeah, I am using sqlx and it is amazing. The only thing it cannot do is help you in the case when you actually need a query builder, e.g. when you need support for queries with dynamic filters and sort orders. But other than that it is much nicer to work with than every other query library I have used.

Diggsey 2y ago
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)
pimeys 2y ago
You can add sea-orm on top of sqlx for a query builder. It can also introspect your SQL database and generate entity types. I built my own back in the days when I worked for Prisma. None of these crates existed. It's not that complex if you limit the builder to queries you need in your application. But, to be honest, jOOQ or LINQ would be awesome to get for Rust.

by jeltz 2y ago

Diggsey 2y ago

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)

pimeys 2y ago

You can add sea-orm on top of sqlx for a query builder. It can also introspect your SQL database and generate entity types. I built my own back in the days when I worked for Prisma. None of these crates existed. It's not that complex if you limit the builder to queries you need in your application. But, to be honest, jOOQ or LINQ would be awesome to get for Rust.