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<Bigint>,
name -> Varchar,
price -> Unsigned<Decimal>,
user_id -> Unsigned<Bigint>,
#[sql_name = "type"]
type_ -> Unsigned<Tinyint>,
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<diesel::sql_types::Unsigned<diesel::sql_types::Numeric>, Mysql>` is not satisfied
--> src/controller/api/book.rs:19:25
|
19 | Ok(books::table.load::<models::book::Book>(&conn)?)
| ^^^^ the trait `FromSql<diesel::sql_types::Unsigned<diesel::sql_types::Numeric>, Mysql>` is not implemented for `rust_decimal::Decimal`
|
= help: the following implementations were found:
<rust_decimal::Decimal as FromSql<diesel::sql_types::Numeric, Mysql>>
= note: required because of the requirements on the impl of `Queryable<diesel::sql_types::Unsigned<diesel::sql_types::Numeric>, 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<BigInt>, diesel::sql_types::Text, diesel::sql_types::Unsigned<diesel::sql_types::Numeric>, diesel::sql_types::Unsigned<BigInt>, diesel::sql_types::Unsigned<TinyInt>, 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<Conn, U>,
| ^^^^^^^^^^^^^^^^^^ 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
According to the documentation of diesel::sql_types::Unsigned<T> diesel does not provide builtin support for Unsigned<Decimal>. (There are no specific ToSql/FromSql/AsExpression impls listed on that page, in contrast to for example Unsigned<Integer>.) The same is true for rust_numeric::Decimal (Also only a FromSql/ToSql impl for Numeric/Decimal no one for Unsigned<Decimal>.
That all means neither of the crates support an Unsigned<Decimal> 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<Decimal>"]
struct DecimalWrapper(rust_decimal::Decimal);
impl FromSql<Unsigned<Decimal>, Mysql> for DecimalWrapper {
fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
<rust_decimal::Decimal as FromSql<Decimal, Mysql>>::from_sql(bytes).map(Self)
}
}
impl ToSql<Unsigned<Decimal>, Mysql> for DecimalWrapper {
fn to_sql<W: Write>(&self, out: &mut serialize::Output<'_, W, DB>) -> serialize::Result {
<_ as ToSql<Decimal, Mysql>>::to_sql(&self.0, out)
}
}