[Solved] How do i create custom Postgres enum types in Rust SQLx? – Rust

The Problem:

A user is trying to create a custom enum type in PostgreSQL using Rust SQLx. The enum type is successfully created in the database, but when querying the database, the user encounters an error stating that the column type is unsupported. Despite modifying the SQLx macro, the issue persists.

The Solutions:

Solution 1: Using SQLX’s Query Macros with Explicit Alias

To resolve the issue, instead of using the sqlx::query_as! macro with the User model, define an explicit alias for the enum column in the SQL query. This helps SQLx to correctly recognize the custom enum type:

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

In this query, the role column is explicitly aliased as "role: model::Role" to let SQLx know that it should use the custom enum type model::Role for that column. This alias provides the necessary information to deserialize the enum value correctly.

Solution 2: Using the `AS` clause

To fix the issue, you can manually specify the type in your SQL query using the `AS` clause. For example:

SELECT * FROM users WHERE email = $1 AND password = $2 AND role = $3 AS "role!: Role"

This way, SQLx will know how to map the `role` column to the `Role` enum in your Rust code.

Solution 3: Force Type Conversion with `as`

To resolve the “unsupported type” error in this Rust SQLx-based code, you can utilize SQL’s as keyword to explicitly cast the role column to the custom Role type defined in Rust. Here’s the revised query:

let user = match sqlx::query_as!(
	User,
	"SELECT * FROM users WHERE email = $1 AND password = $2",
	&email,
	&password,
) {
	Ok(user) => user,
	Err(e) => return Err(APIError::from(e)),
};

user.role = match sqlx::query_scalar!(
	"SELECT role as "role: Role" FROM users WHERE email = $1 AND password = $2",
	&email,
	&password,
) {
	Ok(role) => role,
	Err(e) => return Err(APIError::from(e)),
};

In this modified code, an additional query is performed to retrieve the role column as a string. Subsequently, a match statement is used to convert the string to the Role enum and assign it to the User‘s role field. This approach ensures the correct interpretation of the enum type during database interactions.