rust: Correct use of Decimal type in diesel

Question

I'm learning to use diesel orm library, my database uses DECIMAL(8,2) type, but when I use Decimal in my model, I get an error

I am using Decimal provided by rust_decimal

diesel = { version="1.4.8", features = ["mysql", "r2d2", "chrono", "numeric"] }

rust_decimal =  { version ="1.23", features = ["serde-with-str", "db-diesel-mysql"] }
rust_decimal_macros = "1.23"

my mysql table

CREATE TABLE `books` ( 
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    `name` VARCHAR(20) NOT NULL , 
    `price` DECIMAL(8,2) UNSIGNED NOT NULL , 
    `user_id` BIGINT UNSIGNED NOT NULL , 
    `type` TINYINT(1) UNSIGNED DEFAULT '1' NOT NULL, 
    `create_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `update_at` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB ;

diesel generated schema

table! {
    books (id) {
        id -> Unsigned,
        name -> Varchar,
        price -> Unsigned,
        user_id -> Unsigned,
        #[sql_name = "type"]
        type_ -> Unsigned,
        create_at -> Datetime,
        update_at -> Datetime,
    }
}

use crate::prelude::*;
use crate::schema::books;
use chrono::NaiveDateTime;
pub use rust_decimal::Decimal;

#[derive(Identifiable, Queryable, Serialize, Deserialize, Debug, Clone)]
#[table_name = "books"]
pub struct Book {
    pub id: PK,
    pub name: String,
    pub price: Decimal,
    pub user_id: PK,
    pub type_: u8,
    pub create_at: NaiveDateTime,
    pub update_at: NaiveDateTime,
}

This is the error I get when I run cargo check

error[E0277]: the trait bound `rust_decimal::Decimal: FromSql, Mysql>` is not satisfied
    --> src/controller/api/book.rs:19:25
     |
19   |         Ok(books::table.load::(&conn)?)
     |                         ^^^^ the trait `FromSql, Mysql>` is not implemented for `rust_decimal::Decimal`
     |
     = help: the following implementations were found:
               >
     = note: required because of the requirements on the impl of `Queryable, Mysql>` for `rust_decimal::Decimal`
     = note: 2 redundant requirements hidden
     = note: required because of the requirements on the impl of `Queryable<(diesel::sql_types::Unsigned, diesel::sql_types::Text, diesel::sql_types::Unsigned, diesel::sql_types::Unsigned, diesel::sql_types::Unsigned, diesel::sql_types::Datetime, diesel::sql_types::Datetime), Mysql>` for `Book`
     = note: required because of the requirements on the impl of `LoadQuery<_, Book>` for `books::table`
note: required by a bound in `load`
    --> /root/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-1.4.8/src/query_dsl/mod.rs:1238:15
     |
1238 |         Self: LoadQuery,
     |               ^^^^^^^^^^^^^^^^^^ required by this bound in `load`

For more information about this error, try `rustc --explain E0277`.
warning: `actix_backend` (bin "server") generated 6 warnings
error: could not compile `actix_backend` due to previous error; 6 warnings emitted

This is the rust version I'm using now

cargo --version
cargo 1.60.0 (d1fd9fe 2022-03-01)

I have also tried using bigdecimal also getting the same error

user_6803492 · Answer

According to the documentation of diesel::sql_types::Unsigned diesel does not provide builtin support for Unsigned. (There are no specific ToSql/FromSql/AsExpression impls listed on that page, in contrast to for example Unsigned.) The same is true for rust_numeric::Decimal (Also only a FromSql/ToSql impl for Numeric/Decimal no one for Unsigned.

That all means neither of the crates support an Unsigned column out of the box. You can provide support for such columns by implementing the corresponding traits on your on your own. That means implementing FromSql/ToSql + deriving AsExpression/FromSqlRow for the corresponding new type wrapper.

That would result in code like this:

use diesel::sql_types::{Unsigned, Decimal};
use diesel::serialize::{self, ToSql};
use diesel::deserialize::{self, FromSql};
use diesel::mysql::Mysql;

#[derive(AsExpression, FromSqlRow)]
#[sql_type = "Unsigned"] 
struct DecimalWrapper(rust_decimal::Decimal);


impl FromSql, Mysql> for DecimalWrapper {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result {
        >::from_sql(bytes).map(Self)
    }
}

impl ToSql, Mysql> for DecimalWrapper {
    fn to_sql(&self, out: &mut serialize::Output<'_, W, DB>) -> serialize::Result {
         <_ as ToSql>::to_sql(&self.0, out)
    }
}

rust: Correct use of Decimal type in diesel

1 Answers