Prisma Client offers out-of-the-box support for several CRUD queries. CRUD stands for:

  • Create
  • Read
  • Update
  • Delete

The following CRUD queries are available in Prisma Client:

This page contains a detailed description for each query. Unless otherwise noted, the examples on this page are based on the following Prisma schema:

1model User {
2 id Int @id @default(autoincrement())
3 name String?
4 email String @unique
5 profileViews Int @default(0)
6 role Role @default(USER)
7 coinflips Boolean[]
8 posts Post[]
9}
10
11model Post {
12 id Int @id @default(autoincrement())
13 title String
14 published Boolean @default(true)
15 author User @relation(fields: [authorId], references: [id])
16 authorId Int
17 comments Json
18}
19
20enum Role {
21 USER
22 ADMIN
23}

CRUD queries are exposed by the model properties on your PrismaClient instance. Taking the User and Post models from above as examples, you'd invoke the CRUD queries via the prisma.user and prisma.post model properties. For example:

1await prisma.user.create({ data: { name: 'Alice' } })
2// or
3await prisma.post.findMany()

findOne

The findOne query lets you retrieve a single database record by ID or another unique attribute. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

findOne takes an object with one of the following types as input:

1export type FindOneUserArgs = {
2 where: UserWhereUniqueInput
3 select?: UserSelect | null
4 include?: UserInclude | null
5}

These are further relevant generated types:

1export type UserWhereUniqueInput = {
2 id?: number | null
3 email?: string | null
4}

In case your model defines a multi-field ID or unique attribute such as the following:

1model User {
2 firstName String
3 lastName String
4
5 @@id([firstName, lastName])
6}

The UserWhereUniqueInput input type looks slightly different:

1export type UserWhereUniqueInput = {
2 firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null
3}
4
5export type FirstNameLastNameCompoundUniqueInput = {
6 firstName: string
7 lastName: string
8}

Reference

NameTypeRequiredDescription
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

findOne returns a plain old JavaScript object or null.

The type of the object that a findOne API call returns depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Single-field ID or unique attribute

Retrieve the User record with an id of 42:

1const result = await prisma.user.findOne({
2 where: {
3 id: 42,
4 },
5})

Retrieve the User record with an email of alice@prisma.io:

1const result = await prisma.user.findOne({
2 where: {
3 email: 'alice@prisma.io',
4 },
5})

Multi-field ID or unique attribute

Assume your model has a multi-field ID or unique attribute, e.g.:

1model User {
2 firstName String
3 lastName String
4
5 @@id([firstName, lastName])
6}

Retrieve the User record with firstName of Alice and lastName of Smith:

1const result = await prisma.user.findOne({
2 where: {
3 firstName_lastName: {
4 firstName: 'Alice',
5 lastName: 'Smith',
6 },
7 },
8})

findMany

The findMany query returns a list of records. You can use the select and include options to determine which properties the returned objects should include. You can also paginate, filter, and order the list.

Options

Type

findMany takes as input an object of the following type:

1export type FindManyUserArgs = {
2 select?: UserSelect | null
3 include?: UserInclude | null
4 where?: UserWhereInput | null
5 orderBy?: UserOrderByInput | null
6 cursor?: UserWhereUniqueInput | null
7 take?: number | null
8 skip?: number | null
9}

These are further relevant generated types:

1export type UserWhereInput = {
2 id?: number | IntFilter | null
3 name?: string | NullableStringFilter | null
4 email?: string | StringFilter | null
5 role?: Role | RoleFilter | null
6 profileViews?: number | IntFilter | null
7 posts?: PostFilter | null
8 AND?: Enumerable<UserWhereInput> | null
9 OR?: Enumerable<UserWhereInput> | null
10 NOT?: Enumerable<UserWhereInput> | null
11}
12
13export type PostFilter = {
14 every?: PostWhereInput | null
15 some?: PostWhereInput | null
16 none?: PostWhereInput | null
17}
18
19export type UserWhereUniqueInput = {
20 id?: number | null
21 email?: string | null
22}
23
24export type UserOrderByInput = {
25 id?: OrderByArg | null
26 name?: OrderByArg | null
27 email?: OrderByArg | null
28 role?: OrderByArg | null
29 profileViews?: OrderByArg | null
30}
31
32export declare const OrderByArg: {
33 asc: 'asc'
34 desc: 'desc'
35}

Reference

NameTypeRequiredDescription
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
orderByUserOrderByInputNoLets you order the returned list by any property.
skipstringNoSpecifies how many of the returned objects in the list should be skipped.
cursorUserWhereUniqueInputNoSpecifies the position for the list (the value typically specifies an id or another unique value).
takenumberNoSpecifies how many objects should be returned in the list (as seen from the beginning (+ve value) or end (-ve value) either of the list or from the cursor position if mentioned)
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

findMany returns an array of plain old JavaScript objects.

The type of the objects returned by the findMany API call depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Retrieve all User records where the name is Alice:

1const user = await prisma.user.findMany({
2 where: { name: 'Alice' },
3})

See filtering documentation for advanced examples.

create

The create query creates a new database record. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

create takes as input an object of the following type:

1export type UserCreateArgs = {
2 select?: UserSelect | null
3 include?: UserInclude | null
4 data: UserCreateInput
5}

These are further relevant generated types:

1export type UserCreateInput = {
2 name?: string | null
3 email: string
4 role?: Role | null
5 profileViews: number
6 coinflips?: UserCreatecoinflipsInput | null
7 posts?: PostCreateManyWithoutAuthorInput | null
8}
9
10export type UserCreatecoinflipsInput = {
11 set?: Enumerable<boolean> | null
12}
13
14export type PostCreateManyWithoutAuthorInput = {
15 create?: Enumerable<PostCreateWithoutAuthorInput> | null
16 connect?: Enumerable<PostWhereUniqueInput> | null
17}
18
19export type PostCreateWithoutAuthorInput = {
20 title: string
21 published?: boolean | null
22 comments?: object | null
23}
24
25export type PostWhereUniqueInput = {
26 id?: number | null
27}

Reference

NameTypeRequiredDescription
dataUserCreateInputYesWraps all the model fields in a type so that they can be provided when creating new records. It also includes relation fields which lets you perform (transactional) nested inserts. Fields that are marked as optional or have default values in the datamodel are optional.
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

create returns a plain old JavaScript object.

The type of the object that's returned by a create API call depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Create a new record with the only required field email

1const user = await prisma.user.create({
2 data: { email: 'alice@prisma.io' },
3})

Nested writes

You can also perform nested writes, such as:

  • Create a User and one or more connected Post records at the same time (see example)
  • Create a Post and connect it to an existing user, or create a new User record (see example)

update

The update query updates an existing database record. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

update takes as input an object of the following type:

1export type UserUpdateArgs = {
2 select?: UserSelect | null
3 include?: UserInclude | null
4 data: UserUpdateInput
5 where: UserWhereUniqueInput
6}

These are further relevant generated types:

1export type UserWhereUniqueInput = {
2 id?: number | null
3 email?: string | null
4}
5
6export type UserUpdateInput = {
7 id?: number | null
8 name?: string | null
9 email?: string | null
10 role?: Role | null
11 profileViews?: number | null
12 coinflips?: UserUpdatecoinflipsInput | null
13 posts?: PostUpdateManyWithoutAuthorInput | null
14}
15
16export type UserUpdatecoinflipsInput = {
17 set?: Enumerable<boolean> | null
18}
19
20export type PostUpdateManyWithoutAuthorInput = {
21 create?: Enumerable<PostCreateWithoutAuthorInput> | null
22 connect?: Enumerable<PostWhereUniqueInput> | null
23 set?: Enumerable<PostWhereUniqueInput> | null
24 disconnect?: Enumerable<PostWhereUniqueInput> | null
25 delete?: Enumerable<PostWhereUniqueInput> | null
26 update?: Enumerable<PostUpdateWithWhereUniqueWithoutAuthorInput> | null
27 updateMany?: Enumerable<PostUpdateManyWithWhereNestedInput> | null
28 deleteMany?: Enumerable<PostScalarWhereInput> | null
29 upsert?: Enumerable<PostUpsertWithWhereUniqueWithoutAuthorInput> | null
30}

Reference

NameTypeRequiredDescription
dataUserUpdateInputYesWraps all the fields of the model so that they can be provided when updating an existing record. Fields that are marked as optional or have default values in the datamodel are optional.
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

update returns a plain old JavaScript object or null.

The type of the object that an update API call returns depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Update the email of the User record with id of 1 to alice@prisma.io:

1const user = await prisma.user.update({
2 where: { id: 1 },
3 data: { email: 'alice@prisma.io' },
4})

Nested writes

You can also perform nested writes during an update, such as:

  • Update a User and update one or more connected Post records at the same time (see example)
  • Update a User and connect it to one or more existing Post records, or add new Post records (see example)

upsert

The upsert query updates an existing or creates a new database record. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

upsert takes as input an object of the following type:

1export type UserUpsertArgs = {
2 select?: UserSelect | null
3 include?: UserInclude | null
4 where: UserWhereUniqueInput
5 create: UserCreateInput
6 update: UserUpdateInput
7}

Refer to findOne, create and update to see what the generated types UserWhereUniqueInput, UserCreateInput and UserUpdateInput types look like.

Reference

NameTypeRequiredDescription
createUserCreateInputYesWraps all the fields of the model so that they can be provided when creating new records. It also includes relation fields which lets you perform (transactional) nested inserts. Fields that are marked as optional or have default values in the datamodel are optional.
updateUserUpdateInputYesWraps all the fields of the model so that they can be provided when updating an existing record. Fields that are marked as optional or have default values in the datamodel are optional.
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

upsert returns a plain old JavaScript object.

The type of the object that an upsert API call returns depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Update (if exists) or create a new User record with an email of alice@prisma.io:

1const user = await prisma.user.upsert({
2 where: { id: 1 },
3 update: { email: 'alice@prisma.io' },
4 create: { email: 'alice@prisma.io' },
5})

delete

The delete query deletes an existing database record. Even though the record is being deleted, delete still returns the object that was deleted. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

delete takes as input an object of the following type:

1export type FindOneUserArgs = {
2 where: UserWhereUniqueInput
3 select?: UserSelect | null
4 include?: UserInclude | null
5}

These are further relevant generated types:

1export type UserWhereUniqueInput = {
2 id?: number | null
3 email?: string | null
4}

In case your model has a multi-field ID or unique attribute such as the following:

1model User {
2 firstName String
3 lastName String
4
5 @@id([firstName, lastName])
6}

The UserWhereUniqueInput input looks slightly different:

1export type UserWhereUniqueInput = {
2 firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null
3}
4
5export type FirstNameLastNameCompoundUniqueInput = {
6 firstName: string
7 lastName: string
8}

Reference

NameTypeRequiredDescription
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectUserSelectNoSpecifies which properties to include on the returned object.
includeUserIncludeNoSpecifies which relations should be eagerly loaded on the returned object.

Return type

delete returns a plain old JavaScript object.

The type of the object that's returned by a delete API call depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

1export type User = {
2 id: number
3 name: string | null
4 email: string
5 role: Role
6 coinflips: boolean[]
7 profileViews: number
8}

Examples

Delete the User record with an id of 1:

1const user = await prisma.user.delete({
2 where: { id: 1 },
3})

updateMany

The updateMany query updates a batch of existing database records in bulk and returns the number of deleted records. You can filter the list of records to be updated.

Options

Type

updateMany takes as input an object of the following type:

1export type UserUpdateManyArgs = {
2 data: UserUpdateManyMutationInput
3 where?: UserWhereInput | null
4}

These are further relevant generated types:

1export type UserUpdateManyMutationInput = {
2 id?: number | null
3 name?: string | null
4 email?: string | null
5 role?: Role | null
6 profileViews?: number | null
7 coinflips?: UserUpdatecoinflipsInput | null
8}
9
10export type UserUpdatecoinflipsInput = {
11 set?: Enumerable<boolean> | null
12}
13
14export type UserWhereInput = {
15 id?: number | IntFilter | null
16 name?: string | NullableStringFilter | null
17 email?: string | StringFilter | null
18 role?: Role | RoleFilter | null
19 profileViews?: number | IntFilter | null
20 posts?: PostFilter | null
21 AND?: Enumerable<UserWhereInput> | null
22 OR?: Enumerable<UserWhereInput> | null
23 NOT?: Enumerable<UserWhereInput> | null
24}

Reference

NameTypeRequiredDescription
dataUserUpdateManyMutationInputYesWraps all the fields of the model so that they can be provided when updating an existing record. Fields that are marked as optional or have default values in the datamodel are optional on data.
whereUserWhereInputNoWraps all fields of a model so that the list can be filtered by any property. If you do not filter the list, all records will be updated.

Return type

updateMany returns an object of type BatchPayload, which is defined as follows:

1export type BatchPayload = {
2 count: number
3}

The value of count is an integer and represents the number of records that were updated.

Examples

Update all User records where the name is Alice to ALICE:

1const updatedUserCount = await prisma.user.updateMany({
2 where: { name: 'Alice' },
3 data: { name: 'ALICE' },
4})

deleteMany

The deleteMany query deletes a batch of existing database records in bulk and returns the number of deleted records. You can filter the list of records to be deleted.

Options

Type

deleteMany takes as input an object of the following type:

1export type UserWhereInput = {
2 id?: number | IntFilter | null
3 name?: string | NullableStringFilter | null
4 email?: string | StringFilter | null
5 role?: Role | RoleFilter | null
6 profileViews?: number | IntFilter | null
7 posts?: PostFilter | null
8 AND?: Enumerable<UserWhereInput> | null
9 OR?: Enumerable<UserWhereInput> | null
10 NOT?: Enumerable<UserWhereInput> | null
11}

Reference

NameTypeRequiredDescription
whereUserWhereInputNoWraps all fields of a model so that the list can be filtered by any field.

Return type

deleteMany returns an object of type BatchPayload, which is defined as follows:

1export type BatchPayload = {
2 count: number
3}

The value of count is an integer and represents the number of records that were deleted.

Examples

Delete all User records where the name is Alice:

1const deletedUserCount = await prisma.user.deleteMany({
2 where: { name: 'Alice' },
3})

count

Use the count() method on any model property on your PrismaClient instance to return a the number of available records. You can filter the list of records to be counted.

Options

Type

count an object of the following type as input:

1export type FindManyUserArgs = {
2 where?: UserWhereInput | null
3 orderBy?: UserOrderByInput | null
4 skip?: number | null
5 after?: UserWhereUniqueInput | null
6 before?: UserWhereUniqueInput | null
7 first?: number | null
8 last?: number | null
9}

These are further relevant generated types:

1export type UserWhereInput = {
2 id?: number | IntFilter | null
3 name?: string | NullableStringFilter | null
4 email?: string | StringFilter | null
5 role?: Role | RoleFilter | null
6 profileViews?: number | IntFilter | null
7 posts?: PostFilter | null
8 AND?: Enumerable<UserWhereInput> | null
9 OR?: Enumerable<UserWhereInput> | null
10 NOT?: Enumerable<UserWhereInput> | null
11}
12
13export type PostFilter = {
14 every?: PostWhereInput | null
15 some?: PostWhereInput | null
16 none?: PostWhereInput | null
17}
18
19export type UserWhereUniqueInput = {
20 id?: number | null
21 email?: string | null
22}
23
24export type UserOrderByInput = {
25 id?: OrderByArg | null
26 name?: OrderByArg | null
27 email?: OrderByArg | null
28 role?: OrderByArg | null
29 profileViews?: OrderByArg | null
30}
31
32export declare const OrderByArg: {
33 asc: 'asc'
34 desc: 'desc'
35}

Reference

NameTypeRequiredDescription
whereUserWhereInputNoWraps all fields of a model so that the list can be filtered by any model property.
orderByUserOrderByInputNoLets you order the returned list by any model property.
skipstringNoSpecifies how many of the returned objects in the list should be skipped.
afterUserWhereUniqueInputNoSpecifies the starting object for the list (the value typically specifies an id or another unique value).
beforeUserWhereUniqueInputNoSpecifies the last object for the list (the value typically specifies an id or another unique value).
firstnumberNoSpecifies how many elements should be returned in the list (as seen from the beginning of the list).
lastnumberNoSpecifies how many elements should be returned in the list (as seen from the end of the list).

Return type

count returns an number:

1export type BatchPayload = {
2 count: number
3}

Examples

Count all User records:

1const result = await prisma.user.count();

Count all User records with at least one published Post:

1const result = await prisma.user.count({
2 where: {
3 post: {
4 some: {
5 published: true,
6 },
7 },
8 },
9})

Working with advanced scalar types

Working with Json fields

See Working with Json.

Working with scalar lists / scalar arrays

The following example demonstrates how to set the value of a scalar list (coinflips) when you create a model:

1const createdUser = await prisma.user.create({
2 data: {
3 email: "eloise@prisma.io",
4 coinflips: {
5 set: [true, true, true, false, true],
6 },
7 },
8});

Scalar lists only support set - you cannot add or remove elements, only overwrite the entire value. The following example retrieves user, uses push() to add three new coin flips, and overwrites the coinflips property in an update:

1const user = await prisma.user.findOne({
2 where: {
3 email: "eloise@prisma.io",
4 },
5});
6
7if (user) {
8 console.log(user.coinflips);
9
10 user.coinflips.push(true, true, false);
11
12 const updatedUser = await prisma.user.update({
13 where: {
14 email: "eloise@prisma.io",
15 },
16 data: {
17 coinflips: {
18 set: user.coinflips,
19 },
20 },
21 });
22
23 console.log(updatedUser.coinflips);
24}
Edit this page on Github