Prisma ORM Complete Guide 2026: Type-Safe Database Access with PostgreSQL

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

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

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

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

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

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String
  published   Boolean   @default(false)
  publishedAt DateTime?
  views       Int       @default(0)
  authorId    String
  author      User      @relation(fields: [authorId], references: [id])
  comments    Comment[]
  tags        Tag[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([authorId])
  @@index([published, publishedAt(sort: Desc)])
  @@map("posts")
}

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

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

npx prisma migrate dev --name init
npx prisma generate

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'sanjeev@webcoderspeed.com',
    name: 'Sanjeev Sharma',
    profile: {
      create: {  // Create related record
        bio: 'Full-stack developer',
      },
    },
  },
  include: { profile: true },  // Include relation in result
})

// READ with relations
const userWithPosts = await prisma.user.findUnique({
  where: { email: 'sanjeev@webcoderspeed.com' },
  include: {
    posts: {
      where: { published: true },
      orderBy: { publishedAt: 'desc' },
      take: 5,
      select: {
        id: true, title: true, slug: true, publishedAt: true,
        _count: { select: { comments: true } },
      },
    },
    _count: { select: { posts: true } },
  },
})

// UPDATE
const updated = await prisma.post.update({
  where: { id: 'post-id' },
  data: {
    published: true,
    publishedAt: new Date(),
    views: { increment: 1 },  // Atomic increment
  },
})

// UPSERT (create or update)
const tag = await prisma.tag.upsert({
  where: { name: 'typescript' },
  create: { name: 'typescript' },
  update: {},  // Don't update if exists
})

// DELETE with cascade
await prisma.user.delete({ where: { id: 'user-id' } })
// Cascades to: posts, comments, profile, sessions

// Pagination with cursor
async function getPostsCursor(cursor?: string, limit = 20) {
  return prisma.post.findMany({
    where: { published: true },
    take: limit,
    skip: cursor ? 1 : 0,  // Skip the cursor itself
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
    include: { author: { select: { name: true, email: true } } },
  })
}

// Full-text search (PostgreSQL)
const results = await prisma.post.findMany({
  where: {
    OR: [
      { title: { search: 'nextjs typescript' } },
      { content: { search: 'nextjs typescript' } },
    ],
  },
})

// Aggregations
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { views: true },
  _max: { views: true },
  _sum: { views: true },
  where: { published: true },
})

// GroupBy
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _sum: { views: true },
  orderBy: { _sum: { views: 'desc' } },
  having: { views: { _sum: { gt: 100 } } },
})

// Interactive transaction — use when you need query results between operations
async function transferCredits(fromId: string, toId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    const from = await tx.user.findUnique({ where: { id: fromId } })
    if (!from || from.credits < amount) throw new Error('Insufficient credits')

    await tx.user.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    })

    await tx.user.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    })

    await tx.transaction.create({
      data: { fromId, toId, amount, type: 'TRANSFER' },
    })

    return { success: true }
  })
}

// Batch transaction — faster for independent operations
await prisma.$transaction([
  prisma.post.update({ where: { id: 'p1' }, data: { views: { increment: 1 } } }),
  prisma.post.update({ where: { id: 'p2' }, data: { views: { increment: 1 } } }),
  prisma.analytics.create({ data: { event: 'page_view', postId: 'p1' } }),
])

// Type-safe raw queries
const users = await prisma.$queryRaw<User[]>`
  SELECT u.*, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  WHERE u.created_at > ${new Date('2026-01-01')}
  GROUP BY u.id
  ORDER BY post_count DESC
  LIMIT ${20}
`
// Parameters are automatically escaped — no SQL injection

// Execute for side effects
await prisma.$executeRaw`
  UPDATE posts SET views = views + 1 WHERE id = ${postId}
`

// Use Prisma Accelerate for connection pooling + global cache
// Replace your DATABASE_URL with Accelerate URL

const prisma = new PrismaClient()

// Cached queries — results cached at the edge
const topPosts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { views: 'desc' },
  take: 10,
  cacheStrategy: {
    ttl: 300,        // Cache for 5 minutes
    swr: 60,         // Stale-while-revalidate: 1 minute
  },
})

// 1. Select only what you need
const post = await prisma.post.findUnique({
  where: { id },
  select: { title: true, content: true },  // Not include: { author: true }
})

// 2. Use findMany with take instead of findAll
const recent = await prisma.post.findMany({
  take: 20,  // Always paginate!
  orderBy: { createdAt: 'desc' },
})

// 3. Avoid N+1 with include
// BAD:
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } })
}

// GOOD:
const postsWithAuthors = await prisma.post.findMany({
  include: { author: true },  // Single query with JOIN
})

// 4. Create indexes for common queries
// @@index([published, createdAt]) in schema