Next.js

Database Integration with Prisma

28 min Lesson 18 of 40

Introduction to Prisma ORM

Prisma is a modern ORM (Object-Relational Mapping) that makes database access easy and type-safe for Next.js applications. It provides an intuitive API, automatic migrations, and excellent TypeScript support. This lesson covers complete database integration with Prisma.

Why Prisma? Prisma offers type safety, auto-completion, automatic migrations, and works seamlessly with Next.js Server Components and API routes. It supports PostgreSQL, MySQL, SQLite, MongoDB, and more.

Installing Prisma

Let's set up Prisma in your Next.js project:

# Install Prisma CLI as dev dependency
npm install -D prisma

# Install Prisma Client
npm install @prisma/client

# Initialize Prisma
npx prisma init

This creates a prisma folder with a schema.prisma file and adds a .env file for your database connection.

Database Configuration

Configure your database connection in the .env file:

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

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (for development)
DATABASE_URL="file:./dev.db"

Development Tip: Use SQLite for quick local development, but switch to PostgreSQL or MySQL for production. Prisma makes switching databases seamless.

Creating Your Schema

Define your data models in prisma/schema.prisma:

// 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?
  hashedPassword String?
  image         String?
  role          Role      @default(USER)
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  posts         Post[]
  comments      Comment[]
  sessions      Session[]
  accounts      Account[]

  @@map("users")
}

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String    @db.Text
  excerpt     String?
  published   Boolean   @default(false)
  views       Int       @default(0)
  authorId    String
  categoryId  String?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  category    Category? @relation(fields: [categoryId], references: [id])
  comments    Comment[]
  tags        PostTag[]

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

model Category {
  id          String   @id @default(cuid())
  name        String   @unique
  slug        String   @unique
  description String?
  createdAt   DateTime @default(now())

  posts       Post[]

  @@map("categories")
}

model Comment {
  id          String   @id @default(cuid())
  content     String   @db.Text
  postId      String
  authorId    String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  post        Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([postId])
  @@index([authorId])
  @@map("comments")
}

model Tag {
  id          String    @id @default(cuid())
  name        String    @unique
  slug        String    @unique
  createdAt   DateTime  @default(now())

  posts       PostTag[]

  @@map("tags")
}

model PostTag {
  postId      String
  tagId       String

  post        Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag         Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@map("post_tags")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

// Auth.js models
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

Schema Features: This schema includes relations (one-to-many, many-to-many), indexes for performance, cascading deletes, default values, and enums for type safety.

Running Migrations

After defining your schema, create and run migrations:

# Create a migration
npx prisma migrate dev --name init

# This does three things:
# 1. Creates SQL migration files in prisma/migrations
# 2. Applies the migration to your database
# 3. Generates Prisma Client

# Apply migrations in production
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

Warning: Never use migrate reset in production as it deletes all data. Use migrate deploy for production deployments.

Setting Up Prisma Client

Create a Prisma Client instance that can be reused across your application:

<!-- lib/prisma.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

Performance Tip: This pattern prevents creating multiple Prisma Client instances during development hot reloading, which can exhaust database connections.

Basic CRUD Operations

Let's implement Create, Read, Update, and Delete operations:

Create Operations

<!-- lib/actions/post-actions.ts -->
'use server'

import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'

export async function createPost(data: {
  title: string
  slug: string
  content: string
  excerpt?: string
  authorId: string
  categoryId?: string
  published?: boolean
}) {
  try {
    const post = await prisma.post.create({
      data: {
        title: data.title,
        slug: data.slug,
        content: data.content,
        excerpt: data.excerpt,
        published: data.published ?? false,
        author: {
          connect: { id: data.authorId }
        },
        ...(data.categoryId && {
          category: {
            connect: { id: data.categoryId }
          }
        })
      },
      include: {
        author: {
          select: {
            id: true,
            name: true,
            email: true,
          }
        },
        category: true,
      }
    })

    revalidatePath('/blog')
    return { success: true, post }
  } catch (error) {
    console.error('Create post error:', error)
    return { success: false, error: 'Failed to create post' }
  }
}

// Create with relations (many-to-many)
export async function createPostWithTags(data: {
  title: string
  slug: string
  content: string
  authorId: string
  tagIds: string[]
}) {
  try {
    const post = await prisma.post.create({
      data: {
        title: data.title,
        slug: data.slug,
        content: data.content,
        author: {
          connect: { id: data.authorId }
        },
        tags: {
          create: data.tagIds.map(tagId => ({
            tag: {
              connect: { id: tagId }
            }
          }))
        }
      },
      include: {
        tags: {
          include: {
            tag: true
          }
        }
      }
    })

    revalidatePath('/blog')
    return { success: true, post }
  } catch (error) {
    return { success: false, error: 'Failed to create post' }
  }
}

Read Operations

<!-- lib/queries/post-queries.ts -->
import { prisma } from '@/lib/prisma'
import { cache } from 'react'

// Get single post (cached)
export const getPostBySlug = cache(async (slug: string) => {
  return await prisma.post.findUnique({
    where: { slug },
    include: {
      author: {
        select: {
          id: true,
          name: true,
          email: true,
          image: true,
        }
      },
      category: true,
      comments: {
        include: {
          author: {
            select: {
              id: true,
              name: true,
              image: true,
            }
          }
        },
        orderBy: {
          createdAt: 'desc'
        }
      },
      tags: {
        include: {
          tag: true
        }
      }
    }
  })
})

// Get all posts with pagination and filtering
export async function getPosts({
  page = 1,
  limit = 10,
  categoryId,
  authorId,
  published = true,
  searchQuery,
}: {
  page?: number
  limit?: number
  categoryId?: string
  authorId?: string
  published?: boolean
  searchQuery?: string
}) {
  const skip = (page - 1) * limit

  const where = {
    ...(published !== undefined && { published }),
    ...(categoryId && { categoryId }),
    ...(authorId && { authorId }),
    ...(searchQuery && {
      OR: [
        { title: { contains: searchQuery, mode: 'insensitive' as const } },
        { content: { contains: searchQuery, mode: 'insensitive' as const } },
      ]
    })
  }

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      skip,
      take: limit,
      orderBy: {
        createdAt: 'desc'
      },
      include: {
        author: {
          select: {
            id: true,
            name: true,
            image: true,
          }
        },
        category: true,
        _count: {
          select: {
            comments: true
          }
        }
      }
    }),
    prisma.post.count({ where })
  ])

  return {
    posts,
    pagination: {
      total,
      pages: Math.ceil(total / limit),
      currentPage: page,
      perPage: limit,
    }
  }
}

// Aggregate queries
export async function getPostStats(authorId: string) {
  const stats = await prisma.post.aggregate({
    where: { authorId },
    _count: {
      id: true
    },
    _sum: {
      views: true
    },
    _avg: {
      views: true
    }
  })

  return stats
}

Update Operations

<!-- lib/actions/post-actions.ts -->
export async function updatePost(
  id: string,
  data: {
    title?: string
    slug?: string
    content?: string
    excerpt?: string
    published?: boolean
    categoryId?: string | null
  }
) {
  try {
    const post = await prisma.post.update({
      where: { id },
      data: {
        ...(data.title && { title: data.title }),
        ...(data.slug && { slug: data.slug }),
        ...(data.content && { content: data.content }),
        ...(data.excerpt !== undefined && { excerpt: data.excerpt }),
        ...(data.published !== undefined && { published: data.published }),
        ...(data.categoryId !== undefined && {
          category: data.categoryId
            ? { connect: { id: data.categoryId } }
            : { disconnect: true }
        })
      }
    })

    revalidatePath('/blog')
    revalidatePath(`/blog/${post.slug}`)
    return { success: true, post }
  } catch (error) {
    return { success: false, error: 'Failed to update post' }
  }
}

// Update with relations
export async function updatePostTags(postId: string, tagIds: string[]) {
  try {
    await prisma.post.update({
      where: { id: postId },
      data: {
        tags: {
          deleteMany: {}, // Remove all existing tags
          create: tagIds.map(tagId => ({
            tag: {
              connect: { id: tagId }
            }
          }))
        }
      }
    })

    revalidatePath('/blog')
    return { success: true }
  } catch (error) {
    return { success: false, error: 'Failed to update tags' }
  }
}

// Increment view count
export async function incrementPostViews(slug: string) {
  await prisma.post.update({
    where: { slug },
    data: {
      views: {
        increment: 1
      }
    }
  })
}

Delete Operations

<!-- lib/actions/post-actions.ts -->
export async function deletePost(id: string) {
  try {
    await prisma.post.delete({
      where: { id }
    })

    revalidatePath('/blog')
    return { success: true }
  } catch (error) {
    return { success: false, error: 'Failed to delete post' }
  }
}

// Soft delete (using a deletedAt field)
export async function softDeletePost(id: string) {
  try {
    await prisma.post.update({
      where: { id },
      data: {
        deletedAt: new Date()
      }
    })

    revalidatePath('/blog')
    return { success: true }
  } catch (error) {
    return { success: false, error: 'Failed to delete post' }
  }
}

// Bulk delete
export async function deletePostsByAuthor(authorId: string) {
  try {
    const result = await prisma.post.deleteMany({
      where: { authorId }
    })

    revalidatePath('/blog')
    return { success: true, count: result.count }
  } catch (error) {
    return { success: false, error: 'Failed to delete posts' }
  }
}

Transactions

Use transactions to ensure multiple operations succeed or fail together:

export async function createUserWithProfile(data: {
  email: string
  name: string
  password: string
  bio?: string
}) {
  try {
    const hashedPassword = await bcrypt.hash(data.password, 12)

    const result = await prisma.$transaction(async (tx) => {
      // Create user
      const user = await tx.user.create({
        data: {
          email: data.email,
          name: data.name,
          hashedPassword,
        }
      })

      // Create profile
      const profile = await tx.profile.create({
        data: {
          userId: user.id,
          bio: data.bio || ''
        }
      })

      // Create default settings
      const settings = await tx.settings.create({
        data: {
          userId: user.id,
          theme: 'light',
          emailNotifications: true,
        }
      })

      return { user, profile, settings }
    })

    return { success: true, data: result }
  } catch (error) {
    return { success: false, error: 'Failed to create user' }
  }
}

Transaction Guarantee: If any operation within a transaction fails, all changes are rolled back, ensuring data consistency.

Advanced Queries

// Complex filtering with nested relations
export async function searchPosts(query: string) {
  return await prisma.post.findMany({
    where: {
      OR: [
        { title: { contains: query, mode: 'insensitive' } },
        { content: { contains: query, mode: 'insensitive' } },
        {
          author: {
            name: { contains: query, mode: 'insensitive' }
          }
        },
        {
          tags: {
            some: {
              tag: {
                name: { contains: query, mode: 'insensitive' }
              }
            }
          }
        }
      ],
      published: true,
    },
    include: {
      author: true,
      category: true,
      tags: {
        include: { tag: true }
      }
    },
    orderBy: [
      { views: 'desc' },
      { createdAt: 'desc' }
    ],
    take: 20
  })
}

// Raw SQL for complex queries
export async function getPopularPostsByViews() {
  return await prisma.$queryRaw`
    SELECT p.*, u.name as author_name, COUNT(c.id) as comment_count
    FROM posts p
    LEFT JOIN users u ON p.author_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.published = true
    GROUP BY p.id, u.name
    ORDER BY p.views DESC, comment_count DESC
    LIMIT 10
  `
}

Practice Exercise:

Create a schema for a blog with users, posts, categories, tags, and comments
Implement full CRUD operations for posts with proper error handling
Add pagination and filtering to the posts list
Create a transaction that publishes a post and sends notifications
Implement a search function that queries across multiple fields and relations
Add view counting that increments atomically without race conditions

Prisma Studio

Prisma Studio provides a visual database browser:

# Launch Prisma Studio
npx prisma studio

# Opens http://localhost:5555

Development Tool: Use Prisma Studio during development to visually browse and edit your database records without writing queries.

Best Practices

Use cache() wrapper for frequently accessed queries in Server Components
Always use revalidatePath() after mutations to update cached data
Add indexes to frequently queried fields for better performance
Use select to fetch only needed fields, reducing data transfer
Implement proper error handling and return user-friendly messages
Use transactions for operations that must succeed or fail together
Never expose Prisma Client in client components - use Server Actions

Summary

Prisma provides a type-safe, intuitive way to work with databases in Next.js. With automatic migrations, excellent TypeScript support, and seamless integration with Server Components and Server Actions, Prisma is the ideal database solution for modern Next.js applications.