Filtering
Filter operators for the where clause, varying by scalar type
Basic Filtering
const users = await client.user.findMany({
where: {
role: "ADMIN", // Exact match
active: true, // Boolean
age: { gte: 18 }, // Comparison
name: { contains: "alice" }, // String search
},
});
Common Operators
All scalar types support:
| Operator | Description |
|---|---|
equals |
Exact match |
not |
Negation |
in |
Match any in array |
notIn |
Match none in array |
where: {
role: "ADMIN", // Shorthand for equals
role: { equals: "ADMIN" }, // Explicit equals
role: { not: "GUEST" }, // Not equal
role: { in: ["ADMIN", "MODERATOR"] }, // In array
role: { notIn: ["GUEST", "BANNED"] }, // Not in array
}
A long same-column OR of equalities is sent as that column’s in; other long
ORs are nested so SQLite’s expression-depth limit is not reached.
Untrusted filter values
The object query API is a query language, not a values-only interface. Never
forward a request body directly into where, especially on authentication or
secret fields: { password: { not: "" } } is a valid query operator. Validate
each external value as the primitive your application expects, allowlist
queryable fields, and construct the query on the server. Parameter binding
prevents SQL injection; it does not authorize query operators.
Logical Operators
Combine conditions with AND, OR, NOT:
// AND (implicit)
where: {
role: "ADMIN",
active: true,
}
// AND (explicit)
where: {
AND: [
{ role: "ADMIN" },
{ active: true },
],
}
// OR
where: {
OR: [
{ role: "ADMIN" },
{ email: { endsWith: "@company.com" } },
],
}
// NOT
where: {
NOT: { role: "GUEST" },
}
// Combined
where: {
AND: [
{ active: true },
{
OR: [
{ role: "ADMIN" },
{ email: { endsWith: "@company.com" } },
],
},
],
}
Filter by Type
String Filters
contains, startsWith, endsWith, mode
Number Filters
lt, lte, gt, gte comparisons
Boolean Filters
equals, not operators
DateTime Filters
Date comparisons and ranges
JSON Filters
Path queries and operations
Array Filters
has, hasEvery, hasSome, isEmpty
GeoPoint Filters
Exact coordinates, bounds, polygons, and distance
Field references and SQL fragments
Compare a column against another column of the same row, or against SQL you write