Prisma ORM | Bootspring Docs

Battle-tested patterns for Prisma ORM with Next.js applications.

Overview#

Prisma provides type-safe database access:

Auto-generated TypeScript types
Intuitive query API
Database migrations
Visual database browser (Prisma Studio)

Prerequisites:

Node.js 18+
PostgreSQL, MySQL, or SQLite database

Installation#

npm install prisma @prisma/client
npx prisma init

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

Implementation#

Client Singleton (Prevents Connection Exhaustion)#

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

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
}

Common Query Patterns#

// Create with relations
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    profile: {
      create: { bio: 'Hello world' }
    }
  },
  include: { profile: true }
})

// Read with filters and pagination
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { email: { contains: '@example.com' } }
  },
  include: { author: { select: { name: true } } },
  orderBy: { createdAt: 'desc' },
  skip: (page - 1) * pageSize,
  take: pageSize
})

// Update
const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Updated Name' }
})

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

// Soft delete pattern
const deleted = await prisma.user.update({
  where: { id: userId },
  data: { deletedAt: new Date() }
})

Transactions#

// Sequential transaction (atomic)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.create({ data: postData })
])

// Interactive transaction (with logic)
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } })
  if (!user) throw new Error('User not found')
  if (user.credits < 10) throw new Error('Insufficient credits')

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

  await tx.transaction.create({
    data: { userId, amount: -10, type: 'PURCHASE' }
  })
})

Server Action Pattern#

// actions/user.ts
'use server'
import { prisma } from '@/lib/db'
import { revalidatePath } from 'next/cache'
import { z } from 'zod'

const UpdateProfileSchema = z.object({
  name: z.string().min(1).max(100),
  bio: z.string().max(500).optional()
})

export async function updateProfile(userId: string, formData: FormData) {
  const validated = UpdateProfileSchema.parse({
    name: formData.get('name'),
    bio: formData.get('bio')
  })

  await prisma.user.update({
    where: { id: userId },
    data: validated
  })

  revalidatePath('/profile')
  return { success: true }
}

Schema Example#

// 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?
  credits   Int      @default(0)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  posts    Post[]
  profile  Profile?

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

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

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

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

Migration Commands#

# Development: create and apply migration
npx prisma migrate dev --name add_posts_table

# Production: apply pending migrations
npx prisma migrate deploy

# Regenerate client after schema changes
npx prisma generate

# Visual database browser
npx prisma studio

# Reset database (WARNING: destroys data)
npx prisma migrate reset

Usage Examples#

API Route#

// app/api/users/[id]/route.ts
import { NextResponse } from 'next/server'
import { prisma } from '@/lib/db'

export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const user = await prisma.user.findUnique({
    where: { id: params.id },
    include: { profile: true }
  })

  if (!user) {
    return NextResponse.json({ error: 'Not found' }, { status: 404 })
  }

  return NextResponse.json(user)
}

Server Component#

// app/users/page.tsx
import { prisma } from '@/lib/db'

export default async function UsersPage() {
  const users = await prisma.user.findMany({
    where: { deletedAt: null },
    orderBy: { createdAt: 'desc' },
    take: 10
  })

  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  )
}

Best Practices#

Use the singleton pattern - Prevent connection exhaustion in development
Enable query logging in dev - Debug slow queries
Add indexes for foreign keys - Improve query performance
Use transactions for related operations - Ensure data consistency
Validate input before database calls - Use Zod for type-safe validation

Transactions - Transaction handling
Migrations - Migration strategies
Soft Delete - Soft delete implementation