I’ve been working with diesel and serde. I use diesel for my postgres datastore, and serde for serializing/deserializing data to the web. Recently I came across a situation where I needed to define my type in diesel as well as implement deserialize in serde. The example below is a fairly simple so it makes for a good example to share so others can learn (and so I can remember how all this works next time I need it).

One type in Diesel

I have a struct to represent a single time-series value which contains an id, value, date_time and a published field. It’s pretty straightforward.

#[derive(PartialEq, Debug, Clone, Queryable, Identifiable, Insertable, AsChangeset, Associations, Serialize, Deserialize)]
#[table_name="readings"]
#[primary_key(date_time)]
pub struct ReadingDbEntity {
    pub id: i64,
    pub date_time: DateTime<Local>,
    pub value: f64,
    pub published: bool,
}

A few weeks ago, I wanted to expand the published field to be more than just a boolean. I want to distinguish between data that is published, unpublished, and pending. There are few options to represent this properly in the Database,

  1. Create an Enum in the database and have three values to represent the three values of state. Then alter the table to use that new Enum. Then update all the previously inserted rows to have the new data-type. (As a note, while support of postgres enums in diesel exists, it requires some work, some of which i’ll be covering here. Another note is that a crate exists to aide with sql enums..
  2. Alter the table in the DB to be a varchar and use string to represent the new states, “published”, “unpublished”, and “pending”. Then update all the previously inserted rows to have the new data-type. (Yuck, this is the worst thing you can do in a DB).
  3. Keep the boolean value in the DB and represent pending and unpublished as false and published as true.

I choose option 3. since it didn’t involve updating all the previous rows in the table, and the pending state is only a state that I care about in the running memory of my program, not in the DB. Publishing the same time-series entry has no ill-effect in my scenario so there is no harm in publishing the same entry multiple times except for bandwidth.

How to get this tri-state variable to get represented as a boolean in the DB? the new structure look like the following,

pub enum PublishState
{
    Published,
    Unpublished,
    Pending,
}

#[derive(PartialEq, Debug, Clone, Queryable, Identifiable, Insertable, AsChangeset, Associations, Serialize, Deserialize)]
#[table_name="readings"]
#[primary_key(date_time)]
pub struct ReadingDbEntity {
    pub id: i64,
    pub date_time: DateTime<Local>,
    pub value: f64,
    pub published: PublishState,
}

The work on altering the ReadingDbEntity struct is done. That was easy, right?. However, PublishState needs some attention. First let’s try to get this value to collapse into a boolean data type for the DB. To do so, the struct needs to have a sql_type annotation to tell Diesel that it’s a bool value.

#[derive(Debug,PartialEq,AsExpression,Clone,Serialize)]
#[sql_type = "Bool"]
pub enum PublishState
{
    Published,
    Unpublished,
    Pending,
}

The #[sql_type = "Bool"] proc-macro tells diesel what date-type to expect. This attribute also requires a function to serialize data from PublishState to Bool using the ToSql trait. for more information see the documentation for ToSql. The implementation of the ToSql trait for PublishState is below. It’s pretty straightforward to how it serializes the data. If you require multiple database backends, you’ll need to reimplement this trait for each database backend.

impl ToSql<Bool, Pg> for PublishState {
    fn to_sql<W: std::io::Write>(&self, out: &mut Output<W, Pg>) -> ::diesel::serialize::Result {
        match self {
            &PublishState::Published => ToSql::<Bool, Pg>::to_sql(&true, out),
            &PublishState::Unpublished | &PublishState::Pending =>  ToSql::<Bool, Pg>::to_sql(&false, out)
        }
    }
}

Ok, at this point we can create a PublishState enum and convert it into a postgres datatype, but that’s not really that useful by itself, we need to be able to deserialize data from the database into a PublishState enum. To accomplish this task, the PublishState enum needs to derive the FromSqlRow by adding FromSqlRow to the derive line. The trait FromSql is also required.

The FromSql trait is trivial to implement for this case. Since we’re expecting a Bool type, the input of type Option<&[u8]> can be checked for a zero or one. I can image that implementing this trait for a more complicated type could be challenging (perhaps not too bad if you use serde), but for a simple boolean, it’s trivial, which is a good place to start. This function checked for the value contained in the Option, if it exists and the first element of the slice is not 0, then use PublishState::Published, otherwise we’ll use PublishState::Unpublished. The implementation is below:

impl FromSql<diesel::sql_types::Bool, Pg> for PublishState {
    fn from_sql(bytes: Option<&[u8]>) -> diesel::deserialize::Result<Self> {
        println!("Reading from sql: bytes:{:?}", bytes);
        match bytes {
            Some(bytes) => if bytes[0] != 0 { Ok(PublishState::Published) } else { Ok(PublishState::Unpublished) },
            None => Ok(PublishState::Unpublished),
        }
    }
}

Now we can have a main function insert data which contains PublishState.

fn main()
{
    let diesel_conn = establish_connection();
    let new_example_readings = NewReadingDbEntity { value: 0.1, published: PublishState::Pending };
    let r : Vec<ReadingDbEntity> = diesel::insert_into(::schema::readings::table).values(&new_example_readings).get_results(&diesel_conn).expect("Can't save new data");
    println!("r: {:?}", r);
}

For the full code example, see my examples gitlab repo

I recommend looking at Diesel’s tests in their Github repo. In particular, their custom type test which demonstrates a simple example using ToSql and FromSql.

My Example code can be found in my gitlab examples repo.