Prisma (Database ORM)

Overview

Prisma is a next-generation ORM with auto-generated TypeScript types from your database schema. Define models in schema.prisma , run migrations, get fully typed queries.

Version: Prisma 6.x/7.x (2024-2025)

Prisma 7 Changes: ES module by default, prisma.config.ts , middleware deprecated

Key Benefit: Change schema → regenerate client → TypeScript errors show everywhere you need updates.

When to Use This Skill

✅ Use Prisma when:

• Building TypeScript backend with PostgreSQL/MySQL/SQLite

• Need type-safe database queries

• Want declarative schema with migrations

• Working with relational data and relations

• Building APIs with tRPC (perfect pair)

❌ Consider alternatives when:

• Complex raw SQL needed (use Prisma + $queryRaw )

• Ultra-high performance critical (consider Drizzle)

• Document-heavy MongoDB (native driver may be better)

• Legacy database with unusual schema

Quick Start

Installation

npm install prisma @prisma/client npx prisma init

Basic Schema

// prisma/schema.prisma datasource db { provider = "postgresql" url = env("DATABASE_URL") directUrl = env("DIRECT_DATABASE_URL") // For migrations with pooler }

generator client { provider = "prisma-client-js" }

model User { id String @id @default(cuid()) email String @unique name String? role Role @default(USER) posts Post[] profile Profile? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@index([email]) }

model Post { id String @id @default(cuid()) title String @db.VarChar(255) content String? published Boolean @default(false) author User @relation(fields: [authorId], references: [id]) authorId String createdAt DateTime @default(now())

@@index([authorId]) @@index([createdAt(sort: Desc)]) }

enum Role { USER ADMIN }

Singleton Client Pattern

Always use singleton in long-running servers:

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

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma || new PrismaClient({ log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'], });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

export default prisma;

Query Patterns

CRUD Operations

// Create with relations const user = await prisma.user.create({ data: { email: 'user@example.com', posts: { create: [{ title: 'First Post' }], }, profile: { create: { bio: 'Developer' }, }, }, include: { posts: true, profile: true }, });

// Find with filters const posts = await prisma.post.findMany({ where: { published: true, author: { role: 'ADMIN' }, }, orderBy: { createdAt: 'desc' }, include: { author: { select: { name: true } } }, });

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

// Delete await prisma.user.delete({ where: { id: userId } });

Cursor-Based Pagination

const posts = await prisma.post.findMany({ take: 10, cursor: lastId ? { id: lastId } : undefined, orderBy: { createdAt: 'desc' }, skip: lastId ? 1 : 0, // Skip the cursor itself });

Transactions

// Interactive transaction const transfer = await prisma.$transaction(async (tx) => { const sender = await tx.account.update({ where: { id: senderId }, data: { balance: { decrement: amount } }, });

if (sender.balance < 0) throw new Error('Insufficient funds');

await tx.account.update({ where: { id: receiverId }, data: { balance: { increment: amount } }, });

return sender; }, { maxWait: 5000, timeout: 10000, });

Select vs Include

// Include — return full related records const user = await prisma.user.findUnique({ where: { id }, include: { posts: true }, // All post fields });

// Select — pick specific fields (better performance) const user = await prisma.user.findUnique({ where: { id }, select: { name: true, posts: { select: { title: true } }, // Only titles }, });

Migration Commands

Development — create and apply migration

npx prisma migrate dev --name add_user_role

Production — apply pending migrations

npx prisma migrate deploy

Quick sync without history (prototyping only)

npx prisma db push

Generate client after schema changes

npx prisma generate

Reset database (dev only!)

npx prisma migrate reset

View database in browser

npx prisma studio

Type Utilities

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

// Payload type with relations type UserWithPosts = Prisma.UserGetPayload<{ include: { posts: true } }>;

// Input types type UserCreateInput = Prisma.UserCreateInput; type UserUpdateInput = Prisma.UserUpdateInput;

// Where types type UserWhereInput = Prisma.UserWhereInput;

Prisma + Zod Schema Sync

Manual Pattern (Recommended)

// src/schemas/user.schema.ts import { z } from 'zod';

export const UserSchema = z.object({ id: z.string().cuid(), email: z.string().email(), name: z.string().nullable(), role: z.enum(['USER', 'ADMIN']), createdAt: z.date(), updatedAt: z.date(), });

export const CreateUserSchema = UserSchema.omit({ id: true, createdAt: true, updatedAt: true, }).extend({ password: z.string().min(8), });

export type User = z.infer<typeof UserSchema>;

Code Generation (Optional)

npm install -D zod-prisma-types

generator zod { provider = "zod-prisma-types" output = "../src/generated/zod" }

Rules

Do ✅

• Use singleton pattern for PrismaClient

• Use include or select to avoid overfetching

• Use cursor-based pagination for large datasets

• Use directUrl for migrations with connection poolers

• Run prisma generate after schema changes

• Add indexes for frequently queried fields

Avoid ❌

• Creating multiple PrismaClient instances

• Using db push in production

• N+1 queries — use include or relationLoadStrategy: 'join'

• Calling $disconnect() in serverless (let runtime handle it)

• Skipping migrations in team environments

Common Schema Patterns

Soft Delete

model Post { id String @id @default(cuid()) deletedAt DateTime?

@@index([deletedAt]) }

// Always filter const posts = await prisma.post.findMany({ where: { deletedAt: null }, });

Polymorphic Relations

model Comment { id String @id @default(cuid()) content String targetType String // "Post" | "Video" targetId String

@@index([targetType, targetId]) }

Troubleshooting

"Types not updating": → Run: npx prisma generate → Restart TypeScript server → Check @prisma/client version matches prisma

"Migration failed": → Check DATABASE_URL is correct → For pooled connections, use directUrl for migrations → Try: npx prisma migrate reset (dev only!)

"Connection pool exhausted": → Use singleton pattern → Set connection_limit in DATABASE_URL → For serverless: npx prisma accelerate

"N+1 query performance": → Add include: { relation: true } → Use relationLoadStrategy: 'join' (Prisma 5+) → Use select instead of include

File Structure

prisma/ ├── schema.prisma # Database schema ├── migrations/ # Migration history └── seed.ts # Seed script

src/lib/ └── prisma.ts # Singleton client

References

• https://prisma.io/docs — Official documentation

• https://prisma.io/docs/concepts/components/prisma-schema — Schema reference

• https://prisma.io/docs/concepts/components/prisma-client — Client API