JSON Documentation for SQLite?

[bitterepic]

Backend

SQLite

Diesel version

2.3.10

Diesel Features

diesel = { version = "2.3.10", features = ["sqlite", "chrono", "serde_json", "returning_clauses_for_sqlite_3_35"] }

Operating System Version

macOS

Third party libraries

No response

What do you want to do?

I'm wondering if there is official documentation for integrating json with Diesel/Sqlite?

Compile time error

N/A

What code do you already have?

This is what I'm currently doing.


#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = crate::schema::fingerprint_cache)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
pub struct FingerprintCache {
    pub fingerprint_cache_id: i32,
    pub last_used: NaiveDateTime,
    response: String,
    pub checksum: String,
}

impl FingerprintCache {
    pub fn response(&self) -> Result<AcoustIdFingerprintResponse, serde_json::Error> {
        serde_json::from_str(&self.response)
    }

    pub fn set_response(&mut self, response: AcoustIdFingerprintResponse) {
        self.response = serde_json::to_string(&response).unwrap()
    }
}

Additional details

I have found some random threads, but I'm wondering if there is some official json serialization/deserialization documentation for diesel with sqlite?

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[yudin-s]

There are two separate layers here: Diesel's SQL type and your app's domain type.

For SQLite, Diesel does have JSON SQL marker types when the serde_json feature is enabled. diesel::sql_types::Json is backed by SQLite TEXT, and Jsonb is backed by SQLite BLOB. The built-in Rust mapping is serde_json::Value, not an arbitrary typed struct.

So your current approach is reasonable if the column is really just a cached JSON document. I would usually make that explicit:

response: String,

pub fn response(&self) -> Result<AcoustIdFingerprintResponse, serde_json::Error> {
    serde_json::from_str(&self.response)
}

If you want SQLite to reject malformed values, add a migration-level check such as CHECK (json_valid(response)).

If you want Diesel to deserialize the column directly, use response -> Json in the schema and make the field a serde_json::Value, then convert that value into AcoustIdFingerprintResponse at the boundary. For a strongly typed field directly in the model, use a small newtype and implement FromSql/ToSql by delegating to JSON text/value serialization. For a cache table, the explicit String plus accessor methods you already have is often the simplest and clearest option.

If this answers the SQLite JSON direction, please mark it as answered.

[bitterepic]

If you want Diesel to deserialize the column directly, use response -> Json in the schema and make the field a serde_json::Value, then convert that value into AcoustIdFingerprintResponse at the boundary. For a strongly typed field directly in the model, use a small newtype and implement FromSql/ToSql by delegating to JSON text/value serialization. For a cache table, the explicit String plus accessor methods you already have is often the simplest and clearest option.

Yeah, for simplicity I basically decided to do this setter/getter approach which works well enough for what I am doing, other than I wonder if should be storing with a binary format.

Just to verify: Do you know if there is official documentation and examples for FromSql/ToSql? Or is this edge case enough that it isn't covered?

[yudin-s]

Yes. The official references are the trait docs:

• FromSql: https://docs.rs/diesel/latest/diesel/deserialize/trait.FromSql.html
• ToSql: https://docs.rs/diesel/latest/diesel/serialize/trait.ToSql.html

There are also source examples in Diesel itself. For SQLite JSON specifically, the built-in implementation is for serde_json::Value, not a typed application struct:

• https://github.com/diesel-rs/diesel/blob/main/diesel/src/sqlite/types/json.rs
• https://github.com/diesel-rs/diesel/blob/main/diesel/src/type_impls/json.rs

So a typed AcoustIdFingerprintResponse column is enough of an edge case that you will mostly compose the traits yourself rather than follow a dedicated cookbook page. The usual shape is:

use diesel::deserialize::{self, FromSql};
use diesel::serialize::{self, IsNull, Output, ToSql};
use diesel::sql_types::Text;
use diesel::sqlite::{Sqlite, SqliteValue};

pub struct AcoustIdFingerprintResponseJson(pub AcoustIdFingerprintResponse);

impl FromSql<Text, Sqlite> for AcoustIdFingerprintResponseJson {
    fn from_sql(value: SqliteValue<'_, '_, '_>) -> deserialize::Result<Self> {
        let raw = <String as FromSql<Text, Sqlite>>::from_sql(value)?;
        serde_json::from_str(&raw)
            .map(AcoustIdFingerprintResponseJson)
            .map_err(Into::into)
    }
}

impl ToSql<Text, Sqlite> for AcoustIdFingerprintResponseJson {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        out.set_value(serde_json::to_string(&self.0)?);
        Ok(IsNull::No)
    }
}

Then your schema can stay response -> Text, and your model field can use the newtype. If you use response -> Json, Diesel's built-in path is basically serde_json::Value first, then convert to your typed struct at the boundary.

For your cache case, I would still lean toward plain JSON text unless you specifically need SQLite JSONB. Text is easier to inspect, back up, and migrate, and Diesel's Json SQLite support already stores text. Binary only starts making sense if you intentionally depend on SQLite JSONB behavior and are comfortable with the SQLite/Diesel version requirements around that.

If that settles the direction, accepting the earlier answer would make the Q&A easier to find.

[bitterepic]

Thank you!

[weiznich]

Just to leave this as comment here: I'm happy to review a PR that adds some documentation about adding custom type mappings for Json and Jsonb as example to the API documentation. Just make sure that the example also works with postgres.

[bitterepic]

Thank you for being preemptive on asking this. I can take a look at documentation PR sometime next week.

I'm still relatively new to the Rust ecosystem, which means my best strength is looking at documentation with fresh eyes to see what may be confusing. Conversely, I'm also starting to realize that rust documentation tends to be api docs over guide books first which is a bit of a culture shock on my side.