Database Integration — Full-Stack with Next.js | Sabaoon Academy

Most full-stack apps need a database. Prisma is the most popular ORM for Next.js projects. It gives you type-safe database access, auto-generated queries, and a migration system.

Setting Up Prisma

Install Prisma and initialize it:

pnpm add @prisma/client
pnpm add -D prisma
npx prisma init

This creates a prisma/ directory with a schema.prisma file and adds a DATABASE_URL to your .env file.

Configuring the Database

Edit .env with your connection string:

# PostgreSQL (Neon, Supabase, Railway, etc.)
DATABASE_URL="postgresql://user:password@host:5432/mydb?sslmode=require"

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

Defining Models

Edit prisma/schema.prisma to define your data models:

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?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

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

Running Migrations

After defining or updating your models, create and apply a migration:

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

# Apply migrations in production
npx prisma migrate deploy

# Reset the database (destructive — development only)
npx prisma migrate reset

Setting Up the Prisma Client

Create a singleton client to avoid creating multiple instances during development hot reloads.

Create app/lib/db.ts:

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

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

CRUD Operations

Create

import { prisma } from "@/app/lib/db";

// Create a user
const user = await prisma.user.create({
  data: {
    email: "alice@example.com",
    name: "Alice",
  },
});

// Create a post with a relation
const post = await prisma.post.create({
  data: {
    title: "My First Post",
    content: "Hello world!",
    authorId: user.id,
    tags: {
      connectOrCreate: [
        {
          where: { name: "nextjs" },
          create: { name: "nextjs" },
        },
      ],
    },
  },
});

Read

// Find all published posts with their author
const posts = await prisma.post.findMany({
  where: { published: true },
  include: { author: true, tags: true },
  orderBy: { createdAt: "desc" },
  take: 10,
});

// Find a single post by ID
const post = await prisma.post.findUnique({
  where: { id: "some-id" },
  include: { author: true },
});

// Search posts
const results = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: "Next.js", mode: "insensitive" } },
      { content: { contains: "Next.js", mode: "insensitive" } },
    ],
  },
});

Update

// Update a post
const updated = await prisma.post.update({
  where: { id: "some-id" },
  data: {
    title: "Updated Title",
    published: true,
  },
});

// Update many
await prisma.post.updateMany({
  where: { authorId: "user-id" },
  data: { published: false },
});

Delete

// Delete a post
await prisma.post.delete({
  where: { id: "some-id" },
});

// Delete many
await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: { lt: new Date("2025-01-01") },
  },
});

Using Prisma in Server Components

Since Server Components run on the server, you can query the database directly:

import { prisma } from "@/app/lib/db";

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

  return (
    <div>
      <h1>Posts</h1>
      {posts.map((post) => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>By {post.author.name}</p>
          <p>{post.content}</p>
        </article>
      ))}
    </div>
  );
}

Using Prisma in Server Actions

"use server";

import { prisma } from "@/app/lib/db";
import { revalidatePath } from "next/cache";
import { z } from "zod";

const CreatePostSchema = z.object({
  title: z.string().min(1).max(200),
  content: z.string().min(1),
});

export async function createPost(formData: FormData) {
  const data = CreatePostSchema.parse({
    title: formData.get("title"),
    content: formData.get("content"),
  });

  await prisma.post.create({
    data: {
      ...data,
      authorId: "current-user-id", // from auth session
    },
  });

  revalidatePath("/posts");
}

Using Prisma in Route Handlers

import { NextRequest, NextResponse } from "next/server";
import { prisma } from "@/app/lib/db";

export async function GET(request: NextRequest) {
  const page = parseInt(request.nextUrl.searchParams.get("page") ?? "1");
  const pageSize = 10;

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

  return NextResponse.json({
    posts,
    total,
    pages: Math.ceil(total / pageSize),
  });
}

Seeding the Database

Create prisma/seed.ts:

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

const prisma = new PrismaClient();

async function main() {
  const alice = await prisma.user.create({
    data: {
      email: "alice@example.com",
      name: "Alice",
      posts: {
        create: [
          { title: "First Post", content: "Hello!", published: true },
          { title: "Draft Post", content: "Work in progress..." },
        ],
      },
    },
  });

  console.log("Seeded:", { alice });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Add the seed command to package.json:

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Run it with:

npx prisma db seed

Summary

Install Prisma and initialize with npx prisma init.
Define models in schema.prisma with relations, defaults, and constraints.
Use prisma migrate dev to create and apply migrations during development.
Create a singleton Prisma client in app/lib/db.ts to avoid connection issues.
Query the database directly in Server Components, Server Actions, and Route Handlers.
Use prisma db seed to populate your database with test data.