Prisma Query Operators Reference

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.

Operator groups at a glance

Equality and inequality — work on any field type:

where: { status: "active" }            // shorthand for { equals: "active" }
where: { status: { not: "archived" } } // excludes a specific value

Comparison — require an orderable scalar (Int, Float, Decimal, DateTime, String):

where: {
  createdAt: { gte: new Date("2024-01-01"), lt: new Date("2025-01-01") },
  score: { gt: 0, lte: 100 },
}

String operators — only on String fields; mode: 'insensitive' requires PostgreSQL or MongoDB:

where: {
  email: { endsWith: "@example.com" },
  name: { contains: "smith", mode: "insensitive" },
  slug: { startsWith: "2024-" },
}

List membership — check whether a scalar value belongs to a set:

where: { role: { in: ["ADMIN", "MODERATOR"] } }       // any of these
where: { status: { notIn: ["DELETED", "BANNED"] } }   // none of these

Logical combinators — compose conditions:

where: {
  AND: [
    { score: { gte: 50 } },
    { score: { lte: 100 } },
  ],
  OR: [
    { emailVerified: true },
    { createdAt: { gt: new Date("2025-01-01") } },
  ],
  NOT: { status: "archived" },
}

Note: multiple top-level keys are implicitly AND-ed, so AND is only needed when you are combining it with OR in the same where clause.

Relation filters — query across associations without a raw join:

// Users who have at least one published post
where: { posts: { some: { published: true } } }

// Users whose every post is published
where: { posts: { every: { published: true } } }

// Users with no unpublished posts
where: { posts: { none: { published: false } } }

// One-to-one: users whose profile has a bio
where: { profile: { is: { bio: { not: null } } } }

Common mistakes

• Forgetting mode: 'insensitive' — without it, contains is case-sensitive on PostgreSQL, which is the most common cause of “my filter doesn’t match obvious results” bugs.
• Mixing up in (scalar set membership) with contains (substring search) — they solve different problems and are not interchangeable.
• Nesting AND unnecessarily at the top level when multiple top-level keys already AND themselves.
• Using some on a to-one relation — some/every/none are for one-to-many list relations; use is/isNot for singular relations.