Sqlite query_as! cannot find types

[D1plo1d]

I am migrating my code base from sqlx postgres to sqlx sqlite. I've ran sqlx migrate run successfully but I am now getting errors for each query_as! macro that every column's type isn't found in the database:

error: database couldn't tell us the type of column #1 ("id"); this can happen for columns that are the result of an expression
  --> src/models/user/mod.rs:64:23
   |
64 |           let db_user = sqlx::query_as!(
   |  _______________________^
65 | |             User,
66 | |             "
67 | |                 SELECT * FROM users
...  |
70 | |             user.user_id.parse::<i32>()?
71 | |         )
   | |_________^
   |
   = note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

Is this a known issue? Something I've misconfigured?

Edit: I should add that running PRAGMA table_info(users); from the sqlite3 CLI shows a user table with an id column.

[JesperAxelsson]

I can't see enough of the code to say whats wrong with any certainty. Did you change your query parameter when you changed to postgres? Postgres queries usually use $1 while sqlite use ?.

[D1plo1d]

Good point, I've replaced the $1 with ? but the errors are still largely the same:

cargo run
   Compiling teg-auth v0.1.0 (/home/[redacted])
error: database couldn't tell us the type of column #1 ("id"); this can happen for columns that are the result of an expression
  --> src/models/user/authenticate.rs:28:22
   |
28 |           let invite = sqlx::query!(
   |  ______________________^
29 | |             "SELECT id FROM invites WHERE public_key=?",
30 | |             identity_public_key
31 | |         )
   | |_________^
   |
   = note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

error: database couldn't tell us the type of column #1 ("id"); this can happen for columns that are the result of an expression
  --> src/models/user/authenticate.rs:37:24
   |
37 |               let user = sqlx::query!(
   |  ________________________^
38 | |                 "SELECT id FROM users WHERE firebase_uid=? AND is_authorized=True",
39 | |                 jwt_payload.sub
40 | |             )
   | |_____________^
   |
   = note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

[D1plo1d]

Src: https://github.com/tegapp/teg/blob/feature/sqlite/packages/teg-auth/src/models/user/authenticate.rs

[abonander]

It looks like if the declared type is not known then it's considered the same as a null decltype which is incorrect: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/sqlite/executor.rs#L161

We should be returning an error on this wildcard branch, not None. Or at the very least emitting a warning.

[abonander]

@D1plo1d as for your specific case, it looks like SQLite doesn't recognize the SERIAL type but because it's crazy it just defaults type declarations it doesn't recognize to NUMERIC. You should just use the INTEGER type for ID columns instead; SQLite will automatically assign a unique value for integer primary keys.

[D1plo1d]

We should be returning an error on this wildcard branch, not None. Or at the very least emitting a warning.

Better errors for this would be awesome. I was very unclear on what was going wrong.

it just defaults type declarations it doesn't recognize to NUMERIC

Wow, that is just an all kind of amazingly bad default. Thanks for helping me find the problem - I think that fixed it (not 100% for certain because now I have all sorts of other presumably unrelated errors of my own to deal with).

Cheers!