How to insert nullable (Optional) value ?

[azzamsa]

Hi.

I have used sqlx for several months, and I always avoid using Option because of this issue. Now it is time to solve it.

Given the code below:

create table if not exists user_ (
   id serial,
   name text not null unique,
   full_name text,
   --
   primary key (id)
);

pub struct CreateUserInput {
    pub name: String,
    pub full_name: Option<String>,
}

pub async fn create(pool: &PgPool, user_input: CreateUserInput) -> anyhow::Result<User> {
    let user = sqlx::query_as!(
        User,
        r#"insert into user_ (name, full_name) values ($1, $2) returning *"#,
        &user_input.name,
        &user_input.full_name,
    )
    .fetch_one(pool)
    .await
    .context("failed to perform a query to insert user")?;

    Ok(user)
}

I always get:

75 |         &user_input.full_name,
   |         ^^^^^^^^^^^^^^^^^^^^^
   |         |
   |         expected `str`, found enum `std::option::Option`
   |         expected due to the type of this binding
   |
   = note: expected reference `&str`
              found reference `&std::option::Option<std::string::String>`

In diesel, it is handled automatically.
I have read related issues but still have no clue.

Thanks in advance.

Related issue:

• How to insert nullable values? #93
• How to optionally add WHERE clauses, to model REST APIs #1207

[dependencies]
sqlx = { version = "0.5", features = ["runtime-async-std-native-tls", "postgres", "offline"] }

Additional note:

The strange thing is that it works with update, but not in insert

This works:

pub struct UpdateUserInput {
    pub id: i32,
    pub name: String,
    pub full_name: Option<String>,
}

pub async fn update(pool: &PgPool, user_input: UpdateUserInput) -> anyhow::Result<User> {
    let user = sqlx::query_as!(
        User,
        r#"update user_ set
              id = $1,
              name = $2,
              full_name = $3
           where id = $1 returning *"#,
        user_input.id,
        user_input.name,
        user_input.full_name,
    )
    .fetch_one(pool)
    .await;
    ```

[azzamsa]

I find the solution,

@@ -68,11 +68,17 @@ pub async fn create(pool: &PgPool, user_input: CreateUserInput) -> anyhow::Resul
         bail!("a user with same `name` already exists")
     }
 
+    let full_name = if user_input.full_name.is_some() {
+        user_input.full_name
+    } else {
+        None
+    };
+
     let user = sqlx::query_as!(
         User,
         r#"insert into user_ (name, full_name) values ($1, $2) returning *"#,
         &user_input.name,
-        &user_input.full_name,
+        full_name
     )
     .fetch_one(pool)
     .await