Prisma ORM cheatsheet

Prisma ORM: Prisma Client queries

13 common Prisma ORM tasks.

Create the client

Create one PrismaClient and reuse it, rather than a new one per request.

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

Create a record

Insert a row and get it back, including generated fields.

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

Create related records together

Create a user and their posts in one nested write.

await prisma.user.create({
  data: {
    email: "[email protected]",
    posts: { create: [{ title: "First post" }, { title: "Second post" }] },
  },
});

Find many with filters

Filter, sort and paginate.

const users = await prisma.user.findMany({
  where: { email: { endsWith: "@example.com" }, role: "ADMIN" },
  orderBy: { createdAt: "desc" },
  take: 20,
  skip: 0,
});

Find one by a unique field

Look up a record by id or unique field. It returns null if nothing matches.

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

Include relations or pick fields

include loads related records, and select returns only the fields you name.

const withPosts = await prisma.user.findMany({ include: { posts: true } });
const names = await prisma.user.findMany({ select: { id: true, name: true } });

Update a record

Change fields of one record.

await prisma.user.update({ where: { id: 1 }, data: { name: "Ada Lovelace" } });

Update or create

Update the record if it exists, otherwise create it.

await prisma.user.upsert({
  where: { email: "[email protected]" },
  update: { name: "Ada" },
  create: { email: "[email protected]", name: "Ada" },
});

Delete records

Delete one record, or many with a filter.

await prisma.post.delete({ where: { id: 1 } });
await prisma.post.deleteMany({ where: { authorId: 1 } });

Caution: Deletes are permanent. Check the where filter, because an empty one deletes every row.

Count and aggregate

Count rows and compute statistics.

const total = await prisma.user.count({ where: { role: "USER" } });
const stats = await prisma.post.aggregate({ _count: true, _max: { createdAt: true } });

Group rows

Group by a field and count each group.

const byRole = await prisma.user.groupBy({ by: ["role"], _count: { _all: true } });

Run several operations atomically

All operations succeed together or none are applied.

await prisma.$transaction([
  prisma.user.update({ where: { id: 1 }, data: { name: "A" } }),
  prisma.post.create({ data: { title: "Hello", authorId: 1 } }),
]);

Run raw SQL safely

Use the tagged template, which sends values as parameters and prevents SQL injection.

const email = "[email protected]";
const rows = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;