HOW-TO: SQLX + Enums

[W2Wizard]

SQLX + Postgre + Prisma + Enums

After having to fight with this pesky thing for 5 decades and having scoured throughout the different docs, discussions and issues I am here to merely document my cure to wanting to insert enums into my PostgreSQL DB and using Prisma to model my schema.

My problem

I want to have user roles, nothing to insane you would assume right? Well apparently it's not that easy...
Using Prisma I generate all my database models including my enums! (Spoiler: Prisma isn't a problem here, I mean it simply creates the enum, nothing else...)

However, query_as! doesn't understand what an enum is and what to do with it.

My solution

Behold:

// Prisma Schema
enum user_role {
    DEMO
    USER
    DEV
}

// Backend
use serde::{Deserialize, Serialize};
use schemars::JsonSchema;
use sqlx::FromRow;
use uuid::Uuid;

#[derive(Debug, Deserialize, Serialize, Clone, Copy, sqlx::Type, PartialEq, JsonSchema)]
#[sqlx(type_name = "user_role", rename_all = "lowercase")]
pub enum UserRole {
    DEMO,
    USER,
    DEV
}

// Only thing missing / erroring is that we are not returning every field so we have to specifiy them ALL individually simply because of the as thingy
    let update_demo = query_as!(
        User,
        r#"
        UPDATE users SET role=($1::text)::user_role WHERE id=$2 RETURNING id, created_at, updated_at, role as "role:UserRole"
        "#,
        UserRole::USER.to_string(),
        Uuid::new_v4()
    );

    let insert_demo = query_as!(
        User,
        r#"
        INSERT INTO users (id, created_at, updated_at, role) VALUES ($1, NOW(), NOW(), ($2::text)::user_role) RETURNING id, created_at, updated_at, role as "role:UserRole"
        "#,
        Uuid::new_v4(),
        UserRole::USER.to_string()
    );

    let select_demo = query_as!(
        User,
        r#"
        SELECT id, created_at, updated_at, role as "role:UserRole" FROM users WHERE id=$1
        "#,
        Uuid::new_v4()
    );

The ugly

One problem that I'm not sure how to address is how I can just do SELECT * because SQLX will error ahead of time to you that this silly enum type isn't compatible bla bla bla.

The unfortunate fix is that we have to specify every individual field so that we can do this weird archaic syntax of role as "role:UserRole" and role=($1::text)::user_role

Summary

For love of of all that is holy P L E A S E document this, add an example idk just anything that shows how you can insert and modify basic primitive types including enums. Figuring this out with only a few references such as #1004 and #3044 was a headache

Ideally I wish I could just throw an enum into query_as! and have it do the magic of whatever it wants do for me but alas, we are limited by the technology of our time.