String Filters
Filter operators for string scalars
Operators
| Operator | Description |
|---|---|
equals |
Exact match |
not |
Not equal |
in |
Match any in array |
notIn |
Match none in array |
lt / lte |
Sorts before (exclusive / inclusive) |
gt / gte |
Sorts after (exclusive / inclusive) |
contains |
Contains substring |
startsWith |
Starts with prefix |
endsWith |
Ends with suffix |
mode |
Case sensitivity |
equals, not, in, and notIn work the same on every scalar type — see Filtering.
contains
// Contains substring
where: { name: { contains: "alice" } }
// Case insensitive
where: { name: { contains: "alice", mode: "insensitive" } }
startsWith / endsWith
// Starts with
where: { email: { startsWith: "admin" } }
// Ends with
where: { email: { endsWith: "@company.com" } }
// Case insensitive
where: { email: { endsWith: "@company.com", mode: "insensitive" } }
mode (Case Sensitivity)
// Default: case sensitive
where: { name: { contains: "Alice" } }
// Case insensitive
where: { name: { contains: "alice", mode: "insensitive" } }
mode: "insensitive" applies to equals, not, in, notIn, contains, startsWith, and endsWith.
Insensitive mode ignores letter case the way the database itself does, with
its own LOWER (and ILIKE on PostgreSQL for contains, startsWith and
endsWith). Only case is ignored: accents and every other difference still
count, so "ecole" never matches "École".
Which letters have a case is the database’s answer, so a non-ASCII letter can match differently from one database to another:
| Database | "école" matches "École" |
|---|---|
PostgreSQL with a libc or ICU locale (en_US.UTF-8, the initdb default) |
Yes |
PostgreSQL with the C locale (PGlite) |
No: only A-Z fold |
MySQL / MariaDB (utf8mb4) |
Yes |
| SQLite, libSQL, D1 | No: SQLite’s lower() folds A-Z only, unless the ICU extension is loaded |
On PostgreSQL the pattern filters keep the column bare, so a trigram index on it
(CREATE INDEX … USING gin (title gin_trgm_ops)) serves them.
Default string equality, membership, and substring filters are case-sensitive on every supported database, independent of its default collation.
Examples
Search Function
async function searchUsers(query: string) {
return client.user.findMany({
where: {
OR: [
{ name: { contains: query, mode: "insensitive" } },
{ email: { contains: query, mode: "insensitive" } },
{ bio: { contains: query, mode: "insensitive" } },
],
},
take: 20,
});
}