Rust中使用SQLX更新PostgreSQL自定义Enum类型的语法问题

问题背景 #

在Rust项目中使用SQLX操作PostgreSQL数据库，查询用户逻辑正常，但执行update_user函数更新用户的role字段（对应PostgreSQL自定义user_role枚举类型）时遇到语法问题，尝试多种SQL写法均未解决，寻求正确语法指导。

相关代码 #

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)
}

数据库迁移脚本 #

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()
);

尝试过的错误写法 #

• 写法1：

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""#

• 写法2：

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""#

• 写法3：

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""#

正确写法指导 #

前提准备 #

确保你的UserRole枚举实现SQLX的PostgreSQL枚举适配，通过宏指定对应数据库的枚举类型名：

#[derive(Debug, Clone, sqlx::Type)]
#[sqlx(type_name = "user_role")]
pub enum UserRole {
    Admin,
    Moderator,
    User,
}

方案1：直接传递枚举类型参数 #

修改update_user的参数类型或转换为枚举后传递，避免字符串转译问题：

// 调整函数参数为UserRole类型，或在调用时转换
async fn update_user(
    &self,
    user_id: Option<Uuid>,
    role: UserRole,
) -> Result<Option<UserModel>, Error> {
    // ...省略其他代码...
    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,
        now,
        user_id,
    )
    .fetch_optional(&self.pool)
    .await?;
    // ...省略其他代码...
}

方案2：字符串参数显式转换为枚举 #

如果必须传递字符串，在SQL语句中显式将字符串转换为user_role枚举类型：

let updated_user = sqlx::query_as!(
    UserModel,
    r#"UPDATE users SET role=$1::user_role,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?;

内容的提问来源于stack exchange，提问作者RAprogramm