Prisma ORM Complete Guide 2026: Schema, Migrations and Next.js Integration

npm install prisma --save-dev
npm install @prisma/client

# Initialize Prisma (creates prisma/schema.prisma + .env)
npx prisma init --datasource-provider postgresql

# For SQLite (development, no server needed)
npx prisma init --datasource-provider sqlite

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

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

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

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

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

  @@map("profiles")
}

model Post {
  id          Int        @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean    @default(false)
  authorId    Int        @map("author_id")
  author      User       @relation(fields: [authorId], references: [id])
  categories  Category[] @relation("PostToCategory")
  createdAt   DateTime   @default(now()) @map("created_at")

  @@map("posts")
  @@fulltext([title, content])  // PostgreSQL full-text search
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[] @relation("PostToCategory")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

# Create and run migration
npx prisma migrate dev --name add_user_profile

# This:
# 1. Generates SQL migration file
# 2. Applies it to development database
# 3. Regenerates Prisma Client

# Introspect existing database (legacy databases)
npx prisma db pull

# Push schema without migration (prototyping only)
npx prisma db push

# View database in Prisma Studio (GUI)
npx prisma studio

# Production deployment
npx prisma migrate deploy  # apply pending migrations (no dev features)

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

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

// ─── CREATE ───────────────────────────────────────────────────

// Create with nested relation
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice Chen',
    profile: {
      create: { bio: 'Senior Developer' },
    },
  },
  include: { profile: true },  // include related data in result
});

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: 'bob@example.com', name: 'Bob' },
    { email: 'carol@example.com', name: 'Carol' },
  ],
  skipDuplicates: true,
});

// ─── READ ────────────────────────────────────────────────────

// Find by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    profile: true,
  },
});

// Find many with pagination
const { users, total } = await prisma.$transaction([
  prisma.user.findMany({
    where: { active: true, role: 'USER' },
    orderBy: { createdAt: 'desc' },
    skip: (page - 1) * pageSize,
    take: pageSize,
    select: {
      id: true,
      name: true,
      email: true,
      createdAt: true,
      // Don't select password!
    },
  }),
  prisma.user.count({ where: { active: true, role: 'USER' } }),
]);

// ─── UPDATE ──────────────────────────────────────────────────

// Update one
const updated = await prisma.post.update({
  where: { id: 1 },
  data: {
    published: true,
    categories: {
      connect: [{ id: 1 }, { id: 3 }],
    },
  },
});

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

// ─── DELETE ──────────────────────────────────────────────────
await prisma.user.delete({ where: { id: 1 } });

// Soft delete pattern
await prisma.user.update({
  where: { id: 1 },
  data: { active: false },  // never hard delete in production
});

// Transaction — all or nothing
const [order, inventory] = await prisma.$transaction([
  prisma.order.create({ data: { userId, total } }),
  prisma.inventory.update({
    where: { productId },
    data: { quantity: { decrement: quantity } },
  }),
]);

// Interactive transaction (when order matters)
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });
  if (!user || !user.active) throw new Error('User not eligible');

  const order = await tx.order.create({ data: { userId, total } });
  await tx.inventory.update({
    where: { productId },
    data: { quantity: { decrement: quantity } },
  });

  return order;
});

// Raw queries for complex SQL
const result = await prisma.$queryRaw`
  SELECT u.id, u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > 5
  ORDER BY post_count DESC
  LIMIT 10
`;

// Full-text search
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: query, mode: 'insensitive' } },
      { content: { contains: query, mode: 'insensitive' } },
    ],
  },
});

// lib/prisma.ts — singleton to avoid too many connections
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'],
  });

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

// API route usage
// app/api/users/route.ts
import { prisma } from '@/lib/prisma';
import { NextResponse } from 'next/server';

export async function GET() {
  const users = await prisma.user.findMany({
    select: { id: true, name: true, email: true },
    where: { active: true },
    orderBy: { createdAt: 'desc' },
  });
  return NextResponse.json(users);
}

# .env — use pgbouncer URL for production
DATABASE_URL="postgresql://user:pass@pgbouncer-host:6432/mydb?pgbouncer=true"
DIRECT_URL="postgresql://user:pass@postgres-host:5432/mydb"

# prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")   # pgbouncer (pooled)
  directUrl = env("DIRECT_URL")     # direct for migrations
}