Drizzle jsonbPathQueryFirst / jsonbGet typed as predicates but return eql_v2_encrypted

[coderdan]

Summary

@cipherstash/stack/drizzle (and the legacy @cipherstash/drizzle/pg) exports jsonbPathQueryFirst, jsonbGet, and jsonbPathExists with identical TypeScript signatures — all three resolve to Promise<SQL>, which Drizzle's .where() happily accepts. But only jsonbPathExists returns a boolean at runtime. The other two return eql_v2_encrypted, so using them in .where() errors at query time:

error: argument of WHERE must be type boolean, not type eql_v2_encrypted

The docstrings actively recommend the broken pattern.

Reproduction

In packages/bench, the recorded #422 plan-shape investigation surfaces this against a 10k-row encrypted fixture:

const where = await ops.jsonbPathQueryFirst(bench.encJsonb, '$.idx')
await db.select().from(bench).where(where)
//                                  ^ runtime ERROR

Compiled SQL (excerpt):

SELECT … FROM bench WHERE eql_v2.jsonb_path_query_first(enc_jsonb, $1::eql_v2_encrypted)
-- ERROR:  argument of WHERE must be type boolean, not type eql_v2_encrypted

For comparison, jsonbPathExists works because eql_v2.jsonb_path_exists() returns boolean (it then seq-scans, but that's #422's territory).

Evidence

• packages/stack/src/drizzle/operators.ts (and the same shape in the older packages/drizzle/src/pg/operators.ts:927-931):
  • jsonb_path_query_first(col, selector) → returns the value at the path → eql_v2_encrypted.
  • col -> selector → returns the value → eql_v2_encrypted.
  • jsonb_path_exists(col, selector) → returns boolean. Only this one is a valid predicate.
• All three are typed as (left, right) => Promise<SQL>. The TypeScript surface gives no hint that the first two aren't predicates.
• The published docstrings show await db.select().from(...).where(await ops.jsonbPathQueryFirst(...)) as the canonical example — that pattern errors at runtime.

What the surface should probably look like

These operators are extractors, not predicates. Two reasonable fixes:

1. Split the surface. Keep jsonbPathExists as a Promise<SQL> predicate. Re-shape jsonbPathQueryFirst and jsonbGet so they return something that's usable as the LHS of a comparison (and not directly as a where argument), e.g. by typing them as a column-shaped SQLWrapper and updating the docstrings to show:

   .where(eq(await ops.jsonbPathQueryFirst(t.metadata, '$.role'), 'admin'))

2. Generic-flavour the return type so .where() rejects the extractors at compile time. Drizzle has SQL<boolean> vs SQL<unknown> distinctions in places; mirroring that here would catch the misuse during typecheck.

(1) is more user-friendly and matches what jsonb_path_query_first actually does in PG. (2) is the minimum to stop the footgun.

Background

Surfaced while building packages/bench for the perf/index-engagement audit (sibling issues on the equality-bypass and call-shape investigation). The bench's #422 `recorded-only` lane caught it because EXPLAIN refused to plan the query at all.