Overview

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:

model User {
id Int @id @default(autoincrement())
name String?
email String @unique
profileViews Int @default(0)
role Role @default(USER)
coinflips Boolean[]
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId Int
comments Json
}
enum Role {
USER
ADMIN
}

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:

await prisma.user.create({ data: { name: 'Alice' } })
// or
await 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:

export type FindOneUserArgs = {
where: UserWhereUniqueInput
select?: UserSelect | null
include?: UserInclude | null
}

These are further relevant generated types:

export type UserWhereUniqueInput = {
id?: number | null
email?: string | null
}

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

model User {
firstName String
lastName String
@@id([firstName, lastName])
}

The UserWhereUniqueInput input type looks slightly different:

export type UserWhereUniqueInput = {
firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null
}
export type FirstNameLastNameCompoundUniqueInput = {
firstName: string
lastName: string
}

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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

Single-field ID or unique attribute

Retrieve the User record with an id of 42:

const result = await prisma.user.findOne({
where: {
id: 42,
},
})

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

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

Multi-field ID or unique attribute

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

model User {
firstName String
lastName String
@@id([firstName, lastName])
}

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

const result = await prisma.user.findOne({
where: {
firstName_lastName: {
firstName: 'Alice',
lastName: 'Smith',
},
},
})

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:

export type FindManyUserArgs = {
select?: UserSelect | null
include?: UserInclude | null
where?: UserWhereInput | null
orderBy?: UserOrderByInput | null
skip?: number | null
after?: UserWhereUniqueInput | null
before?: UserWhereUniqueInput | null
first?: number | null
last?: number | null
}

These are further relevant generated types:

export type UserWhereInput = {
id?: number | IntFilter | null
name?: string | NullableStringFilter | null
email?: string | StringFilter | null
role?: Role | RoleFilter | null
profileViews?: number | IntFilter | null
posts?: PostFilter | null
AND?: Enumerable<UserWhereInput> | null
OR?: Enumerable<UserWhereInput> | null
NOT?: Enumerable<UserWhereInput> | null
}
export type PostFilter = {
every?: PostWhereInput | null
some?: PostWhereInput | null
none?: PostWhereInput | null
}
export type UserWhereUniqueInput = {
id?: number | null
email?: string | null
}
export type UserOrderByInput = {
id?: OrderByArg | null
name?: OrderByArg | null
email?: OrderByArg | null
role?: OrderByArg | null
profileViews?: OrderByArg | null
}
export declare const OrderByArg: {
asc: 'asc'
desc: 'desc'
}

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.
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 objects should be returned in the list (as seen from the beginning of the list).
lastnumberNoSpecifies how many objects should be returned in the list (as seen from the end of the list).
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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

Retrieve all User records where the name is Alice:

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

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:

export type UserCreateArgs = {
select?: UserSelect | null
include?: UserInclude | null
data: UserCreateInput
}

These are further relevant generated types:

export type UserCreateInput = {
name?: string | null
email: string
role?: Role | null
profileViews: number
coinflips?: UserCreatecoinflipsInput | null
posts?: PostCreateManyWithoutAuthorInput | null
}
export type UserCreatecoinflipsInput = {
set?: Enumerable<boolean> | null
}
export type PostCreateManyWithoutAuthorInput = {
create?: Enumerable<PostCreateWithoutAuthorInput> | null
connect?: Enumerable<PostWhereUniqueInput> | null
}
export type PostCreateWithoutAuthorInput = {
title: string
published?: boolean | null
comments?: object | null
}
export type PostWhereUniqueInput = {
id?: number | null
}

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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

Create a new record with the only required field email:

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

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:

export type UserUpdateArgs = {
select?: UserSelect | null
include?: UserInclude | null
data: UserUpdateInput
where: UserWhereUniqueInput
}

These are further relevant generated types:

export type UserWhereUniqueInput = {
id?: number | null
email?: string | null
}
export type UserUpdateInput = {
id?: number | null
name?: string | null
email?: string | null
role?: Role | null
profileViews?: number | null
coinflips?: UserUpdatecoinflipsInput | null
posts?: PostUpdateManyWithoutAuthorInput | null
}
export type UserUpdatecoinflipsInput = {
set?: Enumerable<boolean> | null
}
export type PostUpdateManyWithoutAuthorInput = {
create?: Enumerable<PostCreateWithoutAuthorInput> | null
connect?: Enumerable<PostWhereUniqueInput> | null
set?: Enumerable<PostWhereUniqueInput> | null
disconnect?: Enumerable<PostWhereUniqueInput> | null
delete?: Enumerable<PostWhereUniqueInput> | null
update?: Enumerable<PostUpdateWithWhereUniqueWithoutAuthorInput> | null
updateMany?: Enumerable<PostUpdateManyWithWhereNestedInput> | null
deleteMany?: Enumerable<PostScalarWhereInput> | null
upsert?: Enumerable<PostUpsertWithWhereUniqueWithoutAuthorInput> | null
}

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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

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

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

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:

export type UserUpsertArgs = {
select?: UserSelect | null
include?: UserInclude | null
where: UserWhereUniqueInput
create: UserCreateInput
update: UserUpdateInput
}

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 properts 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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

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

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

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:

export type FindOneUserArgs = {
where: UserWhereUniqueInput
select?: UserSelect | null
include?: UserInclude | null
}

These are further relevant generated types:

export type UserWhereUniqueInput = {
id?: number | null
email?: string | null
}

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

model User {
firstName String
lastName String
@@id([firstName, lastName])
}

The UserWhereUniqueInput input looks slightly different:

export type UserWhereUniqueInput = {
firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null
}
export type FirstNameLastNameCompoundUniqueInput = {
firstName: string
lastName: string
}

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:

export type User = {
id: number
name: string | null
email: string
role: Role
coinflips: boolean[]
profileViews: number
}

Examples

Delete the User record with an id of 1:

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

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:

export type UserUpdateManyArgs = {
data: UserUpdateManyMutationInput
where?: UserWhereInput | null
}

These are further relevant generated types:

export type UserUpdateManyMutationInput = {
id?: number | null
name?: string | null
email?: string | null
role?: Role | null
profileViews?: number | null
coinflips?: UserUpdatecoinflipsInput | null
}
export type UserUpdatecoinflipsInput = {
set?: Enumerable<boolean> | null
}
export type UserWhereInput = {
id?: number | IntFilter | null
name?: string | NullableStringFilter | null
email?: string | StringFilter | null
role?: Role | RoleFilter | null
profileViews?: number | IntFilter | null
posts?: PostFilter | null
AND?: Enumerable<UserWhereInput> | null
OR?: Enumerable<UserWhereInput> | null
NOT?: Enumerable<UserWhereInput> | null
}

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:

export type BatchPayload = {
count: number
}

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:

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

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:

export type UserWhereInput = {
id?: number | IntFilter | null
name?: string | NullableStringFilter | null
email?: string | StringFilter | null
role?: Role | RoleFilter | null
profileViews?: number | IntFilter | null
posts?: PostFilter | null
AND?: Enumerable<UserWhereInput> | null
OR?: Enumerable<UserWhereInput> | null
NOT?: Enumerable<UserWhereInput> | null
}

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:

export type BatchPayload = {
count: number
}

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:

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

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:

export type FindManyUserArgs = {
where?: UserWhereInput | null
orderBy?: UserOrderByInput | null
skip?: number | null
after?: UserWhereUniqueInput | null
before?: UserWhereUniqueInput | null
first?: number | null
last?: number | null
}

These are further relevant generated types:

export type UserWhereInput = {
id?: number | IntFilter | null
name?: string | NullableStringFilter | null
email?: string | StringFilter | null
role?: Role | RoleFilter | null
profileViews?: number | IntFilter | null
posts?: PostFilter | null
AND?: Enumerable<UserWhereInput> | null
OR?: Enumerable<UserWhereInput> | null
NOT?: Enumerable<UserWhereInput> | null
}
export type PostFilter = {
every?: PostWhereInput | null
some?: PostWhereInput | null
none?: PostWhereInput | null
}
export type UserWhereUniqueInput = {
id?: number | null
email?: string | null
}
export type UserOrderByInput = {
id?: OrderByArg | null
name?: OrderByArg | null
email?: OrderByArg | null
role?: OrderByArg | null
profileViews?: OrderByArg | null
}
export declare const OrderByArg: {
asc: 'asc'
desc: 'desc'
}

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:

export type BatchPayload = {
count: number
}

Examples

Count all User records:

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

Count all User records with at least one published Post:

const result = await prisma.user.count({
where: {
post: {
some: {
published: true,
},
},
},
})
Edit this page on Github