Custom PostgreSQL types with Diesel

Diesel is a rust library for interacting with SQL databases. This article demonstrates how to use custom PostgreSQL types with Diesel. For an example, we will implement the MPAA Rating type from the PostgreSQL DVD Rental sample database. This article uses Diesel 1.4.5 with Rust 1.45.0.

The mpaa_rating type is defined as

CREATE TYPE mpaa_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

We first need to create a struct that will represent the SQL Type. This struct needs to derive from SqlType and the type_name value needs to match the name of the type in the database (mpaa_rating in this case).

#[derive(SqlType)]
#[postgres(type_name = "mpaa_rating")]
pub struct MpaaRatingType;

This is the type we will use in the schema.rs file to declare the column's type. To use a custom type in the table! macro, we need to import the default diesel SQL types if any of the columns are standard SQL types and the type we just created (which I declared in the dbtypes module, update to match your module name).

table! {
    use diesel::sql_types::*;
    use crate::dbtypes::Mpaa_rating;

    film (film_id) {
        /* Other fields omitted */
        rating -> Nullable<Mpaa_rating>,
    }
}

Since this is an enum type, we will need an enum that matches the definition in the database. The sql_type matches the struct we just defined.

#[derive(Debug, PartialEq, FromSqlRow, AsExpression, Serialize)]
#[sql_type = "MpaaRatingType"]
pub enum MpaaRating {
    G,
    PG,
    PG13,
    NC17,
    R,
}

Finally, we need to implement some traits to be able to serialize and deserialize our type.

impl ToSql<MpaaRatingType, Pg> for MpaaRating {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
        match *self {
            MpaaRating::G => out.write_all(b"G")?,
            MpaaRating::PG => out.write_all(b"PG")?,
            MpaaRating::PG13 => out.write_all(b"PG13")?,
            MpaaRating::NC17 => out.write_all(b"NC17")?,
            MpaaRating::R => out.write_all(b"R")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<MpaaRatingType, Pg> for MpaaRating {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        match not_none!(bytes) {
            b"G" => Ok(MpaaRating::G),
            b"PG" => Ok(MpaaRating::PG),
            b"PG-13" => Ok(MpaaRating::PG13),
            b"NC-17" => Ok(MpaaRating::NC17),
            b"R" => Ok(MpaaRating::R),
            rating => Err(format!("Unknown Mpaa Rating: {:?}", rating).into()),
        }
    }
}

Full code

use diesel::deserialize::{self, FromSql};
use diesel::pg::Pg;
use diesel::serialize::{self, IsNull, Output, ToSql};
use diesel::*;
use std::io::Write;
use serde::Serialize;

#[derive(SqlType)]
#[postgres(type_name = "mpaa_rating")]
pub struct MpaaRatingType;

#[derive(Debug, PartialEq, FromSqlRow, AsExpression, Serialize)]
#[sql_type = "MpaaRatingType"]
pub enum MpaaRating {
    G,
    PG,
    PG13,
    NC17,
    R,
}

impl ToSql<MpaaRatingType, Pg> for MpaaRating {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
        match *self {
            MpaaRating::G => out.write_all(b"G")?,
            MpaaRating::PG => out.write_all(b"PG")?,
            MpaaRating::PG13 => out.write_all(b"PG13")?,
            MpaaRating::NC17 => out.write_all(b"NC17")?,
            MpaaRating::R => out.write_all(b"R")?,
        }
        Ok(IsNull::No)
    }
}

impl FromSql<MpaaRatingType, Pg> for MpaaRating {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        match not_none!(bytes) {
            b"G" => Ok(MpaaRating::G),
            b"PG" => Ok(MpaaRating::PG),
            b"PG-13" => Ok(MpaaRating::PG13),
            b"NC-17" => Ok(MpaaRating::NC17),
            b"R" => Ok(MpaaRating::R),
            rating => Err(format!("Unknown Mpaa Rating: {:?}", rating).into()),
        }
    }
}

Sean Humeniuk

Software Developer

Custom PostgreSQL types with Diesel

Full code