4 ms·

> I really like using Diesel because it generates all your SQL queries for you, from a typed SQL schema that it generates from your SQL migrations. Fair that t

by dijit 4y ago

> I really like using Diesel because it generates all your SQL queries for you, from a typed SQL schema that it generates from your SQL migrations. Fair that the author says that this is their opinion, however... I can't agree. Maybe I'm too stupid, but for me SQLx is the real killer crate when it comes to databases. Everything, from the migrate macro, to the way it validates at compile-time the types in the DB, feels (to me) very ergonomic. Diesel, by contrast, I spent so long trying to set up and I never really understood the three different files that need to somehow be in sync and generated. I just never really grokked the split of these files and when to run the diesel-cli commands... but SQLx is great, I often lament that there isn't anything comparable in Golang.

necubi 4y ago
We originally used SQLx but eventually moved away for a couple of reasons: * The sqlx::query! macro is a neat hack, but the fact that it calls out to a database in a proc macro is pretty insane. It significantly slows down compilation with a lot of queries, and doesn't work reliably with rust-analyzer. * Inconsistent handling of nullability that would regularly break compilation We now use Cornucopia (https://cornucopia-rs.netlify.app/ https://cornucopia-rs.netlify.app/) which I think is a much better approach. You write your queries in a separate SQL file, alongside annotations that let you specify nullability. A standard build.rs integration performs codegen from that, which means compilation itself doesn't require a DB and IDE features work well. It also lets you share data types between queries which reduces boilerplate significantly.
- biorach 4y ago
  > * The sqlx::query! macro is a neat hack, but the fact that it calls out to a database in a proc macro is pretty insane. wait... proc macros are run at compile time, right? so db queries at compile time?! but.. but.. why?
  - Klonoar 4y ago
    It's not running the queries, just sanity checking them against table structures/etc. It's actually quite nice and worth checking out. The real issue is that in performance benchmarks Diesel beats SQLx, at least last I checked. I'm hopeful the rewrite/upgrades to SQLx that are coming shorten this gap.
    - chatmasta 4y ago
      Doesn't it need to run introspection queries to check the table structures? This is still a significant overhead compared to code-only compilation, and it also requires an additional component (the database) in your build stack, which also needs to have a schema in sync with other developers and whatever deployment target to which you intend to push the compiled program. That also means that if you're building with a local copy of the DB, and deploying to a remote with its own DB, you have another source of risk for disparity between compile and runtime (I assume such an error would be caught quickly, so maybe it's not so much about additional "risk" as it is increased operational complexity).
      - winrid 4y ago
        I mean, the db schema has to stay in sync with the code no matter what right? This is just ensuring it is valid at compile time. On deploy you just run your migrations first. Obviously you have to do some planning. It's a major productivity boost honestly, after building a semi large app with it. And yes, it does actually run the queries. It populates dummy data and runs it in a transaction from what I can tell.
        preseinger 4y ago
        The .sql files or migrations or whatever else in your repo represents an intent, or contract, defined by your application. But those files aren't the source of truth, the database is the authority. Whether a SQL query is or is not valid is, by definition, something that can only be evaluated at runtime.
        4y ago
  - lars_francke 4y ago
    So compilation can fail if your SQL query doesn't match what's actually in the database. The idea is kinda neat but....
  - imron 4y ago
    > but.. but.. why? Compile time verification that you haven't made a typo in your SQL, and that if you have updated your db schema that your code also reflects these changes. Turning runtime errors in to compile time errors is one of Rust's superpowers.
- sp0ck 4y ago
  FYI: You can disable SQLx compile time db verification since macros support an offline mode.
  - necubi 4y ago
    This sort of works, but is finnicky to set up in each environment and introduces the risk that you forget to update it and your queries are broken.
- winrid 4y ago
  Cornucopia looks cool. Regarding your second bullet point, can you elaborate more? I haven't seen this. You have to explicitly use Option<> for non null columns but that's what I expect.
Thaxll 4y ago
Why don't you use sqlc + pgx? What's missing from the two? https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc https://github.com/jackc/pgx https://github.com/jackc/pgx
jeremychone 4y ago
Agree, for our approach, sqlx has the right level of abstraction. Not too high, not too low, and close enough of a sql builder pattern to be very useful. (We are not using the compile time type validation though).
davidatbu 4y ago
Personally, what I'm looking for from an ORM is composability and type safety. To my knowledge, it's impossible with sqlx, for e.g., to conditionally add a WHERE clause, and have compile time checked queries. With diesel, it is possible. I 100% agree with your point about Diesel's dev experience leaving a lot to be desired when it comes to docs and ease-of-learning though. But to me, that is a straightforward problem to solve when compared to the fundamental limitation of sqlx that I described above.
- weiznich 4y ago
  Please reach out on the diesel discussion forum[1] about the lacking dev experience. I'm happy to discuss these issues and potential solutions there. [1] https://github.com/diesel-rs/diesel/discussions https://github.com/diesel-rs/diesel/discussions
  - davidatbu 4y ago
    Thank you weiznich! Tbh, it's really hard to come up with concrete proposals to solve these problems, and I still haven't figured out how much of the "not-super-easy-to-learn" issue with Diesel is inherent complexity that can't be avoided if one wants to have runtime-constructed-and-yet-compile-time-checked db queries in Rust. I think it will require some thought. I hope that, once I get around to using diesel in a second substantial project (already using it for my first), I will write up my thoughts on how to make DX better for diesel.
adamch 4y ago
Hello, author here. My coworker Olivia (of https://losslessbits.com/ https://losslessbits.com/) really likes SQLx, I haven't personally used it though. I'd love to try it for a future project, as long as it can help typecheck my SQL queries.
Pepe1vo 4y ago
Maybe I'm holding it wrong, but my experience with sqlx is a bit more lukewarm. It's really great that compilation success means that you didn't fuck up any queries, but... there's just SO much boilerplate. I find that due to Rust's extremely strict type system, I have to create dedicated record structs for all of my domain models representing how a model is stored in the database. So once everything's up and running it almost always implies there's essentially no bugs, but man is getting there a drag. Also, testing is quite annoying too. If you're using sqlite, you can use in-memory mode and that's quite nice. But for other databases you basically have only two options: 1. setup and teardown an actual testing db 2. wrap your sqlx interfacing code in something vaguely resembling the repository pattern and create an in-memory implementation for testing. I'd call both of these options less than ideal.
- zamnos 4y ago
  The question is what's the cost of a bug? Lost hours for customer support to collect user reports (which take some time and usually multiple reports before being believed), time spend by CSRs to individually, time by the dev or ops team to fix the affected accounts en-masse, more time by the CSRs to manually fix up affected accounts and give them individual credits where credits are due (which may be manual and thus error-prone process), and finally time spent by the dev team to identify and fix the bug for future users, which is time spent not adding new features or paying down other tech-debt. That's not to say using scripting languages isn't necessarily worth it for increased speed of development, but the time spent above may be impossible to quantify in aggregate. If you're in a low stakes environment, where a bug costs you single digit basis points/bips, not a problem. If you're somewhere like crypto where a bug that an attacker discovered ends up costing the exchange, your employer, 9-figures, or worse, the medical or aerospace industry where bug(s) literally costs people's lives, then avoiding bugs is of paramount importance. The other option is avoid such a high stakes environment, but someones got to be in those fields.
- nightpool 4y ago
  > But for other databases you basically have only two options: 1. setup and teardown an actual testing db 2. wrap your sqlx interfacing code in something vaguely resembling the repository pattern and create an in-memory implementation for testing. I'd call both of these options less than ideal. Why not just BEGIN a transaction and then ROLLBACK it after every test case? That's what every other significant backend codebase (using SQL) that I've worked on does. Maybe that's what you meant by #1? Not sure why you'd want to avoid it then—it will help significantly in testing out any piece of code you have that's even slightly outside of the CRUD norms. What does Diesel do, reimplement a completely separate "testing" version for all of its SQL queries?

by dijit 4y ago

necubi 4y ago

We originally used SQLx but eventually moved away for a couple of reasons: * The sqlx::query! macro is a neat hack, but the fact that it calls out to a database in a proc macro is pretty insane. It significantly slows down compilation with a lot of queries, and doesn't work reliably with rust-analyzer. * Inconsistent handling of nullability that would regularly break compilation We now use Cornucopia (https://cornucopia-rs.netlify.app/ https://cornucopia-rs.netlify.app/) which I think is a much better approach. You write your queries in a separate SQL file, alongside annotations that let you specify nullability. A standard build.rs integration performs codegen from that, which means compilation itself doesn't require a DB and IDE features work well. It also lets you share data types between queries which reduces boilerplate significantly.

biorach 4y ago
> * The sqlx::query! macro is a neat hack, but the fact that it calls out to a database in a proc macro is pretty insane. wait... proc macros are run at compile time, right? so db queries at compile time?! but.. but.. why?
- Klonoar 4y ago
  It's not running the queries, just sanity checking them against table structures/etc. It's actually quite nice and worth checking out. The real issue is that in performance benchmarks Diesel beats SQLx, at least last I checked. I'm hopeful the rewrite/upgrades to SQLx that are coming shorten this gap.
  - chatmasta 4y ago
    Doesn't it need to run introspection queries to check the table structures? This is still a significant overhead compared to code-only compilation, and it also requires an additional component (the database) in your build stack, which also needs to have a schema in sync with other developers and whatever deployment target to which you intend to push the compiled program. That also means that if you're building with a local copy of the DB, and deploying to a remote with its own DB, you have another source of risk for disparity between compile and runtime (I assume such an error would be caught quickly, so maybe it's not so much about additional "risk" as it is increased operational complexity).
    - winrid 4y ago
      I mean, the db schema has to stay in sync with the code no matter what right? This is just ensuring it is valid at compile time. On deploy you just run your migrations first. Obviously you have to do some planning. It's a major productivity boost honestly, after building a semi large app with it. And yes, it does actually run the queries. It populates dummy data and runs it in a transaction from what I can tell.
      - preseinger 4y ago
        The .sql files or migrations or whatever else in your repo represents an intent, or contract, defined by your application. But those files aren't the source of truth, the database is the authority. Whether a SQL query is or is not valid is, by definition, something that can only be evaluated at runtime.
        4y ago
- lars_francke 4y ago
  So compilation can fail if your SQL query doesn't match what's actually in the database. The idea is kinda neat but....
- imron 4y ago
  > but.. but.. why? Compile time verification that you haven't made a typo in your SQL, and that if you have updated your db schema that your code also reflects these changes. Turning runtime errors in to compile time errors is one of Rust's superpowers.
sp0ck 4y ago
FYI: You can disable SQLx compile time db verification since macros support an offline mode.
- necubi 4y ago
  This sort of works, but is finnicky to set up in each environment and introduces the risk that you forget to update it and your queries are broken.
winrid 4y ago
Cornucopia looks cool. Regarding your second bullet point, can you elaborate more? I haven't seen this. You have to explicitly use Option<> for non null columns but that's what I expect.

Thaxll 4y ago

Why don't you use sqlc + pgx? What's missing from the two? https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc https://github.com/jackc/pgx https://github.com/jackc/pgx

jeremychone 4y ago

Agree, for our approach, sqlx has the right level of abstraction. Not too high, not too low, and close enough of a sql builder pattern to be very useful. (We are not using the compile time type validation though).

davidatbu 4y ago

Personally, what I'm looking for from an ORM is composability and type safety. To my knowledge, it's impossible with sqlx, for e.g., to conditionally add a WHERE clause, and have compile time checked queries. With diesel, it is possible. I 100% agree with your point about Diesel's dev experience leaving a lot to be desired when it comes to docs and ease-of-learning though. But to me, that is a straightforward problem to solve when compared to the fundamental limitation of sqlx that I described above.

weiznich 4y ago
Please reach out on the diesel discussion forum[1] about the lacking dev experience. I'm happy to discuss these issues and potential solutions there. [1] https://github.com/diesel-rs/diesel/discussions https://github.com/diesel-rs/diesel/discussions
- davidatbu 4y ago
  Thank you weiznich! Tbh, it's really hard to come up with concrete proposals to solve these problems, and I still haven't figured out how much of the "not-super-easy-to-learn" issue with Diesel is inherent complexity that can't be avoided if one wants to have runtime-constructed-and-yet-compile-time-checked db queries in Rust. I think it will require some thought. I hope that, once I get around to using diesel in a second substantial project (already using it for my first), I will write up my thoughts on how to make DX better for diesel.

adamch 4y ago

Hello, author here. My coworker Olivia (of https://losslessbits.com/ https://losslessbits.com/) really likes SQLx, I haven't personally used it though. I'd love to try it for a future project, as long as it can help typecheck my SQL queries.

Pepe1vo 4y ago

Maybe I'm holding it wrong, but my experience with sqlx is a bit more lukewarm. It's really great that compilation success means that you didn't fuck up any queries, but... there's just SO much boilerplate. I find that due to Rust's extremely strict type system, I have to create dedicated record structs for all of my domain models representing how a model is stored in the database. So once everything's up and running it almost always implies there's essentially no bugs, but man is getting there a drag. Also, testing is quite annoying too. If you're using sqlite, you can use in-memory mode and that's quite nice. But for other databases you basically have only two options: 1. setup and teardown an actual testing db 2. wrap your sqlx interfacing code in something vaguely resembling the repository pattern and create an in-memory implementation for testing. I'd call both of these options less than ideal.

zamnos 4y ago
The question is what's the cost of a bug? Lost hours for customer support to collect user reports (which take some time and usually multiple reports before being believed), time spend by CSRs to individually, time by the dev or ops team to fix the affected accounts en-masse, more time by the CSRs to manually fix up affected accounts and give them individual credits where credits are due (which may be manual and thus error-prone process), and finally time spent by the dev team to identify and fix the bug for future users, which is time spent not adding new features or paying down other tech-debt. That's not to say using scripting languages isn't necessarily worth it for increased speed of development, but the time spent above may be impossible to quantify in aggregate. If you're in a low stakes environment, where a bug costs you single digit basis points/bips, not a problem. If you're somewhere like crypto where a bug that an attacker discovered ends up costing the exchange, your employer, 9-figures, or worse, the medical or aerospace industry where bug(s) literally costs people's lives, then avoiding bugs is of paramount importance. The other option is avoid such a high stakes environment, but someones got to be in those fields.
nightpool 4y ago
> But for other databases you basically have only two options: 1. setup and teardown an actual testing db 2. wrap your sqlx interfacing code in something vaguely resembling the repository pattern and create an in-memory implementation for testing. I'd call both of these options less than ideal. Why not just BEGIN a transaction and then ROLLBACK it after every test case? That's what every other significant backend codebase (using SQL) that I've worked on does. Maybe that's what you meant by #1? Not sure why you'd want to avoid it then—it will help significantly in testing out any piece of code you have that's even slightly outside of the CRUD norms. What does Diesel do, reimplement a completely separate "testing" version for all of its SQL queries?