Problems working with rust and postgres data types

Question

I'm triying to do an Api REST with rust and postres but I cant make it work because the relation between these two.

The actual problem is that I have a column in postgres as jsonb and when I return the data and try to save it in a struct always gives error. Same problem when I try to save the data.

This are the models.(The option is only because I'm testing thing, it should return a value)

#[derive(Debug, Serialize, Deserialize)]
pub struct CategoryView {
    pub id: i32,
    pub category_name: String,
    pub category_custom_fields: Option<serde_json::Value>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CategoryPayload {
    pub category_name: String,
    pub category_custom_fields: Option<serde_json::Value>,
}

This are the postgres queries:

fn find_all(conn: &mut DbPooled) -> Result<Vec<CategoryView>, DbError> {
    let mut query = "SELECT id, category_name, category_custom_fields FROM accounting.categories".to_owned();
    query.push_str(" WHERE user_id = $1");
    query.push_str(" AND is_deleted = false");
    let items = conn.query(&query, &[&unsafe { CURRENT_USER.to_owned() }])?;
    let items_view: Vec<CategoryView> = items
        .iter()
        .map(|h| CategoryView {
            id: h.get("id"),
            category_name: h.get("category_name"),
            category_custom_fields: h.get("category_custom_fields"),
        })
        .collect();
    Ok(items_view)
}

fn add(payload: &CategoryPayload, conn: &mut DbPooled) -> Result<CategoryView, DbError> {
    let mut query =
        "INSERT INTO accounting.categories (user_id, category_name, category_custom_fields, create_date, update_date)"
            .to_owned();
    query.push_str(" VALUES ($1, $2, $3, now(), now())");
    query.push_str(" RETURNING id");
    let item_id = conn
        .query_one(
            &query,
            &[
                &unsafe { CURRENT_USER.to_owned() },
                &payload.category_name,
                &payload.category_custom_fields,
            ],
        )?
        .get(0);
    let inserted_item = CategoryView {
        id: item_id,
        category_name: payload.category_name.to_string(),
        category_custom_fields: payload.category_custom_fields,
    };

    Ok(inserted_item)
}

with update happens to but I think is the same solution that the one form the add function.

The error is:

the trait bound `serde_json::Value: ToSql` is not satisfied
the following other types implement trait `ToSql`:
  &'a T
  &'a [T]
  &'a [u8]
  &'a str
  Box<[T]>
  Box<str>
  Cow<'a, str>
  HashMap<std::string::String, std::option::Option<std::string::String>, H>
and 17 others
required for `std::option::Option<serde_json::Value>` to implement `ToSql`
required for the cast from `std::option::Option<serde_json::Value>` to the object type `dyn ToSql + Sync`rustcClick for full compiler diagnostic`

For what I read serde_json::Value is the equivalent to jsonb so I don't understand it.

I had a similar problem previously trying to work with a decimal value in postgres, I had to change it to integer and save the value multiplied in the database. Is a money column so maybe if you help me with that too I will change it back.

I was hopping some could explain to me how to fix it and why this happens so I can avoid have to ask for help with the datatypes in the future.

score 0 · Accepted Answer · answered Feb 10 '23 at 21:11

The problem was in the depencies.

It looks like some dependencies have features that add aditional functionablility.

I had installed the dependencie without any feature so when I added the features it started to work without issues.

Only had to change from:

[dependencies]
postgres = "0.19.4"

to:

[dependencies]
postgres = { version = "0.19.4", features = ["with-chrono-0_4", "with-serde_json-1"] }

Chrono for dates and serde_json for jsonb.

I'll check the decimal problem but I think will be the same solution.

Problems working with rust and postgres data types

1 Answers1