How to compile diesel with SQLite JSON support

[dlukes]

SQLite comes with JSON support, but it's only enabled by default in the SQLite shell. I'm trying to create a column of type JSON, which works fine from the SQLite shell, but fails with Unsupported type: json when run as a migration via diesel.

The SQLite docs mention a compile-time option to enable the JSON extension, but I don't know how that applies in the context of diesel. Is there a way to pass this option through via cargo? If not, is there another way to enable the extension?

Or maybe the JSON type is not supported by design in diesel and I should stop trying?

[dlukes]

Having slept on it, the compile-time option doesn't make sense: I'm using the SQLite library as provided by my OS, which is already compiled (with the option enabled, I've checked), and diesel just links against that, right? So either JSON support has to be explicitly enabled at the diesel level as well (via a --feature perhaps?) or I'm out of luck :)

[dlukes]

After some more exploration, it turns out that the JSON manipulation functions do work from diesel, the only thing that doesn't work is specifying JSON as the datatype of a column (one has to use TEXT instead). The best I could come up with is this: given the following table...

-- up.sql
CREATE TABLE docs (
  id INTEGER PRIMARY KEY NOT NULL,
  meta TEXT NOT NULL
)

INSERT INTO docs (meta) VALUES ('{"foo": 1}');

... and model...

// models.rs
#[derive(Queryable, Debug)]
pub struct Doc {
    pub id: i32,
    pub meta: String,
    pub foo: i32,
}

... one can formulate queries like these:

use schema::docs::dsl::*;
use diesel::{
    dsl::sql, sql_types::{Integer, Text},
};

let query = docs.select(sql::<(Integer, Text, Integer)>(
    "id, meta, json_extract(meta, '$.foo') as foo",
));

If anyone knows of a better / less verbose way, please share, but I'm going to close this since diesel clearly is compiled with SQLite JSON support (since json_extract above works) as long as the SQLite library is, it just doesn't support the JSON datatype :)