Handle sqlc v1.31 SQLite JSONB projection alias drift

[mkuznets]

Summary

The generated-drift CI job has failed since it was introduced in #38 because it runs fixture generation with the configured sqlc ceiling, v1.31.1, while test/miniflare/src/queries_sql.ts was generated with pre-v1.31 behavior.

This is not merely stale generated output. Accepting the raw v1.31.1 output breaks runtime row mapping for nullable SQLite JSONB columns.

Reproduction

With sqlc v1.31.1 and the current publication candidate:

make test-generated-drift-worktree \
  CANDIDATE_WASM="$PWD/build/plugin.wasm" \
  CANDIDATE_SHA256="$(shasum -a 256 build/plugin.wasm | awk '{print $1}')"

fails with:

generated drift in clean worktree:
M test/miniflare/src/queries_sql.ts

Version boundary established locally:

• sqlc v1.29.0: clean
• sqlc v1.30.0: clean
• sqlc v1.31.0: drift
• sqlc v1.31.1: drift

The output is deterministic and reproduces with both Linux and macOS sqlc v1.31.1 binaries.

What changed upstream

For SQLite JSONB columns, sqlc rewrites projections so applications receive textual JSON rather than SQLite's internal JSONB blob representation.

A source query such as:

SELECT * FROM samples;

is expanded in generated SQL to include:

json(json_null)

instead of:

json_null

The expression is emitted without an alias. SQLite/D1 therefore names the result field json(json_null), while sqlc's result metadata still identifies the logical column as json_null. This plugin consequently generates a name-based mapper equivalent to:

rowJsonOrNull(row, "json_null", "jsonNull", ctx)

The D1 result contains a json(json_null) key rather than json_null, so mapping fails with a missing-field QueryResultError.

Raw regeneration followed by the Miniflare suite produces five failures, including:

result field jsonNull of query CreateSample must be JSON text or null

The failure is a missing result key, not an incorrect physical JSON value.

Upstream context

• sqlc PR #3968 introduced SQLite JSONB-to-JSON projection coercion.
• sqlc issue #4337 reported that coercion was skipped for uppercase JSONB declarations.
• sqlc PR #4385, included in v1.31, made matching case-insensitive and exposed this repository's uppercase JSONB fixture to the rewrite.
• No existing sqlc issue was found for the missing result alias specifically.

The likely upstream behavior should be:

json(json_null) AS json_null

Proven local workaround

Replace affected SELECT * / RETURNING * projections in test/miniflare/queries.sql with explicit column lists and preserve the nullable JSONB result name explicitly:

json_null AS json_null

After regenerating test/miniflare/src/queries_sql.ts with sqlc v1.31.1, this was verified locally with:

• generated-drift mirror check: pass
• Miniflare suite: 33/33 tests pass
• generator suite: 109/109 tests pass
• git diff --check: pass

Proposed work

• Commit an explicit-projection fixture fix so CI generation under sqlc v1.31.1 is stable and D1 row keys remain compatible with generated mappers.
• Add or retain a regression assertion that exercises nullable JSONB through real D1 and catches a lost result alias.
• File an upstream sqlc issue with a minimal name-based row-mapping reproduction and link it here.
• Revisit the explicit-projection workaround if upstream sqlc begins emitting aliases for coerced JSONB expressions.

Acceptance criteria

• generated-drift passes with the configured sqlc ceiling.
• Miniflare JSON/JSONB round-trip and command tests pass against the regenerated fixture.
• Generated SQL exposes the nullable JSONB field under json_null, not json(json_null).
• This issue links to the corresponding upstream report.

[mkuznets]

Repository workaround committed in 5c96dc5. CI run 31746439563 passed, including generated-drift, Miniflare, exact-candidate, public-types, canonical-example, and all sqlc compatibility cells. Keeping this issue open to track the upstream sqlc report and eventual removal of the workaround.