What is the best way to store an enum in a database in rusqlite?

Question

If I have a simple struct, one attribute of which contains a simple enum, how can I best store examples of this struct with their enumerations in the rusqlite database? Something like:

use rusqlite::{params, Connection, Result};

enum Sex{
    Unknown,
    Male,
    Female,
}

struct Person{
    name: String,
    sex: Sex
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory()?;

    conn.execute(
        "CREATE TABLE  people(
            name TEXT NOT NULL,
            sex TEXT NOT NULL
        )",
        (), // empty list of parameters.
    )?;


    let person_01 = Person{
        name: String::from("Adam"),
        sex: Sex::Male
    };

    conn.execute(
        "INSERT INTO people (name, sex) VALUES (?1, ?2)",
        (&person_01.name, &person_01.sex),
    )?;

    Ok(())
}

The problem is that sqlite only allows data of restricted types (NULL, INTEGER, REAL, TEXT), trying to use TEXT here for the enum gives the following error:

error[E0277]: the trait bound `Sex: ToSql` is not satisfied
   --> src/main.rs:33:9
    |
31  |     conn.execute(
    |          ------- required by a bound introduced by this call
32  |         "INSERT INTO tasklist (name, sex) VALUES (?1, ?2)",
33  |         (&person_01.name, &person_01.sex),
    |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `ToSql` is not implemented for `Sex`

This error makes sense, but what is the best way to implement this? Cast the enum to int? I read here that this is "removes the guarantee that values always represent the variants of the enum", which I agree with. It would be nicer to match the string.

I have tried to do this using strum, which allows me to add to_str & from_str to the enum, allowing it to be added to the database like so:

#[derive(strum_macros::Display, strum_macros::EnumString, Debug)]
enum Sex{
    Unknown,
    Male,
    Female,
}

...

    conn.execute(
        "INSERT INTO people (name, sex) VALUES (?1, ?2)",
        (&person_01.name, &person_01.sex.to_string())
    )?;

and retrieved like so:

    let mut stmt = conn.prepare("SELECT name, sex FROM people")?;
    let person_itr = stmt.query_map([], |row|{
        Ok(
            Person{
                name: row.get(0)?,
                sex: Sex::from_str(String::as_str(&row.get(1)?)).unwrap(),
            }
        )
    });

but this feels messy. Is there a better way?

I have seen here people manually implementing FromSqlRow for the enum, but is there a better (quicker) way?

The most common approach seesm to be, to implement ToSql for the enum and map it to a TEXT. This way you can use the most the approach described in https://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite — somnium, Jul 21 '22 at 15:33
@cdhowie, with a slight modification I was able to get this to work: `Sex::from_str(String::as_str(&row.get(1)?)).unwrap()`. But it is somewhat messy. I will modify the question to reflect this. Thank you. — FinleyGibson, Jul 21 '22 at 15:58
@FinleyGibson You probably want `?` (or maybe `.map_err(...)?`) instead of `.unwrap()` so you don't panic out of the whole program if there is bad data in the DB. Having said that, implementing `ToSql` and `FromSql` on your type is the right way to handle this. — cdhowie, Jul 21 '22 at 16:26

cdhowie · Accepted Answer · 2022-07-21T19:15:09.943

The right way to handle this is to implement ToSql and FromSql directly on your enum. This will make using it substantially more ergonomic, and possibly more efficient since you don't first have to convert to a type with an allocation, like String.

It also means the conversion to/from a string doesn't "infect" every interaction you have with the database; the conversion becomes automatic. So, while there is a bit more boilerplate, it will pay off every time you use this type in conjunction with the database.

impl ToSql for Sex {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        Ok(self.to_string().into())
    }
}

impl FromSql for Sex {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        value.as_str()?.parse()
            .map_err(|e| FromSqlError::Other(Box::new(e)))
    }
}

Now you can just do this when converting to Person:

sex: row.get(1)?,

Note that FromSqlRow is a trait specific to a postgres client; rusqlite has no such trait. If you wanted to, you could create a factory method on Person that constructs from a Row. That's up to you.

this works but seems like a good place for a derive macro, no? — sbeam, Nov 16 '22 at 17:21

What is the best way to store an enum in a database in rusqlite?

1 Answers1