rustrust-sqlx

If I insert an Option<sqlx::types::Json<Struct>> sqlx writes the text 'null' in DB instead of mark that field as NULL

I thought the problem was me and my code and so I tried to reproduce the problem with sqlx example directly.

I only made minor changes by adding an Option<> to simulate my real problem.

- async fn add_person(pool: &PgPool, person: Person) -> anyhow::Result<i64> {
+ async fn add_person(pool: &PgPool, person: Option<Person>) -> anyhow::Result<i64> {

and in migrations:

- person JSONB NOT NULL
+ person JSONB

If I run it it is saved in the DB:

image

and instead I expect this:

image

Where am I doing wrong?

Minimal Reproduction

https://github.com/frederikhors/iss-sqlx-option-null

Info

Solution

  • So the function (from official example with your changes) looks like this:

    async fn add_person(pool: &PgPool, person: Option<Person>) -> anyhow::Result<i64> {
    let rec = sqlx::query!(
        r#"
INSERT INTO people ( person )
VALUES ( $1 )
RETURNING id
        "#,
        Json(person) as _
    )
    .fetch_one(pool)
    .await?;

    Ok(rec.id)
}

    This isn't passing an Option<Json<_>>, its passing a Json<_>. So the person will be used as a JSON value. If you pass None as a person, that is interpreted as a JSON null value rather than an SQL NULL. You instead want to express that a None person should not have a JSON value.

    Try this:

    sqlx::query!(
    r#"
INSERT INTO people ( person )
VALUES ( $1 )
RETURNING id
    "#,
    person.map(Json) as _
)

    This is now passing an Option<Json<_>> instead.