drizzle-team / drizzle-team/drizzle-orm
[BUG]: node-postgres driver implicitly converts numeric looking strings to numbers
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Report hasn't been filed before.
- [x] I have verified that the bug I'm about to report hasn't been filed before.
### What version of `drizzle-orm` are you using?
0.45.1
### What version of `drizzle-kit` are you using?
irrelevant
### Other packages
_No response_
### Describe the Bug
What we are seeing:
1. store: `"\"0.1\""` in a `driz.jsonb()` column.
2. Read that column with raw postgres on the cli get `"0.1"` as expected
3. read that column with db.select.from(myTable) get `0.1`
It seems like between the DB and the output JSON.parse is being called twice so "\"0.1\"" -> "0.1" -> 0.1
If we use the workaround mentioned here: https://github.com/drizzle-team/drizzle-orm/issues/4385#issuecomment-3958495833
it solves the double parse issue but then we seem to loose the json.stringify on the way in to the DB ("0.1" gets saved as 0.1)
For us the correct workaround seems to be this custom type (which ironically is in the drizzle docs)
```
import { customType } from 'drizzle-orm/pg-core';
const customJsonb = (name: string) =>
customType<{ data: TData; driverData: string }>({
dataType() {
return 'jsonb';
},
toDriver(value: TData): string {
return JSON.stringify(value);
},
})(name);
```
This seems like a very serious bug with drizzle's jsonb() column type though! Implicitly converting strings to numbers could result in serious correctness issues in some cases. Please tell me we are just using it wrong somehow.
(We are using `drizzle-orm/node-postgres` with a connection from a `pg` Pool. Pretty straightforward.)
Update:
Looking at the code this seems like the obvious issue. If the postgres connection itself already returns the value as JS object and not a JSON string, this will look fine for strings like "foobar" but will definitely implicitly convert strings like "0.1" incorrectly.
```
mapFromDriverValue(value) {
if (typeof value === "string") {
try {
return JSON.parse(value);
} catch {
return value;
}
}
return value;
}
```
I have verified that querying for that jsonb column directly with the pg.Pool connection returns the data already JSON.parsed FWIW.
Contributor guide
Assessment
This issue has not been assessed yet.