Chapter 38Lesson 10

The raw SQL escape hatch

Dropping below the Drizzle query builder into raw SQL with the sql template tag, safely.

The query builder has covered every read and write this chapter needed. So if it’s that complete, why does Drizzle ship a sql template at all, and when does an experienced engineer reach for it?

You already have, more than once. The filter (where …) aggregate, the row_number() over (…) window, the websearch_to_tsquery match, and the @> containment check were each a small, deliberate drop below the builder into raw SQL. The rule behind every one: reach rarely, reach deliberately, and always be able to answer one question — where did this string come from?

Three reasons to drop below the builder

The builder owns the query’s structure: the select, from, where, orderBy. When you drop below it you’re filling a gap inside that structure, not abandoning it, so the choice is never “builder or raw SQL” but “the builder, plus a small raw fragment where it’s missing something.” That sets a high bar. There are three good reasons to clear it.

The first is a Postgres feature with no Drizzle helper. Drizzle wraps the common surface, not all of it. A custom operator like the trigram distance <->, a set-returning function, or a LATERAL join has no builder method, so a raw fragment is the only way to express it. The test is precise: the builder is missing the feature, not merely more verbose.

The second is a sub-expression inside an otherwise-builder query — the shape you’ll hit most: where(sql`…`) or orderBy(desc(sql`ts_rank(…)`)). The builder keeps the whole query’s structure and types, and the sql fills exactly one predicate or ordering key.

The third is a one-off statement — maintenance with no place in feature code: refresh materialized view …, an ad-hoc admin fix, a helper inside a migration. These run once, and no builder shape fits because there’s barely a query to speak of.

Then there’s the anti-trigger: the builder felt verbose, or you remember the SQL and would have to look up the method. That’s the wrong reason. A sql in a diff invites the question “what does the builder not do here?”, and there should always be a real answer — a documentation lookup is never it.

The reach is a spectrum, not a cliff. A sql inside a where is a shallow drop: the result type is untouched and the builder still projects your columns. A whole db.execute(sql`select … `) is a full drop: no inference, no builder safety, the typed world left entirely. Always prefer the shallowest drop that solves the problem.

When do you reach for raw SQL?

What the `sql` tag binds and what gets spliced

The sql tag does two different things with the values you interpolate, and only one of them is safe. Knowing which is which is how you keep a vulnerability from slipping in.

Interpolate a table or column and it becomes a quoted identifier. Drizzle knows the SQL names from your schema, so sql`select * from ${invoices} where ${invoices.id} = …` emits "invoices"."id". The interpolation is structural: it names part of the query rather than supplying a value.

Interpolate a value and it becomes a bound $1 parameter. In sql`… where ${invoices.id} = ${id}`, the value of id never enters the SQL string; the driver sends the query text and the value separately and binds them on the far side.

That second behavior is the whole safety story. Here are three shapes that look nearly identical and behave completely differently. Only the first is safe.

const rows = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.customerName} = ${userInput}`);

Bound as $1, safe. The sql tag hands userInput to the driver as a separate parameter, so it never becomes part of the SQL text. Same guarantee as eq(invoices.customerName, userInput).

const { rows } = await pool.query(
  `select * from invoices where customer_name = '${userInput}'`,
);

Concatenated into the string, injectable. No sql tag, just a plain literal passed to the driver’s raw pool.query, so userInput is spliced into the SQL text. A value like ' or '1'='1 rewrites the query. The missing tag is exactly what would have prevented it.

const rows = await db.execute(
  sql.raw(`select * from invoices where customer_name = '${userInput}'`),
);

Concatenated inside the tag, still injectable. The sql is present, but .raw opts out of binding and passes the string through verbatim. It looks safe precisely because the tag’s usual protection is the part that’s missing.

A bound parameter is safe because it travels beside the query, never inside it. The diagram traces the same fragment down both tracks.

The sql tag parameterized

sql`… where email = ${input}`

SQL string … where email = $1

params [ input ]

driver

The value is bound beside the query — it never enters the executable string.

sql.raw / plain literal spliced

sql`… where email = ${input}`

SQL string … where email = ''; drop table invoices; --'

driver

The value is spliced into the string — the driver executes whatever it spells.

Parameterized values ride alongside the query as `$1`; raw concatenation puts the value — and anything it spells — into the executable SQL.

Several values are interpolated below. Click only the ones the sql tag binds, and leave the spliced ones.

Click every interpolation that reaches the driver as a bound $1 parameter — not the ones spliced into the SQL string. Then press Check.

const matched = await db
  .select()
  .from(invoices)
  .where(sql`${invoices.searchVector} @@ websearch_to_tsquery('english', ${term})`)
  .orderBy(sql.raw(`${sortColumn} desc`));

const legacy = await pool.query(
  `select * from invoices where status = '${status}' and org_id = ${orgId}`,
);

Show the answer

Only ${term} is bound. It sits inside a sql`…` tag, so the tag hands it to the driver as a $1 parameter. (The other interpolation under that tag, ${invoices.searchVector}, is a column, so it becomes the quoted identifier "invoices"."search_vector" — structural, not a value, which is why it isn’t clickable.)

All three decoys are spliced into the SQL string:

${sortColumn} is inside sql.raw(…). The sql is present, but .raw opts out of binding, so the string passes through verbatim: the camouflaged-danger case from the tabs above.
${status} and ${orgId} sit in a plain literal with no sql tag, so pool.query receives the values already concatenated in. ${orgId} is the trap: it shares the ${…} shape of ${term} and looks bindable, but the literal around it was never tagged, so nothing binds it.

Claiming the return type with `sql<T>`

Drop below the builder and you lose its inferred return type, so you put it back by hand. The tag’s type parameter does this: sql<number>`ts_rank(…)` tells the builder the expression is a number, which it needs because Postgres returns ts_rank as a float that nothing downstream could have guessed.

The compiler takes the claim on faith, so keep it honest to the real Postgres return type: ts_rank and a count(*) filter (where …) are number, a JSONB ->> leaf is a string until you cast it.

const ranked = await db
  .select({
    id: invoices.id,
    rank: sql<number>`ts_rank(${invoices.searchVector}, websearch_to_tsquery('english', ${term}))`,
  })
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .orderBy(desc(sql`ts_rank(${invoices.searchVector}, websearch_to_tsquery('english', ${term}))`));

A sql fragment inside where(…) rarely needs a <T>: the builder still projects your columns, so the rows stay typed, and the fragment itself returns a boolean predicate nothing reads. The type only goes missing on a full drop, where there’s no projection to lean on.

`db.execute` and the one-off statement

db.execute(sql`…`) runs an arbitrary statement against the connection pool and hands back the raw driver result. This is the full drop: reserve it for statements with no row shape worth typing or none the builder can express, like refreshing a materialized view , a one-off maintenance update, or a migration helper.

Dropping this far also turns off two guards.

The “every mutation carries a where” guard is gone: a raw delete from invoices with no where deletes every row, with no warning, so the check is now yours.

And the result shape is driver-specific. Neon’s serverless driver and node-postgres put the rows and metadata in different places; the driver in db/index.ts fixes the shape for the course. Knowing the difference exists is what sends you to the right place when a result looks wrong.

The cleanest case is a single line with nothing to type and no builder equivalent:

await db.execute(sql`refresh materialized view concurrently invoice_totals`);

The unsafe corner: `sql.raw` and dynamic identifiers

sql.raw(input) interpolates a string into the SQL without parameterization, so the whole string becomes query text. The rule: never pass it anything user input has touched. That is the injection vector.

It exists because of the one thing a bound parameter genuinely cannot be: an identifier. Postgres won’t accept a $1 placeholder where a table or column name goes; order by $1 sorts by the literal string, not by a column. So when you need a dynamic identifier , such as a sort key the user picks from a dropdown, you must build it as text. That text must come from a fixed allow-list in your own code: a hardcoded map keyed by a value you’ve validated. The user picks the key; your code owns what each key maps to.

For that case, prefer sql.identifier(name) over the lower-level sql.raw. It quotes a runtime-chosen identifier properly, wrapping the name and escaping the delimiter. But quoting is necessary, not sufficient:

So keep Drizzle patched, but treat the quoting helper as a backstop, not the defense. Even with a patched sql.identifier, validate the input against a fixed set of permitted names first, then build the identifier. The allow-list is the actual control; patched quoting only limits the damage if you slip. You want both.

const sortParam = searchParams.get('sort') ?? 'created_at';

const rows = await db
  .select()
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .orderBy(sql.identifier(sortParam));

The CVE pattern, injectable one version behind. sortParam goes straight from the request to sql.identifier. Patched, the quoting holds; unpatched, an attacker controls part of your SQL. Request input should never reach an identifier helper directly.

const SORTABLE = ['created_at', 'amount_due'] as const;

const requested = searchParams.get('sort');
const sort = SORTABLE.find((column) => column === requested) ?? 'created_at';

const rows = await db
  .select()
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .orderBy(sql.identifier(sort));

Validated against a fixed set first. find returns a member of SORTABLE or undefined, and ?? supplies the default, so sort is one of two strings you wrote, not attacker-controlled.

const SORT_COLUMNS = {
  created_at: 'created_at desc',
  amount_due: 'amount_due desc, created_at desc',
} as const;

const sort: keyof typeof SORT_COLUMNS = 'created_at';

const rows = await db
  .select()
  .from(invoices)
  .where(eq(invoices.organizationId, orgId))
  .orderBy(sql.raw(SORT_COLUMNS[sort]));

Same discipline, one level lower. A tiebreaker or direction is more than a bare identifier, so sql.identifier doesn’t fit and you drop to sql.raw. It’s safe only because sort indexes a fixed map you wrote, never the request.

In review, when sql.raw or sql.identifier on a runtime value appears in a diff, ask where the name came from; if it can’t be traced to a fixed set in your code, that is the bug, patched quoting or not.

Drizzle security advisory — sql.identifier injection (CVE-2026-39356)

github.com

The real advisory: the escaping bug, the fix in 0.45.2 / 1.0.0-beta.20, and the patched-version floor.

Migration files: where raw SQL is the norm

Everywhere else, raw SQL is the careful exception. Inside a migration file it is the language you write in.

Drizzle Kit reads your schema and generates the DDL for tables, columns, and constraints. A whole category of database objects falls outside that, the same ones this chapter kept marking “owned by a migration”:

Custom indexes, like the GIN index for the tsvector search column from last lesson. Each is a hand-written create index.
Triggers, like the updatedAt trigger that stamps updated_at on every row change: a hand-written create trigger.
Extensions, like pg_trgm or pgcrypto: Postgres features you switch on before using.
Constraint tweaks Drizzle Kit can’t express from the schema, such as a check shape or an exclusion constraint.

A create index or alter table in a generated migration’s .sql file is exactly what belongs there. The discipline isn’t avoiding SQL; it’s reading every migration before it ships, generated DDL and hand edits alike. How those migrations run is the next chapter.

Here are an extension, a GIN index, and the updatedAt trigger as migration-file SQL:

create extension if not exists pg_trgm;

create index idx_invoices_search_vector_gin
  on invoices using gin (search_vector);

create or replace function set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger invoices_set_updated_at
  before update on invoices
  for each row
  execute function set_updated_at();

What dropping to raw SQL costs

“Reach rarely” only holds if the cost is concrete. Drop below the builder and you give up four things:

Type inference. You re-claim the result type by hand with sql<T>, a claim Drizzle never checks. The “derive your type, never hand-write it” discipline stops at the edge of a raw fragment.

Builder-level safety. A raw mutation has no missing-where guard; a raw query has no schema-aware column checking. Every guard the builder applied silently is now yours.

Forward-compatibility. The builder adjusts the SQL it emits across Postgres and Drizzle versions. Hand-written SQL is frozen the day you write it; if the syntax shifts, migrating it is your job.

Reader cost. This one lands on someone else: every sql forces the next reader to switch between builder calls and raw SQL. A fragment that had to exist earns that cost; one that was just a skipped lookup imposes it for nothing.

So the rule: reach for raw SQL rarely, deliberately, at the shallowest depth that works, always parameterized, and never with sql.raw on input you can’t trace to an allow-list. Sort each situation below into where you’d handle it.

Sort each situation into where an experienced engineer would handle it. Drag each item into the bucket it belongs to, then press Check.

Reach for raw SQL The builder is missing the feature, or it's not a query at all

Stay in the builder A method or operator helper already covers it

Ordering by trigram <-> distance (no Drizzle operator)

where status = 'sent'

refresh materialized view (a db.execute one-off)

A paginated list with a createdAt + id tiebreaker

create index … using gin (lives in a migration)

Selecting one invoice by its id

Ordering by ts_rank (a shallow sql fragment)

A dynamic column name from a fixed allow-list (via sql.identifier)

External resources

Two canonical references cover the rest: the magic sql operator and the runner that executes it.

Drizzle — the magic sql operator

orm.drizzle.team

The full reference for the sql template tag and its .raw / .identifier / .placeholder helpers.

Drizzle — db.execute

orm.drizzle.team

Running an arbitrary statement against the pool, and what the raw driver result looks like.