drizzle-team / drizzle-team/drizzle-orm
[BUG]: updating JSONB column turns it into corrupt JSONB
- 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;
```
```sql
SELECT data->>'a' FROM corrupt_jsonb_demo;
```
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
## 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
Assessment
This issue has not been assessed yet.