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:

and instead I expect this:

Where am I doing wrong?

Minimal Reproduction

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

Info

SQLx version: "0.7.4"
SQLx features enabled: "macros", "postgres", "runtime-tokio", "chrono", "uuid"
Database server and version: Postgres 16
Operating system: Windows
rustc --version: rustc 1.79.0 (129f3b996 2024-06-10)

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.