Basic row filtering
Retrieve only rows matching a column comparison.
Drizzle ORM · Module
.where
.where() filters rows in Drizzle ORM queries using SQL expressions such as eq(), and(), and or(). Custom sql expressions can express arbitrary predicates.
How it works
.where() adds a SQL filter to a query. Pass a filter operator such as eq() or gt(), combine conditions with and() or or(), or use sql for a custom expression.
Syntax
db.select().from(table).where(condition)Parameters
| Name | Type | Description |
|---|---|---|
filter | SQL expression | A Drizzle filter expression, commonly created with operators such as eq, lt, gte, or ne. |
Returns
A query builder containing the SQL WHERE condition; executing the builder returns the filtered rows.
Key points
- Use
.where()to restrict returned rows with Drizzle filter operators. - Built-in filter operators are grounded in Drizzle’s
sqlexpression system. - Pass a custom
sqlexpression for arbitrary predicates or reusable operator implementations. - Compose predicates with
and()andor()to represent compound filtering logic.
Common mistakes
- A query builder normally permits
.where()only once; combine predicates withand()oror()instead. - Use
.$dynamic()when a reusable function must add query clauses incrementally.
Example: Filter rows with combined conditions
import { eq, and, gt } from 'drizzle-orm';
const adults = await db
.select()
.from(users)
.where(and(eq(users.active, true), gt(users.age, 17)));Filters active users whose age is greater than 17.
When to use it
Compound conditions
Require several predicates or allow alternatives.
Custom SQL filters
Implement a predicate not covered by the built-in operators.
Interview questions
QWhat does .where() do in Drizzle ORM?
.where() applies filtering to a query, and its argument is typically a predicate built with operators such as eq, lt, gte, or ne. The resulting SQL contains the corresponding WHERE clause.
Follow-up: How do you express multiple conditions? Use and() or or() to combine filter operators before passing the result to .where().
QHow do you combine multiple .where() conditions?
Use and() and or() to logically combine filter operators. For example, where(and(eq(users.active, true), gt(users.age, 17))) requires both conditions.
Follow-up: Can .where() be called repeatedly? Normally no; switch the builder to dynamic mode with .$dynamic() when incremental composition is required.
QWhen should you use sql with .where()?
Drizzle’s filter operators are implemented with the sql function. You can therefore pass a custom sql expression to .where() when the built-in operators do not express the needed filter.
Follow-up: What does this enable beyond custom filtering? It also allows developers to create their own filter operators.
Related
and()or()sqleq()lt()gte()ne().$dynamic()