Rust Sqlx handle INSERT ON CONFLICT

Question

I have a query which inserts the data of an user into the db. It works fine, but the users table has an unique index on the username, so when trying to create a row with a username already existing, it throws an error:

pub async fn create(user: UserRequest, pool: &PgPool) -> anyhow::Result<User> {
   let new_user = sqlx::query_as!(
       User,
       r#"
       INSERT INTO users (first_name, username)
       VALUES ($1, $2)
       RETURNING id, first_name, username
       "#,
       &user.first_name,
       &user.username,
   )
   .fetch_one(&pool.clone())
   .await?;
    
   Ok(User {
       id: new_user.id,
       first_name: new_user.first_name,
       username: new_user.username,
   })
}

&user is a function argument defined as:

pub struct UserRequest {
    pub first_name: String,
    pub username: String,
}

The endpoint using this function uses match to check if it's Ok(user) or anything else, and returns its response:

pub async fn create_user(
    user: web::Json<UserRequest>,
    pool: Data<PgPool>,
) -> impl Responder {
    let new_user = User::create(user.into_inner(), pool.get_ref()).await;
    match new_user {
        Ok(user) => HttpResponse::Ok().json(user),
        _ => HttpResponse::BadRequest().body(ERROR_MESSAGE),
    }
}

What can I do in order to capture that case, when there's an error because the row is trying to be duplicated? The case is I'd need to return a different HTTP status when that happens.

Answer 1

You can check the error code using get_code.

match new_user {
    Ok(user) => HttpResponse::Ok().json(user),
    Err(err) if err.get_code() == "23505" {
    // returns unique constraint violation response.
    },
    _ => HttpResponse::BadRequest().body(ERROR_MESSAGE),
}

To make this work as @MichaelAnderson pointed out in the comments,

you'd need to change the return type of create to Result<User, sqlx::postgres::PgDatabaseError>, or instead of changing the return type of create, you could use anyhow::Error::downcast or its related functions to get back to a PgDatabaseError