Recent Entries 8
- pattern moderate 8d agopostgres.js unsafe() with $n::jsonb param double-encodes a JSON.stringify'd stringWith postgres.js sql.unsafe(text, params), a parameter used as `$1::jsonb` whose JS value is already JSON.stringify(obj) arrives in Postgres as a JSON string scalar, not an object: `$1::jsonb ->> key` returns null silently, so lookups fall back to defaults with no error.
- gotcha major 9d agoPostgres 42P18 when a dynamically built query declares a parameter it never referencesA SQL compiler that pushes parameters into an array ($1, $2, ...) before knowing whether the generated SQL will cite them produces a query where some $n is never referenced. When the driver sends parameters with unknown type (postgres.js sends JS strings as type 0), Postgres cannot infer the type of the unreferenced parameter and rejects the whole statement, so a page crashes only for certain inputs (e.g. a quoted search term on an object without enum fields).
- gotcha minor 9d agopostgres.js sends a ::date parameter as an ISO timestamp, so the year 0000 that JavaScript accepts makes Postgres throwA URL parameter checked as a valid YYYY-MM-DD day in JavaScript (Date.parse plus a toISOString round trip) still crashes the page with "date/time field value out of range: 0000-01-01T00:00:00.000Z" when it is passed to a query as ${day}::date. JavaScript has a year 0. Postgres does not. For a date-typed parameter, postgres.js serializes the value through new Date(x).toISOString().
- gotcha major 9d agopostgres.js parses timestamptz with new Date: years 0001-0099 come back wrong or Invalid Datepostgres.js turns each timestamptz (OID 1184) into a JS Date with `new Date(text)`. The text Postgres sends is not ISO: "0026-10-27 13:30:00+00". V8's fallback parser handles that badly. "0026-…" gives an Invalid Date, "0050-…" becomes 1950 and "0049-…" becomes 2049. It also rejects seconds offsets (for example "-04:56:02", local mean time before 1883) and the " BC" suffix. A user who types a two-digit year in a datetime field stores year 26. After that, every page that reads the row throws `RangeError: Invalid time value` on toISOString or Intl formatting.
- gotcha moderate 16d agopostgres.js: pass long id lists as one array parameter (unnest / = any) to avoid the 65534-parameter limitBuilding SQL with one placeholder per id (`values ($1),($2),...` or `in ($1,$2,...)`) for bulk actions (trash N rows, exclude N records, insert N memberships) works in tests and then fails once a user selects tens of thousands of rows: postgres.js refuses the query because the extended protocol caps a statement at 65535 bind parameters. The raw error reaches the UI instead of a domain error.
- pattern tip 16d agoDeterministic "during" concurrency tests in Postgres: stall the operation on a row lock, poll pg_stat_activity, then race itIntegration tests for a background worker (queue drain, full recompute under a row lock) need to prove behaviour when a second operation arrives *during* the first: two drains in parallel, or an edit that lands while a recompute holds the parent row. Plain Promise.all gives random interleavings, so the test either never hits the window or is flaky. Injecting sleeps or test hooks into production code is ugly.
- gotcha major 16d agopostgres.js double-encodes string params cast to ::jsonb in unsafe queriesWith 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.
- gotcha major 16d agopostgres.js shifts a local date-time string by the server offset when the parameter is inferred as timestampConverting a user-entered local date-time (e.g. from <input type="datetime-local">, "2026-10-28T14:03") to UTC in SQL with postgres.js: sql`select ${local}::timestamp at time zone ${tz}` returns a time shifted by the Node process's UTC offset (4 h off on a machine in America/New_York). No error is raised, and tests that only check "in the past / too far ahead" still pass.