[BUG]:Expect to return type number but got string

[gauravterrascope]

What version of drizzle-orm are you using?

0.34.1

What version of drizzle-kit are you using?

0.25.0

Describe the Bug

The $inferInsert returns the type as string for keys which are numeric or floats in drizzle pgTable schema

Steps to reproduce

1. Create schema file and a pgTable conatining some columns with numeric or float type.
2. Use $inserInsert to get the type from drizzle schema.

Expected behavior

Here property age should be number in typescript

Environment & setup

Node 18 Macbook M1 pro

[dennismuench]

@gauravterrascope This is not a bug. Numeric and Decimal fields in PG are numeric strings not numbers. That's actually the whole point of using those fields. It ensures accurate handling of big numbers, especially those with a lot of decimal places. You typically use those kind of fields to store currency values (and anything else where perfect accuracy is important). Converting them to JavaScript numbers is not possible without loosing precision. The values could change. If you want to do math with those numeric strings you even use special libraries like big.js etc. This ensures you can add, multiply and divide them without ever converting them to "real" numbers. You can imagine how catastrophic some missing or wrong decimal places would be when handling things like (for example) bitcoin values...

[dennismuench]

If you actually want a float, then real or doublePrecision is probably what you are looking for!

[kevinsalgatar]

Just to confirm, if my field is to input a let's say a salary, i should store them as Decimal in database, but i should define this type as a "string" in my validation schema (e.g. Zod)?

[dennismuench]

Yes, because numeric and decimal fields are strings in the database and should stay that way. When you use createInsertSchema, these fields are defined as strings by default. Depending on your use case, you might also want to store a salary as the smallest currency amount. For example, 500 to resemble $5. Then you can safely use an integer. But this is only feasible if you are working with a single currency and do not need fractional cents. If an accountant or HR is involved, it is best to check for possible requirements for decimal places.

[TGTGamer]

Just dropping a note here to thank @dennismuench this was very helpful. Might be worth putting a little footnote or something in the docs just pointing to either this issue or something as I was looking for the same answer :)

[gauravterrascope]

Closing this issue. As it is being done to handle the big floating point numbers and their precision in javascript properly.