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 @uniqueprofileViews Int @default(0)role Role @default(USER)coinflips Boolean[]posts Post[]city Stringcountry Stringprofile ExtendedProfile?pets Json}model ExtendedProfile {id Int @id @default(autoincrement())userId Int? @uniquebio String?User User? @relation(fields: [userId], references: [id])}model Post {id Int @id @default(autoincrement())title Stringpublished Boolean @default(true)author User @relation(fields: [authorId], references: [id])authorId Intcomments Jsonviews Int @default(0)likes Int @default(0)}enum Role {USERADMIN}
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 property | Example value | Description |
---|---|---|
db | { url: 'file:./dev_qa.db' } | The database connection URL. |
Remarks
- You must re-generate 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, replacedb
with the name of yourdatasource
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
Option | Example | Description |
---|---|---|
Array of log levels | [ "info", "query" ] | |
Array of log definitions | [ { level: "info", emit: "event" }, { level: "warn", emit: "stdout" }] |
Log levels
Name | Example |
---|---|
query | Logs all queries run by Prisma. For relational databases this logs all SQL queries. Example: 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 For MongoDB this logs queries using the mongosh shell format. Example: prisma:query db.User.deleteMany({ _id: ( $in: [ “6221ce49f756b0721fc00542”, ], }, }) |
info | Example: prisma:info Started http server on http://127.0.0.1:58471 |
warn | Warnings. |
error | Errors. |
Emit formats
Name | Description |
---|---|
stdout | See: stdout |
event | Raises 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: LogLevel4 emit: 'stdout' | 'event'5}
Event types
The query
event type:
index.d.ts
1export type QueryEvent = {2 timestamp: Date3 query: string // Query sent to the database4 params: string // Query parameters5 duration: number // Time elapsed (in milliseconds) between client issuing query and database responding - not only time taken to run query6 target: string7}
Note that for MongoDB, the params
and duration
fields will be undefined.
All other log level event types:
index.d.ts
1export type LogEvent = {2 timestamp: Date3 message: string4 target: string5}
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().then(async () => {await prisma.$disconnect()}).catch(async (e) => {console.error(e)await prisma.$disconnect()process.exit(1)})
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().then(async () => {await prisma.$disconnect()}).catch(async (e) => {console.error(e)await prisma.$disconnect()process.exit(1)})
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' },],})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().then(async () => {await prisma.$disconnect()}).catch(async (e) => {console.error(e)await prisma.$disconnect()process.exit(1)})
errorFormat
Determines the level and formatting of errors returned by Prisma.
Error formats
Name | Description |
---|---|
undefined | If it's not defined, the default is colorless. |
pretty | Enables pretty error formatting. |
colorless (default) | Enables colorless error formatting. |
minimal | Enables 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
Deprecated: rejectOnNotFound
is deprecated in v4.0.0. From v4.0.0, use the queries findUniqueOrThrow
or findFirstOrThrow
.
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
- You can configure
rejectOnNotFound
on a per-request level for bothfindUnique
andfindFirst
Options
Option | Description |
---|---|
RejectOnNotFound | Enable globally (true / false ) or throw a custom error. |
RejectPerOperation | Enable 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
- Prisma's dataloader automatically batches
findUnique
queries with the sameselect
andwhere
parameters. - If you want the query to throw an error if the record is not found, then consider using
findUniqueOrThrow
instead.
Options
Name | Example type (User ) | Required | Description |
---|---|---|---|
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
rejectOnNotFound (deprecated) | RejectOnNotFound | No | If true, throw a NotFoundError: No User found error . You can also configure rejectOnNotFound globally. Note: rejectOnNotFound is deprecated in v4.0.0. From v4.0.0, use findUniqueOrThrow instead. |
Return type
Return type | Example | Description |
---|---|---|
JavaScript object (typed) | User | |
JavaScript object (plain) | { title: "Hello world" } | Use select and include to determine which fields to return. |
null | null | Record not found |
Error | If rejectOnNotFound is true, findUnique throws an error (NotFoundError by default, customizable globally) instead of returning null . |
Reference
findUnique
accepts the following input type:
index.d.ts
1export type UserFindUniqueArgs = {2 where: UserWhereUniqueInput3 select?: UserSelect | null4 include?: UserInclude | null5}
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 StringlastName String@@unique(fields: [firstName, lastName], name: "fullname")}
const result = await prisma.user.findUnique({where: {fullname: {// name property of @@unique attribute - default is firstname_lastnamefirstName: 'Alice',lastName: 'Smith',},},})
Get the User
record with firstName
of Alice
and lastName
of Smith
(@@id
)
model User {firstName StringlastName String@@id([firstName, lastName])}
const result = await prisma.user.findUnique({where: {firstName_lastName: {firstName: 'Alice',lastName: 'Smith',},},})
findUniqueOrThrow
We introduced findUniqueOrThrow
in v4.0.0. It replaces the rejectOnNotFound
option. rejectOnNotFound
is deprecated in v4.0.0.
findUniqueOrThrow
retrieves a single data record in the same way as findUnique
. However, if the query does not find a record, it returns NotFoundError: No User found error
.
findUniqueOrThrow
differs from findUnique
as follows:
Its return type is non-nullable. For example,
post.findUnique()
can returnpost
ornull
, butpost.findUniqueOrThrow()
always returnspost
.It is not compatible with sequential operations in the
$transaction
API. If the query returnsNotFoundError
, then the API will not roll back any operations in the array of calls. As a workaround, you can use interactive transactions with the$transaction
API, as follows:$transaction(async (prisma) => {await prisma.model.create({ data: { ... });await prisma.model.findUniqueOrThrow();})
findFirst
findFirst
returns the first record in a list that matches your criteria.
Remarks
- If you want the query to throw an error if the record is not found, then consider using
findFirstOrThrow
instead.
Options
Name | Example type (User ) | Required | Description |
---|---|---|---|
distinct | Enumerable<UserDistinct FieldEnum> | No | Lets you filter out duplicate rows by a specific field - for example, return only distinct Post titles. |
where | UserWhereInput | No | Wraps all model fields in a type so that the list can be filtered by any property. |
cursor | UserWhereUniqueInput | No | Specifies the position for the list (the value typically specifies an id or another unique value). |
orderBy | XOR<Enumerable<User OrderByInput>,UserOrderByInput> | No | Lets you order the returned list by any property. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
take | number | No | Specifies 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. |
rejectOnNotFound (deprecated) | RejectOnNotFound | No | If true, throw a NotFoundError: No User found error . You can also configure rejectOnNotFound globally. Note: rejectOnNotFound is deprecated in v4.0.0. From v4.0.0, use findFirstOrThrow instead. |
Return type
Return type | Example | Description |
---|---|---|
JavaScript object (typed) | User | Specifies which properties to include on the returned object. |
JavaScript object (plain) | { title: "Hello world" } | Use select and include to determine which fields to return. |
null | null | Record not found |
Error | If rejectOnNotFound is true, findUnique throws an error (NotFoundError by default, customizable globally) instead of returning null . |
Remarks
findFirst
callsfindMany
behind the scenes and accepts the same query options.- Passing in a negative
take
value when you use afindFirst
query reverses the order of the list.
Reference
findFirst
accepts the following input type:
index.d.ts
1export type UserFindFirstArgs = {2 select?: XOR<UserSelect, null>3 include?: XOR<UserInclude, null>4 where?: UserWhereInput5 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>6 cursor?: UserWhereUniqueInput7 take?: number8 skip?: number9 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()
findFirstOrThrow
We introduced findFirstOrThrow
in v4.0.0. It replaces the rejectOnNotFound
option. rejectOnNotFound
is deprecated in v4.0.0.
findFirstOrThrow
retrieves the first record in a list in the same way as findFirst
. However, if the query does not find a record, it returns NotFoundError: No User found error
.
findFirstOrThrow
differs from findFirst
as follows:
- Its return type is non-nullable. For example,
post.findFirst()
can returnpost
ornull
, butpost.findFirstOrThrow
always returnspost
. - It is not compatible with sequential operations in the
$transaction
API. If the query returnsNotFoundError
, then the API will not roll back any operations in the array of calls. As a workaround, you can use interactive transactions with the$transaction
API, as follows:
prisma.$transaction(async (tx) => {await tx.model.create({ data: { ... });await tx.model.findFirstOrThrow();})
findMany
findMany
returns a list of records.
Options
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereInput | No | Wraps all model fields in a type so that the list can be filtered by any property. |
orderBy | XOR<Enumerable<PostOrder ByInput>, PostOrderByInput> | No | Lets you order the returned list by any property. |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
cursor | UserWhereUniqueInput | No | Specifies the position for the list (the value typically specifies an id or another unique value). |
take | number | No | Specifies 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) |
select | XOR<PostSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<PostInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
distinct | Enumerable<UserDistinctFieldEnum> | No | Lets you filter out duplicate rows by a specific field - for example, return only distinct Post titles. |
Return type
Return type | Example | Description |
---|---|---|
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?: UserWhereInput5 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>6 cursor?: UserWhereUniqueInput7 take?: number8 skip?: number9 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
Name | Type | Required | Description |
---|---|---|---|
data | XOR<UserCreateInput, UserUncheckedCreateInput> | Yes | Wraps 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. |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
Return type | Example | Description |
---|---|---|
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 aUser
and twoPost
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
In most cases, you can carry out batch inserts with the createMany
query. However, there are scenarios where create
is the best option to insert multiple records.
The following example results in two INSERT
statements:
import { Prisma, PrismaClient } from '@prisma/client'const prisma = new PrismaClient({ log: ['query'] })async function main() {let users: Prisma.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().then(async () => {await prisma.$disconnect()}).catch(async (e) => {console.error(e)await prisma.$disconnect()process.exit(1)})
prisma:query BEGINprisma: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 $3prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"prisma:query COMMITprisma: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 $3prisma:query COMMIT
update
update
updates an existing database record.
Options
Name | Type | Required | Description |
---|---|---|---|
data | XOR<UserUpdateInput UserUncheckedUpdateInput> | Yes | Wraps 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. |
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
Return type | Example | Description |
---|---|---|
JavaScript object (typed) | User | |
JavaScript object (plain) | { name: "Alice Wonderland" } | Use select and include to determine which fields to return. |
RecordNotFound exception | Exception 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: UserWhereUniqueInput6}
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
does the following:
- If an existing database record satisfies the
where
condition, it updates that record - If no database record satisfies the
where
condition, it creates a new database record
Options
Name | Type | Required | Description |
---|---|---|---|
create | XOR<UserCreateInput, UserUncheckedCreateInput> | Yes | Wraps 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. |
update | XOR<UserUpdateInput, UserUncheckedUpdateInput> | Yes | Wraps 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. |
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
Return type | Example | Description |
---|---|---|
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.
- If two or more upsert operations happen at the same time and the record doesn't already exist, then a race condition might happen. As a result, one or more of the upsert operations might throw a unique key constraint error. Your application code can catch this error and retry the operation. Learn more.
- From version 4.6.0, Prisma hands over upsert queries to the database where possible. Learn more.
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: UserWhereUniqueInput5 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' },})
Unique key constraint errors on upserts
Problem
If multiple upsert operations happen at the same time and the record doesn't already exist, then one or more of the operations might return a unique key constraint error.
Cause
When Prisma does an upsert, it first checks whether that record already exists in the database. To make this check, Prisma performs a read operation with the where
clause from the upsert operation. This has two possible outcomes, as follows:
- If the record does not exist, then Prisma creates that record.
- If the record exists, then Prisma updates it.
When your application tries to perform two or more concurrent upsert operations, then a race condition might happen where two or more operations do not find the record and therefore try to create that record. In this situation, one of the operations succesfully creates the new record but the other operations fail and return a unique key constraint error.
Solution
Handle the P2002 error in your application code. When it occurs, retry the upsert operation to update the row.
Database upserts
Where possible, Prisma Client hands over an upsert
query to the database. This is called a database upsert.
Database upserts have the following advantages:
- They are faster than upserts handled by Prisma
- Unique key constraint errors cannot happen
Prisma Client uses a database upsert automatically when specific criteria are met. When these criteria are not met, Prisma Client handles the upsert
.
To use a database upsert, Prisma Client sends the SQL construction INSERT ... ON CONFLICT SET .. WHERE
to the database.
Database upsert prerequisites
Prisma Client can use database upserts if your stack meets the following criteria:
- You use Prisma version 4.6.0 or later
- Your application uses a CockroachDB, PostgreSQL, or SQLite data source
Database upsert query criteria
Prisma Client uses a database upsert for an upsert
query when the query meets the following criteria:
- There are no nested queries in the
upsert
'screate
andupdate
options - The query does not include a selection that uses a nested read
- The query modifies only one model
- There is only one unique field in the
upsert
'swhere
option - The unique field in the
where
option and the unique field in thecreate
option have the same value
If your query does not meet these criteria, then Prisma Client handles the upsert itself.
Database upsert examples
The following examples use this schema:
model User {id Int @idprofileViews IntuserName String @uniqueemail String@@unique([id, profileViews])}
The following upsert
query meets all of the criteria, so Prisma Client uses a database upsert.
prisma.user.upsert({where: {userName: 'Alice',},create: {id: 1,profileViews: 1,userName: 'Alice',email: 'alice@prisma.io',},update: {email: 'updated@example.com',},})
In this situation, Prisma uses the following SQL query:
INSERT INTO "public"."User" ("id","profileViews","userName","email") VALUES ($1,$2,$3,$4)ON CONFLICT ("userName") DO UPDATESET "email" = $5 WHERE ("public"."User"."userName" = $6 AND 1=1) RETURNING "public"."User"."id", "public"."User"."profileViews", "public"."User"."userName", "public"."User"."email"
The following query has multiple unique values in the where
clause, so Prisma Client does not use a database upsert:
prisma.User.upsert({where: {userName: 'Alice',profileViews: 1,id: 1,},create: {id: 1,profileViews: 1,userName: 'Alice',email: 'alice@prisma.io',},update: {email: 'updated@example.com',},})
In the following query, the values for userName
in the where
and create
options are different, so Prisma Client does not use a database upsert.
prisma.User.upsert({where: {userName: 'Alice',},create: {id: 1,profileViews: 1,userName: 'AliceS',email: 'alice@prisma.io',},update: {email: 'updated@example.com',},})
In the following query, the selection on the title
field in posts
is a nested read, so Prisma Client does not use a database upsert.
prisma.user.upsert({select: {email: true,id: true,posts: {select: {title: true,},},},where: {userName: 'Alice',},create: {id: 1,profileViews: 1,userName: 'Alice',email: 'alice@prisma.io',},update: {email: 'updated@example.com',},})
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
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more |
select | XOR<UserSelect, null> | No | Specifies which properties to include on the returned object. |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
Return type | Example | Description |
---|---|---|
JavaScript object (typed) | User | The 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 exception | Throws an exception if record does not exist. |
Remarks
- To delete multiple records based on some criteria (for example, all
User
records with aprisma.io
email address, usedeleteMany
)
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: UserWhereUniqueInput5}
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,},})
{ "email": "elsa@prisma.io", "name": "Elsa" }
createMany
createMany
creates multiple records in a transaction.
Options
Name | Type | Required | Description |
---|---|---|---|
data | Enumerable<UserCreateManyInput> | Yes | Wraps 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? | boolean | No | Do not insert records with unique fields or ID fields that already exist. Only supported by databases that support ON CONFLICT DO NOTHING . This excludes MongoDB and SQLServer |
Return type
Return type | Example | Description |
---|---|---|
BatchPayload | { count: 3 } | A count of the number of records created. |
Remarks
createMany
is not supported by SQLite.- The
skipDuplicates
option is not supported by MongoDB and SQLServer. - You cannot create or connect relations - you cannot nest
create
,createMany
,connect
,connectOrCreate
inside a top-levelcreateMany
- You can nest a
createMany
inside anupdate
orcreate
query - for example, add aUser
and twoPost
records at the same time.
Reference
createMany
accepts the following input type:
index.d.ts
1export type UserCreateManyArgs = {2 data: Enumerable<UserCreateManyInput>3 skipDuplicates?: boolean4}
Examples
Create 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
Name | Type | Required | Description |
---|---|---|---|
data | XOR<UserUpdateManyMutationInput, UserUncheckedUpdateManyInput> | Yes | Wraps 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 . |
where | UserWhereInput | No | Wraps 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 type | Example | Description |
---|---|---|
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?: UserWhereInput4}
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
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereInput | No | Wraps all fields of a model so that the list can be filtered by any field. |
Return type
Return type | Example | Description |
---|---|---|
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?: UserWhereInput3}
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
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereInput | No | Wraps all model fields in a type so that the list can be filtered by any property. |
cursor | UserWhereUniqueInput | No | Specifies the position for the list (the value typically specifies an id or another unique value). |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
take | number | No | Specifies 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) |
orderBy | XOR<Enumerable<PostOrder ByInput>, PostOrderByInput> | No | Lets you order the returned list by any property. |
select | UserCountAggregateInputType | No | Select which fields to count (non-null values) - you can also count _all . |
Return type
Return type | Example | Description |
---|---|---|
number | 29 | The 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 excluded3 where?: UserWhereInput4 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>5 cursor?: UserWhereUniqueInput6 take?: number7 skip?: number8 distinct?: Enumerable<UserDistinctFieldEnum>9}1011export type UserCountAggregateOutputType = {12 id: number13 name: number | null14 email: number | null15 profileViews: number16 role: number | null17 coinflips: number | null18 testing: number | null19 city: number | null20 country: number | null21 _all: number22}
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
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereInput | No | Wraps all model fields in a type so that the list can be filtered by any property. |
orderBy | XOR<Enumerable<UserOrderByInput>, UserOrderByInput> | No | Lets you order the returned list by any property. |
cursor | UserWhereUniqueInput | No | Specifies the position for the list (the value typically specifies an id or another unique value). |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
take | number | No | Specifies 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) |
_count | true | No | Returns a count of matching records or non-null fields. |
_avg | UserAvgAggregateInputType | No | Returns an average of all values of the specified field. |
_sum | UserSumAggregateInputType | No | Returns the sum of all values of the specified field. |
_min | UserMinAggregateInputType | No | Returns the smallest available value of the specified field. |
_max | UserMaxAggregateInputType | No | Returns the largest available value of the specified field. |
Reference
aggregate
accepts the following input type:
index.d.ts
1export type UserAggregateArgs = {2 where?: UserWhereInput3 orderBy?: XOR<Enumerable<UserOrderByInput>, UserOrderByInput>4 cursor?: UserWhereUniqueInput5 take?: number6 skip?: number7 distinct?: Enumerable<UserDistinctFieldEnum>8 _count?: true | UserCountAggregateInputType9 _avg?: UserAvgAggregateInputType10 _sum?: UserSumAggregateInputType11 _min?: UserMinAggregateInputType12 _max?: UserMaxAggregateInputType13}
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,},})
Return _sum
of all profileViews
for all User
records
const setValue = await prisma.user.aggregate({_sum: {profileViews: true,},})
groupBy
See also: Aggregation, grouping, and summarizing
Options
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereInput | No | Wraps all model fields in a type so that the list can be filtered by any property. |
orderBy | XOR<Enumerable<UserOrderByInput>, UserOrderByInput> | No | Lets you order the returned list by any property that is also present in by . |
by | Array<UserScalarFieldEnum> | No | Specifies the field or combination of fields to group records by. |
having | UserScalarWhereWithAggregatesInput | No | Allows you to filter groups by an aggregate value - for example, only return groups having an average age less than 50. |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
take | number | No | Specifies 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) |
_count | true | UserCountAggregateInputType | No | Returns a count of matching records or non-null fields. |
_avg | UserAvgAggregateInputType | No | Returns an average of all values of the specified field. |
_sum | UserSumAggregateInputType | No | Returns the sum of all values of the specified field. |
_min | UserMinAggregateInputType | No | Returns the smallest available value of the specified field. |
_max | UserMaxAggregateInputType | No | Returns the largest available value of the specified field. |
Reference
groupBy
accepts the following input type:
index.d.ts
1export type UserGroupByArgs = {2 where?: UserWhereInput3 orderBy?: Enumerable<UserOrderByInput>4 by: Array<UserScalarFieldEnum>5 having?: UserScalarWhereWithAggregatesInput6 take?: number7 skip?: number8 _count?: true | UserCountAggregateInputType9 _avg?: UserAvgAggregateInputType10 _sum?: UserSumAggregateInputType11 _min?: UserMinAggregateInputType12 _max?: UserMaxAggregateInputType13}
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,},},},})
;[{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
- You cannot combine
select
andinclude
on the same level. - In 3.0.1 and later, you can select a
_count
of relations.
Reference
index.d.ts
1export type UserSelect = {2 id?: boolean3 name?: boolean4 email?: boolean5 profileViews?: boolean6 role?: boolean7 coinflips?: boolean8 posts?: boolean | PostFindManyArgs9 _count?: boolean | UserCountOutputTypeArgs10}
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,},})
Select the email
and role
fields of a multiple User
records
const result = await prisma.user.findMany({select: {email: true,role: true,},})
Select a _count
of relations
const usersWithCount = await prisma.user.findMany({select: {_count: {select: { posts: true },},},})
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,},},},})
include
inside select
const result = await prisma.user.findMany({select: {id: true,name: true,posts: {include: {author: true,},},},})
Generated types for select
The following examples demonstrate how to use the validator
with select
:
SelectAndInclude
UserSelect
// SelectAndIncludeconst selectNameIncludeEmail = Prisma.validator<Prisma.SelectAndInclude>()({select: {name: true,},include: {email: true,},})// UserSelectconst selectNameEmailNotPosts = Prisma.validator<Prisma.UserSelect>()({name: true,email: true,posts: false,})
include
include
defines which relations are included in the result that Prisma Client returns. See: Select fields and include relations .
Remarks
- In 3.0.1 and later, you can
include
a_count
of relations
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 postsprofile: 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
// SelectAndIncludeconst selectNameIncludeEmail = Prisma.validator<Prisma.SelectAndInclude>()({select: {name: true,},include: {email: true,},})// UserIncludeconst includePosts = Prisma.validator<Prisma.UserInclude>()({posts: true,})
Include a _count
of relations
const usersWithCount = await prisma.user.findMany({include: {_count: {select: { posts: true },},},})
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 | number6 name?: StringNullableFilter | string | null7 email?: StringFilter | string8 profileViews?: IntFilter | number9 role?: EnumRoleFilter | Role10 coinflips?: BoolNullableListFilter11 posts?: PostListRelationFilter12}
findFirst
is afindMany
query with atake: 1
, and also acceptsUserWhereInput
.
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?: number3 email?: string4}
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
// UserWhereInputconst whereNameIs = Prisma.validator<Prisma.UserWhereInput>()({name: 'Rich',})// It can be combined with conditional operators tooconst 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
.From version 4.5.0, this type exposes all fields on the model. This means that when you filter for a single record based on a unique field, you can check additional non-unique and unique fields at the same time. Learn more.
// UserWhereUniqueInputconst whereEmailIsUnique = Prisma.validator<Prisma.UserWhereUniqueInput>()({email: 'rich@boop.com',})
PostScalarWhereInput
const whereScalarTitleIs = Prisma.validator<Prisma.PostScalarWhereInput>()({title: 'boop',})PostUpdateWithWhereUniqueWithoutAuthorInput
- This type accepts a uniquewhere
field (an@id
or another assigned@unique
) and updates any field on thePost
model except theAuthor
. TheAuthor
is the scalar field on thePost
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 thePost
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 allPost
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
In 2.16.0 and later, you can order by relation fields - for example, order posts by the author's name.
In 3.5.0 and later, in PostgreSQL you can order by relevance. For details, see Sort by relevance.
In 4.1.0 and later, you can sort
null
records first or last. For details, see Sort with nulls first or last.
Inputs for sort argument
Name | Description |
---|---|
asc | Sort ascending (A → Z) |
desc | Sort descending (Z → A) |
Inputs for nulls argument
Note:
- This argument is optional.
- It is for use on optional scalar fields only. If you try to sort by nulls on a required or relation field, Prisma Client throws a P2009 error.
- It is available in version 4.1.0 and later, as a preview feature. See sort with nulls first or last for details of how to enable the feature.
Name | Description |
---|---|
first | Sort with null values first. |
last | Sort with null values last. |
Reference
orderBy
accepts the following input type:
index.d.ts
1export type UserOrderByInput = {2 id?: SortOrder3 name?: SortOrder | SortOrderInput4 email?: SortOrder5 profileViews?: SortOrder6 role?: SortOrder7 coinflips?: SortOrder8 posts?: PostOrderByRelationAggregateInput9 city?: SortOrder10 country?: SortOrder11 profile?: ExtendedProfileOrderByWithRelationAndSearchRelevanceInput12 pets?: SortOrder13 _relevance?: UserOrderByRelevanceInput14}1516export type SortOrderInput = {17 sort: SortOrder18 nulls?: NullsOrder19}
Related types:
export declare const SortOrder: {asc: 'asc'desc: 'desc'}export declare const NullsOrder: {first: 'first'last: 'last'}export type PostOrderByRelationAggregateInput = {_count?: SortOrder}export type ExtendedProfileOrderByWithRelationAndSearchRelevanceInput = {id?: SortOrderuserId?: SortOrderbio?: SortOrderUser?: UserOrderByWithRelationAndSearchRelevanceInput_relevance?: ExtendedProfileOrderByRelevanceInput}export type UserOrderByRelevanceInput = {fields: Enumerable<UserOrderByRelevanceFieldEnum>sort: SortOrdersearch: string}
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
The following query orders posts by user name:
const posts = await prisma.post.findMany({orderBy: {author: {name: 'asc',},},})
Sort Post
by the related User
record's name
, with null
records first
The following query orders posts by user name, with null
records first:
const posts = await prisma.post.findMany({orderBy: {author: {name: { sort: 'asc', nulls: 'first' },},},})
Sort Post
by relevance of the title
This feature is available from version 3.5.0 onwards in PostgreSQL only. You'll need to use the fullTextSearch
preview flag to enable this feature.
The following query orders posts by relevance of the search term 'database'
to the title:
const posts = await prisma.post.findMany({orderBy: {_relevance: {fields: ['title'],search: 'database',sort: 'asc'},})
Sort User
by the posts
count
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',},],})
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',},],})
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,},})
Sort User
records by email
and sort nested Post
records by title
The following example:
- Returns all
User
records sorted byemail
- For each
User
record, returns thetitle
field of all nestedPost
records sorted bytitle
const usersWithPosts = await prisma.user.findMany({orderBy: {email: 'asc',},include: {posts: {select: {title: true,},orderBy: {title: 'asc',},},},})
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,},},},})
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,},})
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: