Ban raw column references in your sql templates — one rule that kills a schema-drift bug class

Typed query builders catch schema drift everywhere except inside raw sql templates. The string column reference is the hole. Ban it. What's left is enough for every legitimate raw-SQL case — and the bug class becomes structurally impossible.

AppX team ·

Ban raw column references in your sql templates — one rule that kills a schema-drift bug class

We had a query that hadn't returned a row in four months. Nobody noticed — "zero rows" is a valid result and the dashboard that consumed it showed a flat line everyone assumed was real.

The query was inside a `sql`` template. The schema had moved on without it.

This is the bug class. Here's the rule that kills it.

The bug we kept seeing

Most teams using a typed query builder — Drizzle, Prisma, Kysely, sqlc, jOOQ — still drop into raw `sql`` templates for things the builder can't express: JSON_MERGE_PATCH, NOW() arithmetic, INTERVAL math, JSONB containment, window functions with weird framing.

The template is escape-hatch territory by design. That's fine. The problem is what people write inside it:

// BEFORE — column name is a string literal inside the template
await db.execute(sql`
  UPDATE agent_turns
  SET metadata = JSON_MERGE_PATCH(metadata, ${patch})
  WHERE role = 'assistant'
    AND created_at > NOW() - INTERVAL 7 DAY
`);

The typed builder cannot see the word role. It is a string of bytes inside a tagged template. Rename role to chat_role in the schema, push the migration, run the typed build — every typed reference across the codebase updates or fails the compile. Every raw template silently continues to reference a column that no longer exists.

In MySQL you get Unknown column 'role' in 'where clause'. If you're lucky.

If the rename was the other direction — the old name still exists as a column that's now NULL for all new rows, or as a generated alias — the query runs cleanly and returns zero rows. Forever. Quietly. Until someone audits the dashboard and asks why the line is flat.

We had this shape rot for months. The fix in the moment is one character. The fix to the class is one rule.

The rule

Column references inside `sql`` templates are banned. Function fragments only.

A `sql`` template may contain SQL syntax that the builder cannot express: function names, operators, structural punctuation, RDBMS-specific keywords. It may not contain a bare column identifier as a string. If you need a column reference inside a function fragment, splice in the typed reference from your schema:

// AFTER — column ref comes from the typed schema; rename breaks the build
import { agentTurns } from './schema';

await db.update(agentTurns)
  .set({
    metadata: sql`JSON_MERGE_PATCH(${agentTurns.metadata}, ${patch})`,
  })
  .where(and(
    eq(agentTurns.role, 'assistant'),
    gt(agentTurns.createdAt, sql`NOW() - INTERVAL 7 DAY`),
  ));

Every column name goes through the typed reference. Everything the builder can't say — JSON_MERGE_PATCH(...), NOW() - INTERVAL 7 DAY — stays in the sql\`` fragment, but the fragment contains no column identifiers. Rename roletochat_roleand TypeScript fails the build atagentTurns.role` before the code ever runs.

Why the counting argument works (proof shape, not a heuristic)

Every column reference in your codebase is one of two kinds:

  1. Typed — passed through the schema object. The compiler can see it.
  2. Raw — a string literal inside a `sql`` template. The compiler cannot see it.

Let R be the count of raw column references in your codebase. Each one is an independent chance for schema drift to silently desync. The drift-bug class has size proportional to R.

If R = 0, the class has size 0. There is no string literal anywhere that the compiler isn't checking. A rename either updates every reference (typed) or fails the build (typed mismatch). There is no third path.

This is the same shape as the 5-backtick fence argument: you're not making the bug rarer, you're making it structurally impossible. A guard that catches "query affected zero rows" is a useful smoke alarm, but it's an alarm on a fire that shouldn't be able to start. The rule kills the ignition source.

What's still allowed inside `sql`` templates

The escape hatch exists for a reason. Inside a `sql`` template you may freely write:

  • SQL keywords — SELECT, FROM, WHERE, CASE, WHEN, OVER, PARTITION BY, LATERAL.
  • Function names — NOW, COALESCE, JSON_MERGE_PATCH, JSONB_SET, INTERVAL, DATE_TRUNC.
  • Operators and structural syntax — parentheses, commas, ||, ->, ->>, @>.
  • Literal values — numbers, strings, intervals, bound via the tagged-template parameter binding (${patch}, not string concatenation).

You may not write:

  • Column names. Splice in ${table.column} from the typed schema.
  • Table names. Splice in ${table} from the typed schema, or use the builder.
  • Enum value literals. 'assistant' from an enum should come from the typed enum object: ${ChatRole.Assistant}. The instant someone renames an enum member, the build catches it.

The mental model: a `sql`` template is a stage for SQL grammar that the builder can't dance. The schema-bound symbols are dancers; they don't live on the stage, they're handed in.

How to enforce it

Two tiers, pick what you can sustain.

Tier 1 — CI grep (the 90% solution, ten minutes to set up):

# Fail CI if any sql`` template contains a known column or table identifier as a bareword.
# Generate the identifier list from your schema once; regenerate when schema changes.
git grep -nE "sql\`[^\`]*\\b(role|metadata|created_at|user_id|status)\\b" -- '*.ts' '*.tsx' \
  && { echo "Raw column reference inside sql\`\` template — use typed schema reference instead."; exit 1; } \
  || exit 0

It's coarse — you'll add a few // raw-ok: ... exceptions for keywords that collide with column names. The grep is the alarm; the eslint rule is the lock.

Tier 2 — eslint rule: parse every TaggedTemplateExpression whose tag is named sql, walk the quasi text, reject any identifier-shaped token that matches a column name pulled from your schema metadata. Drizzle and Kysely expose schema introspection at build time; the rule reads the column list once and rejects matches. Roughly 80 lines of AST-walker, one afternoon.

Either tier works. The point is that the rule must be machine-enforced. A code-review convention is not a counting argument; it's a vibe.

The bug class costs you a quarterly incident — a stale dashboard, a missing audit row, a feature flag silently evaluating against the wrong column for six weeks. The rule costs you fifteen minutes of refactor and one CI step. The only reason it isn't already universal is that `sql`` looks safe — tagged template, parameter binding, feels typed. It is not typed. It is a string with a fancy hat.

Takeaways

  • Inside a `sql`` template, a column name written as a string is not type-checked. Your typed builder cannot save you.
  • Ban bare column identifiers inside sql\`` templates. Splice in the typed schema reference (${table.column}`) for every column ref, including ones inside function fragments.
  • Same rule for table names and enum value literals — anything the schema owns, the schema must hand in.
  • Enforce with CI grep first (ten minutes), upgrade to an eslint rule that walks the template AST against schema metadata (one afternoon).
  • The counting argument: if zero raw column references exist, the schema-drift class has size zero. This is structural, not statistical.
  • Row-count guards are an alarm on a fire that shouldn't be able to start. Kill the ignition source.

Try your own app idea

Describe your app in AppX →