Using JSON with SQLITE

[conner-replogle]

I have seen a couple issues and discussions about this however I am having trouble finding how to actually use it in a project

CREATE TABLE chats (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    conversation_id INTEGER NOT NULL,
    -- model_id INTEGER NOT NULL,
    prompt JSONB NOT NULL,
    response JSONB,
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id)
    -- FOREIGN KEY (model_id) REFERENCES models(id)
);

However when I run diesel migration run I get

Running migration 2024-12-23-152619_create_chats
Unsupported type: `jsonb`

My sqlite version is
SQLite version 3.47.2

Cargo.toml

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

[weiznich]

That's expected behaviour as there is no jsonb type in SQLite. See their documentation for details https://sqlite.org/datatype3.html

[conner-replogle]

Okay I see so what is the correct way to to do this when I use TEXT and manually set the type in schema.rs to Json I get errors when using serde_json::Value in a models place

This errors:

#[derive(Queryable, Selectable,Insertable,Serialize,Deserialize,Debug)]
#[table_name = "chats"]
#[diesel(check_for_backend(Sqlite))]
pub struct Chat {
    pub id: i32,
    pub conversation_id: i32,
    pub prompt: serde_json::Value,
    pub response: Option<serde_json::Value>,
    pub sent_at: chrono::NaiveDateTime,
}

When I set schema.rs to

diesel::table! {
    chats (id) {
        id -> Integer,
        conversation_id -> Integer,
        prompt -> Json,
        response -> Nullable<Json>,
        sent_at -> Timestamp,
    }
}

[weiznich]

Again this is expect behaviour as there is no conversation from the Json SQL type to serde_jdon::Value for the SQLite backend as can be seen in the documentation. That might be changed in future diesel versions, but for any currently released version that is the expected state.

The only way to do what your want with the released diesel version is to bring your own (de)serialisation logic for json.

[conner-replogle]

Im confused then what exactly do these PR's do #4284 #4366

[weiznich]

These PR's are not part of any released diesel version yet. So it's expected that you cannot use this functionality on a released version.

[conner-replogle]

Okay thank you