Summary

When inserting JSONB columns using @effect/sql-drizzle with PostgreSQL, JSON values are being serialized to strings instead of stored as JSON, causing issues with JSONB database functionality. Using raw client or SqlClient directly correctly stores JSON, but the PgDrizzle wrapper serializes JSON values to strings. Using SQL query with explicit cast (sql${payload}::jsonb``) works as a workaround. Attempts to enable transformJson in sql-pg did not resolve the issue. The Drizzle client setup was shared, and it was noted that `yield*` can be used with rawClient due to global prototype patching by drizzle. Key takeaways: JSONB handling differs between raw client and PgDrizzle, explicit JSONB casting is a current workaround, and internal transformations or settings like `transformJson` may not affect this behavior.

Discord thread

https://discord.com/channels/795981131316985866/1418585968746692860