[BUG]: jsonb always inserted as a json string when using postgres-js.
Problem
What version of `drizzle-orm` are you using? 0.26.5 What version of `drizzle-kit` are you using? 0.18.1 Describe the Bug Inserting an object into a postgres jsonb field with db.insert only inserts a string when using the postgres-js.adapter. Expected behavior With the pg package, an object is inserted using the code below, which is the expected behavior. With the postgres-js package, a string is inserted into the table using the same code. Environment & setup drizzle packages as above, and, "pg": "8.11.0" and "postgres": "3.3.5" schema.ts: import { pgTable, jsonb } from "drizzle-orm/pg-core"; export const logs = pgTable("log", { line: jsonb("line").$type<object>(), }); load.ts: let lines: { line: object }[] = []; let n = 0; for await (const line of rl) { const lineObj = JSON.parse(line); lines.push({ line: lineObj }); if (++n > numLines) { await runFunction(lines); lines = []; n = 0; } } await runFunction(lines); runFunction: async (lines) => { await db.insert(logs).values(lines); }
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix jsonb Insertion Issue with postgres-js in Drizzle ORM
The issue arises because the postgres-js adapter does not correctly serialize JavaScript objects into JSONB format when inserting into PostgreSQL. Instead, it treats the object as a string, leading to incorrect data storage.
Awaiting Verification
Be the first to verify this fix
- 1
Update Drizzle ORM to Latest Version
Ensure that you are using the latest version of drizzle-orm, as this may include bug fixes related to JSONB handling.
bashnpm install drizzle-orm@latest - 2
Use JSON.stringify for Object Serialization
Before inserting the object into the database, explicitly convert the object to a JSON string using JSON.stringify. This ensures that the data is correctly formatted for the JSONB field.
typescriptlines.push({ line: JSON.stringify(lineObj) }); - 3
Check Database Column Type
Verify that the column type in the PostgreSQL database is set to JSONB. If it is not, alter the column type to JSONB to ensure proper storage of JSON data.
sqlALTER TABLE log ALTER COLUMN line TYPE jsonb; - 4
Test Insertion with Sample Data
Run a test insertion with a sample object to confirm that the data is now being stored correctly in the JSONB format.
typescriptawait db.insert(logs).values([{ line: JSON.stringify({ key: 'value' }) }]); - 5
Review and Update Documentation
If the issue persists, review the documentation for drizzle-orm and postgres-js for any additional configuration settings that may affect JSONB handling.
Validation
Confirm that the data is correctly inserted into the PostgreSQL database by querying the log table and checking that the 'line' column contains valid JSONB objects, not strings.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep