Prisma ORM Guide 2026 — Type-Safe Database Access with PostgreSQL

Introduction

Why This Matters

Prisma is the most widely adopted ORM in the TypeScript ecosystem in 2026. Its generated client provides type-safe database access, auto-completion, and compile-time query validation — eliminating a whole class of runtime SQL errors.

Setup and Schema

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
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])
  @@map("users")
}
 
model Post {
  id          String   @id @default(cuid())
  title       String
  slug        String   @unique
  content     String?
  published   Boolean  @default(false)
  publishedAt DateTime?
  authorId    String
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags        Tag[]
  createdAt   DateTime @default(now())
 
  @@index([authorId])
  @@index([published, publishedAt(sort: Desc)])
  @@map("posts")
}
 
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
 
  @@map("tags")
}
 
model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@map("profiles")
}
 
enum Role {
  ADMIN
  USER
  VIEWER
}

Database Client Singleton

// 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', 'warn', 'error'] : ['error'],
  })
 
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

CRUD and Relations

// Create with nested relation
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    profile: {
      create: { bio: 'Software engineer' },
    },
  },
  include: { profile: true },
})
 
// Paginated query with filtering
async function getPublishedPosts(page: number, limit: number, authorId?: string) {
  const where = {
    published: true,
    ...(authorId ? { authorId } : {}),
  }
 
  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { publishedAt: 'desc' },
      include: {
        author: { select: { id: true, name: true } },
        tags: { select: { name: true } },
      },
    }),
    prisma.post.count({ where }),
  ])
 
  return { posts, total, pages: Math.ceil(total / limit) }
}
 
// Update with upsert
const tag = await prisma.tag.upsert({
  where: { name: 'typescript' },
  update: {},
  create: { name: 'typescript' },
})

Transactions

// Sequential transaction
async function publishPost(postId: string, authorId: string) {
  return prisma.$transaction(async (tx) => {
    const post = await tx.post.findUnique({ where: { id: postId } })
 
    if (!post) throw new Error('Post not found')
    if (post.authorId !== authorId) throw new Error('Unauthorized')
    if (post.published) throw new Error('Already published')
 
    return tx.post.update({
      where: { id: postId },
      data: { published: true, publishedAt: new Date() },
    })
  })
}
 
// Batch operations
async function transferCredits(fromId: string, toId: string, amount: number) {
  return prisma.$transaction([
    prisma.user.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    }),
    prisma.user.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    }),
  ])
}

Migrations Workflow

# Development: create and apply migration
npx prisma migrate dev --name add_published_at_to_posts
 
# Production: apply pending migrations
npx prisma migrate deploy
 
# View migration history
npx prisma migrate status
 
# Reset dev database (destructive)
npx prisma migrate reset
 
# Generate client after schema change
npx prisma generate

Common Mistakes

Instantiating new PrismaClient() in every module — creates too many connections; use a singleton
Using findMany without take — can return millions of rows
Forgetting to add @@index on foreign key columns — causes full table scans
Using select: * in production queries when only a subset of fields is needed
Not using $transaction for multi-step mutations that must be atomic

Best Practices

Add a @@map to every model to control table names independently of model names
Always paginate list queries with take and skip or cursor-based pagination
Add indexes on columns used in where, orderBy, and foreign keys
Use select to fetch only the fields you actually render — reduces data transfer
Run npx prisma validate in CI to catch schema errors before deployment

Key Takeaways

Prisma generates a fully typed client from your schema — no raw SQL strings, no runtime surprises
Use a singleton PrismaClient to avoid exhausting the database connection pool
prisma.$transaction provides ACID guarantees for multi-step mutations
@@index in the schema generates database indexes — missing indexes are the most common Prisma performance issue
prisma migrate deploy (not migrate dev) is safe for production migration runs
include fetches related models eagerly; select reduces the payload to only needed fields
upsert atomically handles insert-or-update in a single round trip
Prisma Data Proxy or PgBouncer is required for serverless deployments to manage connection pooling

Introduction#

Why This Matters#

Setup and Schema#

Database Client Singleton#

CRUD and Relations#

Transactions#

Migrations Workflow#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading