JSONB double-encoding when used with Drizzle ORM

[zxzinn]

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.

// 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:

// 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:

// Drizzle's PgJsonb.mapToDriverValue()
override mapToDriverValue(value: T['data']): string {
  return JSON.stringify(value);  // Already stringified
}

Result: JSON.stringify(JSON.stringify(value))

Reproduction

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:

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:

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

[hilja]

Check if value is already a string

Isn’t a string valid JSON value? Maybe an option if you know what you're doing. Or check for: JsonPrimitive | JsonifiableObject | JsonifiableArray. Taken from type-fest.

Thanks for the workaround.

[zxzinn]

String is valid JSON, but type checking happens after ORM serialization.

The flow

// User writes
db.insert(test).values({ data: { foo: "bar" } })

// Drizzle serializes
{ foo: "bar" } → '{"foo":"bar"}'

// postgres-js receives string
typeof x === 'string'  // true, but already serialized

By the time postgres-js sees the value, it's a string. Can't distinguish:

• Serialized object: '{"foo":"bar"}'
• User string: '"hello"'

ORM contract

data: jsonb().$type<{ foo: string }>()

db.insert(test).values({ data: { foo: "bar" } })       // compiles
db.insert(test).values({ data: '{"foo":"bar"}' })      // error

Users pass objects, not strings. For string storage, use text columns.

Fix

serialize: x => typeof x === 'string' ? x : JSON.stringify(x)

Works because:

• ORMs pass pre-serialized strings
• Direct usage passes objects

Or detect double-encoding:

serialize: x => {
  if (typeof x !== 'string') return JSON.stringify(x)
  try {
    const parsed = JSON.parse(x)
    return typeof parsed === 'string' ? JSON.stringify(x) : x
  } catch {
    return JSON.stringify(x)
  }
}