How do I implement Queryable and Insertable for custom field types in Diesel?

Question

I have an SQL table that I want to work with through Diesel:

CREATE TABLE records (
    id BIGSERIAL PRIMARY KEY,
    record_type SMALLINT NOT NULL,
    value DECIMAL(10, 10) NOT NULL
)

This table generates the following schema:

table! {
    records (id) {
        id -> Int8,
        record_type -> Int2,
        value -> Numeric,
    }
}

Diesel exports decimals as bigdecimal::BigDecimal, but I'd like to work with decimal::d128 instead. I also want to map record_type to an enum, so I declare my model like this:

use decimal::d128;

pub enum RecordType {
    A,
    B,
}

pub struct Record {
    pub id: i64,
    pub record_type: RecordType,
    pub value: d128,
}

I can't use #derive(Queryable, Insertable) because of non-standard type mapping, so I try to implement these traits myself:

impl Queryable<records::SqlType, Pg> for Record {
    type Row = (i64, i16, BigDecimal);

    fn build(row: Self::Row) -> Self {
        Record {
            id: row.0,
            record_type: match row.1 {
                1 => RecordType::A,
                2 => RecordType::B,
                _ => panic!("Wrong record type"),
            },
            value: d128!(format!("{}", row.2)),
        }
    }
}

I can't figure out how to implement Insertable. What is the Values associated type? Diesel's documentation is not very clear on this.

Maybe there's a better way to achieve what I'm trying to do?

Cargo.toml:

[dependencies]
bigdecimal = "0.0.10"
decimal = "2.0.4"
diesel = { version = "1.1.1", features = ["postgres", "bigdecimal", "num-bigint", "num-integer", "num-traits"] }
dotenv = "0.9.0"

Answer 1

I find it more convenient to create newtype wrappers that implement ToSql and FromSql. You can then build with these basic blocks to create larger types that can derive Queryable / Insertable.

This example only shows how to perform the mapping of the enum to and from a SmallInt, but the case for the decimal would be the same. The only difference would be in how you perform the transformations:

#[macro_use]
extern crate diesel;

mod types {
    use diesel::sql_types::*;
    use diesel::backend::Backend;
    use diesel::deserialize::{self, FromSql};
    use diesel::serialize::{self, ToSql, Output};
    use std::io;

    table! {
        records (id) {
            id -> BigInt,
            record_type -> SmallInt,
        }
    }

    #[derive(Debug, Copy, Clone, AsExpression, FromSqlRow)]
    #[sql_type = "SmallInt"]
    pub enum RecordType {
        A,
        B,
    }

    impl<DB: Backend> ToSql<SmallInt, DB> for RecordType
    where
        i16: ToSql<SmallInt, DB>,
    {
        fn to_sql<W>(&self, out: &mut Output<W, DB>) -> serialize::Result
        where
            W: io::Write,
        {
            let v = match *self {
                RecordType::A => 1,
                RecordType::B => 2,
            };
            v.to_sql(out)
        }
    }

    impl<DB: Backend> FromSql<SmallInt, DB> for RecordType
    where
        i16: FromSql<SmallInt, DB>,
    {
        fn from_sql(bytes: Option<&DB::RawValue>) -> deserialize::Result<Self> {
            let v = i16::from_sql(bytes)?;
            Ok(match v {
                1 => RecordType::A,
                2 => RecordType::B,
                _ => return Err("replace me with a real error".into()),
            })
        }
    }

    #[derive(Insertable, Queryable, Debug)]
    #[table_name = "records"]
    pub struct Record {
        pub id: i64,
        pub record_type: RecordType,
    }
}

There's a draft guide describing all the derives and their annotations, but it doesn't yet mention #[sql_type] for an entire type. This lets Diesel know what kind of underlying storage is needed inside of the database.

See also the Diesel tests for custom types.

Answer 2

Sometimes, the easiest way to understand what a macro does (derives are just a different form of macros) is to ask the compiler for the expanded code. With a nightly compiler, you can do this using this command:

cargo rustc -- -Z unstable-options --pretty expanded > expanded.rs

This will output the expanded code in expanded.rs.

We can now look at this file to see what #[derive(Insertable)] expands to. Naturally, I first changed the definition of Record to match the types that Diesel. After some cleaning up, this is the generated code:

impl<'insert> diesel::insertable::Insertable<records::table> for &'insert Record {
    type Values = <(
        Option<diesel::dsl::Eq<records::id, &'insert i64>>,
        Option<diesel::dsl::Eq<records::record_type, &'insert i16>>,
        Option<diesel::dsl::Eq<records::value, &'insert BigDecimal>>
    ) as diesel::insertable::Insertable<records::table>>::Values;

    #[allow(non_shorthand_field_patterns)]
    fn values(self) -> Self::Values {
        let Record {
            id: ref id,
            record_type: ref record_type,
            value: ref value,
        } = *self;
        diesel::insertable::Insertable::values((
            Some(::ExpressionMethods::eq(records::id, id)),
            Some(::ExpressionMethods::eq(records::record_type, record_type)),
            Some(::ExpressionMethods::eq(records::value, value))))
    }
}

impl diesel::query_builder::UndecoratedInsertRecord<records::table> for Record {
}

We can now adapt the Insertable implementation for our custom types. Notice that I've changed the Values associated type to return values directly rather than references to the values, because for two of them, the value is created in the values method, so we couldn't return a reference, and for the other one, returning a reference doesn't gain much in terms of performance.

impl<'insert> diesel::insertable::Insertable<records::table> for &'insert Record {
    type Values = <(
        Option<diesel::dsl::Eq<records::id, i64>>,
        Option<diesel::dsl::Eq<records::record_type, i16>>,
        Option<diesel::dsl::Eq<records::value, BigDecimal>>
    ) as diesel::insertable::Insertable<records::table>>::Values;

    #[allow(non_shorthand_field_patterns)]
    fn values(self) -> Self::Values {
        let Record {
            id: ref id,
            record_type: ref record_type,
            value: ref value,
        } = *self;
        let record_type = match *record_type {
            RecordType::A => 1,
            RecordType::B => 2,
        };
        let value: BigDecimal = value.to_string().parse().unwrap();
        diesel::insertable::Insertable::values((
            Some(::ExpressionMethods::eq(records::id, *id)),
            Some(::ExpressionMethods::eq(records::record_type, record_type)),
            Some(::ExpressionMethods::eq(records::value, value))))
    }
}