Rust Enums in SQLite with Diesel

Hello, future me. Who can't remember how to do this again 👋.

This blog's back end is written in Rust, and ideally, my API is consistent and typed. For a few fields, I'd like to use a Rust Enum to encapsulate certain options, but by default, Diesel, the Rust database ORM I'm using to interface with my SQLite database, only maps to primitive types such as i32 and String.

pub enum State {
    New,
    Processed,
    Failed,
}

String representation

In order to use Enums, we need to implement a few traits. In my case, I use SQLite's TEXT field and not an Int/ TinyInt because it's for my personal blog, and here I place readability higher than efficiency.

If I open the Database with another client after two years, I don't want to read the code to see what the Int 3 was again for the state field.

First, let's ensure we can cast the enum from/to a String by implementing the Display and TryFrom traits.

impl fmt::Display for State {
    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
        match self {
            State::New => write!(f, "New"),
            State::Processed => write!(f, "Processed"),
            State::Failed => write!(f, "Failed"),
        }
    }
}

impl TryFrom<&str> for State {
    type Error = String;

    fn try_from(value: &str) -> Result<Self, Self::Error> {
        match value {
            "New" => Ok(State::New),
            "Processed" => Ok(State::Processed),
            "Failed" => Ok(State::Failed),
            _ => Err(format!("Unknown state: {}", value)),
        }
    }
}

This way, we can convert the enum to a string and back again.

assert_eq!(Ok(State::New), "New".try_into());
assert_eq!("New", State::New.to_string());

Database representation

Next, we need to add a few Diesel derives to the enum:

#[derive(FromSqlRow, Debug, AsExpression)]
#[diesel(sql_type = Text)]
pub enum State {
    New,
    Processed,
    Failed,
}

This tells Diesel to use a Text field for the value, and that it can be used in expressions.

Finally, we can implement the FromSql and ToSql traits that convert the enum to the SQL type and back again. We use the previously implemented Display and TryFrom here, so our String and Database representations use the same string values.

impl FromSql<Text, Sqlite> for State {
    fn from_sql(bytes: SqliteValue) -> diesel::deserialize::Result<Self> {
        let t = <String as FromSql<Text, Sqlite>>::from_sql(bytes)?;
        Ok(t.as_str().try_into()?)
    }
}

impl ToSql<Text, Sqlite> for State {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> diesel::serialize::Result {
        out.set_value(self.to_string());
        Ok(diesel::serialize::IsNull::No)
    }
}

Now that we’ve implemented the traits, we can define the field as Text in the database, and use the State enum in the in- and output.

diesel::table! {
    tasks (id) {
        id -> Text,
        state -> Text,
    }
}

#[derive(Selectable, Queryable, Associations, Clone, Debug)]
pub struct Task {
    pub id: String,
    pub state: State,
}

And for ultimate lazyness, here’s the complete code:

use diesel::{
    deserialize::FromSql,
    serialize::{Output, ToSql},
    sql_types::Text,
    sqlite::{Sqlite, SqliteValue},
    AsExpression, FromSqlRow,
};
use std::fmt;

#[derive(FromSqlRow, Debug, AsExpression)]
#[diesel(sql_type = Text)]
pub enum State {
    New,
    Processed,
    Failed,
}

impl fmt::Display for State {
    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
        match self {
            State::New => write!(f, "New"),
            State::Processed => write!(f, "Processed"),
            State::Failed => write!(f, "Failed"),
        }
    }
}

impl TryFrom<&str> for State {
    type Error = String;

    fn try_from(value: &str) -> Result<Self, Self::Error> {
        match value {
            "New" => Ok(State::New),
            "Processed" => Ok(State::Processed),
            "Failed" => Ok(State::Failed),
            _ => Err(format!("Unknown state: {}", value)),
        }
    }
}

impl FromSql<Text, Sqlite> for State {
    fn from_sql(bytes: SqliteValue) -> diesel::deserialize::Result<Self> {
        let t = <String as FromSql<Text, Sqlite>>::from_sql(bytes)?;
        Ok(t.as_str().try_into()?)
    }
}

impl ToSql<Text, Sqlite> for State {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> diesel::serialize::Result {
        out.set_value(self.to_string());
        Ok(diesel::serialize::IsNull::No)
    }
}