Prisma Client API reference

Programmatically override a datasource url

import { PrismaClient } from '@prisma/client'

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

Based on the following datasource block:

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

Log levels

Emit formats

Event types

The query event type:

index.d.ts
1export type QueryEvent = {
2  timestamp: Date
3  query: string // Query sent to the database
4  params: string // Query parameters
5  duration: number // Time elapsed (in milliseconds) between client issuing query and database responding - not only time taken to run query
6  target: string
7}

Note that for MongoDB, the params and duration fields will be undefined.

All other log level event types:

index.d.ts
1export type LogEvent = {
2  timestamp: Date
3  message: string
4  target: string
5}

Log query and info to stdout

import { PrismaClient } from '@prisma/client'

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

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

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

Log a query event to console

import { PrismaClient } from '@prisma/client'

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

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

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

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

Log info, warn, and error events to console

import { PrismaClient } from '@prisma/client'

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

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

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

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

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

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

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

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

Get the User record with an id of 42

const result = await prisma.user.findUnique({
  where: {
    id: 42,
  },
})

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

const result = await prisma.user.findUnique({
  where: {
    email: 'alice@prisma.io',
  },
})

Get the User record with firstName of Alice and lastName of Smith (@@unique)

model User {
  firstName 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)

model User {
  firstName String
  lastName  String

  @@id([firstName, lastName])
}

const result = await prisma.user.findUnique({
  where: {
    firstName_lastName: {
      firstName: 'Alice',
      lastName: 'Smith',
    },
  },
})

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

Get all User records where the name is Alice

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

Create a single new record with the only required field email

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

Create multiple new records

In most cases, you can carry out batch inserts with the createMany query. However, there are scenarios where create is the best option to insert multiple records.

The following example results in two INSERT statements:

import { Prisma, PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({ log: ['query'] })

async function main() {
  let users: Prisma.UserCreateInput[] = [
    {
      email: 'ariana@prisma.io',
      name: 'Ari',
      profileViews: 20,
      coinflips: [true, false, false],
      role: 'ADMIN',
    },
    {
      email: 'elsa@prisma.io',
      name: 'Elsa',
      profileViews: 20,
      coinflips: [true, false, false],
      role: 'ADMIN',
    },
  ]

  await Promise.all(
    users.map(async (user) => {
      await prisma.user.create({
        data: user,
      })
    })
  )
}

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

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

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

Problem

If multiple upsert operations happen at the same time and the record doesn't already exist, then one or more of the operations might return a unique key constraint error.

Cause

When Prisma does an upsert, it first checks whether that record already exists in the database. To make this check, Prisma performs a read operation with the where clause from the upsert operation. This has two possible outcomes, as follows:

• If the record does not exist, then Prisma creates that record.
• If the record exists, then Prisma updates it.

When your application tries to perform two or more concurrent upsert operations, then a race condition might happen where two or more operations do not find the record and therefore try to create that record. In this situation, one of the operations 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 upsert prerequisites

Prisma Client can use database upserts if your stack meets the following criteria:

• You use Prisma version 4.6.0 or later
• Your application uses a CockroachDB, PostgreSQL, or SQLite data source

Database upsert query criteria

Prisma Client uses a database upsert for an upsert query when the query meets the following criteria:

• There are no nested queries in the upsert'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 the User record with an id of 1

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

Delete the User record where email equals else@prisma.io

The following query deletes a specific user record and uses select to return the name and email of the deleted user:

const deleteUser = await prisma.user.delete({
  where: {
    email: 'elsa@prisma.io',
  },
  select: {
    email: true,
    name: true,
  },
})

{ "email": "elsa@prisma.io", "name": "Elsa" }

Create several new users

const users = await prisma.user.createMany({
  data: [
    { name: 'Sonali', email: 'sonali@prisma.io' },
    { name: 'Alex', email: 'alex@prisma.io' },
  ],
})

Update all User records where the name is Alice to ALICE

const updatedUserCount = await prisma.user.updateMany({
  where: { name: 'Alice' },
  data: { name: 'ALICE' },
})

Update all User records where the email contains prisma.io and at least one related Post has more than 10 likes

const deleteUser = await prisma.user.updateMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
    posts: {
      some: {
        likes: {
          gt: 10,
        },
      },
    },
  },
  data: {
    role: 'USER',
  },
})

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

Return _min, _max, and _count of profileViews of all User records

const minMaxAge = await prisma.user.aggregate({
  _count: {
    _all: true,
  },
  _max: {
    profileViews: true,
  },
  _min: {
    profileViews: true,
  },
})

Return _sum of all profileViews for all User records

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