Database Integration

Database Integration with Prisma

Next.js applications often need persistent data storage, and Prisma has become the most popular ORM for the TypeScript/JavaScript ecosystem. Prisma provides a type-safe database client generated from your schema, automatic migrations, and an intuitive query API. In Next.js, database operations run exclusively on the server — in Server Components, API routes, and Server Actions. Prisma works with PostgreSQL, MySQL, SQLite, MongoDB, and other databases. Its type safety means that query results are fully typed and invalid queries are caught at compile time.

Setting Up Prisma

# Install Prisma
npm install prisma --save-dev
npm install @prisma/client

# Initialize Prisma with SQLite for development
npx prisma init --datasource-provider sqlite

# This creates:
# prisma/schema.prisma — database schema
# .env — database connection string

Defining the Schema

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

datasource db {
  provider = "sqlite"      // Use "postgresql" for production
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  role      String   @default("viewer")
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id          Int      @id @default(autoincrement())
  title       String
  slug        String   @unique
  body        String
  published   Boolean  @default(false)
  author      User     @relation(fields: [authorId], references: [id])
  authorId    Int
  tags        Tag[]
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

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

# Run migrations
npx prisma migrate dev --name init

# Generate the Prisma Client
npx prisma generate

# Open Prisma Studio (GUI for your database)
npx prisma studio

Database Client Singleton

// lib/prisma.ts — singleton pattern for Next.js
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"] : [],
  });

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

export default prisma;

Using Prisma in Server Components

// app/blog/page.tsx — fetch posts directly in Server Component
import prisma from "@/lib/prisma";
import Link from "next/link";

export default async function BlogPage() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: {
      author: { select: { name: true } },
      tags: { select: { name: true } },
    },
    orderBy: { createdAt: "desc" },
    take: 20,
  });

  return (
    <div>
      <h1>Blog</h1>
      {posts.map((post) => (
        <article key={post.id}>
          <Link href={`/blog/${post.slug}`}>
            <h2>{post.title}</h2>
          </Link>
          <p>By {post.author.name}</p>
          <div className="tags">
            {post.tags.map((tag) => (
              <span key={tag.name} className="tag">{tag.name}</span>
            ))}
          </div>
        </article>
      ))}
    </div>
  );
}

CRUD API Routes with Prisma

// app/api/posts/route.ts
import prisma from "@/lib/prisma";
import { NextResponse } from "next/server";

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const page = parseInt(searchParams.get("page") || "1");
  const limit = parseInt(searchParams.get("limit") || "10");

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where: { published: true },
      include: { author: { select: { name: true, email: true } } },
      orderBy: { createdAt: "desc" },
      skip: (page - 1) * limit,
      take: limit,
    }),
    prisma.post.count({ where: { published: true } }),
  ]);

  return NextResponse.json({
    data: posts,
    meta: { page, limit, total, pages: Math.ceil(total / limit) },
  });
}

export async function POST(request: Request) {
  try {
    const { title, body, authorId, tags } = await request.json();

    const slug = title.toLowerCase().replace(/[^a-z0-9]+/g, "-");

    const post = await prisma.post.create({
      data: {
        title,
        slug,
        body,
        authorId,
        tags: {
          connectOrCreate: tags.map((tag: string) => ({
            where: { name: tag },
            create: { name: tag },
          })),
        },
      },
      include: { author: true, tags: true },
    });

    return NextResponse.json(post, { status: 201 });
  } catch (error: any) {
    if (error.code === "P2002") {
      return NextResponse.json(
        { error: "A post with this slug already exists" },
        { status: 409 }
      );
    }
    return NextResponse.json({ error: "Server error" }, { status: 500 });
  }
}

Tip: Always use the Prisma client singleton pattern in Next.js to prevent creating too many database connections during development hot reloads. In production, the singleton is created once. Use prisma.post.findMany in Server Components for direct database access without an API layer.

Key Takeaways

• Prisma provides a type-safe ORM with auto-generated client, migrations, and an intuitive query API.
• Use the singleton pattern for the Prisma client to avoid connection leaks in development.
• Query the database directly in Server Components — no API route needed for read operations.
• Use API routes for mutations (create, update, delete) that client components trigger.
• Prisma supports relations, pagination, filtering, and transactions for complex data operations.