How do i create custom Postgres enum types in Rust SQLx?

Question

I am trying to create a custom enum type in Postgres and have done so successfully. My migration looks like this:

CREATE TYPE role AS ENUM ('admin', 'user');

ALTER TABLE users
ADD role role DEFAULT 'user';

Then i have created the enum type in Rust like this:

#[derive(Serialize, Deserialize, Debug, sqlx::Type)]
#[sqlx(type_name = "role", rename_all = "lowercase")] 
pub enum Role {
    ADMIN,
    USER
}

And i have altered the user model also:

#[derive(sqlx::FromRow, Debug)]
pub struct User {
    pub id: i32,
    pub email: String,
    pub username: String,
    pub password: String,
    pub role: Role,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

But now when i try to query the database like this:

let user = match sqlx::query_as!(
    User,
    "SELECT * FROM users WHERE email = $1 AND password = $2",
    &email,
    &password,
)

I get this error: unsupported type role of column #7 ("role")

What am i doing wrong?

I have tried playing around with the macro part

#[sqlx(type_name = "role", rename_all = "lowercase")],

But that does not seem to help.

Here is the full error from cargo check:

error: unsupported type role of column #7 ("role")
   --> src/routes/auth/mod.rs:140:20
    |
140 |           let user = match sqlx::query_as!(
    |  __________________________^
141 | |             User,
142 | |             "SELECT * FROM users WHERE email = $1 AND password = $2",
143 | |             &payload.email,
144 | |             &hash,
145 | |         )
    | |_________^
    |
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `rust-api-example` (bin "rust-api-example") due to previous error

Answer 1

I ended up fixing like this:

let user = sqlx::query_as!(
    model::User,
    "SELECT
    id,
    email,
    username,
    password,
    role AS \"role: model::Role\",
    created_at,
    updated_at
    FROM users WHERE id = $1",
    user_id
)
.fetch_one(&pool)
.await
.unwrap();