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

Ali Hasan
postgresql rust-analyzer rust-sqlx

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!(
    role AS \"role: model::Role\",
    FROM users WHERE id = $1",

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!(
	"SELECT * FROM users WHERE email = $1 AND password = $2",
) {
	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",
) {
	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.


How do I fix the error "unsupported role type" when querying a Postgres enum in Rust with SQLx?

Manually specify the enum type in the SQL query using the AS clause, e.g., role AS "role: model::Role".

How can I create custom enum types in Postgres and use them in Rust with SQLx?

Create the enum type in Postgres using CREATE TYPE and define a corresponding enum in Rust with the #[sqlx(type_name = "enum_name")] attribute.

How do I solve the issue where SQLx cannot automatically map custom types when using the query_as macro?

Manually specify the type in the SQL query using the AS clause, e.g., user_role AS "user_role!: UserRole".