Sqlx: How to deserialize an enum value

Hi,

I started playing with sqlx and ran into an issue which I'm not quite sure how to solve. Here's some example code:

#[derive(Debug, sqlx::FromRow)]
struct Cat {
    name: String,
    state: Status,
}

#[derive(Debug, PartialEq, Eq)]
enum Status {
    Hungry,
    NotHungry,
}

// this is what bothers me
impl From<String> for Status {
    fn from(s: String) -> Self {
        match s.as_str() {
            "Hungry" => Status::Hungry,
            "NotHungry" => Status::NotHungry,
            _ => Status::NotHungry
        }
    }
}

#[tokio::main]
async fn main() {
    let db_pool = sqlx::MySqlPool::connect("mysql://test:test@localhost:3307/testdb").await;
    let pool = match db_pool {
        Ok(pool) => {
            pool
        },
        Err(e) => {
            panic!("Error: {:?}", e);
        }
    };

    let x = &pool;
    let result:Result<Vec<Cat>, Error> = sqlx::query_as!(Cat, "SELECT a.name, a.state FROM cats a").fetch_all(x).await;

    let result_set = match result {
        Ok(cats) => cats,
        Err(e) => {
            panic!("Error: {:?}", e);
        }
    };

    result_set.iter().filter(|e| e.state == Status::NotHungry).for_each(|e| println!("{:?}", e));
}

//SQL
create table cats
(
    id    int auto_increment primary key,
    name  varchar(20)                  not null,
    state enum ('Hungry', 'NotHungry') not null
);

In order for sqlx to deserialize the status value into an enum value I need to implement the From trait. The data type in the DB is also an enum which makes any value not in the Status enum unrepresentable. What is the proper way to create the enum value from the DB row?

1 Like

Does this SO answer help you?

Hi,

I have only started to use sqlx, too.

I have not used enum with sqlx yet. I searched and found this two years old thread: SQLX Mysql Enum decode error #1379. This answer suggests trait FromRow.

The code in the answer seems to imply that a non-match would default to a specific enum?

I hope the thread offers you some suggestions, too.

Best regards,

...behai.

Thanks very much!

I changed the query string like this and it works:

let result:Result<Vec<Cat>, Error> = sqlx::query_as!(
        Cat,
        "SELECT \
        a.name, \
        a.state as \"state: Status\" \
        FROM cats a")
        .fetch_all(x).await;

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.