How to insert nullable values?

[D1plo1d]

I am using Option<String> to represent a nullable String but my code bellow complains that it expected a String instead of Option. How should nullable values be written with sqlx?

My code that doesn't work:

let name = Some("D1plo1d");
sqlx::query!(
    "INSERT INTO users (name) VALUES ($1)",
    name
);

[abonander]

We do support nullable parameters, it's just the type hack we do to check that String and Option<String> are compatible doesn't take into account Option<&str>.

[D1plo1d]

Thanks for the quick reply. My code appears to still be broken with Option<String>. Is there something else I am missing in this example?

            let name = Some("D1plo1d".to_string());
            sqlx::query!(
                "INSERT INTO users (name) VALUES ($1)",
                name
            );

Error:

mismatched types

expected struct `std::string::String`, found enum `std::option::Option`

note: expected type `std::string::String`
         found type `std::option::Option<std::string::String>`

[abonander]

That's also not supposed to happen but in general I think I can make the errors here much better; I have a promising prototype in #94 but I still need to integrate it into the macros and I also want to add UI tests to see how it handles type mismatches.

[kilpatty]

Also seeing this with other types e.g. Option<i32> in similar scenarios.

[abonander]

#94 looks like it's gonna fix this problem more or less for good but it's a breaking change because we can no longer support unsized expressions (string[..] without a leading &) but that brings us more in line with println!() and was necessary to make the compiler errors really nice.

[kilpatty]

@abonander amazing! Let me know if I can help at all!

[abonander]

Give the branch a try if you don't mind. I've got some testing on it but if it fixes your use-case that would really increase my confidence on it.

[kilpatty]

Just tested it out tonight, working perfectly so far with no code change on our end. Will let you know if I see anything funky, but looking good so far! Awesome job!

EDIT: I actually did end up having to change some scenarios where we were passing a String from a struct. e.g. worker.subscriber_id to &str in the query. That being said, it's actually cleaner for me and like you said replicates println! closer so overall I'm a big fan of that change as well.

[abonander]

@kilpatty that edit is interesting. I tried to finagle the code so it didn't require ownership but I'm guessing those structs were behind & references and you got errors about trying to move out of those?

That's suboptimal. I'm pretty sure println!() doesn't have that problem; we're trying to match its behavior to simplify teaching of query!().

[kilpatty]

Yes exactly they were behind & references. Looks like it's mimicking the behavior of dbg!() vs println!() in that scenario.

[D1plo1d]

@abonander wow, awesome! #94 even fixed some additional Option issues I was running into.

I'm not sure if this is a bug but while on #94 running a query! selecting Nullable types gives me Error: unexpected null for non-null column. Changing that to a queryAs! with Options resolves my issue.

Is this expected behavior, bug or a regression?

Error: unexpected null for non-null column

sqlx::query!(
            "SELECT * FROM invites WHERE public_key=$1",
            identity_public_key
        )

Patched Version

pub struct Invite {
    pub id: i32,
    pub public_key: String,
    pub created_at: DateTime<Utc>,
    pub is_admin: bool,

    pub slug: Option<String>,
    pub private_key: Option<String>,
}

sqlx::query_as!(
            Invite,
            "SELECT * FROM invites WHERE public_key=$1",
            identity_public_key
        )

[abonander]

@D1plo1d nullability in output columns in query!() is actually an orthogonal issue. I have most of the groundwork in #108, I just have to update the wording on the errors after some internal bikeshedding we did. We're still open to opinions on those though.

[D1plo1d]

@abonander That's awesome. Appreciate the work y'all are putting into these quality of life issues!

[Ddystopia]

Hello, if you want to use UNNEST, sqlx still requires T instead of Option<T> - so you can't really insert multiple nullable values