Compose reusable subqueries
Prepare an aggregate or filtered subquery once and reference it from a larger SQL statement.
Drizzle ORM · Module
db.with
db.with(...) attaches a named common table expression to a subsequent query. Define the expression with db.$with(...).as(...), then use it with insert, update, delete, or select operations.
How it works
db.with(...) attaches one or more named common table expressions to a subsequent insert, update, delete, or select query. The expression is created with db.$with(name).as(...), then referenced in the query through its alias.
Syntax
db.with(cte).select(...)
db.with(cte).insert(...)
db.with(cte).update(...)
db.with(cte).delete(...)Returns
The page shows the result assigned from the executed query; the returned shape is determined by the query's returning projection.
Key points
- A WITH clause can precede insert, update, and delete operations.
- Create a reusable named CTE with
$with(name).as(...). - The composed statement is awaited like other asynchronous database queries.
- A delete query can project selected fields from affected rows with
returning.
Common mistakes
- The feature is supported for write operations as well as queries; do not assume WITH is limited to SELECT.
Example: Use a WITH clause with DELETE
import { drizzle } from 'drizzle-orm/node-postgres';
import { sql } from 'drizzle-orm';
import { orders } from './schema';
const averageAmount = db.$with('average_amount').as(
db.select({ value: sql`avg(${orders.amount})`.as('value') }).from(orders),
);
const result = await db
.with(averageAmount)
.delete(orders)
.where(sql`${orders.amount} > (select * from ${averageAmount})`)
.returning({ id: orders.id });This composes a named common table expression with a delete statement and returns deleted identifiers.
When to use it
Combine CTEs with write queries
Apply a CTE while modifying rows with an insert, update, or delete operation.
Interview questions
QHow is a CTE created and attached in Drizzle?
db.$with('name').as(...) defines a named common table expression, and .with(cte) makes it available to the following query. The CTE can then be referenced through its alias inside expressions or query sources.
Follow-up: How would you reuse the same CTE in a write query? Define it once, pass it to .with(...), and reference its alias in the insert, update, or delete statement.
QHow can db.with be used with DELETE?
The documented pattern is to build an aggregate subquery with $with, then call .with(...) before .delete(...). The resulting query can use the named CTE in a predicate and return selected fields.
Follow-up: What kind of SQL construct does this represent? It is a common table expression, allowing a named subquery to be composed with the main statement.
QWhat role does the $with alias play?
The CTE is named with $with('average_amount'), while .as(...) supplies its query body. The name is also the handle used when embedding the CTE in SQL expressions such as a scalar subquery.
Follow-up: Why alias the aggregate column? Give the selected expression an explicit name so the CTE exposes a stable, usable column name.
Related
$withsqldeletereturningWITH statements