Encode a value for writing into a Postgres jsonb column in a way that is correct
under BOTH database drivers this codebase uses.
Local/dev/test connections use node-postgres (pg, PostgresDialect); remote/prod
uses postgres.js (PostgresJSDialect — see createRemoteDb in ../db). The two
drivers type bound parameters differently:
pg sends a bare JS scalar as an UNTYPED text parameter, which Postgres coerces
into jsonb (e.g. true -> jsonb true). So a raw value "just works" locally.
postgres.js infers a concrete Postgres type from the JS value and sends a typed
parameter (a JS boolean -> bool). Postgres has no implicit cast from boolean
(or numeric / text) to jsonb, so the statement is rejected at plan time:
column "..." is of type jsonb but expression is of type boolean.
Objects and arrays are unaffected — postgres.js auto-JSON-encodes them — so this only
bites bare scalars. The fix is to serialize the value ourselves and hand Postgres text
to parse as JSON. The intermediate ::text is load-bearing: without it postgres.js
re-encodes the string and you store a JSON string ("true") instead of the intended
JSON boolean (true).
Verified to round-trip booleans, numbers, strings, and nested objects to the correct
jsonb_typeof under both drivers.
Encode a value for writing into a Postgres
jsonbcolumn in a way that is correct under BOTH database drivers this codebase uses.Local/dev/test connections use node-postgres (
pg,PostgresDialect); remote/prod uses postgres.js (PostgresJSDialect— seecreateRemoteDbin../db). The two drivers type bound parameters differently:pgsends a bare JS scalar as an UNTYPED text parameter, which Postgres coerces intojsonb(e.g.true-> jsonbtrue). So a raw value "just works" locally.postgres.jsinfers a concrete Postgres type from the JS value and sends a typed parameter (a JS boolean ->bool). Postgres has no implicit cast fromboolean(ornumeric/text) tojsonb, so the statement is rejected at plan time:column "..." is of type jsonb but expression is of type boolean.Objects and arrays are unaffected — postgres.js auto-JSON-encodes them — so this only bites bare scalars. The fix is to serialize the value ourselves and hand Postgres text to parse as JSON. The intermediate
::textis load-bearing: without it postgres.js re-encodes the string and you store a JSON string ("true") instead of the intended JSON boolean (true).Verified to round-trip booleans, numbers, strings, and nested objects to the correct
jsonb_typeofunder both drivers.