CRUD

This page describes how to perform CRUD operations with Prisma Client:

Create - Insert records
Read - Query records
Update - Modify records
Delete - Remove records

See the Prisma Client API reference for detailed method documentation.

Create

Create a single record

const user = await prisma.user.create({
  data: {
    email: "[email protected]",
    name: "Elsa Prisma",
  },
});

The id is auto-generated. Your schema determines which fields are mandatory.

Create multiple records

const createMany = await prisma.user.createMany({
  data: [
    { name: "Bob", email: "[email protected]" },
    { name: "Yewande", email: "[email protected]" },
  ],
  skipDuplicates: true, // Skip records with duplicate unique fields
});
// Returns: { count: 2 }

:::note skipDuplicates is not supported on MongoDB, SQLServer, or SQLite. :::

Create and return multiple records

Supported by PostgreSQL, CockroachDB, and SQLite.

const users = await prisma.user.createManyAndReturn({
  data: [
    { name: "Alice", email: "[email protected]" },
    { name: "Bob", email: "[email protected]" },
  ],
});

See Nested writes for creating records with relations.

Read

Get record by ID or unique field

// By unique field
const user = await prisma.user.findUnique({
  where: { email: "[email protected]" },
});

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

Get all records

const users = await prisma.user.findMany();

Get first matching record

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

Filter records

// Single field filter
const users = await prisma.user.findMany({
  where: { email: { endsWith: "prisma.io" } },
});

// Multiple conditions with OR/AND
const users = await prisma.user.findMany({
  where: {
    OR: [{ name: { startsWith: "E" } }, { AND: { profileViews: { gt: 0 }, role: "ADMIN" } }],
  },
});

// Filter by related records
const users = await prisma.user.findMany({
  where: {
    email: { endsWith: "prisma.io" },
    posts: { some: { published: false } },
  },
});

See Filtering and sorting for more examples.

Select fields

const user = await prisma.user.findUnique({
  where: { email: "[email protected]" },
  select: { email: true, name: true },
});
// Returns: { email: '[email protected]', name: "Emma" }

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

See Select fields and Relation queries for more.

Update

Update a single record

const updateUser = await prisma.user.update({
  where: { email: "[email protected]" },
  data: { name: "Viola the Magnificent" },
});

Update multiple records

const updateUsers = await prisma.user.updateMany({
  where: { email: { contains: "prisma.io" } },
  data: { role: "ADMIN" },
});
// Returns: { count: 19 }

Update and return multiple records

Supported by PostgreSQL, CockroachDB, and SQLite.

const users = await prisma.user.updateManyAndReturn({
  where: { email: { contains: "prisma.io" } },
  data: { role: "ADMIN" },
});

Upsert (update or create)

const upsertUser = await prisma.user.upsert({
  where: { email: "[email protected]" },
  update: { name: "Viola the Magnificent" },
  create: { email: "[email protected]", name: "Viola the Magnificent" },
});

:::tip To emulate findOrCreate(), use upsert() with an empty update parameter. :::

Atomic number operations

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

See Relation queries for connecting and disconnecting related records.

Delete\n

Delete a single record

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

const deleteUser = await prisma.user.delete({
  where: {
    email: "[email protected]",
  },
});

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.

:::tip

You can configure cascading deletes using referential actions.

:::

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

const deleteUser = await prisma.user.delete({
  where: {
    email: "[email protected]",
  },
});

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:

prisma

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

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.

Deleting all data with `deleteMany()`

When you know the order in which your tables should be deleted, you can use the deleteMany function. This is executed synchronously in a $transaction and can be used with all types of databases.

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]);

✅ Pros:

Works well when you know the structure of your schema ahead of time
Synchronously deletes each tables data

❌ Cons:

When working with relational databases, this function doesn't scale as well as having a more generic solution which looks up and TRUNCATEs your tables regardless of their relational constraints. Note that this scaling issue does not apply when using the MongoDB connector.

Note: The $transaction performs a cascading delete on each models table so they have to be called in order.

Deleting all data with raw SQL / `TRUNCATE`

If you are comfortable working with raw SQL, you can perform a TRUNCATE query on a table using $executeRawUnsafe.

In the following examples, the first tab shows how to perform a TRUNCATE on a Postgres database by using a $queryRaw look up that maps over the table and TRUNCATES all tables in a single query.

The second tab shows performing the same function but with a MySQL database. In this instance the constraints must be removed before the TRUNCATE can be executed, before being reinstated once finished. The whole process is run as a $transaction

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

const transactions: PrismaPromise<any>[] = [];
transactions.push(prisma.$executeRaw`SET FOREIGN_KEY_CHECKS = 0;`);

const tablenames = await prisma.$queryRaw<
  Array<{ TABLE_NAME: string }>
>`SELECT TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA = 'tests';`;

for (const { TABLE_NAME } of tablenames) {
  if (TABLE_NAME !== "_prisma_migrations") {
    try {
      transactions.push(prisma.$executeRawUnsafe(`TRUNCATE ${TABLE_NAME};`));
    } catch (error) {
      console.log({ error });
    }
  }
}

transactions.push(prisma.$executeRaw`SET FOREIGN_KEY_CHECKS = 1;`);

try {
  await prisma.$transaction(transactions);
} catch (error) {
  console.log({ error });
}