Compose custom SQL expressions
A query needs a database expression that is not directly represented by Drizzle’s higher-level builder.
Drizzle ORM · Type Alias
SQL
Drizzle’s sql template embeds parameterized SQL fragments in type-safe queries. The sql<T> form annotates the expected result type for TypeScript, while .as() names computed fields for later reference.
How it works
The sql template builds SQL fragments while parameterizing interpolated values. Adding a generic such as sql<string> supplies a compile-time result type, and .as() gives a selected expression a usable column alias; neither performs runtime conversion.
Syntax
sql<T>`...`; sql`...`.as('alias_name'); sql.raw('...')Parameters
| Name | Type | Description |
|---|---|---|
T | generic | Optional TypeScript result type expected for the SQL expression; it affects static typing only. |
Returns
A SQL query fragment/expression that can be composed into Drizzle queries; with sql<T>, its compile-time result type is T.
Key points
- Drizzle’s SQL template provides a type-safe way to include SQL-shaped expressions alongside its query builder.
- Interpolated values are designed to be included as query parameters rather than concatenated into SQL text.
- A generic result annotation is useful when Drizzle cannot infer the type of a custom SQL expression.
- The
.as()helper explicitly names a SQL expression so its result can be addressed as a field.
Common mistakes
- The generic type is only a TypeScript aid; it does not convert or validate the value returned by the database.
- Use
sql.raw()only for trusted SQL because its contents bypass parameter processing and escaping.
Example: Typed SQL expression
import { sql } from 'drizzle-orm';
const expression = sql<string>`lower(${users.name})`;
const query = db.select({ name: expression.as('name') }).from(users);This embeds a parameterized SQL expression, declares its expected TypeScript type, and aliases it for use as a selected field.
When to use it
Name derived query fields
A computed SQL value must be returned as a named field or reused by a CTE.
Build custom filters
A filter requires an application-specific SQL predicate or operator.
Interview questions
QWhat is the purpose of Drizzle’s sql template?
The sql template lets you write SQL fragments inside Drizzle queries while interpolated values are handled as parameters. It is useful when the high-level query builder does not express a needed SQL operation.
Follow-up: What does sql<T> guarantee? It only supplies a compile-time expected type; it cannot cast or map the runtime database value.
QWhat does the generic parameter in sql<T> do?
sql<T> annotates the expected TypeScript type of an SQL expression. Drizzle does not use that generic to perform runtime mapping or conversion.
Follow-up: What risk follows from an incorrect generic? The static type can disagree with the actual database value, so application behavior may be wrong at runtime.
QWhen should a SQL expression receive an alias?
Use .as('alias_name') on a SQL expression when the expression is selected and must be referenced later, such as from a CTE. Without an alias, Drizzle cannot provide a usable name for that field.
Follow-up: Why can this become a runtime issue? A later query has no column name through which it can reference the expression.
Related
sql.raw()sql<T>sql``.as<T>()DrizzleTypeError