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?
pets Json
}
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
From version 5.2.0 and upwards, you can also use the datasourceUrl
property to programmatically override the database connection string.
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")
}
datasourceUrl
Programmatically overrides the datasource
block in the schema.prisma
file.
Property
Option | Example value | Description |
---|---|---|
Database connection string | 'file:./dev_qa.db' | The database connection URL. |
Examples
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
datasourceUrl: 'postgresql://johndoe:randompassword@localhost:5432/mydb',
});
log
Determines the type and level of logging. See also: Logging
Options
Option | Example |
---|---|
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. |
Event types
The query
event type:
export type QueryEvent = {
timestamp: Date;
query: string; // Query sent to the database
params: string; // Query parameters
duration: number; // Time elapsed (in milliseconds) between client issuing query and database responding - not only time taken to run query
target: string;
};
Note that for MongoDB, the params
and duration
fields will be undefined.
All other log level event types:
export type LogEvent = {
timestamp: Date;
message: string;
target: string;
};
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 Client.
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. |
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',
});
adapter
Defines an instance of a driver adapter. See also Database drivers .
This is available from version 5.4.0 and newer behind the driverAdapters
feature flag.
Example
The example below uses the Neon driver adapter
import { Pool, neonConfig } from '@neondatabase/serverless';
import { PrismaNeon } from '@prisma/adapter-neon';
import { PrismaClient } from '@prisma/client';
import dotenv from 'dotenv';
import ws from 'ws';
dotenv.config();
neonConfig.webSocketConstructor = ws;
const connectionString = `${process.env.DATABASE_URL}`;
const pool = new Pool({ connectionString });
const adapter = new PrismaNeon(pool);
const prisma = new PrismaClient({ adapter });
rejectOnNotFound
Note: rejectOnNotFound
was removed in v5.0.0.
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. |
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!'),
},
},
});
transactionOptions
Note: transactionOptions
was introduced in v5.10.0.
Allows to set transaction options globally on the constructor level.
Remarks
- The transaction levels can be overridden on a per-transaction level.
Options
Option | Description |
---|---|
maxWait | The maximum amount of time Prisma Client will wait to acquire a transaction from the database. The default value is 2 seconds. |
timeout | The maximum amount of time the interactive transaction can run before being canceled and rolled back. The default value is 5 seconds. |
isolationLevel | Sets the transaction isolation level. By default this is set to the value currently configured in your database. The available can vary depending on the database you use. |
Example
const prisma = new PrismaClient({
transactionOptions: {
isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
maxWait: 5000, // default: 2000
timeout: 10000, // default: 5000
},
});
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 Client'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. - You cannot use filter conditions (e.g.
equals
,contains
,not
) to filter fields of the JSON data type. Using filter conditions will likely result in anull
response for that field.
Options
Name | Example type (User ) | Required | Description |
---|---|---|---|
where | UserWhereUniqueInput | Yes | Wraps all fields of a model so that a record can be selected (learn more). Before version 4.5.0, this type only wraps unique fields of a model. |
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0 |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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 |
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
)
Expand for example User model with a @@unique block
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
)
Expand for example User model with an @@id block
model User {
firstName String
lastName String
@@id([firstName, lastName])
}
const result = await prisma.user.findUnique({
where: {
firstName_lastName: {
firstName: 'Alice',
lastName: 'Smith',
},
},
});
findUniqueOrThrow()
findUniqueOrThrow()
retrieves a single record in the same way as findUnique()
. However, if the query does not find the requested record, it throws a PrismaClientKnownRequestError
.
Note that before Prisma v6, it would throw a NotFoundError: No User found error
.
Here’s an example of its usage:
await prisma.user.findUniqueOrThrow({
where: { id: 1 },
});
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 throws aPrismaClientKnownRequestError
, 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 |
---|---|---|---|
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0. |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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. |
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 |
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.
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()
findFirstOrThrow()
retrieves a single data record in the same way as findFirst()
. However, if the query does not find a record, it throws a PrismaClientKnownRequestError
.
Note that before Prisma v6, it would throw a 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 returnsPrismaClientKnownRequestError
, 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 |
---|---|---|---|
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. |
omit | XOR<PostOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0 |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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. |
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) |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
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. |
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0 |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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.
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()
or createManyAndReturn()
queries. 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 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
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 fields of a model so that a record can be selected (learn more). Before version 4.5.0, this type only wraps unique fields of a model. |
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0. |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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.
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()
This section covers the usage of the upsert()
operation. To learn about using nested upsert queries within update()
, reference the linked documentation.
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 fields of a model so that a record can be selected (learn more). Before version 4.5.0, this type only wraps unique fields of a model. |
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0 |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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 ORM hands over upsert queries to the database where possible. Learn more.
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 Client does an upsert, it first checks whether that record already exists in the database. To make this check, Prisma Client 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 Client creates that record.
- If the record exists, then Prisma Client 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 successfully 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 Client
- 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 ORM 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 @id
profileViews Int
userName String @unique
email 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 UPDATE
SET "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 fields of a model so that a record can be selected (learn more). Before version 4.5.0, this type only wraps unique fields of a model. |
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. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned object. In Preview since 5.13.0 |
relationLoadStrategy | 'join' or 'query' | No | Default: join . Specifies the load strategy for a relation query. Only available in combination with include (or select on a relation field). In Preview since 5.9.0. |
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
)
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. 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
- As of Prisma ORM version 5.12.0,
createMany()
is now supported by SQLite. - The
skipDuplicates
option is not supported by MongoDB, SQLServer, or SQLite. - You cannot create or connect relations by using nested
create
,createMany
,connect
,connectOrCreate
queries inside a top-levelcreateMany()
query. See here for a workaround. - You can use a nested
createMany
query inside anupdate()
orcreate()
query - for example, add aUser
and twoPost
records with a nestedcreateMany
at the same time.
Examples
Create several new users
const users = await prisma.user.createMany({
data: [
{ name: 'Sonali', email: 'sonali@prisma.io' },
{ name: 'Alex', email: 'alex@prisma.io' },
],
});
createManyAndReturn()
createManyAndReturn
creates multiple records and returns the resulting objects.
createManyAndReturn()
is only available in Prisma ORM version 5.14.0 and up.
createManyAndReturn()
is only available for PostgreSQL, CockroachDB, and SQLite.
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. 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 objects. |
omit | XOR<UserOmit, null> | No | Specifies which properties to exclude on the returned objects. In Preview since 5.13.0. Mutually exclusive with select . |
include | XOR<UserInclude, null> | No | Specifies which relations should be eagerly loaded on the returned objects. |
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 |
Remarks
- The
skipDuplicates
option is not supported by SQLite. - You cannot create or connect relations by using nested
create
,createMany
,connect
,connectOrCreate
queries inside a top-levelcreateManyAndReturn()
query. See here for a workaround. - When relations are included via
include
, a separate query is generated per relation. relationLoadStrategy: join
is not supported.
Return type
Return type | Example | Description |
---|---|---|
JavaScript array object (typed) | User[] | |
JavaScript array object (plain) | [{ name: "Sonali" }] | Use select , omit and include to determine which fields to return. |
Examples
Create and return several new users
const users = await prisma.user.createManyAndReturn({
data: [
{ name: 'Sonali', email: 'sonali@prisma.io' },
{ name: 'Alex', email: 'alex@prisma.io' },
],
})
[
{ "id": 0, "name": "Sonali", "email": "sonali@prisma.io", "profileViews": 0 },
{ "id": 1, "name": "Alex", "email": "alex@prisma.io", "profileViews": 0 }
]
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;
};
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 updatedUserCount = 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 deleted records. |
export type BatchPayload = {
count: number;
};
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. |
orderBy | XOR<Enumerable<PostOrder ByInput>, PostOrderByInput> | 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). |
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) |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
Return type
Return type | Example | Description |
---|---|---|
number | 29 | The count of records. |
UserCountAggregateOutputType | { _all: 27, name: 10 } | Returned if select is used. |
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). |
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) |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
_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. |
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> | string | 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. |
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) |
skip | number | No | Specifies how many of the returned objects in the list should be skipped. |
_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. |
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,
},
},
];
findRaw()
See: Using Raw SQL (findRaw()
).
aggregateRaw()
See: Using Raw SQL (aggregateRaw()
).
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.
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 example demonstrates how to use the validator
with select
:
const 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
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 example demonstrates how to use the validator
with include
:
const includePosts = Prisma.validator<Prisma.UserInclude>()({
posts: true,
});
Include a _count
of relations
const usersWithCount = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
});
omit
(Preview)
omit
defines which fields are excluded in the object that Prisma Client returns.
Because the omit
option is currently in Preview, you need to enable it via the omitApi
preview feature flag in your Prisma schema file:
generator client {
provider = "prisma-client-js"
previewFeatures = ["omitApi"]
}
After adding this flag, you need to run prisma generate
again to re-generate Prisma Client.
Remarks
- You cannot combine
omit
andselect
since they serve opposite purposes
Examples
Omit the password
field from all User
records
const result = await prisma.user.findMany({
omit: {
password: true,
},
});
Omit the title
fields from all User
's posts
relation
const results = await prisma.user.findMany({
omit: {
password: true,
},
include: {
posts: {
omit: {
title: true,
},
},
},
});
Generated types for omit
The following example demonstrates how to use the validator
with omit
:
const omitPassword = Prisma.validator<Prisma.UserOmit>()({
password: true,
});
relationLoadStrategy
(Preview)
relationLoadStrategy
specifies how a relation should be loaded from the database. It has two possible values:
join
(default): Uses a database-levelLATERAL JOIN
(PostgreSQL) or correlated subqueries (MySQL) and fetches all data with a single query to the database.query
: Sends multiple queries to the database (one per table) and joins them on the application level.
Note: Once
relationLoadStrategy
moves from Preview into General Availability,join
will universally become the default for all relation queries.
You can learn more about join strategies here.
Because the relationLoadStrategy
option is currently in Preview, you need to enable it via the relationJoins
preview feature flag in your Prisma schema file:
generator client {
provider = "prisma-client-js"
previewFeatures = ["relationJoins"]
}
After adding this flag, you need to run prisma generate
again to re-generate Prisma Client. This feature is currently available on PostgreSQL, CockroachDB and MySQL.
Remarks
- In most situations, the default
join
strategy will be more effective. Usequery
if you want to save resources on your database server or if you profiling shows that the application-level join is more performant. - You can only specify the
relationLoadStrategy
on the top-level in your query. The top-level choice will affect all nested sub-queries.
Examples
Load the posts
relation via a database-level JOIN when using include
const users = await prisma.user.findMany({
relationLoadStrategy: 'join',
include: {
posts: true,
},
});
Load the posts
relation via a database-level JOIN when using select
const users = await prisma.user.findMany({
relationLoadStrategy: 'join',
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).
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
.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.
// 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 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. |
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
For PostgreSQL, this feature is still in Preview. Enable the fullTextSearchPostgres
feature flag in order to use it.
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
Deduplicate a list of records from findMany
or findFirst
. See also: Aggregation, grouping, and summarizing
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'],
});
[
{ 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'],
});
[
{ 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'],
});
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 youcreate()
(prisma.user.create(...)
) a new parent record orupdate()
(prisma.user.update(...)
) an existing parent record.- You can use a nested
create
or a nestedcreateMany
to create multiple related records. If you require theskipDuplicates
query option you should usecreateMany
.
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 multiple 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',
},
],
},
},
});
Note: 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 youcreate()
(prisma.user.create(...)
) a new parent record orupdate()
(prisma.user.update(...)
) an existing parent record.- Available in the context of a one-to-many relation — for example, you can
prisma.user.create(...)
a user and use a nestedcreateMany
to create multiple posts (posts have one user). - Not available in the context of a many-to-many relation — for example, you cannot
prisma.post.create(...)
a post and use a nestedcreateMany
to create categories (many posts have many categories).
- Available in the context of a one-to-many relation — for example, you can
- You cannot nest an additional
create
orcreateMany
. - Allows setting foreign keys directly — for example, setting the
categoryId
on a post. - As of Prisma ORM version 5.12.0, nested
createMany
is supported by SQLite. - You can use a nested
create
or a nestedcreateMany
to create multiple related records - if you do not need theskipDuplicates
query option, you should probably usecreate
.
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. 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 |
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.
-
When using
set
andconnect
together, the order in which they are applied significantly impacts the result. Ifset
is used beforeconnect
, the connected records will only reflect the final state established by theconnect
operation, asset
clears all existing connections beforeconnect
establishes new ones. Conversely, ifconnect
is applied beforeset
, theset
operation will override theconnect
action by clearing all connected records and replacing them with its own specified state.
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,
},
});
However, you can't use both the direct approach and the connect
approach in the same query. See this issue comment for details.
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 toconnectOrCreate
a blog post tag namedcomputing
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',
},
},
},
},
})const createPost = await prisma.post.create({
data: {
title: 'How to handle schema drift in production',
content: '...',
author: {
connect: {
id: 15,
},
},
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 A Query hits server, starts transaction B Find record where tagName
equalscomputing
, record not foundFind record where tagName
equalscomputing
, record not foundCreate record where tagName
equalscomputing
and connectCreate record where tagName
equalscomputing
Unique violation, record already created by transaction B To work around this scenario, we recommend catching the unique violation exception (
PrismaClientKnownRequestError
, errorP2002
) 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:
- Creates a
Profile
- Attempts to connect the profile to a
User
where the email address isalice@prisma.io
- Creates a new 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:
- Attempts to connect the user to a
Profile
with anid
of20
- 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-levelupdate
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
This section covers the usage of nested upsert within update()
. To learn about the upsert()
operation, reference the linked documentation.
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 my second post' },
update: { title: 'This is my 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
- From version 4.3.0, you can also use these operators to compare fields in the same model with the
<model>.fields
property. - In versions before 4.3.0, you can compare fields in the same model with raw queries.
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',
},
});
Return all products with a quantity lower than the "warn quantity" threshold
This example compares fields of the same model which is available as of version 4.3.0.
const productsWithLowQuantity = await prisma.product.findMany({
where: {
quantity: {
lte: prisma.product.fields.warnQuantity
}
},
});
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',
},
},
});
not
will return all items that do not match a given value. However, if the column is nullable, NULL
values will not be returned. If you require null values to be returned, use an OR
operator to include NULL
values.
Return all users where name
does not equal "Eleanor"
including users where name
is NULL
await prisma.user.findMany({
where: {
OR: [
{ name: { not: 'Eleanor' } },
{ name: null }
]
}
})
in
Value n
exists in list.
null
values are not returned. For example, if you combine in
and NOT
to return a 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
.
const getUser = await prisma.user.findMany({
where: {
NOT: {
name: { in: ['Saqui', 'Clementine', 'Bob'] },
},
},
});
Get a User
record where at least one Post
has at least one specified Category
const getUser = await prisma.user.findMany({
where: {
// Find users where..
posts: {
some: {
// ..at least one (some) posts..
categories: {
some: {
// .. have at least one category ..
name: {
in: ['Food', 'Introductions'], // .. with a name that matches one of the following.
},
},
},
},
},
},
});
notIn
Value n
does not exist in list.
Remarks
null
values are not returned.
Examples
Get User
records where the id
can not be found in the following list: [22, 91, 14, 2, 5]
const getUser = await prisma.user.findMany({
where: {
id: { notIn: [22, 91, 14, 2, 5] },
},
});
lt
Value n
is less than x
.
Examples
Get all Post
records where likes
is less than 9
const getPosts = await prisma.post.findMany({
where: {
likes: {
lt: 9,
},
},
});
lte
Value n
is less than or equal to x
.
Examples
Get all Post
records where likes
is less or equal to 9
const getPosts = await prisma.post.findMany({
where: {
likes: {
lte: 9,
},
},
});
gt
Value n
is greater than x
.
Examples
Get all Post
records where likes
is greater than 9
const getPosts = await prisma.post.findMany({
where: {
likes: {
gt: 9,
},
},
});
gte
Value n
is greater than or equal to x
.
Examples
Get all Post
records where likes
is greater than or equal to 9
const getPosts = await prisma.post.findMany({
where: {
likes: {
gte: 9,
},
},
});
Examples
Get all Post
records where date_created
is greater than March 19th, 2020
const result = await prisma.post.findMany({
where: {
date_created: {
gte: new Date('2020-03-19T14:21:00+0200') /* Includes time offset for UTC */,
},
},
});
contains
Value n
contains x
.
Examples
Count all Post
records where content
contains databases
const result = await prisma.post.count({
where: {
content: {
contains: 'databases',
},
},
});
Count all Post
records where content
does not contain databases
const result = await prisma.post.count({
where: {
NOT: {
content: {
contains: 'databases',
},
},
},
});
search
Use Full-Text Search to search within a String
field.
For PostgreSQL, this feature is still in Preview. Enable the fullTextSearchPostgres
feature flag in order to use it.
Examples
Find all posts with a title that contains cat
or dog
.
const result = await prisma.post.findMany({
where: {
title: {
search: 'cat | dog',
},
},
});
Find all posts with a title that contains cat
and dog
.
const result = await prisma.post.findMany({
where: {
title: {
search: 'cat & dog',
},
},
});
Find all posts with a title that doesn't contain cat
.
const result = await prisma.post.findMany({
where: {
title: {
search: '!cat',
},
},
});
mode
Remarks
- Supported by the PostgreSQL and MongoDB connectors only
Examples
Get all Post
records where title
contains prisma
, in a case insensitive way
const result = await prisma.post.findMany({
where: {
title: {
contains: 'prisma',
mode: 'insensitive',
},
},
});
startsWith
Examples
Get all Post
records where title
starts with Pr
(such as Prisma
)
const result = await prisma.post.findMany({
where: {
title: {
startsWith: 'Pr',
},
},
});
endsWith
Get all User
records where email
ends with prisma.io
const result = await prisma.user.findMany({
where: {
email: {
endsWith: 'prisma.io',
},
},
});
AND
All conditions must return true
. Alternatively, pass a list of objects into the where
clause - the AND
operator is not required.
Examples
Get all Post
records where the content
field contains Prisma
and published
is false
const result = await prisma.post.findMany({
where: {
AND: [
{
content: {
contains: 'Prisma',
},
},
{
published: {
equals: false,
},
},
],
},
});
Get all Post
records where the content
field contains Prisma
and published
is false
(no AND
)
The following format returns the same results as the previous example without the AND
operator:
const result = await prisma.post.findMany({
where: {
content: {
contains: 'Prisma',
},
published: {
equals: false,
},
},
});
Get all Post
records where the title
field contains Prisma
or databases
, and published
is false
The following example combines OR
and AND
:
const result = await prisma.post.findMany({
where: {
OR: [
{
title: {
contains: 'Prisma',
},
},
{
title: {
contains: 'databases',
},
},
],
AND: {
published: false,
},
},
});
OR
One or more conditions must return true
.
Examples
Get all Post
records where the title
field contains Prisma
or databases
const result = await prisma.post.findMany({
where: {
OR: [
{
title: {
contains: 'Prisma',
},
},
{
title: {
contains: 'databases',
},
},
],
},
});
Get all Post
records where the title
field contains Prisma
or databases
, but not SQL
The following example combines OR
and NOT
:
const result = await prisma.post.findMany({
where: {
OR: [
{
title: {
contains: 'Prisma',
},
},
{
title: {
contains: 'databases',
},
},
],
NOT: {
title: {
contains: 'SQL',
},
},
},
});
Get all Post
records where the title
field contains Prisma
or databases
, and published
is false
The following example combines OR
and AND
:
const result = await prisma