Research & document how to deal with precise numbers

[romeerez]

JS numbers are imprecise, so you have define custom parsers, use an additional library (decimal.js).

This should be covered in docs.

@IlyaSemenov could you share if you have encountered any gotchas with parsing numbers into Decimal? Anything that worth documenting.

[IlyaSemenov]

It mostly works as expected, the only gotcha I'm always forgetting is using Decimal objects in raw SQL. One must always explicitly stringify them:

  const balance = await db.user
    .find(userId)
    .get("balance")
    // Use String() to prevent JSON stringifying Decimal.
    .update({ balance: sql`trim_scale(balance + (${String(amount)}))` })

because Orchid falls back to JSON.stringify() for all unknown types:

https://github.com/romeerez/orchid-orm/blob/3a92c5346c6a97f012e782f6f89958984a2bf391/packages/qb/pqb/src/quote.ts#L21

(I think it should rather use String(...) here by default, but that's a different topic.)

[IlyaSemenov]

Also, I've just discovered that there are 4 (!) different libraries from the same author:

https://github.com/MikeMcl/big.js https://github.com/MikeMcl/bignumber.js https://github.com/MikeMcl/decimal.js https://github.com/MikeMcl/decimal.js-light

There's no reason I'm using decimal.js in particular; big.js seems to be more recently updated.

[romeerez]

Thanks for sharing, so here is an issue with serializing, I'll take a look, probably you're right and in that case it shouldn't be JSON.stringified.

I remember you said about sticky zeros after a dot, like '7.000', but what's wrong if you do not use trim_scale? I mean, if postgres stores extra zeros, let it do so, why not.

So I guess increment and decrement don't work for such decimals, it's also worth fixing.

[IlyaSemenov]

I mean, if postgres stores extra zeros, let it do so, why not.

I used to deliver these string values directly to the front end and present them as is, so the trailing zeroes made the results non human friendly.

I realize this can be fixed by parse() on the ORM column, and/or explicit formatting on the client, but back in the time I decided I'd have less friction overall if I rather simply normalize values on save. I may revisit this approach in further iterations. Anyhow, decimal objects could land in raw SQL this way or another.