Is jsonb fully supported in diesel?


#1

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?


#2

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


#3

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!


#4

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


#5

I tried. It didn’t work.


#7

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.


#8

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


#9

I have these in my dependencies:

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


#10

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


#11

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.


#12

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


#13

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!


#14

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:


#15

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 ```)


#16

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.


#17

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.


#18

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)


#19

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!!!


How to create a new Option<serde_json::Value> value?
#20

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.