drizzle-team / drizzle-team/drizzle-orm

postgres-js driver: transparent serializer override breaks JS Date params in raw sql`` templates

Open
#5,789 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

## Bug

`drizzle()` for the `postgres-js` driver overrides postgres-js's outbound timestamptz **serializer** with a transparent passthrough, breaking every `${jsDate}` parameter passed through a raw `sql\`\`` template. The override is intended to defeat the inbound parser (so reads return raw text for Drizzle to convert itself), but the same loop also rebinds the outbound serializer for OIDs `1184/1082/1083/1114/1182/1185/1115/1231`.

[`drizzle-orm/src/postgres-js/driver.ts:29-36`](https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/postgres-js/driver.ts):

```typescript
const transparentParser = (val: any) => val;

// Override postgres.js default date parsers: https://github.com/porsager/postgres/discussions/761
for (const type of ['1184', '1082', '1083', '1114', '1182', '1185', '1115', '1231']) {
client.options.parsers[type as any] = transparentParser;
client.options.serializers[type as any] = transparentParser; // ← also rebinds outbound serializer
}
```

The native postgres-js serializer for OID `1184` is `x => (x instanceof Date ? x : new Date(x)).toISOString()` ([porsager/postgres/src/types.js:31](https://github.com/porsager/postgres/blob/master/src/types.js#L31)). After Drizzle's override, the passthrough returns the JS `Date` instance unchanged. Then postgres-js's `Bind()` flow at [connection.js:959-964](https://github.com/porsager/postgres/blob/master/src/connection.js) calls `b.str(date)`, which invokes `Buffer.byteLength(date)`, which throws:

```
TypeError [ERR_INVALID_ARG_TYPE]: The "string" argument must be of type string or an instance of Buffer or ArrayBuffer. Received an instance of Date
at Buffer.byteLength (node:buffer:850:11)
at reset.str (.../postgres/cjs/src/bytes.js:22:27)
at .../postgres/cjs/src/connection.js:964:16
at Bind (.../postgres/cjs/src/connection.js:954:16)
```

This surfaces as `DrizzleQueryError: Failed query: …` to the caller, with the actual TypeError hidden on `.cause`.

## Minimal repro

```javascript
const postgres = require('postgres');
const { drizzle } = require('drizzle-orm/postgres-js');
const { sql } = require('drizzle-orm');

const client = postgres({ /* connection */ });
const db = drizzle(client);

await db.execute(sql`
INSERT INTO some_table (ts_col) VALUES (${new Date()})
`);
// → DrizzleQueryError → .cause = TypeError ERR_INVALID_ARG_TYPE
```

The same INSERT via `client.unsafe(sqlString, [new Date()])` (raw postgres-js, no `drizzle()`) works because the native serializer is intact.

## What does work (and why this bug is easy to miss)

- `db.insert(table).values({ ts_col: new Date() })` — the column-aware encoder converts the Date through Drizzle's typed path, not through the raw param flow that hits postgres-js's `Bind()`. So users of the typed query builder don't hit this.
- `sql\`… ${new Date().toISOString()}\`` — pre-stringifying defeats the transparent serializer (it passes the string through unchanged, and PG parses the ISO 8601 literal).
- `sql\`… NOW()\`` — uses PG's server-side timestamp, no parameter.

The bug only surfaces for users mixing the raw `sql\`\`` template (often for cross-table UPSERTs or `ON CONFLICT` clauses that Drizzle's typed builder doesn't model cleanly) with JS `Date` interpolation.

## Suggested fix

The cited [porsager/postgres#761](https://github.com/porsager/postgres/discussions/761) is about Drizzle wanting raw text from PG so it can do its own parsing. The fix is to override **only `parsers`**, not `serializers`:

```diff
const transparentParser = (val: any) => val;

for (const type of ['1184', '1082', '1083', '1114', '1182', '1185', '1115', '1231']) {
client.options.parsers[type as any] = transparentParser;
- client.options.serializers[type as any] = transparentParser;
}
client.options.serializers['114'] = transparentParser;
client.options.serializers['3802'] = transparentParser;
```

Or, if there's a reason to also rebind the serializer (e.g., to defeat postgres-js converting a Date back via the round-trip), provide a serializer that *actually serializes*:

```diff
+ const dateSerializer = (val: any) => (val instanceof Date ? val.toISOString() : val);

for (const type of ['1184', '1082', '1083', '1114', '1182', '1185', '1115', '1231']) {
client.options.parsers[type as any] = transparentParser;
- client.options.serializers[type as any] = transparentParser;
+ client.options.serializers[type as any] = dateSerializer;
}
```

Either change preserves Drizzle's intent (custom inbound date handling) without breaking outbound writes.

## Real-world impact

A WXYC backend job (`library-identity-consumer`) hit this on its first prod run on 2026-05-20: 14,405 / 14,405 UPSERTs failed because `last_verified_at` was passed as `${new Date()}` through a `sql\`\`` raw template containing an `ON CONFLICT … DO UPDATE` clause. Diagnosis cost an afternoon — the `DrizzleQueryError.message` only contains the SQL + params and the actual TypeError is hidden on `.cause`. The local workaround was `${new Date().toISOString()}`, but the trap is easy to fall into for anyone writing raw SQL with date parameters.

## Environment

- `drizzle-orm` 0.44.x (latest at time of report)
- `postgres` 3.4.9
- Node 24 (also reproduces on Node 20+)

## Related

- [porsager/postgres#761](https://github.com/porsager/postgres/discussions/761) — the discussion cited in the source comment
- Drizzle docs don't currently mention this constraint; raw `sql\`\`` templates with Date parameters are a documented pattern

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.