How to store arbitrary JSON object in sqlite using Diesel

Question

I have an input JSON:

{"key1": "val1", "key2": 1}

and I want to store it in an sqlite DB to later respond to some API request with the exact same value.

This is my migration:

CREATE TABLE my_table (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    arbitrary_json TEXT NOT NULL
);

My Cargo.toml:

[package]
name = "diesel_playground"
version = "0.1.0"
authors = ["User <user@example.com>"]
edition = "2018"

[dependencies]
diesel = { version = "1.4" , features = ["sqlite"] }
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"

Using following code:

#[macro_use]
extern crate diesel;

mod schema;

use schema::my_table;
use serde::{Deserialize, Serialize};

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
use std::env;

pub fn establish_connection() -> SqliteConnection {
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    SqliteConnection::establish(&database_url)
        .expect(&format!("Error connecting to {}", database_url))
}

#[derive(Debug, Deserialize, Serialize, Queryable, Identifiable)]
#[table_name = "my_table"]
struct Item {
    id: i32,
    arbitrary_json: serde_json::Value,
}

#[derive(Debug, Deserialize, Serialize, Insertable, Queryable)]
#[table_name = "my_table"]
struct NewItem {
    arbitrary_json: serde_json::Value,
}

fn main() {
    let my_json = serde_json::json!({
    "key1": "val1",
    "key2": 1
    });

    let new_item = NewItem {
        arbitrary_json: my_json,
    };

    let conn = establish_connection();

    diesel::insert_into(my_table::table)
        .values(&new_item)
        .execute(&conn)
        .expect("Error adding new item");

    let my_item = my_table::table
        .find(1)
        .first::<Item>(&conn)
        .expect("Error selecting id 1");

    assert!(my_item.arbitrary_json == new_item.arbitrary_json);
}

I get the following error:

error[E0277]: the trait bound `serde_json::value::Value: diesel::Expression` is not satisfied
  --> src/main.rs:27:41
   |
27 | #[derive(Debug, Deserialize, Serialize, Insertable, Queryable)]
   |                                         ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `serde_json::value::Value`

I could create a model with String JSON representation and derive From / Into for API input type, but I don't want to insert .into() everywhere in my code now. A DRY solution would to be to do this as I proposed in the code attached.

Answer 1

In my answer I will keep JSON object in the DB in a string representation (schema: TEXT).

For our unsupported type we need following traits implemented: ToSql, FromSql, AsExpression and FromSqlRow.

Now, since one can not implement a trait for a type coming from an external crate it has to be wrapped into a single element tuple:

struct MyJsonType(serde_json::Value)

Now FromSql trait implementation :

impl FromSql<Text, DB> for MyJsonType {
    fn from_sql(
        bytes: Option<&<diesel::sqlite::Sqlite as Backend>::RawValue>,
    ) -> deserialize::Result<Self> {
        let t = <String as FromSql<Text, DB>>::from_sql(bytes)?;
        Ok(Self(serde_json::from_str(&t)?))
    }
}

And ToSql trait implementation:

impl ToSql<Text, DB> for MyJsonType {
    fn to_sql<W: Write>(&self, out: &mut Output<W, DB>) -> serialize::Result {
        let s = serde_json::to_string(&self.0)?;
        <String as ToSql<Text, DB>>::to_sql(&s, out)
    }
}

Now the remaining traits can be derived using macros:

#[derive(AsExpression, Debug, Deserialize, Serialize, FromSqlRow)]
#[sql_type = "Text"]
struct MyJsonType(serde_json::Value);

It should be fine to use our new type now:

#[derive(Debug, Deserialize, Serialize, Queryable, Identifiable)]
#[table_name = "my_table"]
struct Item {
    id: i32,
    arbitrary_json: MyJsonType
}