drizzle-team / drizzle-team/drizzle-orm

[BUG]: updating JSONB column turns it into corrupt JSONB

Open
#2,279 4 comments 10 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta db/postgres priority qb/crud
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### What version of `drizzle-orm` are you using?

^0.30.7

### What version of `drizzle-kit` are you using?

^0.20.14

### Describe the Bug

When using `set` after `update` on a JSONB column it adds something to my Postgres db that _is_ some kind of JSONB (it doesn't error out) but I can't query it's properties using JSON operators like ->.

## Steps to reproduce:
1. Create table
```sql
CREATE TABLE IF NOT EXISTS corrupt_jsonb_demo (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL
);
```

2. Insert some data
```sql
INSERT INTO corrupt_jsonb_demo (data) VALUES ('{"a": 1}');
```

2.1. Select this row, SQL Tools for VSCode (displays them differently once they go corrupt, watch the highlighting)
```sql
SELECT * FROM corrupt_jsonb_demo;
```
Screenshot 2024-05-08 at 22 15 42

```sql
SELECT data->>'a' FROM corrupt_jsonb_demo;
```
Screenshot 2024-05-08 at 22 16 14

3. Use drizzle to update the record using an existing object (this is how it initially happened to me)

Table definition:
```ts
export const corrupt_jsonb_demo = pgTable('corrupt_jsonb_demo', {
id: serial('id').primaryKey(),
data: jsonb('data').$type<{ [key: string]: any }>().notNull(),
});
```

Query:
```ts
await db
.update(corrupt_jsonb_demo)
.set({
data: {
...existingRecord.data,
}
// or
// data: existingRecord.data,
})
.where(eq(schema.corrupt_jsonb_demo.id, existingRecord?.id));
```

Result when I rerun queries from 2.1
Screenshot 2024-05-08 at 22 19 25
Screenshot 2024-05-08 at 22 19 35

## temporary fix
I have tried casting it back in Postgres using ::JSONB, ::JSON and same with CAST but that didn't work because it considered them ok. When I put the shape into an online validator it also said the JSON was valid even when I stringified it.

I have managed to fix my predicament by looping over all the records and updating them using the magic `sql` operator like so:

```ts
await db
.update(corrupt_jsonb_demo)
.set({
data: sql`${existingRecord.data}`
})
// ... rest of condition goes here
```

---

Thanks for everything though, drizzle is amazing to use 🚀

### Expected behavior

When I use `set` after `update` it updates the JSON and remains queriable

### Environment & setup

Mac and Linux (Supabase deployment) I guess

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.