# 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