gotchatypescriptMajor
postgres.js double-encodes string params cast to ::jsonb in unsafe queries
Viewed 0 times
sql.unsafejsonb parameterdouble JSON encodingboolean filter never matchesparameter type inference
Error Messages
jsonb_typeof returns 'string' instead of 'boolean' (no error raised)Problem
With postgres.js sql.unsafe(text, params), a JS string param used as
$n::jsonb (for example JSON.stringify(true) === "true") is serialized again with JSON.stringify because the server describes the param as jsonb. The value arrives as the jsonb string "true", not the boolean true, so props -> 'flag' = $n::jsonb never matches. No error is raised and the filter just silently returns nothing.Solution
Do not pass pre-serialized JSON strings to a
$n::jsonb placeholder. Pass the real JS value with a scalar cast and convert it in SQL, for example to_jsonb($n::boolean) with a JS boolean, or to_jsonb($n::text[]) with a JS string array. You can also force text first with $n::text::jsonb when you really have JSON text. Check with: select jsonb_typeof($1::jsonb) using param "true", which returns 'string' instead of 'boolean'.Why
For unsafe queries postgres.js prepares the statement, reads the parameter types the server inferred (jsonb here), then serializes each value with that type's serializer. For json/jsonb the serializer is JSON.stringify, so an already-stringified value gets quoted a second time.
Gotchas
- A related pitfall: an unused $n placeholder makes Postgres fail with 'could not determine data type of parameter $n', so allocate params lazily in SQL builders.
Code Snippets
Wrong vs right
// wrong: matches nothing
sql.unsafe("select * from t where props -> 'vip' = $1::jsonb", [JSON.stringify(true)]);
// right
sql.unsafe("select * from t where props -> 'vip' = to_jsonb($1::boolean)", [true]);Revisions (0)
No revisions yet.