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

Location: ORM > 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](/orm/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",
  },
});
```

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 }
```

> [!INFO]
> `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](/orm/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 } },
  },
});

// Filter by geometry (PostgreSQL with PostGIS)
const nearbyLocations = await prisma.location.findMany({
  where: {
    position: {
      near: {
        point: [13.4, 52.5],
        maxDistance: 1000, // meters
      },
    },
  },
});
```

See [Filtering and sorting](/v6/orm/prisma-client/queries/filtering-and-sorting) for more examples, or [Working with geometry fields](/orm/prisma-client/special-fields-and-types/working-with-geometry-fields) for spatial queries.

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](/v6/orm/prisma-client/queries/select-fields) and [Relation queries](/orm/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" },
});
```

> [!INFO]
> 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](/orm/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()`](/orm/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()`](/orm/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()`](/orm/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]

> [!INFO]
> You can configure cascading deletes using [referential actions](/orm/prisma-schema/data-model/relations/referential-actions).

The following query uses [`delete()`](/orm/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`](/orm/reference/prisma-client-reference#deletemany) function. This is executed synchronously in a [`$transaction`](/orm/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`](/orm/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/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Relation queries`](https://www.prisma.io/docs/orm/prisma-client/queries/relation-queries): Prisma Client provides convenient queries for working with relations, such as a fluent API, nested writes (transactions), nested reads and relation filters
- [`Transactions and batch queries`](https://www.prisma.io/docs/orm/prisma-client/queries/transactions): This page explains the transactions API of Prisma Client