Option<i64> can't be queried

[jayhale]

I'm stumped on this one - I can't shake the error below when adding an Option<i64> field to a DB model:

Error:

error[E0277]: the trait bound `i64: From<std::option::Option<i64>>` is not satisfied
  --> src/db/water.rs:47:9
   |
47 | /         sqlx::query_as!(
48 | |             Water,
49 | |             r#"
50 | |                 SELECT
...  |
59 | |             slug
60 | |         ).fetch_optional(pool).await
   | |_________^ the trait `From<std::option::Option<i64>>` is not implemented for `i64`

The offending query:

pub async fn find_by_slug(pool: &Pool, slug: &str) -> Result<Option<Water>> {
        sqlx::query_as!(
            Water,
            r#"
                SELECT
                    id,
                    slug,
                    name,
                    created_at,
                    updated_at,
                    upstream_id
                FROM waters WHERE slug = $1
            "#,
            slug
        ).fetch_optional(pool).await
    }

The Water model:

#[derive(Deserialize, Serialize, JsonSchema)]
#[serde(rename_all = "camelCase", deny_unknown_fields)]
pub struct Water {
    pub id: i64,
    pub slug: String,
    pub name: String,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
    pub upstream_id: Option<i64>,
}

Using the various type-inference modifiers doesn't resolve the issue, e.g.:

upstream_id as "upstream_id?"
upstream_id as "upstream_id: _"
upstream_id as "upstream_id: Option<i64>"

[jayhale]

FWIW, the issue was that sqlx is depending on the NOT NULL flag in the column definition to infer Option<T>, which I had not included on the id column.

Resolved by changing:

CREATE TABLE waters (
    "id" INTEGER PRIMARY KEY,
    -- ...

... to ...

CREATE TABLE waters (
    "id" INTEGER PRIMARY KEY NOT NULL,
    -- ...

I found the issue by expanding the query_as! macro and discovering that sqlx was retrieving id as an Option<i64>.

Docs on option inference here: https://docs.rs/sqlx/latest/sqlx/macro.query.html#nullability-output-columns

For SQLite we perform a similar check to Postgres, looking for NOT NULL constraints on columns that come from tables. However, for SQLite we also can step through the output of EXPLAIN to identify columns that may or may not be NULL.