JSONB

[windowsdeveloperwannabe]

I couldn't find much info on how to use jsonb columns so I wanted to check how other people do it. Did I manage to guess the correct way to use it? And if I did, how should I handle bubbling up the error if deserialize fails when mapping database results back to a proper struct? 🙂

use crate::cornucopia::queries::authors;
use deadpool_postgres::Client;
use serde::{Deserialize, Serialize};

#[derive(Serialize, Deserialize)]
struct AuthorInfo {
    email: String,
    age: i32,
}

struct Author {
    name: String,
    info: AuthorInfo,
}

async fn get_author(client: &Client, name: &str) -> anyhow::Result<Option<Author>> {
    let author = authors::get()
        .bind(client, &name)
        .map(|a| Author {
            name: a.name.to_owned(),
            info: serde_json::from_str(a.info.0.get()).unwrap(),
        })
        .opt()
        .await?;
    Ok(author)
}

async fn insert_author(client: &Client, author: Author) -> anyhow::Result<()> {
    authors::insert()
        .bind(client, &author.name, &serde_json::to_value(&author.info)?)
        .await?;
    Ok(())
}

CREATE TABLE author (
    name varchar PRIMARY KEY,
    info jsonb NOT NULL
);

--! insert
INSERT INTO author (name, info) VALUES (:name, :info);

--! get
SELECT * FROM author WHERE name = :name;

[windowsdeveloperwannabe]

I figured you can use tokio_postgres::types::Json to make serialisation nicer:

use tokio_postgres::types::Json;

async fn insert_author(client: &Client, author: Author) -> anyhow::Result<()> {
    authors::insert()
        .bind(client, &author.name, &Json(author.info))
        .await?;
    Ok(())
}

Is there any way to make deserialisation a bit easier when retrieving? For example get the more commonly used serde_json::Value instead of a postgres_types::Json<&serde_json::value::RawValue>? 🙂

[LouisGariepy]

Is there any way to make deserialisation a bit easier when retrieving? For example get the more commonly used serde_json::Value instead of a postgres_types::Json<&serde_json::value::RawValue>? slightly_smiling_face

You only get a RawValue if you map the cornucopia query directly. This allows you to map rows without having to allocate them twice (once when cornucopia takes ownership and once for whatever mapping you end up doing),

So, if you want to end up with owned values, one solution is simply to not map the query result, but instead map the Option type that you get, like so:

async fn get_author(client: &Client, name: &str) -> anyhow::Result<Option<Author>> {
    let query_result = authors::get().bind(client, &name).opt().await?;
    Ok(query_result.map(|author| Author {
        name: author.name,
        info: serde_json::from_value(author.info).unwrap(),
    }))
}

how should I handle bubbling up the error if deserialize fails when mapping database results back to a proper struct

If you serialize your strongly typed struct and insert it as a Json/JsonB in the database, then query it from the database and deserialize it into your strongly typed struct, then unwrap is perfectly fine.

If you insert weakly typed values (like serde_json::Value) that were not created directly from your struct, or if your database modifies the Json/JsonB entries, then you might want proper error handling.

Something like this perhaps:

async fn get_author(client: &Client, name: &str) -> anyhow::Result<Option<Author>> {
    let query_result = authors::get().bind(client, &name).opt().await?;
    match query_result {
        Some(author) => Ok(Some(Author {
            name: author.name,
            info: serde_json::from_value(author.info)?,
        })),
        None => Ok(None),
    }
}

or even this if you prefer the functional style

async fn get_author(client: &Client, name: &str) -> anyhow::Result<Option<Author>> {
    let query_result = authors::get().bind(client, &name).opt().await?;
       query_result
        .map(|author| {
            Ok(Author {
                name: author.name,
                info: serde_json::from_value(author.info)?,
            })
        })
        .transpose()
}

Note that I would consider it a pretty serious bug if the thing I'm trying to query from the database unexpectedly doesn't match the struct I'm trying to deserialize into. This is because there's no real recovery path from there usually: once your data doesn't match your model, you're in for trouble if you don't stop and look for what's causing the discrepancy.

I would probably try to prevent this from happening in the first place instead of trying to handle that error. Just my 2c obviously 😄 .

[dlight]

If you serialize your strongly typed struct and insert it as a Json/JsonB in the database, then query it from the database and deserialize it into your strongly typed struct, then unwrap is perfectly fine.

If you care about this you can guard this on the Postgres side with json schema validation https://github.com/supabase/pg_jsonschema - that way non-cornucopia queries won't be able to make jsonb columns invalid and unreadable

You then can generate json schema from your serde-compatible Rust type with https://docs.rs/jsonschema/latest/jsonschema/ (and probably a bunch of other crates) and it may be compatible with the exact serialization adopted by cornucopia. Perhaps cornucopia should include this with a feature flag or something, for ease of use.

[gerwitz]

This was very helpful to run across, thanks! But it sure is frustrating that adding a jsonb column to a table means I have to undo a bunch of .map usage :-(