Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 36 ยท Client Queries

Filtering with where: Operators, AND, OR and NOT

Master Prisma's where option: equality, comparison, string filters, in, null checks, AND, OR, NOT and case-insensitive search, with a stand-in engine you can experiment with.

Almost every query needs a filter. In Prisma you describe the filter as an object in the where option, and the same object works in findMany, findFirst, count, update, delete and more. Learn it once and you can use it everywhere.

We continue with the Product model from the previous lesson (name, category, priceCents, stock, sku, createdAt).

Equality is the default

TypeScript
await prisma.product.findMany({ where: { category: "kitchen" } });
await prisma.product.findMany({ where: { category: "kitchen", stock: 0 } });

Several fields in one object are combined with AND: the row must match all of them. The long form of an equality filter is { equals: "kitchen" }; the short form is what you will write 95% of the time.

Comparison and list operators

TypeScript
await prisma.product.findMany({
  where: {
    priceCents: { gte: 1000, lt: 5000 },   // 1000 <= price < 5000
    category: { in: ["kitchen", "office"] },
    sku: { not: "OLD-001" },
    stock: { notIn: [0] },
  },
});
Operator Meaning
equals same value (also the shorthand)
not different value (also accepts a nested filter)
in / notIn value is (not) in the list
lt, lte, gt, gte less than, at most, greater than, at least

String operators

TypeScript
await prisma.product.findMany({
  where: {
    name: { contains: "mug" },
    sku: { startsWith: "MUG-" },
    category: { endsWith: "ware" },
  },
});

contains, startsWith and endsWith become SQL LIKE patterns. On PostgreSQL they are case-sensitive; add mode: "insensitive" for case-insensitive matching:

TypeScript
await prisma.product.findMany({
  where: { name: { contains: "MUG", mode: "insensitive" } },
});

On MySQL and SQL Server, comparison is usually case-insensitive already (it depends on the collation), and SQLite's LIKE is case-insensitive for ASCII; the mode option is for PostgreSQL and MongoDB. For serious text search (ranking, typos, stemming) use the database's full-text features or a search engine rather than contains.

Null checks

TypeScript
await prisma.user.findMany({ where: { name: null } });          // name IS NULL
await prisma.user.findMany({ where: { name: { not: null } } }); // name IS NOT NULL

A common mistake is where: { name: undefined }. In Prisma, undefined means "do not filter", while null means "is null". This is useful for optional filters but a source of bugs. It is also a security matter: if userId is undefined because a variable was missing, where: { userId } matches every row.

AND, OR, NOT

Use the logical keys to combine conditions:

TypeScript
await prisma.product.findMany({
  where: {
    OR: [
      { category: "kitchen" },
      { priceCents: { lt: 500 } },
    ],
    NOT: { stock: 0 },
    AND: [
      { name: { contains: "blue" } },
      { sku: { startsWith: "A" } },
    ],
  },
});
  • AND takes an object or an array; all must match. (Fields at the top level are an implicit AND.)
  • OR takes an array; at least one must match. An empty OR: [] matches nothing.
  • NOT takes an object or an array; none may match.
  • They nest to any depth.

Build filters dynamically

Real search forms have optional fields. Build the where object from what the user filled in:

TypeScript
function productFilter(q: { text?: string; category?: string; maxPrice?: number }) {
  return {
    ...(q.text && { name: { contains: q.text, mode: "insensitive" as const } }),
    ...(q.category && { category: q.category }),
    ...(q.maxPrice !== undefined && { priceCents: { lte: q.maxPrice } }),
  };
}

await prisma.product.findMany({ where: productFilter({ category: "office" }) });

Typing it as Prisma.ProductWhereInput keeps the helper checked by TypeScript.

Try it: a filter engine

This stand-in evaluates the same where syntax against an array. It supports the operators in this lesson (not mode, which it treats as case-insensitive when present). Change the filters at the bottom and watch the results.

Node.js Stand-in: a where evaluator

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Filtering on relations (preview)

You can filter by fields of related models, and by whether related rows exist:

TypeScript
await prisma.user.findMany({
  where: { posts: { some: { published: true } } },   // users with at least one published post
});

The operators some, every and none work on one-to-many relations. Chapter 4 explains them with a full example.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does where: { category: 'kitchen', stock: 0 } mean?

    Several fields in one where object are combined with AND.
  2. 2. What does a filter value of undefined do, as in where: { name: undefined }?

    undefined means 'no filter' in Prisma. Use null to match null values. Missing variables can silently widen a query.
  3. 3. Which option makes contains case-insensitive on PostgreSQL?

    String filters accept mode: 'insensitive' (PostgreSQL and MongoDB).
  4. 4. Which filter returns products priced from 1000 up to but not including 5000?

    gte is 'greater than or equal', lt is 'less than', so the range is [1000, 5000).
  5. 5. How do you find rows that match at least one of several conditions?

    OR takes an array of conditions and matches if any one matches.

Summary

  • where is an object; fields at one level are ANDed; AND, OR and NOT combine and nest conditions.
  • Operators: equals, not, in, notIn, lt, lte, gt, gte, contains, startsWith, endsWith.
  • mode: "insensitive" for case-insensitive text matching on PostgreSQL; null checks IS NULL; undefined removes the filter.
  • Build dynamic filters from optional inputs, and beware that missing values widen a query.

Next: Selecting Fields, Sorting and Pagination