JSONB double-encoding when used with Drizzle ORM
## Environment
- postgres: 3.4.0
- drizzle-orm: 0.30.0
- PostgreSQL: 15
## Issue
When Drizzle ORM passes pre-stringified JSONB values, postgres-js stringifies them again, causing double-encoding.
```javascript
// Drizzle passes: '{"foo":"bar"}'
// postgres-js serializes to: '"{\\"foo\\":\\"bar\\"}"'
// PostgreSQL stores: string instead of jsonb object
```
## Root cause
postgres-js assumes all JSONB values need serialization:
```javascript
// src/types.js
json: {
to: 114,
from: [114, 3802], // 3802 = jsonb
serialize: x => JSON.stringify(x),
parse: x => JSON.parse(x)
}
```
However, ORMs like Drizzle pre-process values:
```typescript
// Drizzle's PgJsonb.mapToDriverValue()
override mapToDriverValue(value: T['data']): string {
return JSON.stringify(value); // Already stringified
}
```
Result: `JSON.stringify(JSON.stringify(value))`
## Reproduction
```javascript
const { drizzle } = require('drizzle-orm/postgres-js')
const { pgTable, text, jsonb } = require('drizzle-orm/pg-core')
const postgres = require('postgres')
const client = postgres('postgresql://...')
const db = drizzle(client)
const test = pgTable('test', {
id: text('id').primaryKey(),
data: jsonb('data')
})
await db.insert(test).values({ id: '1', data: { foo: 'bar' } })
// SELECT jsonb_typeof(data) FROM test;
// Expected: "object"
// Actual: "string"
```
## Workaround
Override type configuration:
```javascript
const client = postgres(url, {
types: {
json: {
to: 114,
from: [114, 3802],
serialize: x => x, // Pass through
parse: x => JSON.parse(x)
}
}
})
```
## Suggestion
Check if value is already a string:
```javascript
json: {
to: 114,
from: [114, 3802],
serialize: x => typeof x === 'string' ? x : JSON.stringify(x),
parse: x => JSON.parse(x)
}
```
This works for both ORM and direct usage scenarios.
## Impact
Affects all ORMs that pre-process JSONB values (Drizzle, Prisma, TypeORM).
## Related
- [drizzle-team/drizzle-orm#724](https://github.com/drizzle-team/drizzle-orm/issues/724)
2 条评论