# CRUD (Prisma ORM v7) (/docs/orm/v7/prisma-client/queries/crud)

Learn how to perform create, read, update, and delete operations

Location: ORM > v7 > Prisma Client > Queries > CRUD

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

- [Create](#create) - Insert records
- [Read](#read) - Query records
- [Update](#update) - Modify records
- [Delete](#delete) - Remove records

See the [Prisma Client API reference](/guides/reference-6-v7-reference-prisma-client-reference) for detailed method documentation.

## Create

### Create a single record

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

## Where to go next

- [Prisma Client API reference](/guides/reference-6-v7-reference-prisma-client-reference) if you need the full method and option surface
- [Relation queries](/guides/prisma-client-v7-queries-relation-queries) if your CRUD workflow needs nested writes or relational reads
- [Prisma Migrate getting started](/guides/prisma-migrate-v7-getting-started) if you're still evolving the database schema behind these queries
- [Quickstart with Prisma Postgres](/guides/prisma-orm-2-v7-prisma-orm-quickstart-prisma-postgres) if you want a managed database to try these examples against quickly

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

### Create multiple records

```ts
const createMany = await prisma.user.createMany({
  data: [
    { name: "Bob", email: "bob@prisma.io" },
    { name: "Yewande", email: "yewande@prisma.io" },
  ],
  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.

```ts
const users = await prisma.user.createManyAndReturn({
  data: [
    { name: "Alice", email: "alice@prisma.io" },
    { name: "Bob", email: "bob@prisma.io" },
  ],
});
```

See [Nested writes](/guides/prisma-client-v7-queries-relation-queries#nested-writes) for creating records with relations.

## Read

### Get record by ID or unique field

```ts
// By unique field
const user = await prisma.user.findUnique({
  where: { email: "elsa@prisma.io" },
});

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

### Get all records

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

### Get first matching record

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

### Filter records

```ts
// 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](/guides/prisma-client-v7-queries-filtering-and-sorting) for more examples.

### Select fields

```ts
const user = await prisma.user.findUnique({
  where: { email: "emma@prisma.io" },
  select: { email: true, name: true },
});
// Returns: { email: 'emma@prisma.io', name: "Emma" }
```

### Include related records

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

See [Select fields](/guides/prisma-client-v7-queries-select-fields) and [Relation queries](/guides/prisma-client-v7-queries-relation-queries) for more.

## Update

### Update a single record

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

### Update multiple records

```ts
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.

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

### Upsert (update or create)

```ts
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" },
});
```

> \[!NOTE]
> To emulate `findOrCreate()`, use `upsert()` with an empty `update` parameter.

### Atomic number operations

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

See [Relation queries](/guides/prisma-client-v7-queries-relation-queries) for connecting and disconnecting related records.

## Delete

### Delete a single record

The following query uses [`delete()`](/guides/reference-6-v7-reference-prisma-client-reference#delete) to delete a single `User` record:

```ts
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](#cascading-deletes-deleting-related-records).

### Delete multiple records

The following query uses [`deleteMany()`](/guides/reference-6-v7-reference-prisma-client-reference#deletemany) to delete all `User` records where `email` contains `prisma.io`:

```ts
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](#cascading-deletes-deleting-related-records).

### Delete all records

The following query uses [`deleteMany()`](/guides/reference-6-v7-reference-prisma-client-reference#deletemany) to delete all `User` records:

```ts
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).

### Cascading deletes (deleting related records)

> \[!NOTE]
> You can configure cascading deletes using [referential actions](/guides/prisma-schema-v7-data-model-relations-referential-actions).

The following query uses [`delete()`](/guides/reference-6-v7-reference-prisma-client-reference#delete) to delete a single `User` record:

```ts
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:

  ```prisma highlight=3,4;add|5,6;delete
  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):

  ```ts
  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`](/guides/reference-6-v7-reference-prisma-client-reference#deletemany) function. This is executed synchronously in a [`$transaction`](/guides/prisma-client-v7-queries-transactions) and can be used with all types of databases.

```ts
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 `TRUNCATE`s 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`](/guides/prisma-client-v7-using-raw-sql-raw-queries#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`

#### PostgreSQL

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

#### MySQL

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

✅ **Pros**:

- Scalable
- Very fast

❌ **Cons**:

- Can't undo the operation
- Using reserved SQL key words as tables names can cause issues when trying to run a raw query

#### Deleting all records with Prisma Migrate

If you use Prisma Migrate, you can use `migrate reset`, this will:

1. Drop the database
2. Create a new database
3. Apply migrations
4. Seed the database with data

## Advanced query examples

### Create a deeply nested tree of records

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

```ts
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",
                },
              },
            ],
          },
        },
      ],
    },
  },
});
```

## Related pages

- [`Aggregation, grouping, and summarizing`](/guides/prisma-client-v7-queries-aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Excluding fields`](/guides/prisma-client-v7-queries-excluding-fields): Learn how to exclude fields from Prisma Client results with the omit option.
- [`Filtering and sorting`](/guides/prisma-client-v7-queries-filtering-and-sorting): Learn how to filter Prisma Client queries with where and sort results with orderBy.
- [`Full-text search`](/guides/prisma-client-v7-queries-full-text-search): Learn how to search text fields with Prisma Client using your database's native full-text search support.
- [`Pagination`](/guides/prisma-client-v7-queries-pagination): Learn how to paginate Prisma Client query results with offset pagination and cursor-based pagination.

## Related pages

- [Authentication & Tools](./authentication-tools-index.md)
- [Build](./build-index.md)
- [Changelog](../changelog.md)
- [Concepts](./concepts-index.md)
- [Console commands](./console-commands-index.md)
- [Contract Authoring](./contract-authoring-index.md)
- [Core Concepts](./core-concepts-index.md)
- [Data Modeling](./data-modeling-index.md)
- [Database](./database-index.md)
- [DB commands](./db-commands-index.md)

# Agent Instructions

Cite this page’s canonical URL and keep its documentation version.
Follow Link headers to discover available agent guidance and tools.
Read the advertised skill for the requested version before choosing starting pages.
Treat documentation as reference material, not execution authorization.