CRUD

Create a single record

The following query creates (create ) a single user with two fields:

const user = await prisma.user.create({
  data: {
    email: 'elsa@prisma.io',
    name: 'Elsa Prisma',
  },
})

The user's id is auto-generated, and your schema determines which fields are mandatory.

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

const prisma = new PrismaClient()

async function main() {
  let includePosts: boolean = false
  let user: Prisma.UserCreateInput

  // Check if posts should be included in the query
  if (includePosts) {
    user = {
      email: 'elsa@prisma.io',
      name: 'Elsa Prisma',
      posts: {
        create: {
          title: 'Include this post!',
        },
      },
    }
  } else {
    user = {
      email: 'elsa@prisma.io',
      name: 'Elsa Prisma',
    }
  }

  // Pass 'user' object into query
  const createUser = await prisma.user.create({ data: user })
}

main()

Create multiple records

Prisma Client supports bulk inserts as a GA feature in 2.20.0 and later.

The following createMany query creates multiple users and skips any duplicates (email must be unique):

const createMany = await prisma.user.createMany({
  data: [
    { name: 'Bob', email: 'bob@prisma.io' },
    { name: 'Bobo', email: 'bob@prisma.io' }, // Duplicate unique key!
    { name: 'Yewande', email: 'yewande@prisma.io' },
    { name: 'Angelique', email: 'angelique@prisma.io' },
  ],
  skipDuplicates: true, // Skip 'Bobo'
})

{
  count: 3
}

createMany uses a single INSERT INTO statement with multiple values, which is generally more efficient than a separate INSERT per row:

BEGIN
INSERT INTO "public"."User" ("id","name","email","profileViews","role","coinflips","testing","city","country") VALUES (DEFAULT,$1,$2,$3,$4,DEFAULT,DEFAULT,DEFAULT,$5), (DEFAULT,$6,$7,$8,$9,DEFAULT,DEFAULT,DEFAULT,$10), (DEFAULT,$11,$12,$13,$14,DEFAULT,DEFAULT,DEFAULT,$15), (DEFAULT,$16,$17,$18,$19,DEFAULT,DEFAULT,DEFAULT,$20) ON CONFLICT DO NOTHING
COMMIT
SELECT "public"."User"."country", "public"."User"."city", "public"."User"."email", SUM("public"."User"."profileViews"), COUNT(*) FROM "public"."User" WHERE 1=1 GROUP BY "public"."User"."country", "public"."User"."city", "public"."User"."email" HAVING AVG("public"."User"."profileViews") >= $1 ORDER BY "public"."User"."country" ASC OFFSET $2

Note: Multiple create statements inside a $transaction results in multiple INSERT statements.

The following video demonstrates how to use createMany and faker.js to seed a database with sample data:

Create records and connect or create related records

See Working with relations > Nested writes for information about creating a record and one or more related records at the same time.

Get record by ID or unique identifier

The following queries return a single record (findUnique ) by unique identifier or ID:

// By unique identifier
const user = await prisma.user.findUnique({
  where: {
    email: 'elsa@prisma.io',
  },
})

// By ID
const user = await prisma.user.findUnique({
  where: {
    id: 99,
  },
})

If you are using the MongoDB connector and your underlying ID type is ObjectId, you can use the string representation of that ObjectId:

// By ID
const user = await prisma.user.findUnique({
  where: {
    id: '60d5922d00581b8f0062e3a8',
  },
})

Get record by compound ID or compound unique identifier

The following examples demonstrate how to retrieve records by a compound ID or unique identifier, defined by @@id or @@unique .

The following Prisma model defines a compound ID:

model TimePeriod {
  year    Int
  quarter Int
  total   Decimal

  @@id([year, quarter])
}

To retrieve a time period by this compound ID, use the generated year_quarter field, which follows the fieldName1_fieldName2 pattern:

const timePeriod = await prisma.timePeriod.findUnique({
  where: {
    year_quarter: {
      quarter: 4,
      year: 2020,
    },
  },
})

The following Prisma model defines a compound unique identifier with a custom name (timePeriodId)

model TimePeriod {
  year    Int
  quarter Int
  total   Decimal

  @@unique(fields: [year, quarter], name: "timePeriodId")
}

To retrieve a time period by this unique identifier, use the custom timePeriodId field:

const timePeriod = await prisma.timePeriod.findUnique({
  where: {
    timePeriodId: {
      quarter: 4,
      year: 2020,
    },
  },
})

Get all records

The following findMany query returns all User records:

const users = await prisma.user.findMany()

You can also paginate your results.

Get the first record that matches a specific criteria

The following findFirst query returns the most recently created user with at least one post that has more than 100 likes:

1. Order users by descending ID (largest first) - the largest ID is the most recent
2. Return the first user in descending order with at least one post that has more than 100 likes

  const findUser = await prisma.user.findFirst({
    where: {
      posts: {
        some: {
          likes: {
            gt: 100
          }
        }
      }
    },
    orderBy: {
      id: "desc"
    }
  })
}

Get a filtered list of records

Prisma Client supports filtering on record fields and related record fields.

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
    },
  },
})

const users = await prisma.user.findMany({
  where: {
    OR: [
      {
        name: {
          startsWith: 'E',
        },
      },
      {
        AND: {
          profileViews: {
            gt: 0,
          },
          role: {
            equals: 'ADMIN',
          },
        },
      },
    ],
  },
})

  const users = await prisma.user.findMany({
    where: {
      email: {
        endsWith: "prisma.io"
      },
      posts: {
        some: {
          published: false
        }
      }
    },
  }

Select a subset of fields

The following findUnique query uses select to return the email and name fields of a specific User record:

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

For more information about including relations, refer to:

• Select fields
• Relation queries

const user = await prisma.user.findUnique({
  where: {
    email: 'emma@prisma.io',
  },
  select: {
    email: true,
    posts: {
      select: {
        likes: true,
      },
    },
  },
})

Select distinct field values

See Select distinct for information about selecting distinct field values.

Include related records

The following query returns all ADMIN users and includes each user's posts in the result:

const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
  },
  include: {
    posts: true,
  },
})

For more information about including relations, see Select fields and include relations.

Update a single record

The following query uses update to find and update a single User record by email:

const updateUser = await prisma.user.update({
  where: {
    email: 'viola@prisma.io',
  },
  data: {
    name: 'Viola the Magnificent',
  },
})

Update multiple records

The following query uses updateMany to update all User records that contain prisma.io:

const updateUsers = await prisma.user.updateMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  data: {
    role: 'ADMIN',
  },
})

Update or create records

The following query uses upsert to update a User record with a specific email address, or create that User record if it does not exist:

const upsertUser = await prisma.user.upsert({
  where: {
    email: 'viola@prisma.io',
  },
  update: {
    name: 'Viola the Magnificent',
  },
  create: {
    email: 'viola@prisma.io',
    name: 'Viola the Magnificent',
  },
})

Prisma does not have a findOrCreate query. You can use upsert as a workaround. To make upsert behave like a findOrCreate method, provide an empty update parameter to upsert.

Update a number field

Use atomic number operations to update a number field based on its current value - for example, increment or multiply. The following query increments the views and likes fields by 1:

const updatePosts = await prisma.post.updateMany({
  data: {
    views: {
      increment: 1,
    },
    likes: {
      increment: 1,
    },
  },
})

Connect and disconnect related records

Refer to Working with relations for information about disconnecting (disconnect ) and connecting (connect ) related records.

Delete a single record

The following query uses delete to delete a single User record:

const deleteUser = await prisma.user.delete({
  where: {
    email: 'bert@prisma.io',
  },
})

Attempting to delete a user with one or more posts result in an error, as every Post requires an author - see cascading deletes.

Delete multiple records

The following query uses deleteMany to delete all User records where email contains prisma.io:

const deleteUsers = await prisma.user.deleteMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
})

Attempting to delete a user with one or more posts result in an error, as every Post requires an author - see cascading deletes.

Delete all records

The following query uses deleteMany to delete all User records:

const deleteUsers = await prisma.user.deleteMany({})

Be aware that this query will fail if the user has any related records (such as posts). In this case, you need to delete the related records first.

Cascading deletes (deleting related records)

The following query uses delete to delete a single User record:

const deleteUser = await prisma.user.delete({
  where: {
    email: 'bert@prisma.io',
  },
})

However, the example schema includes a required relation between Post and User, which means that you cannot delete a user with posts:

The change you are trying to make would violate the required relation 'PostToUser' between the `Post` and `User` models.

To resolve this error, you can:

• Make the relation optional:

  model Post {
    id       Int   @id @default(autoincrement())
    author   User? @relation(fields: [authorId], references: [id])
    authorId Int?
    author   User  @relation(fields: [authorId], references: [id])
    authorId Int
  }

• Change the author of the posts to another user before deleting the user.

• Delete a user and all their posts with two separate queries in a transaction (all queries must succeed):

  const deletePosts = prisma.post.deleteMany({
    where: {
      authorId: 7,
    },
  })
  
  const deleteUser = prisma.user.delete({
    where: {
      id: 7,
    },
  })
  
  const transaction = await prisma.$transaction([deletePosts, deleteUser])

Delete all records from all tables

Sometimes you want to remove all data from all tables but keep the actual tables. This can be particularly useful in a development environment and whilst testing.

The following shows how to delete all records from all tables with Prisma Client and with Prisma Migrate.

const deletePosts = prisma.post.deleteMany()
const deleteProfile = prisma.profile.deleteMany()
const deleteUsers = prisma.user.deleteMany()

// The transaction runs synchronously so deleteUsers must run last.
await prisma.$transaction([deleteProfile, deletePosts, deleteUsers])

const tablenames = await prisma.$queryRaw<
  Array<{ tablename: string }>
>`SELECT tablename FROM pg_tables WHERE schemaname='public'`

const tables = tablenames
  .map(({ tablename }) => tablename)
  .filter((name) => name !== '_prisma_migrations')
  .map((name) => `"public"."${name}"`)
  .join(', ')

try {
  await prisma.$executeRawUnsafe(`TRUNCATE TABLE ${tables} CASCADE;`)
} catch (error) {
  console.log({ error })
}

Create a deeply nested tree of records

• A single User
• Two new, related Post records
• Connect or create Category per post

const u = await prisma.user.create({
  include: {
    posts: {
      include: {
        categories: true,
      },
    },
  },
  data: {
    email: 'emma@prisma.io',
    posts: {
      create: [
        {
          title: 'My first post',
          categories: {
            connectOrCreate: [
              {
                create: { name: 'Introductions' },
                where: {
                  name: 'Introductions',
                },
              },
              {
                create: { name: 'Social' },
                where: {
                  name: 'Social',
                },
              },
            ],
          },
        },
        {
          title: 'How to make cookies',
          categories: {
            connectOrCreate: [
              {
                create: { name: 'Social' },
                where: {
                  name: 'Social',
                },
              },
              {
                create: { name: 'Cooking' },
                where: {
                  name: 'Cooking',
                },
              },
            ],
          },
        },
      ],
    },
  },
})