JSON for SQLite

[heroin-moose]

JSON is supported by SQLite and it would be nice to support at least the simplest case like

diesel::table!(
    foo {
        id -> Integer,
        meta -> Json
    }
);

struct Foo {
    id: i32,
    meta: HashMap<String, String> // impl Serialize Deserialize
}

while its possible to use type wrappers and serialize_as, built-in support would be much more convenient.

[weiznich]

I'm happy to accept PR's for such additions. I think that would mostly mean to make the exist Json and Jsonb types compatible with SQLite, see which of the currently Postgres only operators are also supported by SQLite and likely define some of the built in sql functions via the sql_function! macro.

[JMLX42]

I am going to try to make a PR for this ASAP. Hopefully today.

I propose to implement serde::Value => BLOB for SQLite. Let's see how it goes from there to make it work for any impl Serialize + Deserialize.

[JMLX42]

Here is what we have for Pg:

#[cfg(all(feature = "postgres_backend", feature = "serde_json"))]
impl ToSql<sql_types::Json, Pg> for serde_json::Value {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
        serde_json::to_writer(out, self)
            .map(|_| IsNull::No)
            .map_err(Into::into)
    }
}

but it cannot work because the SQLite Output does not implement io::Write. And using serde_json::to_string() creates a dropped temporary value:

#[cfg(all(feature = "sqlite", feature = "serde_json"))]
impl ToSql<sql_types::Json, Sqlite> for serde_json::Value {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        let s = serde_json::to_string(self)?.as_str();

        <&str as ToSql<sql_types::Text, Sqlite>>::to_sql(&s, out)
    }
}

error[E0716]: temporary value dropped while borrowed
  --> diesel/src/sqlite/types/json.rs:20:17
   |
19 |     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
   |               -- lifetime `'b` defined here
20 |         let s = serde_json::to_string(self)?.as_str();
   |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^         - temporary value is freed at the end of this statement
   |                 |
   |                 creates a temporary value which is freed while still in use
21 |
22 |         <&str as ToSql<sql_types::Text, Sqlite>>::to_sql(&s, out)
   |         --------------------------------------------------------- argument requires that borrow lasts for `'b`

@weiznich any advice on how to work around this?

[JMLX42]

This works apparently:

#[cfg(all(feature = "sqlite", feature = "serde_json"))]
impl ToSql<sql_types::Json, Sqlite> for serde_json::Value {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        out.set_value(serde_json::to_string(self)?);
        Ok(IsNull::No)
    }
}

Or at least it builds xD

[JMLX42]

PR: !4284