Chapter 38Lesson 10

The raw SQL escape hatch

When and how to drop below the Drizzle query builder into raw SQL with the sql template tag, safely and deliberately.

The query builder has covered every read and write this chapter needed: selects, joins, the relational API, aggregations, upserts, pagination, subqueries, full-text search, and JSONB. That raises a fair question to end on. If the builder is that complete, why does Drizzle ship a sql template at all, and when does an experienced engineer actually reach for it?

You already have, more than once. The filter (where …) aggregate you wrote when counting paid invoices, the row_number() over (…) window for cursor pagination, the websearch_to_tsquery match for search, and the @> containment check on JSONB were each a small, deliberate drop below the builder into raw SQL. You’ve been using the escape hatch all chapter without naming it, and this lesson turns that scattered habit into one consistent model. Think of the builder as the floor you stand on and raw SQL as a controlled way to drop just below it when you have to. By the end you’ll know the triggers that justify reaching for it, the one rule that keeps it safe, and the one corner of the hatch, sql.raw, that doesn’t get that safety for free. The short version is that an experienced engineer reaches rarely, reaches deliberately, and can always answer one question about the result: where did this string come from?

When the builder stops and raw SQL begins

Most days you don’t reach. The builder owns the structure of the query: the select, the from, the where, the orderBy. On the rare occasion you drop below it, you’re only ever filling a gap inside that structure, not abandoning it. So the decision isn’t “builder or raw SQL,” it’s “the builder, plus a small raw fragment where the builder is missing something.” That default is worth keeping in mind, because it sets a high bar for what counts as a good reason to reach.

There are three legitimate triggers.

The first is a Postgres feature with no Drizzle helper. Postgres is enormous, and Drizzle wraps the common surface but not all of it. A custom operator like the trigram distance <-> (nearest-neighbour text similarity), the @@ full-text match you used last lesson, 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 here is precise: the builder is missing a feature, not merely too verbose.

The second is a sub-expression inside an otherwise-builder query. This is by far the shape you’ll hit most often, and it’s the one you already know: where(sql`…`) or orderBy(desc(sql`ts_rank(…)`)). The builder keeps the whole query structure and all of its types, and the sql fills exactly one predicate or one ordering key. You’re not leaving the builder, you’re handing it a small expression it couldn’t write itself.

The third is a one-off statement, maintenance work that has no place in your feature code at all: refresh materialized view …, an ad-hoc admin fix, or a helper inside a migration. These run once, by hand or on a schedule, and no builder shape fits because there’s barely a query to speak of.

Then there’s the anti-trigger, the reason that feels legitimate and isn’t. You reach for raw SQL because the builder felt verbose, or because you remember the SQL and would have to look up the builder method. That’s the wrong reason. The cost of dropping below the builder, which we’ll itemize at the end of the lesson, is never worth saving yourself a documentation lookup. A sql showing up in a diff invites the question “what does the builder not do here?”, and there should always be a real answer. “I couldn’t be bothered to find the method” is not one.

One more distinction is worth drawing, because it’s easy to treat raw SQL as all-or-nothing: the reach is a spectrum, not a cliff. Embedding a sql in a single where is a shallow drop. The surrounding result type is untouched, the builder still projects your columns, and you’ve only gone raw for one boolean predicate. Running a whole db.execute(sql`select … `) is a full drop: no inference, no builder safety, you’ve left the typed world entirely. These are not the same act, so always prefer the shallowest drop that solves the problem. Fill the gap rather than abandoning the builder because part of it was inconvenient.

The walk below puts that decision in the order an experienced engineer actually asks it: builder first, shallowest drop next, raw only at the leaf. Step through it for a query you’re unsure about.

When do you reach for raw SQL?

Inside the `sql` template: identifiers, values, and the parameter boundary

This is the part that matters most, because it’s where a vulnerability can slip into the codebase unnoticed. To see how, you first need to name the two things the sql tag does with the values you interpolate. You’ve used both, probably without distinguishing them.

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

When you interpolate a value, it becomes a bound $1 parameter. Write sql`… where ${invoices.id} = ${id}` and the value of id never enters the SQL string at all. The database driver sends the query text and the value separately, then binds them on the other side. This is the same guarantee as eq(col, value) from the start of the chapter, just reached one level lower. The tag does the parameterizing for you.

That second behavior is the whole safety story, so it’s worth making the failure mode unmistakable. Here are three shapes that look nearly identical and behave completely differently. The first is the only safe one.

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

Bound as $1, safe. The sql tag intercepts the interpolation and hands userInput to the driver as a separate bound parameter. It never becomes part of the SQL text, so there is nothing to inject. This is the same guarantee as eq(invoices.customerName, userInput).

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

Concatenated into the string, injectable. There’s no sql tag here, just a plain backtick literal handed to the driver’s raw pool.query, so userInput is spliced straight into the SQL text. A value like ' or '1'='1 rewrites your query. This is textbook SQL injection, and the missing sql 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, and that’s exactly what makes this dangerous: .raw opts out of binding and passes the string through verbatim. It looks safe and isn’t, because the usual protection of the sql tag is what hides the problem here.

The reason the safe form is safe comes down to a single idea: a bound parameter travels beside the query, never inside it. The diagram below traces the same fragment, sql`… where email = ${input}`, down two tracks so you can see the split.

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.

Now put the distinction into practice. In the block below, several values are interpolated. Click only the ones that reach the driver as a bound parameter, the ones the sql tag protects, and leave the spliced ones alone.

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 intercepts the interpolation and hands the value to the driver as a separate $1 parameter; it never enters the SQL text. (The other interpolation under that same 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.)

The three decoys are all spliced straight 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 backtick literal with no sql tag at all. pool.query receives a string that already has the values concatenated into it. ${orgId} is the trap: it looks like it should be bound, since it has the same ${…} shape as ${term}, but the literal around it was never tagged, so nothing binds it. That missing tag is the whole difference between safe and injectable.

Re-claiming the type you dropped

When you drop below the builder, you lose its inferred return type along with everything else, so you put it back by hand. The tool for this is the type parameter on the tag: sql<T>`…` tells the surrounding builder what the expression evaluates to. The ts_rank ordering key from last lesson is the canonical example. sql<number>`ts_rank(…)` tells the builder this ordering key is a number, because Postgres returns a float and nothing downstream could have guessed that.

One caveat is worth holding onto, the same one that’s followed every sql<T> you’ve written this chapter: it’s a TypeScript-side claim, not a runtime check, exactly like as. Drizzle can’t validate it against what Postgres actually returns; it simply trusts you. If you claim sql<number> on something that comes back as a string, the compiler believes you, and the mismatch surfaces later as a cast error at runtime, or worse, a silently wrong type that propagates through your code. So the discipline is narrow: claim a type only when you know the real Postgres return type, and keep the claim honest. ts_rank is a number. A count(*) filter (where …) is a number. A JSONB ->> leaf is a string until you cast it. You’ve written each of these already; now you can name what the <T> was doing.

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}))`));

How deep you drop decides how much type you lose, and it’s worth being precise about it. Inside where(sql`…`), the surrounding result type is untouched: the builder still projects your columns, so the rows come back fully typed. Only the fragment itself is untyped, and it usually doesn’t even need a <T>, because it returns a boolean predicate that nothing reads. A full db.execute(sql`select …`) is the opposite. There’s no projection to lean on, so the result comes back in the driver’s shape and is essentially unknown. That’s the practical reason the typed builder stays the better default whenever the shape of the returned rows matters, and it sets up the next section.

`db.execute` and the one-off statement

db.execute(sql`…`) runs an arbitrary statement against the connection pool and hands you back the raw driver result, rows plus driver metadata, not a builder-typed array. This is the full drop: you’ve left the typed world, so reserve it for statements that have no meaningful row shape to type or that the builder simply can’t express. Examples are refreshing a materialized view, a one-off maintenance update, or a with recursive … walk of the kind the subqueries lesson pointed here for. Maintenance scripts, migration helpers, and ad-hoc admin queries are the typical setting.

Two things are worth watching, and both are worth keeping in mind before you type db.execute rather than treating them as a footnote.

The builder’s “every mutation carries a where” rule is gone the moment you drop to db.execute. A raw delete from invoices with no where will run and delete every row, and Drizzle will not warn you, because there’s no missing-where guard down here. When you drop to db.execute for a mutation, that protection no longer applies, so the check is now yours to make.

The result shape is also driver-specific. Neon’s serverless driver and node-postgres differ slightly in what db.execute returns: where the rows sit, and what metadata comes alongside them. The setup chapter that wires db/index.ts settles which driver the course uses, so the shape is fixed there rather than guessed at here. The point to remember is that this difference exists, so a result that doesn’t look the way you expected sends you to the right place.

The one clearly legitimate shape is a single line, a statement 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 part of the query text. The rule comes first because it matters most: never pass it anything user input has touched. That’s the injection vector the figure two sections ago drew as a picture, and sql.raw is the function that exposes it.

So why does it exist? Because there’s 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 doesn’t sort by a column, it sorts by the literal string. So when you need a dynamic identifier , such as a sort key the user picks from a dropdown, a bound parameter is structurally impossible, and you have to build the identifier as text. Even then, the text must come from a fixed allow-list in your own code: a hardcoded map keyed by a value you’ve validated, never the raw request value. The user picks which key, and your code owns what each key maps to.

For that common case, though, sql.raw is not even the right tool. sql.identifier(name) quotes a runtime-chosen identifier properly: it wraps the name and escapes the delimiter. So most cases of “I need a dynamic column name” should reach for sql.identifier, not the lower-level sql.raw. (You’ll also come across sql.placeholder('foo'). That’s the named placeholder for prepared statements, a performance detail the next chapter owns. It’s unrelated to sql.raw, so just don’t confuse the two when you meet it.)

There’s one more point here that separates an experienced engineer from someone who read half the docs, and it’s not hypothetical: having sql.identifier quote your identifier is necessary but not sufficient.

That CVE teaches two things, and you need both. First, keep Drizzle patched: the quote-escaping fix is the baseline, and sql.identifier on a patched version (≥ 0.45.2) quotes correctly. Second, and this is the lasting lesson, the quoting helper is not your defense; the allow-list is. Even with a fully patched sql.identifier, you never pass request input to it. You validate the input against a fixed set of permitted names first, and only then build the identifier. Patched quoting limits the damage if you slip, while the allow-list is the actual control. Each one protects you when the other fails, so you want both.

The three tabs below move from the bug to the fix. The first is the CVE pattern; read each tab’s verdict before the code.

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 even when patched. sortParam comes straight from the request and goes straight to sql.identifier. On a patched Drizzle the quoting holds, but the moment you’re a version behind, an attacker controls part of your SQL. Either way, 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, safe. sort can only be a member of SORTABLE: find returns one of those literals or undefined, and the ?? supplies the default. By the time it reaches sql.identifier, it’s no longer attacker-controlled; it’s one of two strings you wrote. The allow-list does the real work here, and the quoting is the backup.

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 allow-list discipline, one level lower. When you need more than a bare identifier, like a tiebreaker or a direction, sql.identifier doesn’t fit, so you drop to sql.raw. It’s only safe because the string is keyed from a map you wrote: sort indexes a fixed object, never the request. The rule is the same as before, that the value can be traced to a fixed set in your code.

This is the habit to carry out of the chapter, and it shows up most concretely in code review. When sql.raw, or sql.identifier on a runtime value, appears in a diff, the reviewer’s first question is “where did this name come from?”, and the only acceptable answer is “a fixed set in our code.” If the value can’t be traced to an allow-list, that’s the bug, patched quoting or not. The helper reduces the damage, but the allow-list is what makes the code correct.

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.

Migrations: where hand-written SQL is the default, not the exception

Everything so far treats raw SQL as the careful exception: reach rarely, parameterize always, trace every identifier. There is one place where that framing flips entirely. Inside a migration file, hand-authored SQL is normal and expected. It isn’t a last resort there; it’s the language you author in.

Here’s why. Drizzle Kit reads your schema and generates the DDL for tables, columns, and constraints. But there’s a whole category of database objects it doesn’t generate, and that category is exactly the running list of items this chapter kept marking as “owned by a migration.” All of those come due here:

Custom indexes. The GIN index for the tsvector search column from last lesson, the GIN index for JSONB containment, partial indexes, the composite cursor index, and expression indexes. The chapter deferred every index to the next chapter, but that’s where how indexes work and how to tune them live. What we’re naming now is where they go: a hand-written create index in a migration file.
Triggers. The updatedAt trigger from the schema chapter, the one that stamps updated_at on every row change, is a hand-written create trigger in a migration. That’s not a workaround; it’s the correct home for it.
Extensions. create extension if not exists pg_trgm, pgcrypto, and similar: Postgres features that have to be switched on before you can use them.
Constraint tweaks Drizzle Kit doesn’t generate. A check constraint shape or an exclusion constraint beyond what the schema can express.

The model to hold is this: the builder governs queries, while migrations are a different surface where you write SQL by hand. A hand-written create index or alter table inside a generated migration’s .sql file is exactly what’s supposed to be there. The discipline on that surface isn’t to avoid SQL, it’s to read every migration before it ships, the generated DDL and your hand edits alike. How migrations are structured and run belongs to the migrations chapter. All you need from this lesson is that raw SQL is normal in migrations, and which artifacts live there.

Here is what that looks like: an extension, a GIN index, and the updatedAt trigger, side by side. Read it as migration-file SQL, not as anything you’d write inside a feature.

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 actually costs

The “reach rarely” rule only holds up if the cost of reaching is concrete, so let’s itemize what you give up the moment you drop below the builder. There are four costs, each a capability the chapter spent a lesson building.

Type inference is gone, and you re-claim it by hand with sql<T>, a claim Drizzle never checks. The “derive your result type, never hand-write it” discipline that ran through the whole chapter stops at the edge of a raw fragment; inside it, you’re back to asserting.

Builder-level safety is gone too. A raw mutation has no missing-where guard, and a raw query has no schema-aware column checking. Every guard the builder was quietly applying for you is now yours to apply.

Forward-compatibility goes as well. The builder tracks changes across Postgres and Drizzle versions and adjusts the SQL it emits. Hand-written SQL is frozen the day you wrote it; if the syntax shifts under you, migrating it is your job.

Reader cost is easy to discount, because it lands on someone else. Every sql makes the next reader switch context between builder calls and raw SQL. A fragment that needed to exist earns that cost; one that was just a skipped lookup adds it for everyone who opens the file after you.

Put it all together and the lesson comes down to one sentence: an experienced engineer reaches for raw SQL rarely, deliberately, at the shallowest depth that works, always parameterized, and never with sql.raw on input they can’t trace to an allow-list.

Now put the judgment to work. Each situation below is drawn from the chapter you just finished. Sort each into the bucket where an experienced engineer would put 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