Is jsonb fully supported in diesel?

I'm trying to use diesel with PostgreSQL JsonB datatype and I'm getting these errors:

error[E0277]: the trait bound *const str: diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> is not satisfied

9 | #[derive(Insertable)]
| ^^^^^^^^^^ the trait diesel::Expression is not implemented for str
|
= note: required because of the requirements on the impl of diesel::Expression for &str
= note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

I have this in my models.rs

#[derive(Insertable)]
#[table_name="photos"]
pub struct NewPhoto<'a> {
pub id: i64,
pub datadoc: &'a str,
}

So, I wonder if JSONB is fully supported in diesel. Could someone help me?

1 Like

I think the jsonb support is there but only if your type is serde_json::Value.

So, what change do I have to make to my code?

Appreciate if you could help with some sample code, as I'm quite new to Rust. Thanks!

I’m guessing datadoc has to be of type serde_json::Value rather than &str.

I tried. It didn't work.

Didn't work how? Compiler error? If so, what's the error and what's the code?

I'm not intimately familiar with diesel, but perhaps @killercup can assist. Alternatively, you can try filing a diesel github issue and get some focused help there.

1 Like

I'm now getting:

| ^^^^ the trait diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> is not implemented for serde_json::value::Value

I have these in my dependencies:

diesel = { version = "1.1.1", features = ["postgres", "serde_json"] }
dotenv = "0.10.1"
serde_json = "1.0.9"

Are you using serde_json::Value or Option<serde_json::Value>?

1 Like

You can read the error like this:

Cannot get a Nullable<Jsonb> from a field of type serde_json::Value

which tells me that your database field is nullable, but your Rust field is not. Try Option<serde_json::Value>, as Sebastián already suggested.

Tip to getting help faster: Quote more of your code and the full error message.

2 Likes

lib.rs:

#[macro_use]
extern crate diesel;
extern crate dotenv;
extern crate serde_json;

pub mod schema;
pub mod models;

use diesel::prelude::*;
use diesel::pg::PgConnection;
use dotenv::dotenv;
use std::env;

pub fn establish_connection() -> PgConnection {
dotenv().ok();

let database_url = env::var("DATABASE_URL")
    .expect("DATABASE_URL must be set");
PgConnection::establish(&database_url)
    .expect(&format!("Error connecting to {}", database_url))

}

schema.rs:

table! {
photo (id) {
id -> Int8,
datadoc -> Nullable,
}
}

models.rs:

use super::schema::photo;
use serde_json::Value;

#[derive(Queryable)]
pub struct Photo {
pub id: i64,
pub datadoc: String,
}

#[derive(Insertable)]
#[table_name="photo"]
pub struct NewPhoto {
pub id: i64,
pub datadoc: Option,
}

main.rs:

extern crate diesel;
extern crate database;

use database::models::Photo;
use diesel::prelude::*;

fn main() {
use database::schema::photo::dsl::*;

let connection = database::establish_connection();
let results = photo
    .load::<Photo>(&connection)
    .expect("Error loading photos");

println!("loaded photos");

}

Compiler error message:

Compiling photography v0.1.0 (file:///C:/.../source_code)
error[E0277]: the trait bound *const str: diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> is not satisfied
--> src\main.rs:12:10
|
12 | .load::(&connection)
| ^^^^ the trait diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> is not implemented for *const str
|
= help: the following implementations were found:
<*const str as diesel::deserialize::FromSql<diesel::sql_types::Text, DB>>
<*const [u8] as diesel::deserialize::FromSql<diesel::sql_types::Binary, DB>>
= note: required because of the requirements on the impl of diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> for std::string::String
= note: required because of the requirements on the impl of diesel::deserialize::FromSqlRow<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> for std::string::String
= note: required because of the requirements on the impl of diesel::Queryable<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> for std::string::String
= note: required because of the requirements on the impl of diesel::Queryable<(diesel::sql_types::BigInt, diesel::sql_types::Nullable<diesel::sql_types::Jsonb>), _> for (i64, std::string::String)
= note: required because of the requirements on the impl of diesel::Queryable<(diesel::sql_types::BigInt, diesel::sql_types::Nullable<diesel::sql_types::Jsonb>), _> for database::models::Photo
= note: required because of the requirements on the impl of diesel::query_dsl::LoadQuery<_, database::models::Photo> for database::schema::photo::table

error: aborting due to previous error

error: Could not compile photography.

To learn more, run the command again with --verbose.

Process finished with exit code 101

1 Like

I just tried "Option<serde_json::Value>" and still stuck with pretty much the same error. I've posted more of my code and error above. Please help. Thanks!

Rust's error messages can take some getting-used to, it's ok, we understand! :slight_smile:

The code you posted doesn't match what you say you tried:

Did you try more than one combination? Or only what you posted?
Have you tried "pub datadoc: Option<serde_json::Value>,"
It is unclear to me from your words, and the syntax can be very picky, especially when so much derive-magic is involved as in Diesel's awesomeness. :wink:

I'm pretty sure the <serde_json::Value> got swallowed because they didn't put the code in markdown code blocks (indent with 4 spaces, or wrap in ```)

You have two structs that use the datadoc field. This error is about the other one: Your Photo struct assigns it the type String. This doesn't work AFAIK, and it needs to be serde_json::Value as well.

Yes, I tried that pattern already:

models.rs:

use super::schema::photo;
use serde_json::Value;

#[derive(Queryable)]
pub struct Photo {
pub id: i64,
pub datadoc: String,
}

#[derive(Insertable)]
#[table_name="photo"]
pub struct NewPhoto {
pub id: i64,
pub datadoc: Option<serde_json::Value>,
}

Note that this is "Option<serde_json::Value>"... and it gave me:

compiling database v0.1.0 (file:///C:/.../source_code/database)
error[E0433]: failed to resolve. Use of undeclared type or module serde_json
--> database\src\models.rs:14:25
|
14 | pub datadoc: Option<serde_json::Value>,
| ^^^^^^^^^^ Use of undeclared type or module serde_json

error[E0433]: failed to resolve. Use of undeclared type or module serde_json
--> database\src\models.rs:10:10
|
10 | #[derive(Insertable)]
| ^^^^^^^^^^ Use of undeclared type or module serde_json

warning: unused import: serde_json::Value
--> database\src\models.rs:2:5
|
2 | use serde_json::Value;
| ^^^^^^^^^^^^^^^^^
|
= note: #[warn(unused_imports)] on by default

error: aborting due to 2 previous errors

error: Could not compile database.

and I tried:

use serde_json::Value;
...
pub datadoc: Option

which gave me the error:

| ^^^^ the trait diesel::deserialize::FromSql<diesel::sql_types::Nullable<diesel::sql_types::Jsonb>, _> is not implemented for *const str

(sorry for the confusion)

Alright, I got everything working now :slight_smile:

use super::schema::photo;
use serde_json;

#[derive(Queryable)]
pub struct Photo {
pub id: i64,
pub datadoc: Option<serde_json::Value>,
}

#[derive(Insertable)]
#[table_name="photo"]
pub struct NewPhoto {
pub id: i64,
pub datadoc: Option<serde_json::Value>,
}

Thanks!!!

2 Likes

I took a slightly different approach which helped me to do a direct map from JsonB object to a specific model struct.

For every table with json/jsonb-type columns I implemented the Queryable trait in order to deserialize serde_json::Value to a proper helper type.

Take a look at https://github.com/manchoz/kiotlogdb-rs/blob/master/src/models.rs#L89 for some example.

5 Likes