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

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

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](https://www.prisma.io/docs/orm/v7/reference/prisma-client-reference) for detailed method documentation.

## Create [#create]

### Create a single record [#create-a-single-record]

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

## Where to go next [#where-to-go-next]

* [Prisma Client API reference](https://www.prisma.io/docs/orm/v7/reference/prisma-client-reference) if you need the full method and option surface
* [Relation queries](https://www.prisma.io/docs/orm/v7/prisma-client/queries/relation-queries) if your CRUD workflow needs nested writes or relational reads
* [Prisma Migrate getting started](https://www.prisma.io/docs/orm/v7/prisma-migrate/getting-started) if you're still evolving the database schema behind these queries
* [Quickstart with Prisma Postgres](https://www.prisma.io/docs/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 [#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 [#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](https://www.prisma.io/docs/orm/v7/prisma-client/queries/relation-queries#nested-writes) for creating records with relations.

## Read [#read]

### Get record by ID or unique field [#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 [#get-all-records]

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

### Get first matching record [#get-first-matching-record]

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

### Filter records [#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](https://www.prisma.io/docs/orm/v7/prisma-client/queries/filtering-and-sorting) for more examples.

### Select fields [#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 [#include-related-records]

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

See [Select fields](https://www.prisma.io/docs/orm/v7/prisma-client/queries/select-fields) and [Relation queries](https://www.prisma.io/docs/orm/v7/prisma-client/queries/relation-queries) for more.

## Update [#update]

### Update a single record [#update-a-single-record]

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

### Update multiple records [#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 [#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) [#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 [#atomic-number-operations]

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

See [Relation queries](https://www.prisma.io/docs/orm/v7/prisma-client/queries/relation-queries) for connecting and disconnecting related records.

## Delete [#delete]

### Delete a single record [#delete-a-single-record]

The following query uses [`delete()`](https://www.prisma.io/docs/orm/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 [#delete-multiple-records]

The following query uses [`deleteMany()`](https://www.prisma.io/docs/orm/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 [#delete-all-records]

The following query uses [`deleteMany()`](https://www.prisma.io/docs/orm/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) [#cascading-deletes-deleting-related-records]

> [!NOTE]
> You can configure cascading deletes using [referential actions](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/relations/referential-actions).

The following query uses [`delete()`](https://www.prisma.io/docs/orm/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 [#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()` [#deleting-all-data-with-deletemany]

When you know the order in which your tables should be deleted, you can use the [`deleteMany`](https://www.prisma.io/docs/orm/v7/reference/prisma-client-reference#deletemany) function. This is executed synchronously in a [`$transaction`](https://www.prisma.io/docs/orm/v7/prisma-client/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` [#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`](https://www.prisma.io/docs/orm/v7/prisma-client/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 [#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 [#advanced-query-examples]

### Create a deeply nested tree of records [#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`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Excluding fields`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/excluding-fields): Learn how to exclude fields from Prisma Client results with the omit option.
- [`Filtering and sorting`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/filtering-and-sorting): Learn how to filter Prisma Client queries with where and sort results with orderBy.
- [`Full-text search`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/full-text-search): Learn how to search text fields with Prisma Client using your database's native full-text search support.
- [`Pagination`](https://www.prisma.io/docs/orm/v7/prisma-client/queries/pagination): Learn how to paginate Prisma Client query results with offset pagination and cursor-based pagination.