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, replace db with the name of your datasource block.

Examples

Programmatically override a datasource url

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: 'file:./dev_qa.db',
    },
  },
})

Based on the following datasource block:

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

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:

index.d.ts

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:

index.d.ts

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)
  })

Show CLI results

Log a query event to console

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  log: [{ level: 'query', emit: 'event' }],
})

prisma.$on('query', (e) => {
  console.log(e)
})

async function main() {
  const countUsers = await prisma.user.count({})
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

Show CLI results

Log info, warn, and error events to console

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  log: [
    { level: 'warn', emit: 'event' },
    { level: 'info', emit: 'event' },
    { level: 'error', emit: 'event' },
  ],
})

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)
  })

Show CLI results

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 .

info

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

info

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 both findUnique() and findFirst

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

info

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 same select and where 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 a null response for that field.

Options

Name	Example type (User)	Required	Description
where	UserWhereUniqueInput	Yes	Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more
select	XOR<UserSelect, null>	No	Specifies which properties to include on the returned object.
include	XOR<UserInclude, null>	No	Specifies which relations should be eagerly loaded on the returned object.
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.
rejectOnNotFound (deprecated)	RejectOnNotFound	No	If true, throw a NotFoundError: No User found error. You can also configure rejectOnNotFound globally. Note: rejectOnNotFoundis deprecated in v4.0.0. From v4.0.0, use findUniqueOrThrow instead.

Return type

Return type	Example	Description
JavaScript object (typed)	User
JavaScript object (plain)	{ title: "Hello world" }	Use select and include to determine which fields to return.
null	null	Record not found
Error		If rejectOnNotFound is true, findUnique() throws an error (NotFoundError by default, customizable globally) instead of returning null.

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()

info

We introduced findUniqueOrThrow in v4.0.0. It replaces the rejectOnNotFound option. rejectOnNotFound is deprecated in v4.0.0.

findUniqueOrThrow retrieves a single data record in the same way as findUnique(). However, if the query does not find a record, it returns NotFoundError: No User found error.

findUniqueOrThrow differs from findUnique() as follows:

• Its return type is non-nullable. For example, post.findUnique() can return post or null, but post.findUniqueOrThrow() always returns post.

• It is not compatible with sequential operations in the $transaction API. If the query returns NotFoundError, 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<User OrderByInput>,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. 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.
skip	number	No	Specifies how many of the returned objects in the list should be skipped.
distinct	Enumerable<UserDistinct FieldEnum>	No	Lets you filter out duplicate rows by a specific field - for example, return only distinct Post titles.
rejectOnNotFound (deprecated)	RejectOnNotFound	No	If true, throw a NotFoundError: No User found error. You can also configure rejectOnNotFound globally. Note: rejectOnNotFoundis deprecated in v4.0.0. From v4.0.0, use findFirstOrThrow instead.

Return type

Return type	Example	Description
JavaScript object (typed)	User	Specifies which properties to include on the returned object.
JavaScript object (plain)	{ title: "Hello world" }	Use select and include to determine which fields to return.
null	null	Record not found
Error		If rejectOnNotFound is true, findUnique() throws an error (NotFoundError by default, customizable globally) instead of returning null.

Remarks

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

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()

info

We introduced findFirstOrThrow in v4.0.0. It replaces the rejectOnNotFound option. rejectOnNotFound is deprecated in v4.0.0.

findFirstOrThrow retrieves the first record in a list in the same way as findFirst. However, if the query does not find a record, it returns NotFoundError: No User found error.

findFirstOrThrow differs from findFirst as follows:

• Its return type is non-nullable. For example, post.findFirst() can return post or null, but post.findFirstOrThrow always returns post.

• It is not compatible with sequential operations in the $transaction API. If the query returns NotFoundError, 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 a User and two Post 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)
  })

Show CLI results

prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"
prisma:query COMMIT
prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3
prisma:query COMMIT

update()

update updates an existing database record.

Options

Name	Type	Required	Description
data	XOR<UserUpdateInput UserUncheckedUpdateInput>	Yes	Wraps all the fields of the model so that they can be provided when updating an existing record. Fields that are marked as optional or have default values in the datamodel are optional.
where	UserWhereUniqueInput	Yes	Wraps all unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more
select	XOR<UserSelect, null>	No	Specifies which properties to include on the returned object.
include	XOR<UserInclude, null>	No	Specifies which relations should be eagerly loaded on the returned object.
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()

info

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 unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more
select	XOR<UserSelect, null>	No	Specifies which properties to include on the returned object.
include	XOR<UserInclude, null>	No	Specifies which relations should be eagerly loaded on the returned object.
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's create and update 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's where option
• The unique field in the where option and the unique field in the create 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 unique fields of a model so that individual records can be selected. From version 4.5.0, this type wraps all fields of a model. Learn more
select	XOR<UserSelect, null>	No	Specifies which properties to include on the returned object.
include	XOR<UserInclude, null>	No	Specifies which relations should be eagerly loaded on the returned object.
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 a prisma.io email address, use deleteMany)

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,
  },
})

Show CLI results

{ "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-level createMany() query.
• You can use a nested createMany query inside an update() or create() query - for example, add a User and two Post records with a nested createMany 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.

info

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-level createManyAndReturn() query.
• 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' },
  ],
})

Show CLI results

[
  { "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,
  },
})

Show CLI results

Return _sum of all profileViews for all User records

const setValue = await prisma.user.aggregate({
  _sum: {
    profileViews: true,
  },
})

Show CLI results

groupBy()

See also: Aggregation, grouping, and summarizing

Options

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,
      },
    },
  },
})

Show CLI results

;[
  {
    country: 'Denmark',
    city: 'Copenhagen',
    _sum: { profileViews: 490 },
    _count: {
      _all: 70,
      city: 8,
    },
  },
  {
    country: 'Sweden',
    city: 'Stockholm',
    _sum: { profileViews: 500 },
    _count: {
      _all: 50,
      city: 3,
    },
  },
]

findRaw()

See: Using Raw SQL (findRaw()).

aggreagateRaw()

See: Using Raw SQL (aggreagateRaw()).

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 and include 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,
  },
})

Show CLI results

Select the email and role fields of a multiple User records

const result = await prisma.user.findMany({
  select: {
    email: true,
    role: true,
  },
})

Show CLI results

Select a _count of relations

const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: { posts: true },
    },
  },
})

Show CLI results

Select the 'id' and 'title' fields of related Post records

const result = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
    },
  },
})

Show CLI results

include inside select

const result = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      include: {
        author: true,
      },
    },
  },
})

Show CLI results

Generated types for select

The following 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 },
    },
  },
})

Show CLI results

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 and select since they serve opposite purposes

Examples

Omit the password field from all User records

const result = await prisma.user.findMany({
  omit: {
    password: true,
  },
})

Show CLI results

Omit the title fields from all User's posts relation

const results = await prisma.user.findMany({
  omit: {
    password: true,
  },
  include: {
    posts: {
      omit: {
        title: true,
      },
    },
  },
})

Show CLI results

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-level LATERAL 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. Use query 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 unique where field (an @id or another assigned @unique) and updates any field on the Post model except the Author. The Author is the scalar field on the Post model.

  const updatePostByIdWithoutAuthor =
    Prisma.validator<Prisma.PostUpdateWithWhereUniqueWithoutAuthorInput>()({
      where: {
        id: 1,
      },
      data: {
        content: 'This is some updated content',
        published: true,
        title: 'This is a new title',
      },
    })

• PostUpsertWithWhereUniqueWithoutAuthorInput - This type will update the Post records title field where the id matches, if it doesn't exist it will create it instead.

  const updatePostTitleOrCreateIfNotExist =
    Prisma.validator<Prisma.PostUpsertWithWhereUniqueWithoutAuthorInput>()({
      where: {
        id: 1,
      },
      update: {
        title: 'This is a new title',
      },
      create: {
        id: 1,
        title: 'If the title doesnt exist, then create one with this text',
      },
    })

• PostUpdateManyWithWhereWithoutAuthorInput - This type will update all Post records where published is set to false.

  const publishAllPosts =
    Prisma.validator<Prisma.PostUpdateManyWithWhereWithoutAuthorInput>()({
      where: {
        published: {
          equals: false,
        },
      },
      data: {
        published: true,
      },
    })

orderBy

Sorts a list of records. See also: Sorting

Remarks

• 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

warning

This feature is available from version 3.5.0 onwards in PostgreSQL and MySQL only. You'll need to use the fullTextSearch preview flag to enable this feature.

The following query orders posts by relevance of the search term 'database' to the title:

const posts = await prisma.post.findMany({
  orderBy: {
    _relevance: {
      fields: ['title'],
      search: 'database',
      sort: 'asc'
    },
})

Sort User by the posts count

The following query orders users by post count:

const getActiveusers = await prisma.user.findMany({
  orderBy: {
    posts: {
      count: 'desc',
    },
  },
})

Sort User by multiple fields - email and role

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

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

Show CLI results

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

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

Show CLI results

Sort User by email, select name and email

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

const users3 = await prisma.user.findMany({
  orderBy: {
    email: 'asc',
  },
  select: {
    name: true,
    email: true,
  },
})

Show CLI results

Sort User records by email and sort nested Post records by title

The following example:

• Returns all User records sorted by email
• For each User record, returns the title field of all nested Post records sorted by title

const usersWithPosts = await prisma.user.findMany({
  orderBy: {
    email: 'asc',
  },
  include: {
    posts: {
      select: {
        title: true,
      },
      orderBy: {
        title: 'asc',
      },
    },
  },
})

Show CLI results

Sort one user's nested list of Post records

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

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

Show CLI results

Sort by enum

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

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

Show CLI results

Generated types for orderBy

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

• UserOrderByInput

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

distinct

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'],
})

Show CLI results

;[
  { city: 'Paris', country: 'France' },
  { city: 'Lyon', country: 'France' },
]

Select distinct on multiple fields

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

const distinctCitiesAndCountries = await prisma.user.findMany({
  select: {
    city: true,
    country: true,
  },
  distinct: ['city', 'country'],
})

Show CLI results

;[
  { city: 'Paris', country: 'France' },
  { city: 'Paris', country: 'Denmark' },
  { city: 'Lyon', country: 'France' },
]

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

Select distinct in combination with a filter

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

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

Show CLI results

Nested queries

create

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

Remarks

• create is available as a nested query when you create() (prisma.user.create(...)) a new parent record or update() (prisma.user.update(...)) an existing parent record.
• You can use a nested create or a nested createMany to create multiple related records. If you require the skipDuplicates query option you should use createMany.

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 you create() (prisma.user.create(...)) a new parent record or update() (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 nested createMany to create multiple posts (posts have one user).
  • Not available in the context of a many-to-many relation — for example, you cannot prisma.post.create(...) a post and use a nested createMany to create categories (many posts have many categories).
• You cannot nest an additional create or createMany.
• 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 nested createMany to create multiple related records - if you do not need the skipDuplicates query option, you should probably use create.

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.

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 to connectOrCreate a blog post tag named computing at the same time (tag names must be unique):

  Query A

  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',
          },
        },
      },
    },
  })

  Query B

  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 equals computing, record not found
  Find record where tagName equals computing, record not found
  	Create record where tagName equals computing and connect
  Create record where tagName equals computing
  Unique violation, record already created by transaction B

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

Examples

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

The following example:

1. Creates a Profile
2. Attempts to connect the profile to a User where the email address is alice@prisma.io
3. Creates a new user if a matching user does not exist

const user = await prisma.profile.create({
  data: {
    bio: 'The coolest Alice on the planet',
    user: {
      connectOrCreate: {
        where:  { email: 'alice@prisma.io' },
        create: { email: 'alice@prisma.io'}
    },
  },
})

Create a new Post record and connect it to an existing User record, or create a new User

const user = await prisma.post.create({
  data: {
    title: 'Hello World',
    author: {
      connectOrCreate: {
        where: { email: 'alice@prisma.io' },
        create: { email: 'alice@prisma.io' },
      },
    },
  },
})

Update an existing User record by connecting it to an existing Profile record, or creating a new Profile record

The following example:

1. Attempts to connect the user to a Profile with an id of 20
2. Creates a new profile if a matching profile does not exist

const updateUser = await prisma.user.update({
  where: { email: 'alice@prisma.io' },
  data: {
    profile: {
      connectOrCreate: {
        where: { id: 20 },
        create: {
          bio: 'The coolest Alice in town',
        },
      },
    },
  },
})

Update an existing User record by connect it to two existing Post records, or creating two new Post records

const user = await prisma.user.update({
  where: { email: 'alice@prisma.io' },
  data: {
    posts: {
      connectOrCreate: [
        {
          where: { id: 32 },
          create: { title: 'This is my first post' },
        },
        {
          where: { id: 19 },
          create: { title: 'This is my second post' },
        },
      ],
    },
  },
})

disconnect

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

Remarks

• disconnect is only available if the relation is optional.

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

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

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

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

Examples

Update an existing User record by disconnecting the Profile record it's connected to

const user = await prisma.user.update({
  where: { email: 'bob@prisma.io' },
  data: {
    profile: {
      disconnect: true,
    },
  },
})

Update an existing User record by disconnecting two Post records it's connected to

const user = await prisma.user.update({
  where: { email: 'alice@prisma.io' },
  data: {
    posts: {
      disconnect: [{ id: 44 }, { id: 46 }],
    },
  },
})

update

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

Remarks

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

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

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

• If the related record that you want to update does not exist, Prisma Client throws an exception:

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

Examples

Update an existing User record by updating the Profile record it's connected to

const user = await prisma.user.update({
  where: { email: 'alice@prisma.io' },
  data: {
    profile: {
      update: { bio: 'Hello World' },
    },
  },
})

Update an existing User record by updating two Post records it's connected to

const user = await prisma.user.update({
  where: { email: 'alice@prisma.io' },
  data: {
    posts: {
      update: [
        {
          data: { published: true },
          where: { id: 32 },
        },
        {
          data: { published: true },
          where: { id: 23 },
        },
      ],
    },
  },
})

upsert

info

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

info

• 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',
  },
})

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',
    },
  },
})

warning

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.

note

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.

Full-Text Search is currently in Preview and only available for PostgreSQL and MySQL. To use search, you'll need to enable the fullTextSearch preview feature.

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

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.post.findMany({
  where: {
    OR: [
      {
        title: {
          contains: 'Prisma',
        },
      },
      {
        title: {
          contains: 'databases',
        },
      },
    ],
    AND: {
      published: false,
    },
  },
})

NOT

All conditions must return false.

Examples

Get all Post records where the title field contains Prisma or databases, but not SQL

const result = await prisma.post.findMany({
  where: {
    OR: [
      {
        title: {
          contains: 'Prisma',
        },
      },
      {
        title: {
          contains: 'databases',
        },
      },
    ],
    NOT: {
      title: {
        contains: 'SQL',
      },
    },
  },
}