4 ms·

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 databas

by 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.
        winrid 4y ago
        Yes, with sqlx, the migrations must be the source of truth for your DB schema. You cannot (ideally), manage them elsewhere. This is why the sqlx migration tool is so good, you kinda have to use it. It's honestly very nice. The code, my migrations, my DB browser, all in one window in my IDE. And the query is validated by running it, at compile time, by inserting mock inputs inside a transaction. It works really well. Even if you disagree that the DB must be the source of authority and it can differ, the development boon of the process is worth the 5% chance there is some issue later, which can probably be easily resolved.
        preseinger 4y ago
        a .sql migration file is like a .tf terraform file, it describes intent your infrastructure is not that .tf definition, it's whatever is currently deployed to ec2 in the same way, your schema is not that .sql file, it's whatever the database says it is :)
        winrid 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.

by necubi 4y ago

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.
      - winrid 4y ago
        Yes, with sqlx, the migrations must be the source of truth for your DB schema. You cannot (ideally), manage them elsewhere. This is why the sqlx migration tool is so good, you kinda have to use it. It's honestly very nice. The code, my migrations, my DB browser, all in one window in my IDE. And the query is validated by running it, at compile time, by inserting mock inputs inside a transaction. It works really well. Even if you disagree that the DB must be the source of authority and it can differ, the development boon of the process is worth the 5% chance there is some issue later, which can probably be easily resolved.
        preseinger 4y ago
        a .sql migration file is like a .tf terraform file, it describes intent your infrastructure is not that .tf definition, it's whatever is currently deployed to ec2 in the same way, your schema is not that .sql file, it's whatever the database says it is :)
        winrid 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.