Summary
`@cipherstash/drizzle`'s equality operators (`eq`, `ne`, `inArray`, `notInArray`) compile to plain Drizzle `eq()` / `ne()` — which produce bare `col = value` SQL. On Supabase or any `--exclude-operator-family` install, this misses the documented `eql_v2.hmac_256(col)` functional hash index and falls back to a sequential scan.
This is fixable as a code change (unlike the structurally-constrained encryptedSupabase issue tracked separately) — Drizzle already uses call-shaped forms (`eql_v2.gt(...)`, `eql_v2.like(...)`, `eql_v2.order_by(...)`) for other operator families. Just needs the same treatment for equality.
Evidence
`packages/drizzle/src/pg/operators.ts:731`:
```ts
return operator === 'eq' ? eq(left, encrypted) : ne(left, encrypted)
```
The `eq` and `ne` here are Drizzle's built-in operators, which generate plain `column = $N` / `column <> $N` SQL.
Confirmed by `packages/drizzle/tests/operators.test.ts:45` showing the resulting `query.sql` contains literal `=`.
Suggested fix
Change to the wrapped form so the hash functional index engages:
```ts
// Equality: WHERE eql_v2.hmac_256(col) = eql_v2.hmac_256(value)
return sql`eql_v2.hmac_256(${left}) ${sql.raw(operator === 'eq' ? '=' : '<>')} eql_v2.hmac_256(${encrypted})`
```
(Pseudocode — match the surrounding sql template style in the file.)
The same shape extends to `inArray` / `notInArray` — wrap each side of each comparison.
What about `like` / `ilike` / `jsonb_*` / `order_by`?
Drizzle does emit call-shaped forms for these:
- `eql_v2.like(col, …)` / `eql_v2.ilike(col, …)` (line 853)
- `eql_v2.gt(col, …)` / `eql_v2.gte(col, …)` / `eql_v2.lt(col, …)` / `eql_v2.lte(col, …)` (line 697)
- `ORDER BY eql_v2.order_by(col) ASC/DESC` (lines 1636, 1653)
- `eql_v2.jsonb_path_query_first(col, …)` etc. (lines 927–931)
But the function names emitted don't match EQL's documented Supabase functional-index extractors (`eql_v2.hmac_256`, `eql_v2.bloom_filter`, `eql_v2.ste_vec`). Whether these forms engage indexes depends on whether the EQL operators / functions are inlinable enough for the planner to peek inside and match. This needs separate investigation with EXPLAIN against the bench fixture — tracked as a follow-up issue.
For `eq` / `ne` / `inArray` specifically, the fix is direct and verifiable: switch to `eql_v2.hmac_256(...)` wrapping and EXPLAIN should show `Bitmap Index Scan on bench_text_hmac_idx` instead of seq scan.
Verification
Reproducible on the encrypt-query-language repo's bench fixture:
```sql
-- Apply: tests/sqlx/fixtures/{bench_data.sql, bench_setup.sql, drop_operator_classes.sql}
EXPLAIN SELECT * FROM bench WHERE encrypted_text = '<encrypted_jsonb>'::jsonb;
-- Plan: Parallel Seq Scan, cost ~5029
EXPLAIN SELECT * FROM bench
WHERE eql_v2.hmac_256(encrypted_text)
= eql_v2.hmac_256('<encrypted_jsonb>'::jsonb::eql_v2_encrypted);
-- Plan: Bitmap Index Scan on bench_text_hmac_idx, cost ~2391
```
Related
Summary
`@cipherstash/drizzle`'s equality operators (`eq`, `ne`, `inArray`, `notInArray`) compile to plain Drizzle `eq()` / `ne()` — which produce bare `col = value` SQL. On Supabase or any `--exclude-operator-family` install, this misses the documented `eql_v2.hmac_256(col)` functional hash index and falls back to a sequential scan.
This is fixable as a code change (unlike the structurally-constrained encryptedSupabase issue tracked separately) — Drizzle already uses call-shaped forms (`eql_v2.gt(...)`, `eql_v2.like(...)`, `eql_v2.order_by(...)`) for other operator families. Just needs the same treatment for equality.
Evidence
`packages/drizzle/src/pg/operators.ts:731`:
```ts
return operator === 'eq' ? eq(left, encrypted) : ne(left, encrypted)
```
The `eq` and `ne` here are Drizzle's built-in operators, which generate plain `column = $N` / `column <> $N` SQL.
Confirmed by `packages/drizzle/tests/operators.test.ts:45` showing the resulting `query.sql` contains literal `=`.
Suggested fix
Change to the wrapped form so the hash functional index engages:
```ts
// Equality: WHERE eql_v2.hmac_256(col) = eql_v2.hmac_256(value)
return sql`eql_v2.hmac_256(${left}) ${sql.raw(operator === 'eq' ? '=' : '<>')} eql_v2.hmac_256(${encrypted})`
```
(Pseudocode — match the surrounding sql template style in the file.)
The same shape extends to `inArray` / `notInArray` — wrap each side of each comparison.
What about `like` / `ilike` / `jsonb_*` / `order_by`?
Drizzle does emit call-shaped forms for these:
But the function names emitted don't match EQL's documented Supabase functional-index extractors (`eql_v2.hmac_256`, `eql_v2.bloom_filter`, `eql_v2.ste_vec`). Whether these forms engage indexes depends on whether the EQL operators / functions are inlinable enough for the planner to peek inside and match. This needs separate investigation with EXPLAIN against the bench fixture — tracked as a follow-up issue.
For `eq` / `ne` / `inArray` specifically, the fix is direct and verifiable: switch to `eql_v2.hmac_256(...)` wrapping and EXPLAIN should show `Bitmap Index Scan on bench_text_hmac_idx` instead of seq scan.
Verification
Reproducible on the encrypt-query-language repo's bench fixture:
```sql
-- Apply: tests/sqlx/fixtures/{bench_data.sql, bench_setup.sql, drop_operator_classes.sql}
EXPLAIN SELECT * FROM bench WHERE encrypted_text = '<encrypted_jsonb>'::jsonb;
-- Plan: Parallel Seq Scan, cost ~5029
EXPLAIN SELECT * FROM bench
WHERE eql_v2.hmac_256(encrypted_text)
= eql_v2.hmac_256('<encrypted_jsonb>'::jsonb::eql_v2_encrypted);
-- Plan: Bitmap Index Scan on bench_text_hmac_idx, cost ~2391
```
Related