drizzle-team / drizzle-team/drizzle-orm

JSONB double-encoding with postgres-js driver

Open
#5,139 4 comments 5 reactions 0 assignees View on GitHub
bug/fixed-in-beta
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.