CRUD
This page describes how to perform CRUD operations with Prisma Client:
See the Prisma Client API reference for detailed method documentation.
const user = await prisma.user.create({
data: {
email: "elsa@prisma.io",
name: "Elsa Prisma",
},
});- Prisma Client API reference if you need the full method and option surface
- Relation queries if your CRUD workflow needs nested writes or relational reads
- Prisma Migrate getting started if you're still evolving the database schema behind these queries
- Quickstart with 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.
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 }Supported by PostgreSQL, CockroachDB, and SQLite.
const users = await prisma.user.createManyAndReturn({
data: [
{ name: "Alice", email: "alice@prisma.io" },
{ name: "Bob", email: "bob@prisma.io" },
],
});See Nested writes for creating records with relations.
// 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 },
});const users = await prisma.user.findMany();const user = await prisma.user.findFirst({
where: { posts: { some: { likes: { gt: 100 } } } },
orderBy: { id: "desc" },
});// 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.
const user = await prisma.user.findUnique({
where: { email: "emma@prisma.io" },
select: { email: true, name: true },
});
// Returns: { email: 'emma@prisma.io', name: "Emma" }const users = await prisma.user.findMany({
where: { role: "ADMIN" },
include: { posts: true },
});See Select fields and Relation queries for more.
const updateUser = await prisma.user.update({
where: { email: "viola@prisma.io" },
data: { name: "Viola the Magnificent" },
});const updateUsers = await prisma.user.updateMany({
where: { email: { contains: "prisma.io" } },
data: { role: "ADMIN" },
});
// Returns: { count: 19 }Supported by PostgreSQL, CockroachDB, and SQLite.
const users = await prisma.user.updateManyAndReturn({
where: { email: { contains: "prisma.io" } },
data: { role: "ADMIN" },
});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" },
});await prisma.post.updateMany({
data: {
views: { increment: 1 },
likes: { increment: 1 },
},
});See Relation queries for connecting and disconnecting related records.
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.
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.
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)
Section titled “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]);
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.
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
$transactionperforms a cascading delete on each models table so they have to be called in order.
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 });
}✅ 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
If you use Prisma Migrate, you can use migrate reset, this will:
- Drop the database
- Create a new database
- Apply migrations
- Seed the database with data
- A single
User - Two new, related
Postrecords - Connect or create
Categoryper 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",
},
},
],
},
},
],
},
},
});