CRUD (Reference)

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:

findFirst
findUnique
findMany
create
update
updateMany
upsert
delete
deleteMany
count

This page contains a detailed description for each query. Unless otherwise noted, the examples on this page are based on the following Prisma schema:

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

CRUD queries are exposed by the model properties on your PrismaClient instance. Taking the User and Post models from above as examples, you'd invoke the CRUD queries via the prisma.user and prisma.post model properties. For example:

await prisma.user.create({ data: { name: 'Alice' } })
// or
await prisma.post.findMany()

`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 previously findOne. 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: UserWhereUniqueInput
  select?: UserSelect | null
  include?: UserInclude | null
  rejectOnNotFound?: RejectOnNotFound
}

These are further relevant generated types:

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

Expand for a multi-field ID/unique example

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

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

The UserWhereUniqueInput input type looks slightly different:

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

Reference

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: number
  name: string | null
  email: string
  role: Role
  coinflips: boolean[]
  profileViews: number
}

Examples

Single-field ID or unique attribute

Retrieve the `User` record with an `id` of `42`

const result = await prisma.user.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 String
  lastName  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 String
  lastName  String
  @@unique(fields: [firstName, lastName], name: "fullname")
}

Note: This example of @@unique includes an optional name. If you do not specify a name, the field name in the generated client is firstName_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 attribute
      firstName: '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 | null
  include?: UserInclude | null
  where?: UserWhereInput
  orderBy?: Enumerable<UserOrderByInput> | UserOrderByInput
  cursor?: UserWhereUniqueInput
  take?: number
  skip?: number
  distinct?: Enumerable<UserDistinctFieldEnum>
  rejectOnNotFound?: RejectOnNotFound
}

These are further relevant generated types:

export type UserWhereInput = {
  id?: number | IntFilter | null
  name?: string | NullableStringFilter | null
  email?: string | StringFilter | null
  role?: Role | RoleFilter | null
  profileViews?: number | IntFilter | null
  posts?: PostFilter | null
  AND?: Enumerable<UserWhereInput> | null
  OR?: Enumerable<UserWhereInput> | null
  NOT?: Enumerable<UserWhereInput> | null
}
export type PostFilter = {
  every?: PostWhereInput | null
  some?: PostWhereInput | null
  none?: PostWhereInput | null
}
export type UserWhereUniqueInput = {
  id?: number | null
  email?: string | null
}
export type UserOrderByInput = {
  id?: SortOrder | null
  name?: SortOrder | null
  email?: SortOrder | null
  role?: SortOrder | null
  profileViews?: 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: number
  name: string | null
  email: string
  role: Role
  coinflips: 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'
2
3const prisma = new PrismaClient({})
4
5async function mainAsync() {
6  const a = await prisma.post.create({
7    data: {
8      title: 'A test 1',
9    },
10  })
11
12  const b = await prisma.post.create({
13    data: {
14      title: 'A test 2',
15    },
16  })
17
18  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  })
29
30  console.log(c)
31}
32
33mainAsync()

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 | null
  include?: UserInclude | null
  where?: UserWhereInput | null
  orderBy?: Enumerable<UserOrderByInput> | null
  cursor?: UserWhereUniqueInput | null
  take?: number | null
  skip?: number | null
  distinct?: Enumerable<UserDistinctFieldEnum>
}

These are further relevant generated types:

export type UserWhereInput = {
  id?: number | IntFilter | null
  name?: string | NullableStringFilter | null
  email?: string | StringFilter | null
  role?: Role | RoleFilter | null
  profileViews?: number | IntFilter | null
  posts?: PostFilter | null
  AND?: Enumerable<UserWhereInput> | null
  OR?: Enumerable<UserWhereInput> | null
  NOT?: Enumerable<UserWhereInput> | null
}
export type PostFilter = {
  every?: PostWhereInput | null
  some?: PostWhereInput | null
  none?: PostWhereInput | null
}
export type UserWhereUniqueInput = {
  id?: number | null
  email?: string | null
}
export type UserOrderByInput = {
  id?: SortOrder | null
  name?: SortOrder | null
  email?: SortOrder | null
  role?: SortOrder | null
  profileViews?: 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: number
  name: string | null
  email: string
  role: Role
  coinflips: boolean[]
  profileViews: number
}

Examples

Retrieve all User records where the name is Alice:

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

See filtering documentation for advanced examples.

create

The create query creates a new database record. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

create takes as input an object of the following type:

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

These are further relevant generated types:

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

Reference

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: number
  name: string | null
  email: string
  role: Role
  coinflips: boolean[]
  profileViews: number
}

Examples

Create a new record with the only required field `email`

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

Nested writes

You can also perform nested writes, such as:

Create a User and one or more connected Post records at the same time (see example)
Create a Post and connect it to an existing user, or create a new User record (see example)

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 | null
  include?: UserInclude | null
  data: UserUpdateInput
  where: UserWhereUniqueInput
}

These are further relevant generated types:

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

Reference

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: number
  name: string | null
  email: string
  role: Role
  coinflips: boolean[]
  profileViews: number
}

Examples

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

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

Nested writes

You can also perform nested writes during an update, such as:

Update a User and update one or more connected Post records at the same time (see example)
Update a User and connect it to one or more existing Post records, or add new Post records (see example)

`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 | null
  include?: UserInclude | null
  where: UserWhereUniqueInput
  create: UserCreateInput
  update: 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: number
  name: string | null
  email: string
  role: Role
  coinflips: boolean[]
  profileViews: number
}

Examples

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

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

delete

The delete query deletes an existing database record. Even though the record is being deleted, delete still returns the object that was deleted. You can use the select and include options to determine which properties should be included on the returned object.

Options

Type

delete takes as input an object of the following type:

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

These are further relevant generated types:

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

Expand for a multi-field ID/unique example

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

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

The UserWhereUniqueInput input looks slightly different:

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

Reference

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 a prisma.io email address, use deleteMany)

The type of the object that's returned by a delete API call depends on whether you use the select and include options.

If you use neither of these options, the return type will correspond to the TypeScript type that's generated for the model. For the User model from above, this type looks as follows:

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

Examples

Delete the User record with an id of 1:

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

updateMany

The updateMany query updates a batch of existing database records in bulk and returns the number of 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: UserUpdateManyMutationInput
  where?: UserWhereInput | null
}

These are further relevant generated types:

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

Reference

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 | null
  name?: string | NullableStringFilter | null
  email?: string | StringFilter | null
  role?: Role | RoleFilter | null
  profileViews?: number | IntFilter | null
  posts?: PostFilter | null
  AND?: Enumerable<UserWhereInput> | null
  OR?: Enumerable<UserWhereInput> | null
  NOT?: Enumerable<UserWhereInput> | null
}

Reference

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 | null
  where?: UserWhereInput
  orderBy?: Enumerable<UserOrderByInput>
  cursor?: UserWhereUniqueInput
  take?: number
  skip?: number
  distinct?: 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 users
      city: true, // Count users with cities
      name: true, // Count users with names
    }
  })

Show CLI output

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 @id
  tags  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 include databases:

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.

Overview: Race conditions

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 | null
  increment?: number
  decrement?: number
  multiply?: number
  divide?: 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,
  },
})

Was this helpful?

Edit this page on GitHub

CRUD

findUnique

Options

Type

Reference

Return type

Examples

Single-field ID or unique attribute

Retrieve the User record with an id of 42

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

Multi-field ID attribute

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

Multi-field unique attribute

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

findFirst

Options

Type

Reference

Return type

Examples

Retrieve the first User record where the name is Alice

Retrieve the first User record where the name is Alice and throw a NotFoundError if not found

Retrieve the first Post record where the title starsts with A test, reverse the list with take

findMany

Options

Type

Reference

Return type

Examples

create

Options

Type

Reference

Return type

Examples

Create a new record with the only required field email

Nested writes

update

Options

Type

Reference

Return type

Examples

Nested writes

upsert

Options

Type

Reference

Return type

Examples

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

delete

Options

Type

Reference

Return type

Examples

updateMany

Options

Type

Reference

Return type

Examples

deleteMany

Options

Type

Reference

Return type

Examples

count

Options

Type

Reference

Return type

Examples

Working with Json fields

Working with scalar lists / scalar arrays

Filtering scalar lists

NULL values in arrays

Atomic operations on update

`findUnique`

Retrieve the `User` record with an `id` of `42`

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

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

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

Retrieve the first `User` record where the `name` is `Alice`

Retrieve the first `User` record where the `name` is `Alice` and throw a `NotFoundError` if not found

Retrieve the first `Post` record where the `title` starsts with `A test`, reverse the list with `take`

Create a new record with the only required field `email`

`upsert`

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

`count`

Working with `Json` fields

`NULL` values in arrays

Increment all `view` and `likes` fields of all `Post` records by `1`

Set all `count` fields of all `Post` records to `0`