drizzle-team / drizzle-team/drizzle-orm
JSONB double-encoding with postgres-js driver
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
# JSONB double-encoding with postgres-js driver
## Environment
- drizzle-orm: 0.30.0
- postgres: 3.4.0
- PostgreSQL: 15
## Issue
JSONB columns store JSON strings instead of objects when using postgres-js driver.
```typescript
await db.insert(spans).values({
attributes: { service: { name: "test" } }
})
// Expected in DB: {"service":{"name":"test"}} (jsonb object)
// Actual in DB: "{\"service\":{\"name\":\"test\"}}" (string)
```
This breaks all JSONB operators (`->`, `->>`, `@>`, etc).
## Root cause
1. Drizzle's `PgJsonb.mapToDriverValue()` stringifies:
```typescript
override mapToDriverValue(value: T['data']): string {
return JSON.stringify(value);
}
```
[Source](https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/jsonb.ts#L42-L44)
2. postgres-js type handler stringifies again:
```javascript
json: {
to: 114,
from: [114, 3802], // 3802 = jsonb
serialize: x => JSON.stringify(x),
parse: x => JSON.parse(x)
}
```
[Source](https://github.com/porsager/postgres/blob/master/src/types.js#L15-L20)
Result: double-encoded string in database.
## Reproduction
```typescript
import { drizzle } from 'drizzle-orm/postgres-js'
import { pgTable, text, jsonb } from 'drizzle-orm/pg-core'
import postgres from '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
Configure postgres-js to skip serialization:
```typescript
const client = postgres(url, {
types: {
json: {
to: 114,
from: [114, 3802],
serialize: x => x, // Drizzle already stringified
parse: x => JSON.parse(x)
}
}
})
```
## Suggestions
1. Detect postgres-js and skip `mapToDriverValue()`
2. Document the required postgres-js configuration
3. Export pre-configured postgres client from `drizzle-orm/postgres-js`
## Related
- #724 (original report from 2023)
Contributor guide
Assessment
This issue has not been assessed yet.