Case-insensitive search
Search posts by title while ignoring letter case.
Drizzle ORM · Function
like
ilike builds a case-insensitive SQL pattern-matching expression for use in Drizzle query filters. Pass it to .where() directly or compose it with other conditions.
How it works
ilike creates a case-insensitive SQL pattern-matching expression for a column and value, which can be passed to .where() or combined with other filter expressions.
Syntax
ilike(column, value)Parameters
| Name | Type | Description |
|---|---|---|
column | Column | The table column to test. |
value | string | The case-insensitive SQL pattern to match. |
Returns
A SQL expression suitable for use as a query filter.
Key points
- Pass the
ilikeexpression to a query’s.where()method to filter returned rows. - A search term can be optional, allowing the query builder to choose whether to add the filter.
- Use a conditional expression to apply
ilikeonly when a term exists; otherwise provideundefined. - Combine the case-insensitive match with additional predicates through
and()oror(). - An array of dynamically collected filter expressions can be expanded into
and()inside.where().
Common mistakes
- Treat
ilikeas a SQL expression: place it in.where()and combine it with other expressions rather than expecting it to execute independently.
Example: Case-insensitive title filter
import { ilike } from 'drizzle-orm';
const result = await db
.select()
.from(posts)
.where(ilike(posts.title, 'AI'));Filters posts whose title matches the supplied pattern without regard to letter case.
When to use it
Optional filtering
Add a text filter only when a user supplies a search term.
Composed conditions
Combine text matching with category or numeric constraints.
Interview questions
QWhat does ilike do and where is it used?
ilike is imported from drizzle-orm and accepts a column plus a pattern value. It produces a case-insensitive filter expression that can be supplied to .where().
Follow-up: How would you make the filter optional? Pass undefined to .where() when the input is absent, for example term ? ilike(posts.title, term) : undefined.
QHow do you combine ilike with other conditions?
Use and() or or() to combine ilike with other filter operators. These operators compose SQL expressions into a single condition for .where().
Follow-up: How can filters assembled in separate parts of a program be applied together? Store them in an array and pass them to and(...filters) or or(...filters).
QHow does the ilike expression fit Drizzle's query model?
The pattern is supplied as the second argument, so the caller controls the matching text or wildcard pattern. The resulting expression is used by the query builder when the query runs.
Follow-up: What abstraction does Drizzle use for filter operators? They are SQL expressions, and the sql function can also be used to write arbitrary filters or custom operators.
Related
whereandorsqlgtinArray