What operators does Prisma use for comparison?

Prisma uses equals, not, gt (greater than), gte (greater or equal), lt (less than) and lte (less or equal). They apply to numbers, DateTime and other orderable scalars, and equals or not work on every type.

How do I do a case-insensitive string search?

Pair a string operator such as contains, startsWith or endsWith with mode: 'insensitive'. For example { name: { contains: 'ann', mode: 'insensitive' } } matches Ann, ANN and joann on supported databases like PostgreSQL.

What is the difference between in and contains?

in tests whether a scalar field equals one of several values in a list, such as { status: { in: ['A', 'B'] } }. contains tests whether a string field includes a substring, such as { title: { contains: 'sale' } }. They solve different problems.

How do AND, OR and NOT work?

AND and OR take an array of filter objects and combine them; NOT negates the nested condition. Multiple keys at the top level of a where object are implicitly AND-ed, so you only need AND for grouping alternatives.

Can I filter on related records?

Yes. Use relation filters some, every and none on a list relation, or is and isNot on a to-one relation, nesting a where on the related model's fields. For example { posts: { some: { published: true } } }.

Prisma Query Operators Reference

Email me this result

Get this tool's output sent to your inbox, plus one useful tool a week. No spam, unsubscribe any time.

Prisma where-clause operators

Prisma Client filters records with a where object whose keys are field names and whose values are operator objects. The operators cover equality, comparison, string matching, list membership, logical combination and relation filtering. This reference groups every operator, notes which field types it applies to, and gives a usable example so you can compose precise queries.

How it works

Each field maps to an operator object; multiple keys are implicitly AND-ed:

const rows = await prisma.user.findMany({
  where: {
    age: { gte: 18, lt: 65 },
    name: { contains: "ann", mode: "insensitive" },
    role: { in: ["ADMIN", "EDITOR"] },
    OR: [{ verified: true }, { invitedById: { not: null } }],
  },
});

Comparison operators (gt, gte, lt, lte) need orderable types; string operators (contains, startsWith, endsWith) accept mode: 'insensitive' on PostgreSQL and MongoDB; in/notIn test list membership; AND/OR/NOT group conditions.

Tips and notes

Top-level keys are AND-ed automatically; only use AND to group OR blocks.
mode: 'insensitive' is required for case-insensitive matching, not the default.
Use in for value-set membership and contains for substring search.
Relation filters some/every/none query one-to-many; is/isNot query to-one.