Case-insensitive text search
Search post titles without requiring users to match capitalization.
Drizzle ORM · Function
ilike
ilike is a Drizzle ORM filter operator for case-insensitive pattern matching. Use it in .where() with a column and pattern, and combine it with and() or or() for compound conditions.
How it works
Import ilike from drizzle-orm and pass a table column plus a pattern to use it as a filter inside .where(). The generated condition performs a case-insensitive pattern comparison; combine it with and() or or() when the query needs additional predicates.
Syntax
ilike(column, value)Parameters
| Name | Type | Description |
|---|---|---|
column | Column | The table column whose value is tested. |
value | string | The pattern to compare against, using SQL pattern matching semantics. |
Returns
A SQL filter expression suitable for use with .where() or with logical filter combinators.
Key points
ilikecompares a value with a pattern without treating letter case as significant.- A search helper can accept an optional term and use it to construct a reusable query function.
- Pass the operator expression to
.where()and useundefinedwhen no search term should constrain the query. - A condition can be collected in a
SQLfilter array before the final query is built.
Common mistakes
ilikeperforms a case-insensitive comparison, unlikelike, so case differences do not prevent a match.- Use
ilikeas a.where()filter; omitting the condition requires passingundefinedconditionally rather than calling it without a column and pattern.
Example: Case-insensitive title search
import { ilike } from 'drizzle-orm';
const posts = await db
.select()
.from(postsTable)
.where(ilike(postsTable.title, '%AI%'));Selects posts whose title contains “AI”, ignoring letter case.
When to use it
Optional query filtering
Add a search predicate only when a query parameter is present.
Compound filtering
Combine text search with category or numeric constraints.
Interview questions
QWhat does ilike do and where is it used?
ilike is a Drizzle filter operator for a case-insensitive pattern comparison. It is imported from drizzle-orm and used as ilike(column, pattern) inside .where().
Follow-up: How would you add another predicate? Use and(ilike(...), eq(...)) or or(...) and pass the combined expression to .where().
QWhat are the arguments to ilike?
The first argument identifies the table column, while the second supplies the comparison pattern. A pattern such as '%AI%' matches values containing AI regardless of letter case.
Follow-up: How can the filter be optional? Use a conditional expression such as term ? ilike(posts.title, term) : undefined as the .where() argument.
QHow do you combine ilike with other filters?
Drizzle supports composing filter operators with and() and or(). This lets a case-insensitive search coexist with category membership or numeric constraints in one .where() expression.
Follow-up: How can filters be assembled across separate code paths? Store SQL conditions in an array, then combine them with and(...filters) or or(...filters).
Related
likenotIlikenotLikeandorinArrayeq