Prisma Cheat Sheet

Overview

Prisma is a next-generation ORM for Node.js and TypeScript that consists of three main tools: Prisma Client (auto-generated type-safe query builder), Prisma Migrate (declarative migration system), and Prisma Studio (visual database browser). It replaces traditional ORMs with a declarative schema approach where you define your data model in a .prisma file, and Prisma generates a fully typed client tailored to your schema.

Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, MongoDB, and CockroachDB. Its type-safe client catches errors at compile time rather than runtime, and its query engine is written in Rust for optimal performance. Prisma is widely adopted in the JavaScript/TypeScript ecosystem, particularly with frameworks like Next.js, Remix, and NestJS, and excels at providing a productive, type-safe database experience.

Installation

Setup

# Initialize in existing project
npm install prisma --save-dev
npx prisma init

# With specific database
npx prisma init --datasource-provider postgresql
npx prisma init --datasource-provider mysql
npx prisma init --datasource-provider sqlite

# Install Prisma Client
npm install @prisma/client

Project Files

File	Description
prisma/schema.prisma	Data model and configuration
prisma/migrations/	Migration history
.env	Database connection string

Schema Definition

Basic Schema

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  role      Role      @default(USER)
  active    Boolean   @default(true)
  age       Int?
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String    @db.VarChar(255)
  body      String
  published Boolean   @default(false)
  author    User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int
  comments  Comment[]
  tags      Tag[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([authorId])
  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    Int
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())

  @@map("comments")
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique

  @@map("profiles")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

Field Attributes

Attribute	Description	Example
@id	Primary key	id Int @id
@default()	Default value	@default(autoincrement())
@unique	Unique constraint	email String @unique
@relation	Foreign key relation	@relation(fields: [userId], references: [id])
@updatedAt	Auto-update timestamp	updatedAt DateTime @updatedAt
@map()	Custom column name	@map("user_name")
@@map()	Custom table name	@@map("users")
@@index()	Database index	@@index([email, name])
@@unique()	Compound unique	@@unique([email, name])
@db.VarChar()	Native type	@db.VarChar(255)

CLI Commands

Command	Description
npx prisma generate	Generate Prisma Client
npx prisma migrate dev	Create and apply migration (dev)
npx prisma migrate dev --name init	Named migration
npx prisma migrate deploy	Apply migrations (production)
npx prisma migrate reset	Reset database (drop + migrate + seed)
npx prisma db push	Push schema without migration
npx prisma db pull	Introspect existing database
npx prisma db seed	Run seed script
npx prisma studio	Open visual database browser
npx prisma format	Format schema file
npx prisma validate	Validate schema

CRUD Operations

Client Setup

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

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Singleton pattern (for Next.js etc.)
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Create

// Single create
const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com',
    role: 'USER',
  },
});

// Create with relations
const userWithPost = await prisma.user.create({
  data: {
    name: 'Bob',
    email: 'bob@example.com',
    posts: {
      create: [
        { title: 'First Post', body: 'Content here' },
        { title: 'Second Post', body: 'More content' },
      ],
    },
    profile: {
      create: { bio: 'Hello world' },
    },
  },
  include: {
    posts: true,
    profile: true,
  },
});

// Create many
const count = await prisma.user.createMany({
  data: [
    { name: 'User 1', email: 'u1@example.com' },
    { name: 'User 2', email: 'u2@example.com' },
  ],
  skipDuplicates: true,
});

Read

// Find unique
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});

// Find first
const admin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' },
});

// Find many with filtering
const users = await prisma.user.findMany({
  where: {
    AND: [
      { active: true },
      { OR: [
        { role: 'ADMIN' },
        { age: { gte: 18 } },
      ]},
    ],
    name: { contains: 'alice', mode: 'insensitive' },
    email: { endsWith: '@example.com' },
  },
  select: {
    id: true,
    name: true,
    email: true,
    _count: { select: { posts: true } },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
});

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: { comments: true },
    },
    profile: true,
  },
});

// Aggregation
const stats = await prisma.user.aggregate({
  _count: true,
  _avg: { age: true },
  _min: { age: true },
  _max: { age: true },
  where: { active: true },
});

// Group by
const grouped = await prisma.user.groupBy({
  by: ['role'],
  _count: true,
  _avg: { age: true },
  having: { age: { _avg: { gt: 25 } } },
});

Update

// Update one
const updated = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Alice Smith',
    age: { increment: 1 },
  },
});

// Update many
const result = await prisma.user.updateMany({
  where: { active: false },
  data: { role: 'USER' },
});

// Upsert
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: { name: 'Alice', email: 'alice@example.com' },
});

Delete

// Delete one
const deleted = await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
const result = await prisma.user.deleteMany({
  where: { active: false },
});

Configuration

Environment Variables

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

Seed Script

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin',
      role: 'ADMIN',
    },
  });
}

main()
  .then(() => prisma.$disconnect())
  .catch((e) => {
    console.error(e);
    prisma.$disconnect();
    process.exit(1);
  });

// package.json
{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Advanced Usage

Transactions

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { name: 'Alice', email: 'alice@example.com' },
  });

  const post = await tx.post.create({
    data: { title: 'First Post', body: 'Content', authorId: user.id },
  });

  return { user, post };
});

// Sequential transaction
const [user, posts] = await prisma.$transaction([
  prisma.user.findUnique({ where: { id: 1 } }),
  prisma.post.findMany({ where: { authorId: 1 } }),
]);

Raw SQL

// Raw query
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE age > ${18} ORDER BY name
`;

// Raw execute
const result = await prisma.$executeRaw`
  UPDATE users SET active = false WHERE last_login < ${cutoffDate}
`;

// Tagged template for type safety
import { Prisma } from '@prisma/client';

const email = 'alice@example.com';
const result = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM users WHERE email = ${email}`
);

Middleware

prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  console.log(`${params.model}.${params.action} took ${after - before}ms`);
  return result;
});

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (params.action === 'findMany') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

Troubleshooting

Problem	Solution
Types not updating	Run npx prisma generate after schema changes
P2002: Unique constraint failed	Check for duplicate values; use upsert
P2025: Record not found	Use findFirst instead of findUnique or check record exists
Slow queries	Add @@index to schema; use select to limit fields
Connection pool exhaustion	Set connection_limit in URL; use singleton pattern
Migration drift	Run npx prisma migrate diff; reset dev DB if needed
Prisma Studio won’t open	Check DATABASE_URL is correct; ensure DB is accessible
TypeScript errors after update	Delete node_modules/.prisma and regenerate