CRUD
Prisma Client offers out-of-the-box support for several CRUD queries. CRUD stands for:
- Create
- Read
- Update
- Delete
The following CRUD queries are available in Prisma Client:
This page contains a detailed description for each query. Unless otherwise noted, the examples on this page are based on the following Prisma schema:
model User {id Int @id @default(autoincrement())name String?email String @uniqueprofileViews Int @default(0)role Role @default(USER)coinflips Boolean[]posts Post[]}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}
CRUD queries are exposed by the model properties on your PrismaClient
instance. Taking the User
and Post
models from above as examples, you'd invoke the CRUD queries via the prisma.user
and prisma.post
model properties. For example:
await prisma.user.create({ data: { name: 'Alice' } })// orawait prisma.post.findMany()
findUnique
The findUnique
query lets you retrieve a single database record by ID or another unique attribute. You can use the select
and include
options to determine which properties should be included on the returned object.
findUnique
was previouslyfindOne
.findOne
was deprecated in version 2.12.0.
Options
Type
findUnique
takes an object with one of the following types as input:
export type UserFindUniqueArgs = {where: UserWhereUniqueInputselect?: UserSelect | nullinclude?: UserInclude | nullrejectOnNotFound?: RejectOnNotFound}
These are further relevant generated types:
export type UserWhereUniqueInput = {id?: number | nullemail?: string | null}
In case your model defines a multi-field ID or unique attribute such as the following:
model User {firstName StringlastName String@@id([firstName, lastName])}
The UserWhereUniqueInput
input type looks slightly different:
export type UserWhereUniqueInput = {firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null}export type FirstNameLastNameCompoundUniqueInput = {firstName: stringlastName: string}
Reference
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | No | Specifies which relations should be eagerly loaded on the returned object. |
rejectOnNotFound | RejectOnNotFound | No | If true , throw a NotFoundError: No User found error. |
Return type
findUnique
returns a plain old JavaScript object or null
. If rejectOnNotFound
is true
, findUnique
throws a NotFoundError
insted of returning null
.
The type of the object that a findUnique
API call returns depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Single-field ID or unique attribute
Retrieve the User
record with an id
of 42
const result = await prisma.user.findUnique({where: {id: 42,},})
Retrieve the User
record with an email
of alice@prisma.io
const result = await prisma.user.findUnique({where: {email: 'alice@prisma.io',},})
Multi-field ID attribute
Assume your model has a multi-field ID or unique attribute, e.g.:
model User {firstName StringlastName String@@id([firstName, lastName])}
Retrieve the User
record with firstName
of Alice
and lastName
of Smith
const result = await prisma.user.findUnique({where: {firstName_lastName: {firstName: 'Alice',lastName: 'Smith',},},})
Multi-field unique attribute
Assume your model has a multi-field unique attribute:
model User {firstName StringlastName String@@unique(fields: [firstName, lastName], name: "fullname")}
Note: This example of
@@unique
includes an optionalname
. If you do not specify a name, the field name in the generated client isfirstName_lastName
Retrieve the User
record with firstName
of Alice
and lastName
of Smith
const result = await prisma.user.findUnique({where: {fullname: {// name property of @@unique attributefirstName: 'Alice',lastName: 'Smith',},},})
findFirst
The findFirst
query returns the first record in a list that matches your criteria. findFirst
accepts the same arguments as findMany
.
Options
Type
findFirst
takes as input an object of the following type:
export type FindFirstUserArgs = {select?: UserSelect | nullinclude?: UserInclude | nullwhere?: UserWhereInputorderBy?: Enumerable<UserOrderByInput> | UserOrderByInputcursor?: UserWhereUniqueInputtake?: numberskip?: numberdistinct?: Enumerable<UserDistinctFieldEnum>rejectOnNotFound?: RejectOnNotFound}
These are further relevant generated types:
export type UserWhereInput = {id?: number | IntFilter | nullname?: string | NullableStringFilter | nullemail?: string | StringFilter | nullrole?: Role | RoleFilter | nullprofileViews?: number | IntFilter | nullposts?: PostFilter | nullAND?: Enumerable<UserWhereInput> | nullOR?: Enumerable<UserWhereInput> | nullNOT?: Enumerable<UserWhereInput> | null}export type PostFilter = {every?: PostWhereInput | nullsome?: PostWhereInput | nullnone?: PostWhereInput | null}export type UserWhereUniqueInput = {id?: number | nullemail?: string | null}export type UserOrderByInput = {id?: SortOrder | nullname?: SortOrder | nullemail?: SortOrder | nullrole?: SortOrder | nullprofileViews?: SortOrder | null}export declare const SortOrder: {asc: 'asc'desc: 'desc'}
Reference
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 | Enumerable<UserOrderByInput> | No | Lets you order the returned list by any property. |
skip | string | 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. 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. |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | 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. |
rejectOnNotFound | RejectOnNotFound | No | If true , throw a NotFoundError: No User found error. |
Return type
findFirst
returns a plain old JavaScript object or null
. If rejectOnNotFound
is true
, findFirst
throws a NotFoundError
insted of returning null
.
The type of the object returned by the findFirst
API call depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Retrieve the first User
record where the name
is Alice
const user = await prisma.user.findFirst({where: { name: 'Alice' },})
See filtering documentation for advanced examples.
Retrieve the first User
record where the name
is Alice
and throw a NotFoundError
if not found
const user = await prisma.user.findFirst({where: { name: 'Alice' },rejectOnNotFound: true,})
Retrieve the first Post
record where the title
starsts with A test
, reverse the list with take
Providing a negative value for take
when you use a findFirst
query reverses the order of the list. Use the following example to experiment - the value of c
changes depending on whether or not take
is negative or positive:
"script.ts"
1import { PrismaClient } from '@prisma/client'23const prisma = new PrismaClient({})45async function mainAsync() {6 const a = await prisma.post.create({7 data: {8 title: 'A test 1',9 },10 })1112 const b = await prisma.post.create({13 data: {14 title: 'A test 2',15 },16 })1718 const c = await prisma.post.findFirst({19 where: {20 title: {21 startsWith: 'A test',22 },23 },24 orderBy: {25 title: 'asc',26 },27 take: -1,28 })2930 console.log(c)31}3233mainAsync()
findMany
The findMany
query returns a list of records. You can use the select
and include
options to determine which properties the returned objects should include. You can also paginate, filter, and order the list.
Options
Type
findMany
takes as input an object of the following type:
export type UserFindManyArgs = {select?: UserSelect | nullinclude?: UserInclude | nullwhere?: UserWhereInput | nullorderBy?: Enumerable<UserOrderByInput> | nullcursor?: UserWhereUniqueInput | nulltake?: number | nullskip?: number | nulldistinct?: Enumerable<UserDistinctFieldEnum>}
These are further relevant generated types:
export type UserWhereInput = {id?: number | IntFilter | nullname?: string | NullableStringFilter | nullemail?: string | StringFilter | nullrole?: Role | RoleFilter | nullprofileViews?: number | IntFilter | nullposts?: PostFilter | nullAND?: Enumerable<UserWhereInput> | nullOR?: Enumerable<UserWhereInput> | nullNOT?: Enumerable<UserWhereInput> | null}export type PostFilter = {every?: PostWhereInput | nullsome?: PostWhereInput | nullnone?: PostWhereInput | null}export type UserWhereUniqueInput = {id?: number | nullemail?: string | null}export type UserOrderByInput = {id?: SortOrder | nullname?: SortOrder | nullemail?: SortOrder | nullrole?: SortOrder | nullprofileViews?: SortOrder | null}export declare const SortOrder: {asc: 'asc'desc: 'desc'}
Reference
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 | Enumerable<UserOrderByInput> | No | Lets you order the returned list by any property. |
skip | string | 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 (+ve value) or end (-ve value) either of the list or from the cursor position if mentioned) |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | 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
findMany
returns an array of plain old JavaScript objects.
The type of the objects returned by the findMany
API call depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Retrieve all User
records where the name
is Alice
:
const user = await prisma.user.findMany({where: { name: 'Alice' },})
See filtering documentation for advanced examples.
create
The create
query creates a new database record. You can use the select
and include
options to determine which properties should be included on the returned object.
Options
Type
create
takes as input an object of the following type:
export type UserCreateArgs = {select?: UserSelect | nullinclude?: UserInclude | nulldata: UserCreateInput}
These are further relevant generated types:
export type UserCreateInput = {name?: string | nullemail: stringrole?: Role | nullprofileViews: numbercoinflips?: UserCreatecoinflipsInput | nullposts?: PostCreateManyWithoutAuthorInput | null}export type UserCreatecoinflipsInput = {set?: Enumerable<boolean> | null}export type PostCreateManyWithoutAuthorInput = {create?: Enumerable<PostCreateWithoutAuthorInput> | nullconnect?: Enumerable<PostWhereUniqueInput> | null}export type PostCreateWithoutAuthorInput = {title: stringpublished?: boolean | nullcomments?: object | null}export type PostWhereUniqueInput = {id?: number | null}
Reference
Name | Type | Required | Description |
---|---|---|---|
data | UserCreateInput | 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 | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
create
returns a plain old JavaScript object.
The type of the object that's returned by a create
API call depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Create a new record with the only required field email
const user = await prisma.user.create({data: { email: 'alice@prisma.io' },})
Nested writes
You can also perform nested writes, such as:
update
The update
query updates an existing database record. You can use the select
and include
options to determine which properties should be included on the returned object.
To perform arithmeic operations (add, subtract, multiply, divide), use atomic updates to prevent race conditions.
Options
Type
update
takes as input an object of the following type:
export type UserUpdateArgs = {select?: UserSelect | nullinclude?: UserInclude | nulldata: UserUpdateInputwhere: UserWhereUniqueInput}
These are further relevant generated types:
export type UserWhereUniqueInput = {id?: number | nullemail?: string | null}export type UserUpdateInput = {id?: number | nullname?: string | nullemail?: string | nullrole?: Role | nullprofileViews?: number | nullcoinflips?: UserUpdatecoinflipsInput | nullposts?: PostUpdateManyWithoutAuthorInput | null}export type UserUpdatecoinflipsInput = {set?: Enumerable<boolean> | null}export type PostUpdateManyWithoutAuthorInput = {create?: Enumerable<PostCreateWithoutAuthorInput> | nullconnect?: Enumerable<PostWhereUniqueInput> | nullset?: Enumerable<PostWhereUniqueInput> | nulldisconnect?: Enumerable<PostWhereUniqueInput> | nulldelete?: Enumerable<PostWhereUniqueInput> | nullupdate?: Enumerable<PostUpdateWithWhereUniqueWithoutAuthorInput> | nullupdateMany?: Enumerable<PostUpdateManyWithWhereNestedInput> | nulldeleteMany?: Enumerable<PostScalarWhereInput> | nullupsert?: Enumerable<PostUpsertWithWhereUniqueWithoutAuthorInput> | null}
Reference
Name | Type | Required | Description |
---|---|---|---|
data | UserUpdateInput | 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. |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
update
returns a plain old JavaScript object or throws an exception (RecordNotFound
).
The type of the object that an update
API call returns depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Update the email
of the User
record with id
of 1
to alice@prisma.io
:
const user = await prisma.user.update({where: { id: 1 },data: { email: 'alice@prisma.io' },})
Nested writes
You can also perform nested writes during an update, such as:
upsert
The upsert
query updates an existing or creates a new database record. You can use the select
and include
options to determine which properties should be included on the returned object.
To perform arithmeic operations (add, subtract, multiply, divide), use atomic updates to prevent race conditions.
Options
Type
upsert
takes as input an object of the following type:
export type UserUpsertArgs = {select?: UserSelect | nullinclude?: UserInclude | nullwhere: UserWhereUniqueInputcreate: UserCreateInputupdate: UserUpdateInput}
Refer to findUnique
, create
and update
to see what the generated types UserWhereUniqueInput
, UserCreateInput
and UserUpdateInput
types look like.
Reference
Name | Type | Required | Description |
---|---|---|---|
create | UserCreateInput | 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 | UserUpdateInput | 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. |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
upsert
returns a plain old JavaScript object.
The type of the object that an upsert
API call returns depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Update (if exists) or create a new User
record with an email
of alice@prisma.io
const user = await prisma.user.upsert({where: { id: 1 },update: { email: 'alice@prisma.io' },create: { email: 'alice@prisma.io' },})
delete
The delete
query deletes an existing database record. Even though the record is being deleted, delete
still returns the object that was deleted. You can use the select
and include
options to determine which properties should be included on the returned object.
Options
Type
delete
takes as input an object of the following type:
export type UserFindUniqueArgs = {where: UserWhereUniqueInputselect?: UserSelect | nullinclude?: UserInclude | null}
These are further relevant generated types:
export type UserWhereUniqueInput = {id?: number | nullemail?: string | null}
In case your model has a multi-field ID or unique attribute such as the following:
model User {firstName StringlastName String@@id([firstName, lastName])}
The UserWhereUniqueInput
input looks slightly different:
export type UserWhereUniqueInput = {firstName_lastName?: FirstNameLastNameCompoundUniqueInput | null}export type FirstNameLastNameCompoundUniqueInput = {firstName: stringlastName: string}
Reference
Name | Type | Required | Description |
---|---|---|---|
where | UserWhereUniqueInput | Yes | Wraps all unique fields of a model so that individual records can be selected. |
select | UserSelect | No | Specifies which properties to include on the returned object. |
include | UserInclude | No | Specifies which relations should be eagerly loaded on the returned object. |
Return type
delete
returns a plain old JavaScript object or throws a RecordNotFound
exception if the unique identifier does not exist:
$Error for binding '0': RecordNotFound("Record to delete does not exist.")
Tip: If you need to delete multiple records based on some criteria (for example, all
User
records with aprisma.io
email address, usedeleteMany
)
The type of the object that's returned by a delete
API call depends on whether you use the select
and include
options.
If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User
model from above, this type looks as follows:
export type User = {id: numbername: string | nullemail: stringrole: Rolecoinflips: boolean[]profileViews: number}
Examples
Delete the User
record with an id
of 1
:
const user = await prisma.user.delete({where: { id: 1 },})
updateMany
The updateMany
query updates a batch of existing database records in bulk and returns the number of updated records. You can filter the list of records to be updated.
To perform arithmeic operations (add, subtract, multiply), use atomic updates to prevent race conditions.
Options
Type
updateMany
takes as input an object of the following type:
export type UserUpdateManyArgs = {data: UserUpdateManyMutationInputwhere?: UserWhereInput | null}
These are further relevant generated types:
export type UserUpdateManyMutationInput = {id?: number | nullname?: string | nullemail?: string | nullrole?: Role | nullprofileViews?: number | nullcoinflips?: UserUpdatecoinflipsInput | null}export type UserUpdatecoinflipsInput = {set?: Enumerable<boolean> | null}export type UserWhereInput = {id?: number | IntFilter | nullname?: string | NullableStringFilter | nullemail?: string | StringFilter | nullrole?: Role | RoleFilter | nullprofileViews?: number | IntFilter | nullposts?: PostFilter | nullAND?: Enumerable<UserWhereInput> | nullOR?: Enumerable<UserWhereInput> | nullNOT?: Enumerable<UserWhereInput> | null}
Reference
Name | Type | Required | Description |
---|---|---|---|
data | UserUpdateManyMutationInput | 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
updateMany
returns an object of type BatchPayload
, which is defined as follows:
export type BatchPayload = {count: number}
The value of count
is an integer and represents the number of records that were updated.
Examples
Update all User
records where the name
is Alice
to ALICE
:
const updatedUserCount = await prisma.user.updateMany({where: { name: 'Alice' },data: { name: 'ALICE' },})
deleteMany
The deleteMany
query deletes a batch of existing database records in bulk and returns the number of deleted records. You can filter the list of records to be deleted.
Options
Type
deleteMany
takes as input an object of the following type:
export type UserWhereInput = {id?: number | IntFilter | nullname?: string | NullableStringFilter | nullemail?: string | StringFilter | nullrole?: Role | RoleFilter | nullprofileViews?: number | IntFilter | nullposts?: PostFilter | nullAND?: Enumerable<UserWhereInput> | nullOR?: Enumerable<UserWhereInput> | nullNOT?: Enumerable<UserWhereInput> | null}
Reference
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
deleteMany
returns an object of type BatchPayload
, which is defined as follows:
export type BatchPayload = {count: number}
The value of count
is an integer and represents the number of records that were deleted.
Examples
Delete all User
records where the name
is Alice
:
const deletedUserCount = await prisma.user.deleteMany({where: { name: 'Alice' },})
count
Use the count()
method on any model property on your PrismaClient
instance to return the number of available records. You can filter the list of records to be counted.
Options
Type
count
an object of the following type as input:
export type UserFindManyArgs = {select?: UserSelect | nullwhere?: UserWhereInputorderBy?: Enumerable<UserOrderByInput>cursor?: UserWhereUniqueInputtake?: numberskip?: numberdistinct?: Enumerable<UserScalarFieldEnum>}
Reference
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 | Enumerable<UserOrderByInput> | No | Lets you order the returned list by any property. |
skip | string | 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 (+ve value) or end (-ve value) either of the list or from the cursor position if mentioned) |
select | UserSelect | No | Specifies which properties to include 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
count
either returns an number
or a plain old Javascript object (if select
is used in 2.15.0 and later).
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,},},},})
Count all users, all users with cities, and all users with names:
const countUsers = await prisma.user.count({select: {_all: true, // Count all userscity: true, // Count users with citiesname: true, // Count users with names}})
Working with Json
fields
See Working with Json.
Working with scalar lists / scalar arrays
The following example demonstrates how to set the value of a scalar list (coinflips
) when you create a model:
const createdUser = await prisma.user.create({data: {email: 'eloise@prisma.io',coinflips: [true, true, true, false, true],},})
Scalar lists only support setting a value - you cannot add or remove elements, only overwrite the entire value. The following example retrieves user, uses push()
to add three new coin flips, and overwrites the coinflips
property in an update
:
const user = await prisma.user.findUnique({where: {email: 'eloise@prisma.io',},})if (user) {console.log(user.coinflips)user.coinflips.push(true, true, false)const updatedUser = await prisma.user.update({where: {email: 'eloise@prisma.io',},data: {coinflips: user.coinflips,},})console.log(updatedUser.coinflips)}
Filtering scalar lists
Note: Only available for PostgreSQL in 2.15.0 and later.
In 2.15.0 and later (Postgres only), you can filter scalar lists - for example, a list of String
or Enum
types. The following table describes available conditions:
Condition | Description |
---|---|
has | Value exists in list. |
hasEvery | All values exist in list. |
hasSome | At least one value exists in list. |
isEmpty | List is empty. |
Exmples are based on the following schema:
model Post {id Int @idtags String[]}
The following example returns all posts where the tags list includes databases
:
const posts = await client.post.findMany({where: {tags: {has: 'databases',},},})
The following example returns all posts where the tags list includes databases
and typescript
:
const posts = await prisma.post.findMany({where: {tags: {hasEvery: ['databases', 'typescript'],},},})
const posts = await prisma.post.findMany({where: {tags: {equals: ['databases', 'typescript'],},},})
NULL
values in arrays
Array fields with a NULL
value are not included in the results returned by the following conditions:
NOT
(array does not contain X)isEmpty
(array is empty)
This means that records you might expect to see are not returned. Consider the following examples:
The following query returns all posts where the
tags
do not includedatabases
:const posts = await prisma.post.findMany({where: {NOT: {tags: {has: "databases"}}}})✔ Arrays that do not contain
"databases"
, such as{"typescript", "graphql"}
✔ Empty arrays, such as
{}
The query does not return:
✘
NULL
arrays, even though they do not contain"databases"
The following query returns all posts where
tags
is empty:const posts = await prisma.post.findMany({where: {tags: {isEmpty: true}}})The query returns:
✔ Empty arrays, such as
{}
The query does not return:
✘
NULL
arrays, even though they could be considered empty
To work around this issue, you can set the default value of array fields to {}
at a database level (Prisma schema does not currently support default values for arrays.)
Atomic operations on update
Atomic operations on update is available for Float
and Int
field types. This feature allows you to update a field based on its current value (such as subtracting or dividing) without risking a race condition.
A race conditions occurs when two or more operations must be done in sequence in order to complete a task. In the following example, two clients try to increase the same field (postCount
) by one:
Client | Operation | Value |
---|---|---|
Client 1 | Get field value | 21 |
Client 2 | Get field value | 21 |
Client 2 | Set field value | 22 |
Client 1 | Set field value | 22 ✘ |
The value should be 23
, but the two clients did not read and write to the postCount
field in sequence. Atomic operations on update combine read and write into a single operation, which prevents a race condition:
Client | Operation | Value |
---|---|---|
Client 1 | Get and set field value | 21 → 22 |
Client 2 | Get and set field value | 22 → 23 ✔ |
Reference
Option | Description |
---|---|
increment | Adds n to the current value. |
decrement | Subtacts n from the current value. |
multiply | Multiplies the current value by n . |
divide | Divides the current value by n . |
set | Sets the current field value. Identical to { myField : n } . |
Note: You can only perform one atomic update per field, per query.
Int
and Float
fields accept objects with the following type as input:
export type NullableIntFieldUpdateOperationsInput = {set?: number | nullincrement?: numberdecrement?: numbermultiply?: numberdivide?: number}
Examples
Increment all view
and likes
fields of all Post
records by 1
const updatePosts = await prisma.post.updateMany({data: {views: {increment: 1,},likes: {increment: 1,},},})
Set all count
fields of all Post
records to 0
const updatePosts = await prisma.post.updateMany({data: {views: {set: 0,},},})
Can also be written as:
const updatePosts = await prisma.post.updateMany({data: {views: 0,},})