Add example of FromSql/ToSql to use structs for custom Jsonb fields

[naps62]

I was wanting to use custom structs rather than plain serde_json::Value columns. I'm using 2.0.0-rc0, and all I could find were some old discussions, where a) there wasn't any clear solution, and b) the proposed solutions didn't fully work with the new changes to FromSql/ToSql

So I spent some time debugging and building my own. Would this be useful to showcase as an example in the official docs? it seemed to me that the diesel maintainers don't have the interest of adding this feature natively (which is understandable), but this question still showed up a few times

My working example:

[package]
name = "test"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
diesel = { version = "2.0.0-rc.0", features = ["postgres", "serde_json", "r2d2"] }
serde = { version = "1.0.137", features = ["derive"] }
serde_json = "1.0"

// src/schema.rs

table! {
    roots (id) {
        id -> Int4,
        name -> Varchar,
        images -> Array<Text>,
        childs -> Array<Jsonb>,
    }
}

// src/main.rs
#[macro_use]
extern crate diesel;

mod schema;

use diesel::prelude::*;
use diesel::{backend::RawValue, Connection, PgConnection};
use diesel::{
    deserialize::{self, FromSql},
    pg::{sql_types::Jsonb, Pg},
    serialize::{self, Output, ToSql},
};
use serde::{Deserialize, Serialize};

use crate::schema::roots;

#[derive(Queryable, Selectable, Debug)]
pub struct Root {
    pub id: i32,
    pub name: String,
    pub images: Vec<String>,
    pub childs: Vec<Child>,
}

#[derive(Insertable)]
#[diesel(table_name = schema::roots)]
pub struct NewRoot {
    pub name: String,
    pub images: Vec<String>,
    pub childs: Vec<Child>,
}

#[derive(Debug, Deserialize, Serialize, Clone, Eq, PartialEq, PartialOrd)]
pub struct Child {
    pub foo: String,
    pub bar: String,
}

impl FromSql<Jsonb, Pg> for Child {
    fn from_sql(value: RawValue<Pg>) -> deserialize::Result<Self> {
        serde_json::from_value(FromSql::<Jsonb, Pg>::from_sql(value)?).map_err(|e| e.into())
    }
}

impl ToSql<Jsonb, Pg> for Child {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        let json = serde_json::to_value(self).unwrap();

        <serde_json::Value as ToSql<Jsonb, Pg>>::to_sql(&json, &mut out.reborrow())
    }
}

fn main() {
    let conn_spec = std::env::var("DATABASE_URL").expect("DATABASE_URL");
    let mut conn = PgConnection::establish(&conn_spec).unwrap();

    use diesel::insert_into;
    use schema::roots::dsl::*;

    let data = NewRoot {
        name: "name".into(),
        images: vec!["a".into(), "b".into()],
        childs: vec![
            Child {
                foo: "1".into(),
                bar: "2".into(),
            },
            Child {
                foo: "3".into(),
                bar: "4".into(),
            },
        ],
    };

    let res = insert_into(schema::roots::table)
        .values(&data)
        .returning(Root::as_select())
        .get_result(&mut conn);
}

[weiznich]

Generally speaking I would be fine with having something like this in our documentation.

I would like to clarify the following details first:

• Where exactly would you add this?
• Why is the #[allow(unconditional_recursion)] required. That seems like a potential issue.

[naps62]

My best guess about which page people would first look for is Jsonb, which could just reference FromSql and ToSql where the actual examples would live

[weiznich]

The Jsonb documentation would be fine for me 👍 Feel free to fill a PR for this.

[jblachly]

If I could chime in, I think this actually ideally would be great in a guide which covered FromSql and ToSql, which are the key features that make this work. Such a guide could certainly include other types as well (simple Enum, for example).

In fact, the guides do not even mention FromSql which means there is a discoverability problem for real novices (or someone evaluating whether Diesel is suitable for their use case/needs)

[weiznich]

@jblachly We are happy to receive a contribution for such a guide, otherwise we do unfortunately not have the capacity to write such a guide now. Feel free to open a PR with a first draft of such guide + examples in this repository.

[jblachly]

Great, I just needed the green light. I'll try to get together some examples of other impl as well.