3 ms·

SQL, while not great, does have some advantages that are hard to beat: - Everyone knows at least some basic SQL, even non-technical users have often encountere

SQL, while not great, does have some advantages that are hard to beat: - Everyone knows at least some basic SQL, even non-technical users have often encountered it in some form. - The documentation for eg. PostgreSQL is for SQL, so if you write queries in anything else you have to mentally translate back and forth, and so you need to know SQL anyway. - Any external tools you use to interact with the database will use SQL. - Changing the queries doesn't require an expensive compilation step - `sqlx` gets the best of both worlds in this respect, in that it is able to type-check the parameters and rely on the database itself to validate the query, so you don't end up with a ton of type-system shenanigans that increase compilation times. Maybe for a brand new database, a better query language could win out, but having used sqlx I can't imagine going back to a query-builder style interface for existing SQL databases.

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
  - 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.
unshavedyak 2y ago
I am toying with spreadsheet backends these days and it's had me wondering - do spreadsheet query languages offer value that SQL is missing? Beyond "people are used to it" of course. One thing i am wanting beyond SQL though is a more Object oriented language. Ie i want something SQL-like (or more specifically PRQL, which i find very nice), but without the being so tailored towards a flat data structure. I want to use it on JSON/etc.
wslh 2y ago
BTW, do you suggest a LINQ kind of integration in Rust? I just found the Microsoft has a Rust for C#/.NET developers resource [1][2] which is more about integration I think. It is a little weird that there is not Rust.NET yet. .NET is not a garbage collection only platform. I guess Microsoft is working internally on this. [1] https://microsoft.github.io/rust-for-dotnet-devs/latest/linq/index.html https://microsoft.github.io/rust-for-dotnet-devs/latest/linq... [2] https://microsoft.github.io/rust-for-dotnet-devs/latest/ https://microsoft.github.io/rust-for-dotnet-devs/latest/
- estebank 2y ago
  I'm impressed by this individual's effort to bring rust to the clr: https://github.com/FractalFir/rustc_codegen_clr https://github.com/FractalFir/rustc_codegen_clr It is already way more complete given it's age than I would have expected. I agree that the clr is a more natural target than, let's say, the JVM (at least until Project Valhalla comes out).

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

unshavedyak 2y ago

I am toying with spreadsheet backends these days and it's had me wondering - do spreadsheet query languages offer value that SQL is missing? Beyond "people are used to it" of course. One thing i am wanting beyond SQL though is a more Object oriented language. Ie i want something SQL-like (or more specifically PRQL, which i find very nice), but without the being so tailored towards a flat data structure. I want to use it on JSON/etc.

wslh 2y ago

BTW, do you suggest a LINQ kind of integration in Rust? I just found the Microsoft has a Rust for C#/.NET developers resource [1][2] which is more about integration I think. It is a little weird that there is not Rust.NET yet. .NET is not a garbage collection only platform. I guess Microsoft is working internally on this. [1] https://microsoft.github.io/rust-for-dotnet-devs/latest/linq/index.html https://microsoft.github.io/rust-for-dotnet-devs/latest/linq... [2] https://microsoft.github.io/rust-for-dotnet-devs/latest/ https://microsoft.github.io/rust-for-dotnet-devs/latest/

estebank 2y ago
I'm impressed by this individual's effort to bring rust to the clr: https://github.com/FractalFir/rustc_codegen_clr https://github.com/FractalFir/rustc_codegen_clr It is already way more complete given it's age than I would have expected. I agree that the clr is a more natural target than, let's say, the JVM (at least until Project Valhalla comes out).