unsupported type | rust | postgres | sqlx | update | enum | custom type

Question

async fn update_user<T: Into<String> + Send>(
    &self,
    user_id: Option<Uuid>,
    role: T,
) -> Result<Option<UserModel>, Error> {
    let now = chrono::Utc::now();

    if let Some(user_id) = user_id {
        let existing_user = sqlx::query_as!(
            UserModel,
            r#"SELECT id,name,email,password,verified,created_at,updated_at,role as "role: UserRole" FROM users WHERE id = $1"#,
            user_id
            )
        .fetch_optional(&self.pool)
        .await?;

        if existing_user.is_none() {
            return Err(Error::RowNotFound);
        }

        let updated_user = sqlx::query_as!(
            UserModel,
            r#"UPDATE users SET role=$1,updated_at=$2 WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#,
            role.into(),
            now,
            user_id,
        )
        .fetch_optional(&self.pool)
        .await?;

        return Ok(updated_user);
    }

    Err(sqlx::Error::RowNotFound)
}

in existing_user - all ok. all my problem in updated_user. i have migrations

CREATE TYPE user_role AS ENUM ('admin', 'moderator', 'user');

CREATE TABLE "users" (
    id UUID NOT NULL PRIMARY KEY DEFAULT (uuid_generate_v4()),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    verified BOOLEAN NOT NULL DEFAULT FALSE,
    password VARCHAR(100) NOT NULL,
    role user_role NOT NULL DEFAULT 'user',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

i inderstand that in update_user i have to write something like ...SET role=$1 as "role: UserRole"... but HOW??? i have already tried many different variants.

THERE IS PROBLEM WITH SYNTAX in my opinion

i've tried

r#"UPDATE users SET role=$1,updated_at=$2 WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#

and this

r#"UPDATE users SET role as "role:UserRole"=$1,updated_at=$2 WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#

and this

r#"UPDATE users SET role=$1,updated_at=$2 role as "role:UserRole" WHERE id=$3 RETURNING id,name,email,verified,created_at,password,updated_at,role as "role: UserRole""#

and many others

here is on github https://github.com/launchbadge/sqlx/discussions/3044

Answer 1

YES! I FOUND SOLUTION! Here is right query syntax:

r#"UPDATE users SET role=($1::text)::user_role, updated_at=$2 WHERE id=$3 RETURNING id, name, email, verified, created_at, password, updated_at, role as "role:UserRole""#,