Breaking changes (for some of postgres.js users)
Bugs fixed for this breaking change
- [BUG]: jsonb always inserted as a json string when using postgres-js
- [BUG]: jsonb type on postgres implement incorrectly
As we are doing with other drivers, we've changed the behavior of PostgreSQL-JS to pass raw JSON values, the same as you see them in the database. So if you are using the PostgreSQL-JS driver and passing data to Drizzle elsewhere, please check the new behavior of the client after it is passed to Drizzle.
We will update it to ensure it does not override driver behaviors, but this will be done as a complex task for everything in Drizzle in other releases
If you were using postgres-js with jsonb fields, you might have seen stringified objects in your database, while drizzle insert and select operations were working as expected.
You need to convert those fields from strings to actual JSON objects. To do this, you can use the following query to update your database:
if you are using jsonb:
update table_name
set jsonb_column = (jsonb_column #>> '{}')::jsonb;if you are using json:
update table_name
set json_column = (json_column #>> '{}')::json;We've tested it in several cases, and it worked well, but only if all stringified objects are arrays or objects. If you have primitives like strings, numbers, booleans, etc., you can use this query to update all the fields
if you are using jsonb:
UPDATE table_name
SET jsonb_column = CASE
-- Convert to JSONB if it is a valid JSON object or array
WHEN jsonb_column #>> '{}' LIKE '{%' OR jsonb_column #>> '{}' LIKE '[%' THEN
(jsonb_column #>> '{}')::jsonb
ELSE
jsonb_column
END
WHERE
jsonb_column IS NOT NULL;if you are using json:
UPDATE table_name
SET json_column = CASE
-- Convert to JSON if it is a valid JSON object or array
WHEN json_column #>> '{}' LIKE '{%' OR json_column #>> '{}' LIKE '[%' THEN
(json_column #>> '{}')::json
ELSE
json_column
END
WHERE json_column IS NOT NULL;If nothing works for you and you are blocked, please reach out to me @AndriiSherman. I will try to help you!