Chapter 7: Database Integration

1. Prisma Installation & Initialization

When integrating a relational database into a Next.js project, Prisma provides a type-safe client and migration workflow. First install the CLI and runtime dependencies:

npm install prisma @prisma/client

Run initialization in the project root to create default configuration and example environment files:

npx prisma init

Common generated files:

• prisma/schema.prisma: Data models, data source, and generator configuration.
• .env: Stores DATABASE_URL and other sensitive connection info (should be added to .gitignore).

Configure the connection string in .env, adjusting protocol and parameters for your chosen database:

# PostgreSQL 示例
DATABASE_URL="postgresql://USER:PASSWORD@localhost:5432/mydb?schema=public"

# MySQL 示例
# DATABASE_URL="mysql://USER:PASSWORD@localhost:3306/mydb"

# SQLite（本地文件，适合学习与原型）
# DATABASE_URL="file:./dev.db"

Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, CockroachDB, MongoDB, and more; specify the provider in schema.prisma's datasource.

2. Schema Definition

prisma/schema.prisma consists of three typical parts: datasource (which database to connect to), generator (how to generate Prisma Client), and several models (table structures and relationships).

• datasource: provider and url = env("DATABASE_URL").
• generator: Typically provider = "prisma-client-js", generating a Node-usable client.
• Model: Maps to database tables; field types include String, Int, Boolean, DateTime, Json, Bytes, etc.; attributes include @id, @default, @unique, @updatedAt.
• Relations: Use field types referencing another Model with @relation to declare foreign keys and relation names; common pattern is one-to-many (e.g., User → Post); many-to-many uses a join table (explicit Model) connecting both sides.

Below is a complete example with User and Post in a one-to-many relationship (PostgreSQL). Prisma uses its own DSL; shown here with TypeScript-style highlighting as an approximation (install the Prisma editor plugin for accurate coloring):

// 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?
  role      String   @default("USER")
  profile   Json?
  createdAt DateTime @default(now())
  posts     Post[]
}

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

  @@index([authorId])
}

After running prisma migrate dev, the migration directory will contain SQL generated for the actual database (PostgreSQL example):

-- 示意：由 Prisma 生成的迁移文件中的 SQL 片段

CREATE TABLE "User" (
  "id" TEXT NOT NULL,
  "email" TEXT NOT NULL,
  "name" TEXT,
  CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Note: Post.authorId is the foreign key; User.posts and Post.author form bidirectional navigation. For many-to-many, add a Tag and PostTag join table with bidirectional @relation.

3. Migration & Database Sync

After modifying the Schema, you need to align the database structure and regenerate the type-safe client.

Migration (recommended for teams and traceable history)

npx prisma migrate dev --name init

This command generates SQL migration files based on the current schema.prisma, applies them to the development database, and runs prisma generate.

Quick Push (prototyping phase)

npx prisma db push

Syncs the Schema directly to the database without generating migration history; suitable for personal experiments or temporary environments. Production environments should prefer the migrate workflow.

Visualization & Client

npx prisma studio
npx prisma generate

• Prisma Studio: Browse and edit table data in the browser, convenient for debugging.
• generate: Generates/updates node_modules/.prisma/client based on the Schema; often explicitly run before CI or deployment.

Typical development flow: Modify Schema → migrate dev (or db push) → Use prisma client in code. After pulling others' migrations, run npx prisma migrate deploy (production) to sync the database.

4. Prisma Client Singleton

Problem: In Next.js development mode, modules may be re-executed multiple times due to hot reload. Creating a new PrismaClient each time can accumulate database connections.

Solution: Cache the singleton on the global object, creating it only when it doesn't exist. In production, also consider using connection pools (e.g., PgBouncer) and managed database limits.

Create src/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;

Then uniformly use import { prisma } from "@/lib/prisma" in Server Components, Route Handlers, and Server Actions (path alias needs @/* configured in tsconfig.json).

5. CRUD Operations

The following assumes User / Post models are defined as above and the client has been generated.

Create

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

await prisma.user.create({
  data: {
    email: "dev@example.com",
    name: "开发者",
  },
});

await prisma.post.create({
  data: {
    title: "第一篇",
    authorId: userId,
  },
});

Read

const users = await prisma.user.findMany({
  where: { role: "USER" },
  orderBy: { createdAt: "desc" },
});

const one = await prisma.user.findUnique({
  where: { email: "dev@example.com" },
});

const first = await prisma.post.findFirst({
  where: { published: true },
});

Update

await prisma.user.update({
  where: { id: userId },
  data: { name: "新名字" },
});

await prisma.post.updateMany({
  where: { authorId: userId },
  data: { published: true },
});

Delete

await prisma.post.delete({
  where: { id: postId },
});

Relation Queries: include / select

const postsWithAuthor = await prisma.post.findMany({
  include: { author: true },
});

const slim = await prisma.user.findMany({
  select: { id: true, email: true, _count: { select: { posts: true } } },
});

Pagination: skip / take

const page = 2;
const pageSize = 10;

const pageRows = await prisma.post.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: "desc" },
});

6. Combining with Server Actions

In App Router, you can call prisma directly inside server functions (with "use server") for form submissions and list refresh after changes. The example below includes: a create post form, a list queried by Server Component, deletion triggered by Server Action, and revalidatePath to invalidate the cached page.

app/posts/actions.ts

"use server";

import { revalidatePath } from "next/cache";
import { prisma } from "@/lib/prisma";

export async function createPost(formData: FormData) {
  const title = String(formData.get("title") ?? "").trim();
  const authorId = String(formData.get("authorId") ?? "").trim();
  if (!title || !authorId) return;

  await prisma.post.create({
    data: { title, authorId, published: false },
  });
  revalidatePath("/posts");
}

export async function deletePost(postId: string) {
  await prisma.post.delete({ where: { id: postId } });
  revalidatePath("/posts");
}

app/posts/page.tsx (List + Form + Delete)

import { prisma } from "@/lib/prisma";
import { createPost, deletePost } from "./actions";

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

  const users = await prisma.user.findMany({ select: { id: true, email: true } });

  return (
    <div className="mx-auto max-w-2xl space-y-8 p-6">
      <form action={createPost} className="space-y-3 rounded border p-4">
        <input name="title" placeholder="标题" className="w-full border px-2 py-1" required />
        <select name="authorId" className="w-full border px-2 py-1" required>
          {users.map((u) => (
            <option key={u.id} value={u.id}>
              {u.email}
            </option>
          ))}
        </select>
        <button type="submit" className="rounded bg-black px-3 py-1 text-white">
          创建帖子
        </button>
      </form>

      <ul className="space-y-2">
        {posts.map((p) => (
          <li key={p.id} className="flex items-center justify-between border-b py-2">
            <div>
              <div className="font-medium">{p.title}</div>
              <div className="text-sm text-gray-500">
                {p.author.email} · {p.createdAt.toISOString()}
              </div>
            </div>
            <form action={deletePost.bind(null, p.id)}>
              <button type="submit" className="text-sm text-red-600">
                删除
              </button>
            </form>
          </li>
        ))}
      </ul>
    </div>
  );
}

Note: createPost and deletePost execute only on the server; the page defaults to Server Component with data read server-side. After changes, revalidatePath("/posts") triggers re-rendering and data update for that route.

7. Environment Variables Configuration

• .env: Local defaults; must be added to .gitignore, never commit to Git.
• .env.local: Next.js loads this with higher priority, commonly used for personal machine overrides (also should not be committed).
• Vercel: Configure DATABASE_URL etc. in project Settings → Environment Variables for Preview / Production; redeploy for new variables to take effect.

Example (structural illustration only, replace with real passwords):

{
  "NOTE": "实际使用 .env 文件，每行 KEY=VALUE；此处为说明用 JSON 结构",
  "DATABASE_URL": "postgresql://user:password@host:5432/dbname"
}

8. Drizzle ORM Introduction

Drizzle is another popular TypeScript data layer solution, leaning toward "SQL-style" with a lightweight runtime, often paired with drizzle-kit for migrations.

Drizzle query example (conceptual demo):

import { eq } from "drizzle-orm";
import { db } from "./db";
import { posts } from "./schema";

export async function listPublished() {
  return db.select().from(posts).where(eq(posts.published, true));
}

Recommendation: If your team is familiar with SQL and wants minimal abstraction with fine-grained control, evaluate Drizzle; for rapid modeling, an all-in-one toolchain, and abundant existing resources, Prisma remains a solid starting point. Both work with Next.js Server Components / Server Actions.

9. Chapter Summary