[BUG]: jsonb type on postgres implement incorrectly
Problem
What version of `drizzle-orm` are you using? 0.29.0 What version of `drizzle-kit` are you using? 0.20.1 Describe the Bug 1. create tbl01 in the postgres db 2. run this code [code block] Expected behavior jsonb type incorrectly save data as json string, so we cannot query data field using ->> operator in the postgres. Environment & setup _No response_
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix jsonb Type Handling in Drizzle-ORM for PostgreSQL
The issue arises because the `drizzle-orm` library is not correctly serializing the `jsonb` type when saving data to PostgreSQL. Instead of storing the data as a JSON object, it is being saved as a JSON string, which prevents the use of PostgreSQL's JSON operators like `->>` for querying.
Awaiting Verification
Be the first to verify this fix
- 1
Update Drizzle-ORM and Drizzle-Kit
Ensure that you are using the latest versions of `drizzle-orm` and `drizzle-kit` as there may have been bug fixes related to JSON handling.
bashnpm install drizzle-orm@latest drizzle-kit@latest - 2
Modify Table Definition
Check the table definition for `tbl01` to ensure that the column intended for JSON storage is defined as `jsonb` in PostgreSQL. If it is defined as `text` or another type, alter the table.
sqlALTER TABLE tbl01 ALTER COLUMN your_json_column TYPE jsonb USING your_json_column::jsonb; - 3
Adjust Data Insertion Logic
Ensure that when inserting data into the `jsonb` column, the data is being passed as a JavaScript object rather than a string. This will allow `drizzle-orm` to correctly serialize it as JSON.
typescriptawait db.insert(tbl01).values({ your_json_column: { key: 'value' } }); - 4
Test JSON Querying
After making the above changes, run a test query using the `->>` operator to confirm that the data is now accessible as expected.
sqlSELECT your_json_column->>'key' FROM tbl01; - 5
Review Documentation
Check the `drizzle-orm` documentation for any additional notes on handling `jsonb` types to ensure compliance with best practices.
Validation
Run the test query after implementing the above steps. If the query returns the expected results without errors, the fix has worked. Additionally, verify that new data is being stored correctly in the `jsonb` format.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep