How to get postgresql enums to work with rust and actix-web: enums, models, schema, and handlers?

I have this enum type proposal_status in my postgresql db
with a default value of "Under_Review" and a NotNull constraint (even though the screenshot says otherwise lol, i do have the constraint and default value at the db level)

I am new to rust and actix-web, so I would really like someone to call-out my mistake(s) :slight_smile:

My goal is to be able to create a Proposal without changing my db structure. I do not have any specific requirements for rust mirroring the db constraints, the rust code just needs to perform the crud actions in my eyes, the schema/model/enums will end up as whatever gets the data to/from the db successfully.

I have tried numerous combinations and the below setup builds successfully with cargo run, however when using postman to POST a new Proposal, with field status as "<Proposal_Status_Enum>" e.g "Under_Review",
I get this error:

"column \"status\" is of type proposal_status but expression is of type text"

And yes, I can GET all proposals, it's not my postman setup.

My models do not reflect the above error:

#[derive(Queryable, Serialize, Deserialize, Debug, Clone)]
pub struct Proposal {
    pub proposal_id: i32,
    pub user_id: i32,
    pub slug: String,
    pub title: String,
    pub description: String,
    pub status: String,
    pub yeas: i32,
    pub nays: i32,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Insertable, Serialize, Deserialize, Debug, Clone)]
#[table_name = "proposals"]
pub struct NewProposal {
    pub user_id: i32,
    pub slug: String,
    pub title: String,
    pub description: String,
    pub status: Option<String>,
    pub yeas: i32,
    pub nays: i32,
}

Neither does my schema:

diesel::table! {
    use diesel::sql_types::*;

    proposals (proposal_id) {
        proposal_id -> Int4,
        user_id -> Int4,
        slug -> Text,
        title -> Text,
        description -> Text,
        status -> Text,
        yeas -> Int4,
        nays -> Int4,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}

and I'm most concerned about my enums (it's scrollable):

#[derive(Debug, Clone, Copy, PartialEq, FromSqlRow, AsExpression, Serialize, Deserialize, EnumString)]
#[sql_type = "ProposalStatusSql"]
pub enum ProposalStatus {
    UnderReview,
    Rejected,
    Active,
    Completed,
}

#[derive(SqlType)]
#[postgres(type_name = "proposal_status")]
pub struct ProposalStatusSql;

impl Display for ProposalStatus {
    fn fmt(&self, f: &mut Formatter<'_>) -> Result {
        match self {
            ProposalStatus::UnderReview => write!(f, "Under_Review"),
            ProposalStatus::Rejected => write!(f, "Rejected"),
            ProposalStatus::Active => write!(f, "Active"),
            ProposalStatus::Completed => write!(f, "Completed"),
        }
    }
}

impl Default for ProposalStatus {
    fn default() -> Self {
        ProposalStatus::UnderReview
    }
}

impl<DB> ToSql<Text, DB> for ProposalStatus
where
    DB: Backend,
    String: ToSql<Text, DB>,
{
    fn to_sql<W: Write>(&self, out: &mut Output<W, DB>) -> serialize::Result {
        let text = match *self {
            ProposalStatus::UnderReview => "Under_Review",
            ProposalStatus::Rejected => "Rejected",
            ProposalStatus::Active => "Active",
            ProposalStatus::Completed => "Completed",
        };
        let text_str = text.to_string();
        <String as ToSql<Text, DB>>::to_sql(&text_str, out)
    }
}

impl FromSql<Text, Pg> for ProposalStatus {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        match <String as FromSql<Text, Pg>>::from_sql(bytes)?.as_str() {
            "Under_Review" => Ok(ProposalStatus::UnderReview),
            "Rejected" => Ok(ProposalStatus::Rejected),
            "Active" => Ok(ProposalStatus::Active),
            "Completed" => Ok(ProposalStatus::Completed),
            _ => Err("Unrecognized status".into()),
        }
    }
}

Lastly here is my query to POST a new Proposal (it's scrollable):

pub async fn create_proposal_handler(
    db_pool: web::Data<DbPool>,
    new_prpsl: web::Json<NewProposal>,
) -> impl Responder {
    let conn = db_pool
        .get()
        .expect("Failed to get DB connection from pool");

    info!("Attempting to fetch proposal by id");    
    match create_proposal_query(&conn, new_prpsl.into_inner()) {
        Ok(proposal) => {
            info!("Successfully created proposal");
            HttpResponse::Ok().json(proposal)
        }, 
        Err(e) => {
            error!("Failed to create proposal");
            HttpResponse::InternalServerError().json(e.to_string())
        }
    }
}
 
pub fn create_proposal_query(conn: &PgConnection, new_prpsl: NewProposal) -> QueryResult<Proposal> {
    let status_enum = match new_prpsl.status {
        Some(ref status_text) => match status_text.as_str() {
            "Under_Review" => ProposalStatus::UnderReview,
            "Rejected" => ProposalStatus::Rejected,
            "Active" => ProposalStatus::Active,
            "Completed" => ProposalStatus::Completed,
            _ => ProposalStatus::UnderReview,
        },
        None => ProposalStatus::UnderReview, // Default case (not required since I have the db default value)
    };

    let status_value = match status_enum {
        ProposalStatus::UnderReview => "Under_Review".to_string(),
        ProposalStatus::Rejected => "Rejected".to_string(),
        ProposalStatus::Active => "Active".to_string(),
        ProposalStatus::Completed => "Completed".to_string(),
    };

    let insertable_proposal = NewProposal {
        status: Some(status_value),
        ..new_prpsl
    };

    diesel::insert_into(proposals)
        .values(&insertable_proposal)
        .get_result(conn)
}

Any help would be appreciated, thanks in advanced!!
I can provide more info if needed.

Please provide less next time. Or rather, try boiling down your code to the essence of your error, stripping everything from it that is not directly involved in causing the error. In the process, you more often than not find a solution on your own.

I'm not familiar with diesel, but to me the error message reads like you once defined your proposals table differently than your types are now. Namely, could it be that you used to have ProposalStatusSql (aka proposal_status on the SQL sideβ€”which is what #[postgres(type_name = "proposal_status")] does I assume) as the type of the status field of your proposals table? Your snippets suggest that status is now a text column and not a proposal_status column. Could you try to regenerate your sql tables so that the definition of your proposals table matches your application's data model and see if that is the cause of the error? Before doing that you could check out your up.sql generated by diesel (I think) and look at the CREATE TABLE proposals { ... } statement and see if I'm right.

1 Like

The error seems to be caused by the mismatch between the schema and the database. In the database, the type of status appears to be proposal_status, but in the schema it's Text. Have you manually modified the schema, or perhaps modified the database table without regenerating the schema? If so, try running diesel migration run to update the schema.

1 Like

Noted, I did feel it was a lengthy post, just wanted to include as much context without dragging out the replies back and forth.

The status column is still type proposal_status (in sql) and I did change the diesel table/models to use text/string respectively because additional implementation to handle their conversion to and from SQL types would be needed, mainly in enums definition and query handlers.

I did modify the schema, so I re-ran diesel migration run and it added ProposalStatus back to the status column.
As per my last comment, this will require a pretty thorough rewrite of the enums and handlers which will likely bring up new issues, but it is the right direction.

I will close this thread tomorrow to give time for additional replies. Thanks for the help all!

It shouldn't be too bad, you already have the ProposalStatus enum and ToSql and FromSql implemented for it, as far as I can tell you don't need to change them.

If I'm not completely mistaken, you just need to change the corresponding field(s) to use ProposalStatus instead of String and take out the conversions between the strings and the enum, and just work with the enum directly. (If there are other queries they may need similar changes to them)

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.