drizzle-orm: [BUG]: jsonb always inserted as a json string when using postgres-js.

I too was able to bypass the issue with this bug by wrapping my values call with sql``:

    const product = await tx
      .insert(products)
      .values({
        entityId: input.entityId,
        eventType: input.eventType,
        payload: sql`${input.payload}::jsonb`,
      })

Is not arbitrary string failing to be detected as json/b in postgres? I’ve stumbled over this today as I’ve realized that somehow all values are escaped and the entire object is treated as a scalar. I have this workaround with sql helper.

const content = { title: 'test', ... };

// before
await db
  .update(Stories)
  .set({ content })
  .where(eq(Stories.id, story.id));

// after
await db
  .update(Stories)
  .set({
    content: sql`${content}::jsonb`,
  })
  .where(eq(Stories.id, story.id));

Looking at the complexity of postgres json handling, one can appreciate drizzle design to go down to plain sql/functions without a need to have it abstracted or non-existent as other “ORMs”. I end up doing something like this:

...set({
  content: sql`jsonb_set(content, '{title}', content->'title' || '{"text": "Hello There"}'::jsonb)`
})

+1 - json/jsonb shouldn’t be listed in the docs as a supported type when this bug exists.

My workaround was to use a db.execute and pass objects into the sql`` template.

const statement = sql`
        INSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks)
        VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks})
        ON CONFLICT (wikidata_id) DO UPDATE
        SET 
            category = ${articleDetails.category},
            grade = ${articleDetails.grade},    
            en_raw_length = ${articleDetails.enBytes},
            en_url_title = ${articleDetails.enUrlTitle},
            labels = ${articleDetails.labels},
            sitelinks = ${articleDetails.sitelinks}
        `;
 await db.execute(statement);

Just wanted to add above when i use this

export const customJsonb = customType<{ data: any }>({
  dataType() {
    return 'jsonb';
  },
  toDriver(val) {
    console.log('🚀 ~ toDriver:', val);
    return val as any;
  },
  fromDriver(value) {
    console.log('🚀 ~ fromDriver', value);
    if (typeof value === 'string') {
      try {
        return JSON.parse(value) as any;
      } catch {}
    }
    return value as any;
  },
});

It does not sort object in same order as i passed, this happens under the fromDriver

Using `sql` same issue

sql`${availability}::jsonb` as AvailabilityT

And then API res is also messed up

@MariuzM I don’t think this is an issue, but it’s by design. The JSONB stores JSON data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. If key ordering is important for you, use JSON instead, but you’ll lose all the other benefits of JSONB.

My use case:

Table Schema

availability: jsonb('availability')

API Response

Table Plus

But if i use

availability: sql`${req.body.availability}::jsonb` as AvailabilityT

Then API Resonse is same but Table Plus

Using this https://github.com/drizzle-team/drizzle-orm/pull/666#issuecomment-1602918513 also fixed issue can confirm

I was working on a fix there, its kinda stale as would need to have different behavior for pg as it expects arrays to be passed as a string https://github.com/drizzle-team/drizzle-orm/pull/666

Just wanted to add above when i use this

export const customJsonb = customType<{ data: any }>({
  dataType() {
    return 'jsonb';
  },
  toDriver(val) {
    console.log('🚀 ~ toDriver:', val);
    return val as any;
  },
  fromDriver(value) {
    console.log('🚀 ~ fromDriver', value);
    if (typeof value === 'string') {
      try {
        return JSON.parse(value) as any;
      } catch {}
    }
    return value as any;
  },
});

It does not sort object in same order as i passed, this happens under the fromDriver

Using `sql` same issue

sql`${availability}::jsonb` as AvailabilityT

And then API res is also messed up

@MariuzM as indicated by @pfurini its postgres jsonb design.

By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

https://www.postgresql.org/docs/16/datatype-json.html

I just contributed to the bounty on this issue.

Each contribution to this bounty has an expiry time and will be auto-refunded to the contributor if the issue is not solved before then.

To make this a public bounty or have a reward split, the maintainer can reply to this comment.

In case someon

+1 - json/jsonb shouldn’t be listed in the docs as a supported type when this bug exists.

My workaround was to use a db.execute and pass objects into the sql`` template.

const statement = sql`
        INSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks)
        VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks})
        ON CONFLICT (wikidata_id) DO UPDATE
        SET 
            category = ${articleDetails.category},
            grade = ${articleDetails.grade},    
            en_raw_length = ${articleDetails.enBytes},
            en_url_title = ${articleDetails.enUrlTitle},
            labels = ${articleDetails.labels},
            sitelinks = ${articleDetails.sitelinks}
        `;
 await db.execute(statement);

This solution works great, but I had a problem with it because I insert an array into jsonb field and for some reason when I passed this array and it had 2+ elements, the elements were destructured, i.e.:

query:

const array_for_jsonb_field = [{"key1": 1}, {"key2": 2}]
sql`INSERT INTO "my_table" VALUES (${id}, ${val}, ${array_for_jsonb_field}`)

result:

INSERT INTO "my_table" VALUES ($1, $2, ($3, $4)) // $3 and $4 should be a single array but this array with 2 elements got destructured into 2 params

In case someone encounters the same problem, use can do the following:

const array_for_jsonb_field = [{"key1": 1}, {"key2": 2}]
sql`INSERT INTO "my_table" VALUES (${id}, ${val}, ${new Param(array_for_jsonb_field)}`) // I wrapped array with `new Param` 

result:

INSERT INTO "my_table" VALUES ($1, $2, $3)

My workaround was to use a db.execute and pass objects into the sql`` template.

This works well as a temporary solution!

You could also do a hybrid approach where you call

...
await db
    .insert(table)
    .values({
    id: id,
...
})
...

and then call

await db.execute(
    sql`UPDATE table set property = ${property} WHERE id = ${id}`
)

afterward to handle the value that’s jsonb. Note that because this would be two separate calls, I would only recommend this if you’re inserting a lot of columns at once/constantly changing your schema and want to maximize type safety (if you rename your columns in schema.ts, it would update in the db.insert syntax but not db.execute(sql... syntax).