Prisma Client API reference

The Prisma Client API reference documentation is based on the following 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[]
city String
country String
profile ExtendedProfile?
}
model ExtendedProfile {
id Int @id @default(autoincrement())
userId Int? @unique
bio String?
User User? @relation(fields: [userId], references: [id])
}
model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId Int
comments Json
views Int @default(0)
likes Int @default(0)
}
enum Role {
USER
ADMIN
}

All example generated types (such as UserSelect and UserWhereUniqueInput) are based on the User model.

PrismaClient

This section describes the PrismaClient constructor and its parameters.

Remarks

  • Parameters are validated at runtime.

datasources

Programmatically overrides properties of the datasource block in the schema.prisma file - for example, as part of an integration test. See also: Data sources

Properties

Example propertyExample valueDescription
db{ url: 'file:./dev_qa.db' }The database connection string.

Remarks

  • You must re-generate the Prisma Client each time you add or rename a data source. Datasource names are included in the generated client.
  • If you named your datasource block something else in the schema, replace db with the name of your datasource block.

Examples

Programmatically override a datasource url
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({
datasources: {
db: {
url: 'file:./dev_qa.db',
},
},
})

Based on the following datasource block:

datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}

log

Determines the type and level of logging. See also: Logging

Options

OptionExampleDescription
Array of log levels[ "info", "query" ]
Array of log definitions[ { level: "info", emit: "event" }, { level: "warn", emit: "stdout" }]
Log levels
NameExample
queryExample:
prisma:query SELECT "public"."User"."id", "public"."User"."email" FROM "public"."User" WHERE ("public"."User"."id") IN (SELECT "t0"."id" FROM "public"."User" AS "t0" INNER JOIN "public"."Post" AS "j0" ON ("j0"."authorId") = ("t0"."id") WHERE ("j0"."views" > $1 AND "t0"."id" IS NOT NULL)) OFFSET $2
infoExample:
prisma:info Started http server on http://127.0.0.1:58471
warnWarnings.
errorErrors.
Emit formats
NameDescription
stdoutSee: stdout
eventRaises an event that you can subscribe to.

Reference

index.d.ts
1log?: Array<LogLevel | LogDefinition>
index.d.ts
1export type LogLevel = 'info' | 'query' | 'warn' | 'error'
2export type LogDefinition = {
3 level: LogLevel
4 emit: 'stdout' | 'event'
5}
Event types

The query event type:

index.d.ts
1export type QueryEvent = {
2 timestamp: Date
3 query: string // Query sent to the database
4 params: string // Query parameters
5 duration: number // Time elapsed (in milliseconds) between client issuing query and database responding - not only time taken to run query
6 target: string
7}

All other log level event types:

index.d.ts
1export type LogEvent = {
2 timestamp: Date
3 message: string
4 target: string
5}

Examples

Log query and info to stdout
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({ log: ['query', 'info'] })
async function main() {
const countUsers = await prisma.user.count({})
}
main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Show CLI results
Log a query event to console
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({
log: [{ level: 'query', emit: 'event' }],
})
prisma.$on('query', e => {
console.log(e)
})
async function main() {
const countUsers = await prisma.user.count({})
}
main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Show CLI results
Log info, warn, and error events to console
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({
log: [
{ level: 'warn', emit: 'event' },
{ level: 'info', emit: 'event' },
{ level: 'error', emit: 'event' },
],
hooks: {
'beforeRequst'
}
})
prisma.$on('warn', e => {
console.log(e)
})
prisma.$on('info', e => {
console.log(e)
})
prisma.$on('error', e => {
console.log(e)
})
async function main() {
const countUsers = await prisma.user.count({})
}
main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Show CLI results

errorFormat

Determines the level and formatting of errors returned by Prisma.

Error formats

NameDescription
undefinedIf it's not defined, the default is colorless.
prettyEnables pretty error formatting.
colorless (default)Enables colorless error formatting.
minimalEnables minimal error formatting.

Reference

index.d.ts
1errorFormat?: ErrorFormat
index.d.ts
1export type ErrorFormat = 'pretty' | 'colorless' | 'minimal'

Examples

No error formatting
const prisma = new PrismaClient({
// Defaults to colorless
})
pretty error formatting
const prisma = new PrismaClient({
errorFormat: 'pretty',
})
colorless error formatting
const prisma = new PrismaClient({
errorFormat: 'colorless',
})
minimal error formatting
const prisma = new PrismaClient({
errorFormat: 'minimal',
})

rejectOnNotFound

Use the rejectOnNotFound parameter to configure findUnique and/or findFirst to throw an error if the record was not found. By default, both operations return null if the record is not found.

Remarks

Options

OptionDescription
RejectOnNotFoundEnable globally (true / false) or throw a custom error.
RejectPerOperationEnable per operation (true / false) or throw a custom error per operation, per model.

Reference

index.d.ts
1rejectOnNotFound?: RejectOnNotFound | RejectPerOperation

Examples

Enable globally for findUnique and findFirst
const prisma = new PrismaClient({
rejectOnNotFound: true,
})
Enable globally for a specific operation
const prisma = new PrismaClient({
rejectOnNotFound: {
findUnique: true,
},
})
Throw a custom error per model and operation if record is not found
const prisma = new PrismaClient({
rejectOnNotFound: {
findFirst: {
User: err => new Error('User error'),
Post: err => new Error('Post error!'),
},
findUnique: {
User: err => new Error('User error'),
Post: err => new Error('Post error!'),
},
},
})

Model queries

Use model queries to perform CRUD operations on your models. See also: CRUD

findUnique

findUnique query lets you retrieve a single database record:

  • By ID
  • By a unique attribute

findUnique replaced findOne in version 2.12.0.

Remarks

Options

NameExample type (User)RequiredDescription
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.
rejectOnNotFoundRejectOnNotFoundNoIf true, throw a NotFoundError: No User found error. You can also configure rejectOnNotFound globally.

Return type

Return typeExampleDescription
JavaScript object (typed)User
JavaScript object (plain){ title: "Hello world" }Use select and include to determine which fields to return.
nullnullRecord not found
ErrorIf rejectOnNotFound is true, findUnique throws an error (NotFoundError by default, customizable globally) insted of returning null.

Reference

findUnique accepts the following input type:

index.d.ts
1export type UserFindUniqueArgs = {
2 where: UserWhereUniqueInput
3 select?: UserSelect | null
4 include?: UserInclude | null
5}

Examples

Get the User record with an id of 42
const result = await prisma.user.findUnique({
where: {
id: 42,
},
})
Get the User record with an email of alice@prisma.io
const result = await prisma.user.findUnique({
where: {
email: 'alice@prisma.io',
},
})
Get the User record with firstName of Alice and lastName of Smith (@@unique)
model User {
firstName String
lastName String
@@unique(fields: [firstName, lastName], name: "fullname")
}
const result = await prisma.user.findUnique({
where: {
fullname: {
// name property of @@unique attribute - default is firstname_lastname
firstName: 'Alice',
lastName: 'Smith',
},
},
})
Get the User record with firstName of Alice and lastName of Smith (@@id)
model User {
firstName String
lastName String
@@id([firstName, lastName])
}
const result = await prisma.user.findUnique({
where: {
firstName_lastName: {
firstName: 'Alice',
lastName: 'Smith',
},
},
})

findFirst

findFirst returns the first record in a list that matches your criteria.

Options

NameExample type (User)RequiredDescription
distinctEnumerable<UserDistinct
FieldEnum>
NoLets you filter out duplicate rows by a specific field - for example, return only distinct Post titles.
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
cursorUserWhereUniqueInputNoSpecifies the position for the list (the value typically specifies an id or another unique value).
orderByXOR<Enumerable<User
OrderByInput>,UserOrderByInput>
NoLets you order the returned list by any property.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
skipnumberNoSpecifies how many of the returned objects in the list should be skipped.
takenumberNoSpecifies how many objects should be returned in the list. When used with findFirst, take is implicitly 1 or -1. findFirst is only affected by whether the value is positive or negative - any negative value reverses the list.
rejectOnNotFoundRejectOnNotFoundNoIf true, throw a NotFoundError: No User found error. You can also configure rejectOnNotFound globally.

Return type

Return typeExampleDescription
JavaScript object (typed)UserSpecifies which properties to include on the returned object.
JavaScript object (plain){ title: "Hello world" }Use select and include to determine which fields to return.
nullnullRecord not found
ErrorIf rejectOnNotFound is true, findUnique throws an error (NotFoundError by default, customizable globally) insted of returning null.

Remarks

  • findFirst calls findMany behind the scenes and accepts the same query options.
  • Passing in a negative take value when you use a findFirst query reverses the order of the list.

Reference

findFirst accepts the following input type:

index.d.ts
1export type FindFirstUserArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 where?: UserWhereInput
5 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>
6 cursor?: UserWhereUniqueInput
7 take?: number
8 skip?: number
9 distinct?: Enumerable<UserDistinctFieldEnum>
10}

Examples

See Filter conditions and operators for examples of how to filter results.

Get the first User record where the name is Alice
const user = await prisma.user.findFirst({
where: { name: 'Alice' },
})
Get the first Post record where the title starts with A test, reverse the list with take
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient({})
async function main() {
const a = await prisma.post.create({
data: {
title: 'A test 1',
},
})
const b = await prisma.post.create({
data: {
title: 'A test 2',
},
})
const c = await prisma.post.findFirst({
where: {
title: {
startsWith: 'A test',
},
},
orderBy: {
title: 'asc',
},
take: -1, // Reverse the list
})
}
main()

findMany

findMany returns a list of records.

Options

NameTypeRequiredDescription
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
orderByXOR<Enumerable<PostOrder
ByInput>, PostOrderByInput>
NoLets you order the returned list by any property.
skipnumberNoSpecifies 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 (positive value) or end (negative value) either of the list or from the cursor position if mentioned)
selectXOR<PostSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<PostInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.
distinctEnumerable<UserDistinctFieldEnum>NoLets you filter out duplicate rows by a specific field - for example, return only distinct Post titles.

Return type

Return typeExampleDescription
JavaScript array object (typed)User[]
JavaScript array object (plain)[{ title: "Hello world" }]Use select and include to determine which fields to return.
Empty array[]No matching records found.

Reference

findMany accepts the following input type:

index.d.ts
1export type UserFindManyArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 where?: UserWhereInput
5 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>
6 cursor?: UserWhereUniqueInput
7 take?: number
8 skip?: number
9 distinct?: Enumerable<UserDistinctFieldEnum>
10}

Examples

See Filter conditions and operators for examples of how to filter results.

Get all User records where the name is Alice
const user = await prisma.user.findMany({
where: { name: 'Alice' },
})

create

create creates a new database record.

Options

NameTypeRequiredDescription
dataXOR<UserCreateInput,
UserUncheckedCreateInput>
YesWraps 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.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.

Return type

Return typeExampleDescription
JavaScript object (typed)User
JavaScript object (plain){ name: "Alice Wonderland" }Use select and include to determine which fields to return.

Remarks

  • You can also perform a nested create - for example, add a User and two Post records at the same time.

Reference

create accepts the following input type:

index.d.ts
1export type UserCreateArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 data: XOR<UserCreateInput, UserUncheckedCreateInput>
5}

Examples

Create a single new record with the only required field email
const user = await prisma.user.create({
data: { email: 'alice@prisma.io' },
})
Create multiple new records

Prisma Client does not yet support batch inserts at a database level. Follow issue #332 on GitHub for updates.

The following example results in two INSERT statements:

import { PrismaClient, UserCreateInput } from '@prisma/client'
const prisma = new PrismaClient({ log: ['query'] })
async function main() {
let users: UserCreateInput[] = [
{
email: 'ariana@prisma.io',
name: 'Ari',
profileViews: 20,
coinflips: [true, false, false],
role: 'ADMIN',
},
{
email: 'elsa@prisma.io',
name: 'Elsa',
profileViews: 20,
coinflips: [true, false, false],
role: 'ADMIN',
},
]
await Promise.all(
users.map(async user => {
await prisma.user.create({
data: user,
})
})
)
}
main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Hide CLI results
prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"
prisma:query COMMIT
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT

update

update updates an existing database record.

Options

NameTypeRequiredDescription
dataXOR<UserUpdateInput
UserUncheckedUpdateInput>
YesWraps 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.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.

Return type

Return typeExampleDescription
JavaScript object (typed)User
JavaScript object (plain){ name: "Alice Wonderland" }Use select and include to determine which fields to return.
RecordNotFound exceptionException is thrown if record does not exist.

Remarks

  • To perform arithmetic operations on update (add, subtract, multiply, divide), use atomic updates to prevent race conditions.
  • You can also perform a nested update - for example, update a user and that user's posts at the same time.

Reference

index.d.ts
1export type UserUpdateArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 data: XOR<UserUpdateInput, UserUncheckedUpdateInput>
5 where: UserWhereUniqueInput
6}

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

upsert updates an existing or creates a new database record.

Options

NameTypeRequiredDescription
createXOR<UserCreateInput,
UserUncheckedCreateInput>
YesWraps 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.
updateXOR<UserUpdateInput,
UserUncheckedUpdateInput>
YesWraps 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.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.

Return type

Return typeExampleDescription
JavaScript object (typed)User
JavaScript object (plain){ name: "Alice Wonderland" }Use select and include to determine which fields to return.

Remarks

  • To perform arithmetic operations on update (add, subtract, multiply, divide), use atomic updates to prevent race conditions.

Reference

upsert accepts the following input type:

index.d.ts
1export type UserUpsertArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 where: UserWhereUniqueInput
5 create: XOR<UserCreateInput, UserUncheckedCreateInput>
6 update: XOR<UserUpdateInput, UserUncheckedUpdateInput>
7}

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

delete deletes an existing database record. You can delete a record:

  • By ID
  • By a unique attribute

To delete records that match a certain criteria, use deleteMany with a filter.

Options

NameTypeRequiredDescription
whereUserWhereUniqueInputYesWraps all unique fields of a model so that individual records can be selected.
selectXOR<UserSelect, null>NoSpecifies which properties to include on the returned object.
includeXOR<UserInclude, null>NoSpecifies which relations should be eagerly loaded on the returned object.

Return type

Return typeExampleDescription
JavaScript object (typed)UserThe User record that was deleted.
JavaScript object (plain){ name: "Alice Wonderland" }Data from the User record that was deleted. Use select and include to determine which fields to return.
RecordNotFound exceptionThrows an exception if record does not exist.

Remarks

  • To delete multiple records based on some criteria (for example, all User records with a prisma.io email address, use deleteMany)

Reference

delete accepts the following input type:

index.d.ts
1export type UserDeleteArgs = {
2 select?: XOR<UserSelect, null>
3 include?: XOR<UserInclude, null>
4 where: UserWhereUniqueInput
5}

Examples

Delete the User record with an id of 1
const user = await prisma.user.delete({
where: { id: 1 },
})
Delete the User record where email equals else@prisma.io

The following query deletes a specific user record and uses select to return the name and email of the deleted user:

const deleteUser = await prisma.user.delete({
where: {
email: 'elsa@prisma.io',
},
select: {
email: true,
name: true,
},
})
Hide CLI results
{ "email": "elsa@prisma.io", "name": "Elsa" }

createMany

createMany creates multiple records in a transaction.

Options

NameTypeRequiredDescription
dataEnumerable<UserCreateManyInput>YesWraps 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.
skipDuplicates?booleanNoDo not insert records with unique fields or ID fields that already exist. Only supported by databases that support ON CONFLICT DO NOTHING.

Return type

Return typeExampleDescription
BatchPayload{ count: 3 }A count of the number of records created.

Remarks

  • createMany is not supported by SQLite.
  • You cannot create or connect relations - you cannot nest create, createMany, connect, connectOrCreate inside a top-level createMany
  • You can nest a createMany inside an update or create query - for example, add a User and two Post records at the same time.

Reference

createMany accepts the following input type:

index.d.ts
1export type UserCreateManyArgs = {
2 data: Enumerable<UserCreateManyInput>
3 skipDuplicates?: boolean
4}

Examples

Create a several new users
const users = await prisma.user.createMany({
data: [
{ name: 'Sonali', email: 'sonali@prisma.io' },
{ name: 'Alex', email: 'alex@prisma.io' },
],
})

updateMany

updateMany updates a batch of existing database records in bulk and returns the number of updated records.

Options

NameTypeRequiredDescription
dataXOR<UserUpdateManyMutationInput,
UserUncheckedUpdateManyInput>
YesWraps 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

Return typeExampleDescription
BatchPayload{ count: 4 }The count of updated records.
export type BatchPayload = {
count: number
}

Reference

updateMany accepts the following input type:

index.d.ts
1export type UserUpdateManyArgs = {
2 data: XOR<UserUpdateManyMutationInput, UserUncheckedUpdateManyInput>
3 where?: UserWhereInput
4}

Examples

Update all User records where the name is Alice to ALICE
const updatedUserCount = await prisma.user.updateMany({
where: { name: 'Alice' },
data: { name: 'ALICE' },
})
Update all User records where the email contains prisma.io and at least one related Post has more than 10 likes
const deleteUser = await prisma.user.updateMany({
where: {
email: {
contains: 'prisma.io',
},
posts: {
some: {
likes: {
gt: 10,
},
},
},
},
data: {
role: 'USER',
},
})

deleteMany

deleteMany deletes multiple records in a transaction.

Options

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

Return type

Return typeExampleDescription
BatchPayload{ count: 4 }The count of updated records.
export type BatchPayload = {
count: number
}

Reference

deleteMany accepts the following input type:

index.d.ts
1export type UserDeleteManyArgs = {
2 where?: UserWhereInput
3}

Examples

Delete all User records
const deletedUserCount = await prisma.user.deleteMany({})
Delete all User records where the name is Alice
const deletedUserCount = await prisma.user.deleteMany({
where: { name: 'Alice' },
})

See Filter conditions and operators for examples of how to filter the records to delete.

count

Options

NameTypeRequiredDescription
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
cursorUserWhereUniqueInputNoSpecifies the position for the list (the value typically specifies an id or another unique value).
skipnumberNoSpecifies how many of the returned objects in the list should be skipped.
takenumberNoSpecifies how many objects should be returned in the list (as seen from the beginning (positive value) or end (negative value) either of the list or from the cursor position if mentioned)
orderByXOR<Enumerable<PostOrder
ByInput>, PostOrderByInput>
NoLets you order the returned list by any property.
distinctEnumerable<UserDistinctFieldEnum>NoLets you filter out duplicate rows by a specific field - for example, return only distinct Post titles.
selectUserCountAggregateInputTypeNoSelect which fields to count (non-null values) - you can also count _all.

Return type

Return typeExampleDescription
number29The count of records.
UserCountAggregateOutputType{ _all: 27, name: 10 }Returned if select is used.

Reference

count accepts the following input type:

index.d.ts
1export type UserFindManyArgs = {
2 // select and include are excluded
3 where?: UserWhereInput
4 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>
5 cursor?: UserWhereUniqueInput
6 take?: number
7 skip?: number
8 distinct?: Enumerable<UserDistinctFieldEnum>
9}
10
11export type UserCountAggregateOutputType = {
12 id: number
13 name: number | null
14 email: number | null
15 profileViews: number
16 role: number | null
17 coinflips: number | null
18 testing: number | null
19 city: number | null
20 country: number | null
21 _all: number
22}

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,
},
},
},
})
Use select to perform three separate counts

The following query returns:

  • A count of all records (_all)
  • A count of all records with non-null name fields
  • A count of all records with non-null city fields
const c = await prisma.user.count({
select: {
_all: true,
city: true,
name: true,
},
})

aggregate

See also: Aggregation, grouping, and summarizing

Options

NameTypeRequiredDescription
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
orderByXOR<Enumerable<UserOrderByInput>,
UserOrderByInput>
NoLets you order the returned list by any property.
cursorUserWhereUniqueInputNoSpecifies the position for the list (the value typically specifies an id or another unique value).
skipnumberNoSpecifies how many of the returned objects in the list should be skipped.
takenumberNoSpecifies how many objects should be returned in the list (as seen from the beginning (positive value) or end (negative value) either of the list or from the cursor position if mentioned)
distinctEnumerable<UserDistinctFieldEnum>NoLets you filter out duplicate rows by a specific field - for example, return only distinct Post titles.
counttrueNoReturns a count of matching records or non-null fields.
avgUserAvgAggregateInputTypeNoReturns an average of all values of the specified field.
sumUserSumAggregateInputTypeNoReturns the sume of all values of the specified field.
minUserMinAggregateInputTypeNoReturns the smallest available value of the specified field.
maxUserMaxAggregateInputTypeNoReturns the largest available value of the specified field.

Reference

aggregate accepts the following input type:

index.d.ts
1export type UserAggregateArgs = {
2 where?: UserWhereInput
3 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>
4 cursor?: UserWhereUniqueInput
5 take?: number
6 skip?: number
7 distinct?: Enumerable<UserDistinctFieldEnum>
8 count?: true | UserCountAggregateInputType
9 avg?: UserAvgAggregateInputType
10 sum?: UserSumAggregateInputType
11 min?: UserMinAggregateInputType
12 max?: UserMaxAggregateInputType
13}

Examples

Return min, max, and count of profileViews of all User records
const minMaxAge = await prisma.user.aggregate({
count: {
_all: true
}
max: {
profileViews: true,
},
min: {
profileViews: true,
},
})
Show CLI results
Return sum of all profileViews for all User records
const setValue = await prisma.user.aggregate({
sum: {
profileViews: true,
},
})
Show CLI results

groupBy

See also: Aggregation, grouping, and summarizing

Options

NameTypeRequiredDescription
whereUserWhereInputNoWraps all model fields in a type so that the list can be filtered by any property.
orderByXOR<Enumerable<UserOrderByInput>,
UserOrderByInput>
NoLets you order the returned list by any property that is also present in by.
byUserWhereUniqueInputNoSpecifies the field or combination of fields to group records by.
havingnumberNoAllows you to filter groups by an aggregate value - for example, only return groups having an average age less than 50.
skipnumberNoSpecifies how many of the returned objects in the list should be skipped.
takenumberNoSpecifies how many objects should be returned in the list (as seen from the beginning (positve value) or end (negative value) either of the list or from the cursor position if mentioned)
counttrueNoReturns a count of matching records or non-null fields.
avgUserAvgAggregateInputTypeNoReturns an average of all values of the specified field.
sumUserSumAggregateInputTypeNoReturns the sume of all values of the specified field.
minUserMinAggregateInputTypeNoReturns the smallest available value of the specified field.
maxUserMaxAggregateInputTypeNoReturns the largest available value of the specified field.

Reference

groupBy accepts the following input type:

index.d.ts
1export type UserGroupByArgs = {
2 where?: UserWhereInput
3 orderBy?: Enumerable<UserOrderByInput>
4 by: Array<UserScalarFieldEnum>
5 having?: UserScalarWhereWithAggregatesInput
6 take?: number
7 skip?: number
8 count?: true | UserCountAggregateInputType
9 avg?: UserAvgAggregateInputType
10 sum?: UserSumAggregateInputType
11 min?: UserMinAggregateInputType
12 max?: UserMaxAggregateInputType
13}

Examples

Group by country/city where the average profileViews is greater than 200, and return the sum of profileViews for each group

The query also returns a count of _all records in each group, and all records with non-null city field values in each group.

const groupUsers = await prisma.user.groupBy({
by: ['country', 'city'],
count: {
_all: true,
city: true,
},
sum: {
profileViews: true,
},
orderBy: {
country: 'desc',
},
having: {
profileViews: {
avg: {
gt: 200,
},
},
},
})
Hide CLI results
;[
{
country: 'Denmark',
city: 'Copenhagen',
sum: { profileViews: 490 },
count: {
_all: 70,
city: 8,
},
},
{
country: 'Sweden',
city: 'Stockholm',
sum: { profileViews: 500 },
count: {
_all: 50,
city: 3,
},
},
]

Model query options

select

select defines which fields are included in the object that Prisma Client returns. See: Select fields and include relations .

Remarks

Reference

index.d.ts
1export type UserSelect = {
2 id?: boolean
3 name?: boolean
4 email?: boolean
5 profileViews?: boolean
6 role?: boolean
7 coinflips?: boolean
8 posts?: boolean | PostFindManyArgs
9 _count?: boolean | UserCountOutputTypeArgs
10}

Examples

Select the name and profileViews fields of a single User record
const result = await prisma.user.findUnique({
where: { id: 1 },
select: {
name: true,
profileViews: true,
},
})
Show CLI results
Select the name and profileViews fields of a multiple User records
const result = await prisma.user.findMany({
select: {
email: true,
role: true,
},
})
Show CLI results
Select a _count of relations
const usersWithCount = await prisma.user.findMany({
select: {
_count: {
select: { posts: true },
},
},
})
Show CLI results
Select the 'id' and 'title' fields of related Post records
const result = await prisma.user.findMany({
select: {
id: true,
name: true,
posts: {
select: {
id: true,
title: true,
},
},
},
})
Show CLI results
include inside select
const result = await prisma.user.findMany({
select: {
id: true,
name: true,
posts: {
include: {
author: true,
},
},
},
})
Show CLI results

Generated types for select

The following examples demonstrate how to use the validator with select:

  • SelectAndInclude
  • UserSelect
// SelectAndInclude
const selectNameIncludeEmail = Prisma.validator<Prisma.SelectAndInclude>()({
select: {
name: true,
},
include: {
email: true,
},
})
// UserSelect
const selectNameEmailNotPosts = Prisma.validator<Prisma.UserSelect>()({
name: true,
email: true,
posts: false,
})

include

include defines which relations are included in the result that the Prisma Client returns. See: Select fields and include relations .

Remarks

Reference

export type UserInclude = {
posts?: boolean | PostFindManyArgs
_count?: boolean | UserCountOutputTypeArgs
}

Examples

Include the posts and profile relation when loading User records
const users = await prisma.user.findMany({
include: {
posts: true, // Returns all fields for all posts
profile: true, // Returns all Profile fields
},
})
Include the posts relation on the returned objects when creating a new User record with two Post records
const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: [{ title: 'This is my first post' }, { title: 'Here comes a second post' }],
},
},
include: { posts: true }, // Returns all fields for all posts
})

Generated types for include

The following examples demonstrate how to use the validator with include:

  • SelectAndInclude
  • UserInclude
// SelectAndInclude
const selectNameIncludeEmail = Prisma.validator<Prisma.SelectAndInclude>()({
select: {
name: true,
},
include: {
email: true,
},
})
// UserInclude
const includePosts = Prisma.validator<Prisma.UserInclude>()({
posts: true,
})
Include a _count of relations
const usersWithCount = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
})
Show CLI results

where

where defines one or more filters, and can be used to filter on record properties (like a user's email address) or related record properties (like a user's top 10 most recent post titles).

Reference

For queries like findMany and updateMany that return multiple records, where accepts the following input type:

index.d.ts
1export type UserWhereInput = {
2 AND?: Enumerable<UserWhereInput>
3 OR?: Enumerable<UserWhereInput>
4 NOT?: Enumerable<UserWhereInput>
5 id?: IntFilter | number
6 name?: StringNullableFilter | string | null
7 email?: StringFilter | string
8 profileViews?: IntFilter | number
9 role?: EnumRoleFilter | Role
10 coinflips?: BoolNullableListFilter
11 posts?: PostListRelationFilter
12}

findFirst is a findMany query with a take: 1, and also accepts UserWhereInput.

For queries like findUnique, which returns a single record by ID or unique identifier, where accepts the following input type:

index.d.ts
1export type UserWhereUniqueInput = {
2 id?: number
3 email?: string
4}

Examples

const results = await prisma.user.findMany({
where: {
email: {
endsWith: 'prisma.io',
},
},
})

Generated types for where

The following examples demonstrate how to use the validator with where:

  • UserWhereInput
// UserWhereInput
const whereNameIs = Prisma.validator<Prisma.UserWhereInput>()({
name: 'Rich',
})
// It can be combined with conditional operators too
const whereNameIs = Prisma.validator<Prisma.UserWhereInput>()({
name: 'Rich',
AND: [
{
email: {
contains: 'rich@boop.com',
},
},
],
})
  • UserWhereUniqueInput This type works by exposing any unique fields on the model. A field assigned @id is considered unique, as is one assigned @unique.
// UserWhereUniqueInput
const whereEmailIsUnique = Prisma.validator<Prisma.UserWhereUniqueInput>()({
email: 'rich@boop.com',
})
  • PostScalarWhereInput
const whereScalarTitleIs = Prisma.validator<Prisma.PostScalarWhereInput>()({
title: 'boop',
})
  • PostUpdateWithWhereUniqueWithoutAuthorInput - This type accepts a unique where field (an @id or another assigned @unique) and updates any field on the Post model except the Author. The Author is the scalar field on the Post model.
const updatePostByIdWithoutAuthor = Prisma.validator<
Prisma.PostUpdateWithWhereUniqueWithoutAuthorInput
>()({
where: {
id: 1,
},
data: {
content: 'This is some updated content',
published: true,
title: 'This is a new title',
},
})
  • PostUpsertWithWhereUniqueWithoutAuthorInput - This type will update the Post records title field where the id matches, if it doesn't exist it will create it instead.
const updatePostTitleOrCreateIfNotExist = Prisma.validator<
Prisma.PostUpsertWithWhereUniqueWithoutAuthorInput
>()({
where: {
id: 1,
},
update: {
title: 'This is a new title',
},
create: {
id: 1,
title: 'If the title doesnt exist, then create one with this text',
},
})
  • PostUpdateManyWithWhereWithoutAuthorInput - This type will update all Post records where published is set to false.
const publishAllPosts = Prisma.validator<Prisma.PostUpdateManyWithWhereWithoutAuthorInput>()({
where: {
published: {
equals: false,
},
},
data: {
published: true,
},
})

orderBy

Sorts a list of records. See also: Sorting

Remarks

Inputs

NameDescription
ascSort ascending (A Z)
descSort descending (Z A)

Reference

orderBy accepts the following input type:

index.d.ts
1export type UserOrderByInput = {
2 id?: SortOrder
3 name?: SortOrder
4 email?: SortOrder
5 profileViews?: SortOrder
6 role?: SortOrder
7 coinflips?: SortOrder
8 posts?: PostOrderByAggregateInput
9 profile?: ExtendedProfileOrderByWithRelationInput
10}

Related types:

export type PostOrderByAggregateInput = {
count: SortOrder
}
export type ExtendedProfileOrderByWithRelationInput = {
id?: SortOrder
biograpy?: SortOrder
userId?: SortOrder
user?: UserOrderByWithRelationInput
}
export declare const SortOrder: {
asc: 'asc'
desc: 'desc'
}

Examples

Sort User by email field

The following example returns all User records sorted by email ascending:

const users = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
})

The following example returns all User records sorted by email descending:

const users = await prisma.user.findMany({
orderBy: {
email: 'desc',
},
})

Sort Post by the related User record's name

Sorting by relation fields is a Preview feature. To enable this feature, add orderByRelation to previewFeatures in your schema:

generator client {
provider = "prisma-client-js"
previewFeatures = ["orderByRelation"]
}

The following query orders posts by user name:

const posts = await prisma.post.findMany({
orderBy: {
author: {
name: 'asc',
},
},
})

Sort User by the posts count

Sorting by relation count is a Preview feature. To enable this feature, add orderByRelation to previewFeatures in your schema:

generator client {
provider = "prisma-client-js"
previewFeatures = ["orderByRelation"]
}

The following query orders users by post count:

const getActiveusers = await prisma.user.findMany({
orderBy: {
posts: {
count: 'desc',
},
},
})
Sort User by multiple fields - email and role

The following example sorts users by two fields - first email, then role:

const users = await prisma.user.findMany({
select: {
email: true,
role: true,
},
orderBy: [
{
email: 'desc',
},
{
role: 'desc',
},
],
})
Show CLI results

The order of sorting parameters matters - the following query sorts by role, then email. Not the difference in the results:

const users = await prisma.user.findMany({
select: {
email: true,
role: true,
},
orderBy: [
{
role: 'desc',
},
{
email: 'desc',
},
],
})
Show CLI results
Sort User by email, select name and email

The following example returns all the name and email fields of all User records, sorted by email:

const users3 = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
select: {
name: true,
email: true,
},
})
Show CLI results
Sort User records by email and sort nested Post records by title

The following example:

  • Returns all User records sorted by email
  • For each User record, returns the title field of all nested Post records sorted by title
const usersWithPosts = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
include: {
posts: {
select: {
title: true,
},
orderBy: {
title: 'asc',
},
},
},
})
Show CLI results
Sort one user's nested list of Post records

The following example retrieves a single User record by ID, as well as a list of nested Post records sorted by title:

const userWithPosts = await prisma.user.findUnique({
where: {
id: 1,
},
include: {
posts: {
orderBy: {
title: 'desc',
},
select: {
title: true,
published: true,
},
},
},
})
Show CLI results
Sort by enum

The following sorts all User records by role (an enum):

const sort = await prisma.user.findMany({
orderBy: {
role: 'desc',
},
select: {
email: true,
role: true,
},
})
Show CLI results

Generated types for orderBy

The following examples demonstrate how to use the validator with orderBy:

  • UserOrderByInput
    const orderEmailsByDescending = Prisma.validator<Prisma.UserOrderByInput>()({
    email: 'desc',
    })

distinct

See also: Aggregation, grouping, and summarizing

Reference

index.d.ts
1distinct?: Enumerable<UserDistinctFieldEnum>
export declare const UserDistinctFieldEnum: {
id: 'id'
name: 'name'
email: 'email'
profileViews: 'profileViews'
role: 'role'
coinflips: 'coinflips'
}

Examples

Select distinct on a single field

The following example returns all distinct city fields, and selects only the city and country fields:

const distinctCities = await prisma.user.findMany({
select: {
city: true,
country: true,
},
distinct: ['city'],
})
Hide CLI results
;[
{ city: 'Paris', country: 'France' },
{ city: 'Lyon', country: 'France' },
]
Select distinct on multiple fields

The following example returns all distinct city and country field combinations, and selects only the city and country fields:

const distinctCitiesAndCountries = await prisma.user.findMany({
select: {
city: true,
country: true,
},
distinct: ['city', 'country'],
})
Hide CLI results
;[
{ city: 'Paris', country: 'France' },
{ city: 'Paris', country: 'Denmark' },
{ city: 'Lyon', country: 'France' },
]

Note that there is now a "Paris, Denmark" in addition to "Paris, France":

Select distinct in combination with a filter

The following example returns all distinct city and country field combinations where the user's email contains "prisma.io", and selects only the city and country fields:

const distinctCitiesAndCountries = await prisma.user.findMany({
where: {
email: {
contains: 'prisma.io',
},
},
select: {
city: true,
country: true,
},
distinct: ['city', 'country'],
})
Show CLI results

Nested queries

create

A nested create query adds a new related record or set of records to a parent record. See: Working with relations .

Remarks

  • create is available as a nested query when you create (prisma.user.create(...)) a new parent record or update (prisma.user.update(...)) an existing parent record.

You can use a nested create or a nested createMany to create multiple related records - each technique pros and cons .

Examples

Create a new User record with a new Profile record
const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
profile: {
create: { bio: 'Hello World' },
},
},
})
Create a new Profile record with a new User record
const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
create: { email: 'alice@prisma.io' },
},
},
})
Create a new User record with a new Post record
const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: { title: 'Hello World' },
},
},
})
Create a new User record with two new Post records

Because it's a one-to-many relation, you can also create several Post records at once by passing an array to create:

const user = await prisma.user.create({
data: {
email: 'alice@prisma.io',
posts: {
create: [
{
title: 'This is my first post',
},
{
title: 'Here comes a second post',
},
],
},
},
})

You can also use a nested createMany to achieve the same result.

Update an existing User record by creating a new Profile record
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
create: { bio: 'Hello World' },
},
},
})
Update an existing User record by creating a new Post record
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
create: { title: 'Hello World' },
},
},
})

createMany

A nested createMany query adds a new set of records to a parent record. See: Working with relations .

Remarks

  • createMany is available as a nested query when you create (prisma.user.create(...)) a new parent record or update (prisma.user.update(...)) an existing parent record.
  • Available in the context of a has-many relation - for example, you can prisma.user.create(...) a user and use a nested createMany to create multiple posts (posts have one user).
  • Not available in the context of a many-to-many relation - for example, you cannot you can prisma.post.create(...) a post and use a nested createMany to create categories (many posts have many categories).
  • Does not support nesting additional relations - you cannot nest an additional create or createMany.
  • Allows setting foreign keys directly - for example, setting the categoryId on a post.

You can use a nested create or a nested createMany to create multiple related records - each technique pros and cons .

Examples

Update a User and multiple new related Post records
const user = await prisma.user.update({
where: {
id: 9,
},
data: {
name: 'Elliott',
posts: {
createMany: {
data: [{ title: 'My first post' }, { title: 'My second post' }],
},
},
},
})

set

set overwrites the value of a relation - for example, replacing a list of Post records with a different list. See: Working with relations

Examples

Update an existing User record by disconnecting any previous Post records and connecting two other existing ones
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
set: [{ id: 32 }, { id: 42 }],
},
},
})

connect

A nested connect query connects a record to an existing related record by specifying an ID or unique identifier. See: Working with relations

Remarks

  • connect is available as a nested query when you create a new parent record or update an existing parent record.

  • If the related record does not exist, Prisma Client throws an exception:

    The required connected records were not found. Expected 1 records to be connected, found 0.

Examples

Create a new Profile record and connect it to an existing User record via unique field
const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
connect: { email: 'alice@prisma.io' },
},
},
})
Create a new Profile record and connect it to an existing User record via an ID field
const user = await prisma.profile.create({
data: {
bio: 'Hello World',
user: {
connect: { id: 42 }, // sets userId of Profile record
},
},
})

In 2.11.0 and later, you can set the foreign key directly:

const user = await prisma.profile.create({
data: {
bio: 'Hello World',
userId: 42,
},
})
Create a new Post record and connect it to an existing User record
const user = await prisma.post.create({
data: {
title: 'Hello World',
author: {
connect: { email: 'alice@prisma.io' },
},
},
})
Update an existing User record by connecting it to an existing Profile record
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
connect: { id: 24 },
},
},
})
Update an existing User record by connecting it to two existing Post records
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
connect: [{ id: 24 }, { id: 42 }],
},
},
})

connectOrCreate

connectOrCreate either connects a record to an existing related record by ID or unique identifier or creates a new related record if the record does not exist. See: Working with relations

Remarks

  • Multiple connectOrCreate queries that run as concurrent transactions can result in a race condition. Consider the following example, where two queries attempt to connectOrCreate a blog post tag named computing at the same time (tag names must be unique):

    Query A
    Query B
    const createPost = await prisma.post.create({
    data: {
    title: 'How to create a compiler',
    content: '...',
    author: {
    connect: {
    id: 9,
    },
    },
    tags: {
    connectOrCreate: {
    create: {
    name: 'computing',
    },
    where: {
    name: 'computing',
    },
    },
    },
    },
    })

    If query A and query B overlap in the following way, query A results in an exception:

    Query A (Fail ❌)Query B (Success ✅)
    Query hits server, starts transaction AQuery hits server, starts transaction B
    Find record where tagName equals computing, record not found
    Find record where tagName equals computing, record not found
    Create record where tagName equals computing and connect
    Create record where tagName equals computing
    Unique violation, record already created by transaction B

    To work around this scenario, we recommend catching the unique violation exception (PrismaClientKnownRequestError, error P2002) and retrying failed queries.

Examples

Create a new Profile record, then connect it to an existing User record or create a new User

The following example:

  1. Creates a Profile
  2. Attempts to connect the profile to a User where the email address is alice@prisma.io
  3. Creates a new user if a user if a matching user does not exist
const user = await prisma.profile.create({
data: {
bio: 'The coolest Alice on the planet',
user: {
connectOrCreate: {
where: { email: 'alice@prisma.io' },
create: { email: 'alice@prisma.io'}
},
},
})
Create a new Post record and connect it to an existing User record, or create a new User
const user = await prisma.post.create({
data: {
title: 'Hello World',
author: {
connectOrCreate: {
where: { email: 'alice@prisma.io' },
create: { email: 'alice@prisma.io' },
},
},
},
})
Update an existing User record by connecting it to an existing Profile record, or creating a new Profile record

The following example:

  1. Attempts to connect the user to a Profile with an id of 20
  2. Creates a new profile if a matching profile does not exist
const updateUser = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
connectOrCreate: {
where: { id: 20 },
create: {
bio: 'The coolest Alice in town',
},
},
},
},
})
Update an existing User record by connect it to two existing Post records, or creating two new Post records
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
connectOrCreate: [
{
where: { id: 32 },
create: { title: 'This is my first post' },
},
{
where: { id: 19 },
create: { title: 'This is my second post' },
},
],
},
},
})

disconnect

A nested disconnect query breaks the connection between a parent record and a related record, but does not delete either record. See: Working with relations

Remarks

  • disconnect is only available if the relation is optional.

  • If the relationship you are attempting to disconnect does not exist:

    • (In 2.21.0 and later), the operation does nothing

    • (Before 2.21.0) Prisma Client throws an exception if the provided ID or unique identifier is not connected:

      The records for relation `PostToUser` between the `User` and `Post` models are not connected.

Examples

Update an existing User record by disconnecting the Profile record it's connected to
const user = await prisma.user.update({
where: { email: 'bob @prisma.io' },
data: {
profile: {
disconnect: true,
},
},
})
Update an existing User record by disconnecting two Post records it's connected to
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
disconnect: [{ id: 44 }, { id: 46 }],
},
},
})

update

A nested update query updates one or more related records where the parent record's ID is n. See: Working with relations

Remarks

  • Nested update queries are only available in the context of a top-level update query (for example, prisma.user.update(...)).

  • If the parent record does not exist, Prisma Client throws an exception:

    AssertionError("Expected a valid parent ID to be present for nested update to-one case.")
  • If the related record that you want to update does not exist, Prisma Client throws an exception:

    AssertionError("Expected a valid parent ID to be present for nested update to-one case.")

Examples

Update an existing User record by updating the Profile record it's connected to
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
update: { bio: 'Hello World' },
},
},
})
Update an existing User record by updating two Post records it's connected to
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
update: [
{
data: { published: true },
where: { id: 32 },
},
{
data: { published: true },
where: { id: 23 },
},
],
},
},
})

upsert

A nested upsert query updates a related record if it exists, or creates a new related record.

Examples

Update an existing User record by updating the Profile record it's connected to or creating a new one (upsert)
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
upsert: {
create: { bio: 'Hello World' },
update: { bio: 'Hello World' },
},
},
},
})
Update an existing User record by updating two Post record it's connected to or creating new ones (upsert)
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
upsert: [
{
create: { title: 'This is my first post' },
update: { title: 'This is my first post' },
where: { id: 32 },
},
{
create: { title: 'This is mt second post' },
update: { title: 'This is mt second post' },
where: { id: 23 },
},
],
},
},
})

delete

A nested delete query deletes a related record. The parent record is not deleted.

Remarks

  • delete is only available if the relation is optional.

Examples

Update an existing User record by deleting the Profile record it's connected to
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
profile: {
delete: true,
},
},
})
Update an existing User record by deleting two Post records it's connected to
const user = await prisma.user.update({
where: { email: 'alice@prisma.io' },
data: {
posts: {
delete: [{ id: 34 }, { id: 36 }],
},
},
})

updateMany

A nested updateMany updates a list of related records and supports filtering - for example, you can update a user's unpublished posts.

Examples

Update all unpublished posts belonging to a specific user
const result = await prisma.user.update({
where: {
id: 2,
},
data: {
posts: {
updateMany: {
where: {
published: false,
},
data: {
likes: 0,
},
},
},
},
})

deleteMany

A nested deleteMany deletes related records and supports filtering. For example, you can delete a user's posts while updating other properties of that user.

Examples

Delete all posts belonging to a specific user as part of an update
const result = await prisma.user.update({
where: {
id: 2,
},
data: {
name: 'Updated name',
posts: {
deleteMany: {},
},
},
})

Filter conditions and operators

equals

Value equals n.

Examples

Return all users where name equals "Eleanor"
const result = await prisma.user.findMany({
where: {
name: {
equals: 'Eleanor',
},
},
})

You can also exclude the equals:

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

not

Value does not equal n.

Examples

Return all users where name does not equal "Eleanor"
const result = await prisma.user.findMany({
where: {
name: {
not: 'Eleanor',
},
},
})

in

Value n exists in list.

Remarks

  • null values are not returned. For example, if you combine in and NOT to return user whose name is not in the list, users with null value names are not returned.

Examples

Get User records where the id can be found in the following list: [22, 91, 14, 2, 5]
const getUser = await prisma.user.findMany({
where: {
id: { in: [22, 91, 14, 2, 5] },
},
})
Get User records where the name can be found in the following list: ['Saqui', 'Clementine', 'Bob']
const getUser = await prisma.user.findMany({
where: {
name: { in: ['Saqui', 'Clementine', 'Bob'] },
},
})
Get User records where name is not present in the list

The following example combines in and NOT. You can also use (notIn)[#notin]