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

A runtime error or a compile-time error? If compile-time, what is the full error from `cargo check`? — Chayim Friedman, Jun 15 '23 at 05:29

rOOb85 · Answer 1 · 2023-06-16T21:26:02.457

I'm currently in very similar situation as you and your question actually helped solve my issue. In my case adding #[sqlx(type_name = "user_role")] to my enum fixed my issue. I'll post all my relevant code that now works. Hopefully this helps solve your problem as well.

I believe your issue is related to using the query_as macro. The macro has trouble automatically mapping custom types so when you SELECT * it doesn't understand how to map the results. I had to manually specify the type in my sql query i.e. user_role AS "user_role!: UserRole. I'm new to postgres so I don't know too much more than using that AS clause fixed it.

These posts go into more detail:
https://github.com/launchbadge/sqlx/issues/235
https://users.rust-lang.org/t/sqlx-postgres-how-to-insert-a-enum-value/53044/2

Here are my sql tables/types:

-- Create the enumeration type
CREATE TYPE user_role AS ENUM ('admin', 'user');

-- Create the roles table
CREATE TABLE
    roles (
        id SERIAL PRIMARY KEY,
        name user_role NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT NOW ()
    );

-- Create the users table
CREATE TABLE
    users (
        id SERIAL PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        user_role user_role NOT NULL,
        created_at TIMESTAMP
        WITH
            TIME ZONE DEFAULT NOW (),
        updated_at TIMESTAMP
        WITH
            TIME ZONE DEFAULT NOW () 
    );

Here are my models

#[derive(Clone, Debug, PartialEq, PartialOrd, sqlx::Type, Deserialize, Serialize)]
#[sqlx(type_name = "user_role", rename_all = "lowercase")]
pub enum UserRole {
    Admin,
    User,
}

#[derive(Debug, sqlx::FromRow, Deserialize, Serialize)]
#[allow(non_snake_case)]
pub struct UserModel {
    pub id: i32,
    pub username: String,
    pub password_hash: String,
    pub email: String, 
    pub user_role: UserRole,
    #[serde(rename = "createdAt")]
    pub created_at: Option<chrono::DateTime<chrono::Utc>>,
    #[serde(rename = "updatedAt")]
    pub updated_at: Option<chrono::DateTime<chrono::Utc>>,
}

Here is my insert function:

let query_result = sqlx::query_as!(
        UserModel,
        r#"INSERT INTO users (username,password_hash,email,user_role) VALUES ($1, $2, $3, $4) RETURNING id,username,password_hash,email,user_role AS "user_role!: UserRole", created_at, updated_at"#,
        body.username.to_string(),
        body.password.to_string(),
        body.email.to_string(),
        UserRole::User as UserRole
    )
    .fetch_one(&data.db)
    .await;

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

1 Answers1