Overview

Prisma Client allows you to filter records on any combination of model fields, including related models, and supports a variety of filter conditions.

For example, the following query returns all User records with at least one published post and an email address that ends with prisma.io:

1const result = await prisma.user.findMany({
2 where: {
3 email: {
4 endsWith: 'prisma.io',
5 },
6 posts: {
7 some: {
8 published: true,
9 },
10 },
11 },
12})

The following queries support filtering:

  • findMany
  • updateMany
  • deleteMany
  • count

The following filter types are generated from the default model:

1export type StringFilter = {
2 equals?: string | null
3 not?: string | StringFilter | null
4 in?: Enumerable<string> | null
5 notIn?: Enumerable<string> | null
6 lt?: string | null
7 lte?: string | null
8 gt?: string | null
9 gte?: string | null
10 contains?: string | null
11 startsWith?: string | null
12 endsWith?: string | null
13}
14
15export type IntFilter = {
16 equals?: number | null
17 not?: number | IntFilter | null
18 in?: Enumerable<number> | null
19 notIn?: Enumerable<number> | null
20 lt?: number | null
21 lte?: number | null
22 gt?: number | null
23 gte?: number | null
24}
25
26export type NullableStringFilter = {
27 equals?: string | null
28 not?: string | null | NullableStringFilter
29 in?: Enumerable<string> | null
30 notIn?: Enumerable<string> | null
31 lt?: string | null
32 lte?: string | null
33 gt?: string | null
34 gte?: string | null
35 contains?: string | null
36 startsWith?: string | null
37 endsWith?: string | null
38}
39
40export type PostFilter = {
41 every?: PostWhereInput | null
42 some?: PostWhereInput | null
43 none?: PostWhereInput | null
44}
45
46export type NullableIntFilter = {
47 equals?: number | null
48 not?: number | null | NullableIntFilter
49 in?: Enumerable<number> | null
50 notIn?: Enumerable<number> | null
51 lt?: number | null
52 lte?: number | null
53 gt?: number | null
54 gte?: number | null
55}
56
57export type BooleanFilter = {
58 equals?: boolean | null
59 not?: boolean | BooleanFilter | null
60}

Filter conditions

Prisma Client supports the following filter conditions:

OperatorSupported typesNotes
equalsstring
number
boolean
Date
[enum]
null
Value equals.
notstring
number
boolean
Date
[enum]
null
Value does not equal.
inEnumerable<string>
Enumerable<number>
Enumerable<Date>
Enumerable<[enum]>
null
Value exists in list.
notInEnumerable<string>
Enumerable<number>
Enumerable<Date>
Enumerable<[enum]>
null
Value does not exist in list.
ltstring
number
Date
null
Value is less than X.
ltestring
number
Date
null
Value is less than or equal to X.
gtstring
number
Date
null
Value is greater than X.
gtestring
number
Date
null
Value is greater than or equal to
containsstring
null
Value contains. Does not support wildcards - for example, b[a-c]d.
startsWithstring
null
Value starts with.
endsWithstring
null
Value ends with.

Examples

Retrieve all Post records where title starts with Pr (such as Prisma)

1const result = await prisma.post.findMany({
2 where: {
3 title: {
4 startsWith: 'Pr',
5 },
6 },
7})

Retrieve all User records where email ends with prisma.io

1const result = await prisma.user.findMany({
2 where: {
3 email: {
4 endsWith: 'prisma.io',
5 },
6 },
7})

Retrieve all Post records where content contains databases

1const result = await prisma.post.findMany({
2 where: {
3 content: {
4 contains: 'databases',
5 },
6 },
7})

Retrieve all Post records where date_created is greater than March 19th, 2020

1const result = await prisma.post.findMany({
2 where: {
3 date_created: {
4 gte: new Date('2020-03-19T14:21:00+0200') /* Includes time offset for UTC */,
5 },
6 },
7})

Count all Post records where content contains databases

1const result = await prisma.post.count({
2 where: {
3 content: {
4 contains: 'databases',
5 },
6 },
7})

Combine multiple filter conditions

You can combine multiple filter conditions with the following operators:

OperatorNotes
ANDAll conditions must returntrue. Alternatively, pass a list of objects into the where clause - the AND operator is not required.
NOTAll conditions must return false.
OROne or more conditions must return true.

Examples

Retrieve all Post records where the content field contains Prisma and published is false

1const result = await prisma.post.findMany({
2 where: {
3 AND: [
4 {
5 content: {
6 contains: 'Prisma',
7 },
8 },
9 {
10 published: {
11 equals: false,
12 },
13 },
14 ],
15 },
16})

The following format returns the same results as above without the AND operator:

1const result = await prisma.post.findMany({
2 where: {
3 content: {
4 contains: 'Prisma',
5 },
6 published: {
7 equals: false,
8 },
9 },
10})

Note: OR and NOT require an array.

Retrieve all Post records where the title field contains Prisma or databases

1const result = await prisma.post.findMany({
2 where: {
3 OR: [
4 {
5 title: {
6 contains: 'Prisma',
7 },
8 },
9 {
10 title: {
11 contains: 'databases',
12 },
13 },
14 ],
15 },
16})

Retrieve all Post records where the title field contains Prisma or databases, and published is false

1const result = await prisma.post.findMany({
2 where: {
3 OR: [
4 {
5 title: {
6 contains: 'Prisma',
7 },
8 },
9 {
10 title: {
11 contains: 'databases',
12 },
13 },
14 ],
15 AND: {
16 published: false,
17 },
18 },
19})

Retrieve all Post records where the title filed contains Prisma or databases, but not SQL

1const result = await prisma.post.findMany({
2 where: {
3 OR: [
4 {
5 title: {
6 contains: 'Prisma',
7 },
8 },
9 {
10 title: {
11 contains: 'databases',
12 },
13 },
14 ],
15 NOT: {
16 title: {
17 contains: 'SQL',
18 },
19 },
20 },
21})

You can enable logging to debug complex queries:

1const prisma = new PrismaClient({
2 log: [
3 {
4 emit: 'event',
5 level: 'query',
6 },
7 ],
8})
9
10prisma.on('query', e => {
11 e.query, console.log(e)
12})

You can filter records based on related record fields. For example, the following query returns all Post records where the user's email address is sarah@prisma.io:

1const result = await prisma.post.findMany({
2 where: {
3 user: {
4 email: {
5 equals: 'sarah@prisma.io',
6 },
7 },
8 },
9})

Prisma Client offers the following operators for filtering on one-to-many relationships:

OperatorDescription
someReturns all records where one or more related records match filtering criteria.
everyReturns all records where all related records match filtering criteria.
noneReturns all records where no related records match filtering criteria.

Examples

Retrieve all User records where all posts are published and a least one related Post mentions Prisma

1 const result = await prisma.user.findMany({
2 where: {
3 post: {
4 every: {
5 published: true
6 }
7 some: {
8 content: {
9 contains: "Prisma"
10 }
11 }
12 }
13 }
14 }

Retrieve all Post records where the title field contains Prisma or databases, but not SQL, and the related User record' email address does not contain sarah

1const result = await prisma.post.findMany({
2 where: {
3 OR: [
4 {
5 title: {
6 contains: 'Prisma',
7 },
8 },
9 {
10 title: {
11 contains: 'databases',
12 },
13 },
14 ],
15 NOT: {
16 title: {
17 contains: 'SQL',
18 },
19 },
20 user: {
21 NOT: {
22 email: {
23 contains: 'sarah',
24 },
25 },
26 },
27 },
28 include: {
29 user: true,
30 },
31})

Filter within include

When you use include to load related records (for example, a user's posts), you can filter which related records should be returned. For example, you might want to return all users, but only include posts that have been published. Some user records will be returned without any posts.

Examples

Retrieve all User records, and include all Post records that have not been published yet

1const result = await prisma.user.findMany(
2 include: {
3 Post: {
4 where: {
5 published: false,
6 },
7 },
8 },
9})

Note: Some users records will be returned without any posts.

Retrieve User records with at least one Post that isn't published, and include all Post records that have not been published yet

1const result = await prisma.user.findMany({
2 where: {
3 Post: {
4 some: {
5 published: false,
6 },
7 },
8 },
9 include: {
10 Post: {
11 where: {
12 published: false,
13 },
14 },
15 },
16})

Retrieve all User records where the email contains Prisma, and include all Post records that have not been published yet

1const result = await prisma.user.findMany({
2 where: {
3 email: {
4 contains: 'prisma.io',
5 },
6 },
7 include: {
8 Post: {
9 where: {
10 published: false,
11 },
12 },
13 },
14})

Filter within select

When you use select to return a subset of data from related records (for example, a user's posts), you can filter which related records should be returned. For example, you might want to query all users with a prisma.io email address and select the titles of their published posts.

Examples

Retrieve all User records where email contains prisma.io, and select the title of all Post records that have not been published yet

1const result = await prisma.user.findMany({
2 where: {
3 email: {
4 contains: 'prisma.io',
5 },
6 },
7 select: {
8 Post: {
9 where: {
10 published: false,
11 },
12 select: {
13 title: true,
14 },
15 },
16 },
17})

You can use the post model property to write the same query:

1const result = await prisma.post.findMany({
2 where: {
3 published: false,
4 User: {
5 email: {
6 contains: 'prisma.io',
7 },
8 },
9 },
10 select: {
11 title: true,
12 },
13})
Edit this page on Github