Dealing with postgres composite types with diesel-rs

Budi SyahiddinPRO @zeon256

06 June 2020

3 min read

blog database diesel-rs postgresql rust

# Overview

You will learn how to deal with Postgres composite types using Diesel.rs without any additional dependencies. Currently it is quite hard to look for documentation on how to do it and by writing this blog post hopefully will help someone. If you just want to see the code example, click here.

# Setup

In this example, we will have a Person struct with the following properties

Code

#[derive(Debug, Clone, Queryable, Identifiable)]
pub struct Person {
    pub id: i32,
    pub name: String,
    pub age: i32,
    pub pets: Vec<Pet>,
}


#[derive(Debug, Clone, FromSqlRow, AsExpression, PartialEq)]
#[sql_type = "PgPet"]
pub struct Pet {
    pub name: String,
    pub animal_type: AnimalType,
}


#[derive(Debug, Clone, FromSqlRow, AsExpression, PartialEq)]
#[sql_type = "PgAnimalType"]
pub enum AnimalType {
    Cat,
    Fish,
}

Code

CREATE TYPE animal_type AS ENUM ('Cat','Fish');
CREATE TYPE pet AS (name text, animal_type animal_type);

CREATE TABLE persons
(
    id   serial  not null
        constraint person_pk
            primary key,
    name varchar not null,
    age  int     not null,
    pets pet[]   not null
);

Notice that the custom types have #[sql_type = "PgType"]. This means that you will have to create types that will "point" to the real postgres type.

Code


#[derive(SqlType)]
#[postgres(type_name = "pet")]
pub struct PgPet;

#[derive(SqlType)]
#[postgres(type_name = "animal_type")]
pub struct PgAnimalType;

Now when you generate schema.rs, it will have the wrong types. So you need to replace it with the following. Take note at the imports.

Code

table! {
    use diesel::sql_types::*;
    use crate::PgPet;
    persons (id) {
        id -> Int4,
        name -> Varchar,
        age -> Int4,
        pets -> Array<PgPet>, //the original is Pg_pets
    }
}

If you try to compile the program the compiler will complain that you are missing some trait implementations, namely ToSql and FromSql for those types. You can implement them like so. Do note that a type is represented as a tuple from the database side. So using WriteTuple and Record<(T,T2)> works.

Code

impl ToSql<PgAnimalType, Pg> for AnimalType {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
        let animal_type = format!("{:?}", self);
        ToSql::<Text, Pg>::to_sql(&animal_type, out)
    }
}

impl FromSql<PgAnimalType, Pg> for AnimalType {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        // You can find the From impl in the github repo
        FromSql::<Text, Pg>::from_sql(bytes)
            .map(|v: String| AnimalType::from(v.as_str()))
    }
}

impl ToSql<PgPet, Pg> for Pet {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
        WriteTuple::<(Text, PgAnimalType)>::write_tuple(
            &(self.name.clone(), self.animal_type.clone()),
            out,
        )
    }
}

impl FromSql<PgPet, Pg> for Pet {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        let (name, animal_type) = 
            FromSql::<Record<(Text, PgAnimalType)>, Pg>::from_sql(bytes)?;

        Ok(Pet { name, animal_type })
    }
}

# Results

Code

fn main() -> Result<(), Box<dyn std::error::Error>> {
    use schema::persons::dsl::*;

    let conn = establish_connection();

    let inverse = NewPerson::new(
        "iNverse",
        21,
        vec![
            Pet {
                name: "John".into(),
                animal_type: AnimalType::Cat,
            },
            Pet {
                name: "Cena".into(),
                animal_type: AnimalType::Fish,
            },
        ],
    );

    let res = insert_into(persons)
        .values(&inverse)
        .get_result::<Person>(&conn)?;

    dbg!(res);
    Ok(())
}

The code above will print the following to the console.

Code

[src/main.rs:133] res = Person {
    id: 1,
    name: "iNverse",
    age: 21,
    pets: [
        Pet {
            name: "John",
            animal_type: Cat,
        },
        Pet {
            name: "Cena",
            animal_type: Fish,
        },
    ],
}

That's all that you have to do. Unfortunately it is quite verbose and as of now I can't find any library that simplify things.

# Questions

I got an error saying I got the wrong type.

Take a look at this and try to match the types accordingly.

I am just using enums. Is there any library that can help?

Yes. Take a look at diesel-derive-enum