Cannot use `jiff-sqlx` with SQLx's `query!` and `query_as!` macros

[BurntSushi]

I attempted to migrate one of my projects from chrono to jiff using the jiff-sqlx crate, but it appears that jiff-sqlx is not compatible with the query! and query_as! macros which are used for static type checking of SQL queries (not to be confused with the query and query_as functions).

I've created a minimal reproducer here: https://github.com/tonyfinn/jiff-sqlx-poc/tree/main

but the gist is that if you don't enable any of the other time crates then a crate using sqlx fails to build as it there is no From<()> implementation for the jiff_sqlx types, and tells you you need to enable the time feature to use the TIMESTAMPTZ type (example error output) and if you do enable the other time crates then it complains that e.g. time::OffsetDateTime and jiff_sqlx::Timestamp are not the same type.

(Let me know if you want me to file this as a seperate issue, just keeping it with the existing sqlx discussion as its newly implemented)

I suspect this will require changes inside sqlx to make it work

Originally posted by @tonyfinn in #240 (comment)

[BurntSushi]

@tonyfinn Can you provide more details on how to reproduce your errors here? When I cargo build your git repo, these are the first two errors:

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:20:5
   |
20 | /     sqlx::query!(
21 | |         "INSERT INTO groups (id, name, created_at) VALUES ($1, $2, $3)",
22 | |         input.id,
23 | |         input.name,
24 | |         input.created_at.to_sqlx())
   | |___________________________________^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:30:18
   |
30 |     let result = sqlx::query_as!(DbRow, "SELECT id, name, created_at FROM groups")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

Then I tried DATABASE_URL='postgres://postgres:password@localhost/test' cargo b and got:

error: error returned from database: relation "groups" does not exist
  --> src/main.rs:20:5
   |
20 | /     sqlx::query!(
21 | |         "INSERT INTO groups (id, name, created_at) VALUES ($1, $2, $3)",
22 | |         input.id,
23 | |         input.name,
24 | |         input.created_at.to_sqlx())
   | |___________________________________^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: error returned from database: relation "groups" does not exist
  --> src/main.rs:30:18
   |
30 |     let result = sqlx::query_as!(DbRow, "SELECT id, name, created_at FROM groups")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

warning: unused import: `jiff_sqlx::ToSqlx`
 --> src/main.rs:2:5
  |
2 | use jiff_sqlx::ToSqlx;
  |     ^^^^^^^^^^^^^^^^^
  |
  = note: `#[warn(unused_imports)]` on by default

error[E0308]: arguments to this function are incorrect
  --> src/main.rs:46:5
   |
46 |       save(AppObject {
   |  _____^^^^_-
47 | |         id: "XYZ".into(),
48 | |         name: "ABC".into(),
49 | |         created_at: Timestamp::now()
50 | |     }, &db).await.unwrap();
   | |_____- expected `&AppObject`, found `AppObject`
   |
note: expected `Pool<Postgres>`, found `&Pool<Postgres>`
  --> src/main.rs:50:8
   |
50 |     }, &db).await.unwrap();
   |        ^^^
   = note: expected struct `Pool<_>`
           found reference `&Pool<_>`
note: function defined here
  --> src/main.rs:19:10
   |
19 | async fn save(input: &AppObject, db: PgPool) -> Result<(), sqlx::Error> {
   |          ^^^^ -----------------  ----------
help: consider borrowing here
   |
46 |     save(&AppObject {
   |          +
help: consider removing the borrow
   |
50 -     }, &db).await.unwrap();
50 +     }, db).await.unwrap();
   |

error[E0308]: mismatched types
  --> src/main.rs:53:5
   |
44 | async fn main() {
   |                - expected `()` because of default return type
...
53 |     dbg!(fetched)
   |     ^^^^^^^^^^^^^ expected `()`, found `Vec<AppObject>`
   |
   = note: expected unit type `()`
                 found struct `Vec<AppObject>`

For more information about this error, try `rustc --explain E0308`.
warning: `jiff-sqlx-poc` (bin "jiff-sqlx-poc") generated 1 warning
error: could not compile `jiff-sqlx-poc` (bin "jiff-sqlx-poc") due to 4 previous errors; 1 warning emitted

[BurntSushi]

I also installed sqlx-cli and did this:

[andrew@duff jiff-sqlx-poc]$ DATABASE_URL='postgres://postgres:password@localhost/test' cargo sqlx prepare
    Checking jiff-sqlx-poc v0.1.0 (/home/andrew/tmp/issues/jiff/i272-jiff-sqlx-query-macro/jiff-sqlx-poc)
error: error returned from database: relation "groups" does not exist
  --> src/main.rs:20:5
   |
20 | /     sqlx::query!(
21 | |         "INSERT INTO groups (id, name, created_at) VALUES ($1, $2, $3)",
22 | |         input.id,
23 | |         input.name,
24 | |         input.created_at.to_sqlx())
   | |___________________________________^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: error returned from database: relation "groups" does not exist
  --> src/main.rs:30:18
   |
30 |     let result = sqlx::query_as!(DbRow, "SELECT id, name, created_at FROM groups")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

Note that I am not a SQLx user. My only experience with it is the examples in this repo and my attempt at providing integration for it with Jiff.

[tonyfinn]

Sure, you need to have the database setup first. The SQL script to do that is in the migrations directory. You can run that manually or you can use sqlx database create then sqlx migrate run with sqlx-cli (after setting DATABASE_URL to your database connection settings). After that cargo build will produce the errors in the text files.

---

A small bit of sqlx info (which you can ignore if you want to set up a test DB) about the cargo sqlx prepare is that it's a tool that basically produces a JSON file storing the database schema that is used for compile time type checks in lieu of the database so you don't need a running database to compile. I should have committed those files to save you from setting up a DB, but I forgot last night. When I finish work today I'll generate those files and commit them to simplify reproduction. However, actually running cargo sqlx prepare needs the database up and available.

[tonyfinn]

Actually, realised I could do it from my laptop, so I've pushed the sqlx schema files to the repo now: https://github.com/tonyfinn/jiff-sqlx-poc/blob/main/.sqlx/query-ef8165b29a48650641e5577eda029c2717c47c95883442188f8970770d2c7580.json

You'll still need a running database to cargo run the example, but cargo build should now produce the same errors for you as it did for me even without a local database set up.

[BurntSushi]

Hmmm, I still get the same error. I have no DATABASE_URL set.

[andrew@duff jiff-sqlx-poc]$ g rev-parse HEAD
d9882adb6444de9d0cbe988b287a3fd792fb9017
[andrew@duff jiff-sqlx-poc]$ cargo b
   Compiling jiff-sqlx-poc v0.1.0 (/home/andrew/tmp/issues/jiff/i272-jiff-sqlx-query-macro/jiff-sqlx-poc)
error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:20:5
   |
20 | /     sqlx::query!(
21 | |         "INSERT INTO groups (id, name, created_at) VALUES ($1, $2, $3)",
22 | |         input.id,
23 | |         input.name,
24 | |         input.created_at.to_sqlx())
   | |___________________________________^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:30:18
   |
30 |     let result = sqlx::query_as!(DbRow, "SELECT id, name, created_at FROM groups")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

Instead, I tried doing the sqlx database create approach, and I think that gives me what you're seeing:

[andrew@duff jiff-sqlx-poc]$ export DATABASE_URL='postgres://postgres:password@localhost/test'
[andrew@duff jiff-sqlx-poc]$ cargo sqlx database create
[andrew@duff jiff-sqlx-poc]$ cargo sqlx migrate run
Applied 1/migrate create table (4.620553ms)
[andrew@duff jiff-sqlx-poc]$ cargo b
   Compiling jiff-sqlx-poc v0.1.0 (/home/andrew/tmp/issues/jiff/i272-jiff-sqlx-query-macro/jiff-sqlx-poc)
error: optional sqlx feature `time` required for type TIMESTAMPTZ of param #3
  --> src/main.rs:20:5
   |
20 | /     sqlx::query!(
21 | |         "INSERT INTO groups (id, name, created_at) VALUES ($1, $2, $3)",
22 | |         input.id,
23 | |         input.name,
24 | |         input.created_at.to_sqlx())
   | |___________________________________^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: optional sqlx feature `time` required for type TIMESTAMPTZ of column #3 ("created_at")
  --> src/main.rs:30:18
   |
30 |     let result = sqlx::query_as!(DbRow, "SELECT id, name, created_at FROM groups")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

[BurntSushi]

I have no idea what that error means unfortunately. I'll ask upstream on the SQLx repo, but I've already discovered other problems that prevents Jiff from being as good as its chrono/time integrations.

[BurntSushi]

I asked here: launchbadge/sqlx#3487 (comment)

[abonander]

https://docs.rs/sqlx/latest/sqlx/macro.query.html#type-overrides-bind-parameters-postgres-only

[tonyfinn]

Note that @abonander 's linked solution does this by effectively disabling the type check from the macros which is a lot of the reason to use the macros in the first place. As well it makes the SQL query harder to read. So it would be ideal if there was work towards making the jiff timestamps work as chrono and time do today.

[abonander]

That would be possible with launchbadge/sqlx#3383

[tonyfinn]

So looking at launchbadge/sqlx#3383 , most of the todo items left on that one are around testing and judging by the comments on launchbadge/sqlx#3713 at least part of that is about example apps that use these new features. Is that something you'd be open to taking contributions for? I can't promise I'll have time, but if that's any help it's something I could try have a look at

[bionicles]

(I'm a nix noob) one issue preventing it from being easy to reproduce this is there's no readme guide to set up the direnv / envrc stuff you're using to manage environment variables, can you please write a clear step by step guide of how to use that in the readme of your repro ? or just a general getting started guide could help us get our local environments to match yours enough to run your code

to be more in line with the cargo ecosystem it might be good to make a Justfile (or bash script) and include commands with environment variables inline in there, like "just build" would pass the DATABASE_URL, right now if we're not already rolling on nix & direnv we can't reproduce your example easily

it seems like some of the issues here may be related to your IDE setup, are you using rust-analyzer?

^^ that wouldn't compile even if there were no issue with jiff_sqlx because it passes an owned value to a function expecting a reference

[BurntSushi]

@bionicles I showed a complete repro in this comment: launchbadge/sqlx#3487 (comment)

The AppObject errors might indeed be real and not work even if the jiff-sqlx problems didn't occur, but they seem like red herrings at this point.

My take on the situation here based on @abonander's response above is that you need to opt out of type checking when using Jiff data types, and that there is no current way to do any better.