rustrust-sqlx

How to use sqlx query_as with rust_decimal for money postgresql type


I'm trying to use rust_decimal with sqlx for money postgresql type and got error.

Of course i use decimal feature for sqlx.

There is full example

Table

create table inventory (
    id         bigserial not null,
    name       text      not null,
    price      money     not null,
    constraint inventory_pk primary key (id)
);

Structure

pub struct Item {
    pub id: i64,
    pub name: String,
    pub price: Decimal,
}

Query

let si = sqlx::query_as!(
    Item,
    r#"
        insert into inventory(name, price)
        values ($1, $2)
        returning id, name, price
    "#,
    i.name,
    i.price
)
.fetch_one(pool)
.await?;

Error

error[E0308]: mismatched types
 --> src/main.rs:38:14
 |
 |       let si = sqlx::query_as!(
 |  ______________^
 | |         Item,
 | |         r#"
 | |             insert into inventory(name, price)
   |
 | |         i.price
 | |     )
 | |_____^ expected struct `Decimal`, found struct `PgMoney`

What i'm doing wrong?


Solution

  • There is two problems. First I didn't read the documentation carefully.

    Reading MONEY value in text format is not supported and will cause an error.

    But even after that, you need to use the conversion manually.

    impl FromRow<'_, PgRow> for Item {
        fn from_row(row: &PgRow) -> Result<Self, sqlx::Error> {
            let id: i64 = row.try_get("id")?;
            let name: String = row.try_get("name")?;
            let money: PgMoney = row.try_get("price")?;
            let locale_frac_digits = 2;
            let price = money.to_decimal(locale_frac_digits);
            Ok(Item { id, name, price })
        }
    }
    
    async fn add(pool: &PgPool, i: Item) -> AppResult<Item> {
        let row = sqlx::query(
            r#"
                insert into inventory(name, price)
                values ($1, $2)
                returning id, name, price
            "#
            )
            .bind(i.name)
            .bind(PgMoney::from_decimal(i.price, 2))
            .fetch_one(pool)
            .await?;
    
        let si = Item::from_row(&row)?;
    
        Ok(si)
    }