第 10 章：数据库集成（Prisma + PostgreSQL）

# 安装 Prisma CLI（开发依赖）和客户端（运行时依赖）
npm install -D prisma
npm install @prisma/client

# 初始化 Prisma
npx prisma init

# .env

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

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

# MongoDB
# DATABASE_URL="mongodb://user:password@localhost:27017/mydb?retryWrites=true"

# PlanetScale（需要 SSL）
# DATABASE_URL="mysql://user:password@aws.connect.psdb.cloud/mydb?sslaccept=strict"

# Neon（Serverless PostgreSQL）
# DATABASE_URL="postgresql://user:password@ep-xxx.us-east-2.aws.neon.tech/mydb?sslmode=require"

// 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;
}

# 创建并应用迁移
npx prisma migrate dev --name init_schema

# 指定迁移名称
npx prisma migrate dev --name add_article_tags

# 只生成迁移 SQL（不执行）
npx prisma migrate dev --create-only --name add_index

# 重置数据库（危险！会删除所有数据）
npx prisma migrate reset

# 生产环境只部署迁移（不创建新迁移）
npx prisma migrate deploy

# Dockerfile

FROM node:20-alpine AS base

# 安装依赖
FROM base AS deps
WORKDIR /app
COPY package.json package-lock.json ./
RUN npm ci

# 构建
FROM base AS builder
WORKDIR /app
COPY --from=deps /app/node_modules ./node_modules
COPY . .

# 生成 Prisma Client
RUN npx prisma generate

RUN npm run build

# 运行
FROM base AS runner
WORKDIR /app

ENV NODE_ENV=production

RUN addgroup --system --gid 1001 nodejs
RUN adduser --system --uid 1001 nextjs

COPY --from=builder /app/public ./public
COPY --from=builder /app/.next/standalone ./
COPY --from=builder /app/.next/static ./.next/static
COPY --from=builder /app/prisma ./prisma
COPY --from=builder /app/node_modules/.prisma ./node_modules/.prisma
COPY --from=builder /app/node_modules/@prisma ./node_modules/@prisma

USER nextjs

EXPOSE 3000

# 启动时执行迁移
CMD ["sh", "-c", "npx prisma migrate deploy && node server.js"]

// prisma/seed-reading-progress.ts

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

const prisma = new PrismaClient();

async function main() {
  // 为已发布文章设置默认阅读进度
  const result = await prisma.article.updateMany({
    where: {
      published: true,
      readingProgress: null,
    },
    data: {
      readingProgress: 0,
    },
  });

  console.log(`Updated ${result.count} articles`);
}

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

// lib/services/article.ts

import { prisma } from '@/lib/prisma';
import { cache } from 'react';
import { revalidatePath, revalidateTag } from 'next/cache';
import type { Article, Prisma } from '@prisma/client';

// ============ 查询（Read） ============

// 使用 React cache 实现请求级去重
export const getArticleBySlug = cache(async (slug: string) => {
  return prisma.article.findUnique({
    where: { slug, published: true },
    include: {
      author: {
        select: { id: true, name: true, avatar: true, bio: true },
      },
      _count: {
        select: { comments: true, likes: true },
      },
    },
  });
});

export const getArticles = cache(async (options: {
  page?: number;
  limit?: number;
  category?: string;
  tag?: string;
  search?: string;
  authorId?: string;
  featured?: boolean;
} = {}) => {
  const {
    page = 1,
    limit = 10,
    category,
    tag,
    search,
    authorId,
    featured,
  } = options;

  const skip = (page - 1) * limit;

  const where: Prisma.ArticleWhereInput = {
    published: true,
    ...(category && { category }),
    ...(tag && { tags: { has: tag } }),
    ...(authorId && { authorId }),
    ...(featured !== undefined && { featured }),
    ...(search && {
      OR: [
        { title: { contains: search, mode: 'insensitive' } },
        { excerpt: { contains: search, mode: 'insensitive' } },
        { content: { contains: search, mode: 'insensitive' } },
      ],
    }),
  };

  const [articles, total] = await Promise.all([
    prisma.article.findMany({
      where,
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
      select: {
        id: true,
        title: true,
        slug: true,
        excerpt: true,
        coverImage: true,
        category: true,
        tags: true,
        views: true,
        readTime: true,
        createdAt: true,
        publishedAt: true,
        author: {
          select: { id: true, name: true, avatar: true },
        },
        _count: {
          select: { comments: true, likes: true },
        },
      },
    }),
    prisma.article.count({ where }),
  ]);

  return {
    articles,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1,
    },
  };
});

// ============ 创建（Create） ============

export async function createArticle(data: {
  title: string;
  slug: string;
  content: string;
  excerpt?: string;
  coverImage?: string;
  category?: string;
  tags?: string[];
  published?: boolean;
  authorId: string;
}) {
  const article = await prisma.article.create({
    data: {
      ...data,
      publishedAt: data.published ? new Date() : null,
      readTime: Math.ceil(data.content.length / 500), // 按 500 字/分钟估算
    },
  });

  revalidatePath('/articles');
  revalidateTag('articles');

  return article;
}

// ============ 更新（Update） ============

export async function updateArticle(
  id: string,
  data: Prisma.ArticleUpdateInput
) {
  // 如果从未发布变为发布，设置 publishedAt
  if (data.published === true) {
    const existing = await prisma.article.findUnique({
      where: { id },
      select: { published: true },
    });
    if (existing && !existing.published) {
      data.publishedAt = new Date();
    }
  }

  const article = await prisma.article.update({
    where: { id },
    data,
  });

  revalidatePath('/articles');
  revalidatePath(`/articles/${article.slug}`);
  revalidateTag('articles');

  return article;
}

// ============ 删除（Delete） ============

export async function deleteArticle(id: string) {
  const article = await prisma.article.findUnique({
    where: { id },
    select: { slug: true },
  });

  if (!article) {
    throw new Error('Article not found');
  }

  await prisma.article.delete({ where: { id } });

  revalidatePath('/articles');
  revalidatePath(`/articles/${article.slug}`);
  revalidateTag('articles');
}

// ============ 统计 ============

export async function incrementViews(slug: string) {
  await prisma.article.update({
    where: { slug },
    data: { views: { increment: 1 } },
  });
}

// app/articles/page.tsx

import { getArticles } from '@/lib/services/article';
import { ArticleCard } from '@/app/components/ArticleCard';
import { Pagination } from '@/app/components/Pagination';
import { Suspense } from 'react';

type Props = {
  searchParams: Promise<{
    page?: string;
    category?: string;
    tag?: string;
    q?: string;
  }>;
};

export default async function ArticlesPage({ searchParams }: Props) {
  const params = await searchParams;

  const { articles, pagination } = await getArticles({
    page: Number(params.page) || 1,
    category: params.category,
    tag: params.tag,
    search: params.q,
  });

  return (
    <div className="max-w-4xl mx-auto py-8 px-4">
      <h1 className="text-3xl font-bold mb-8">文章列表</h1>

      <Suspense fallback={<div className="animate-pulse space-y-4">
        {Array.from({ length: 5 }).map((_, i) => (
          <div key={i} className="h-32 bg-gray-200 rounded" />
        ))}
      </div>}>
        {articles.length === 0 ? (
          <p className="text-gray-500 text-center py-12">暂无文章</p>
        ) : (
          <div className="space-y-6">
            {articles.map((article) => (
              <ArticleCard key={article.id} article={article} />
            ))}
          </div>
        )}
      </Suspense>

      <Pagination
        currentPage={pagination.page}
        totalPages={pagination.totalPages}
        basePath="/articles"
      />
    </div>
  );
}

// lib/services/user.ts

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

// 方式一：批量事务（推荐，性能更好）
export async function deleteAccount(userId: string) {
  try {
    const [comments, articles, likes, user] = await prisma.$transaction([
      prisma.comment.deleteMany({ where: { authorId: userId } }),
      prisma.article.deleteMany({ where: { authorId: userId } }),
      prisma.like.deleteMany({ where: { userId: userId } }),
      prisma.user.delete({ where: { id: userId } }),
    ]);

    return {
      deletedComments: comments.count,
      deletedArticles: articles.count,
      deletedLikes: likes.count,
    };
  } catch (error) {
    console.error('Failed to delete account:', error);
    throw new Error('Account deletion failed, all changes rolled back');
  }
}

// 方式二：交互式事务（需要条件判断时使用）
export async function transferOwnership(
  articleId: string,
  fromUserId: string,
  toUserId: string
) {
  return prisma.$transaction(async (tx) => {
    // 1. 验证文章属于 fromUser
    const article = await tx.article.findFirst({
      where: { id: articleId, authorId: fromUserId },
    });

    if (!article) {
      throw new Error('Article not found or does not belong to the specified user');
    }

    // 2. 验证 toUser 存在
    const toUser = await tx.user.findUnique({
      where: { id: toUserId },
    });

    if (!toUser) {
      throw new Error('Target user not found');
    }

    // 3. 转移所有权
    const updated = await tx.article.update({
      where: { id: articleId },
      data: { authorId: toUserId },
    });

    return updated;
  });
}

// 方式三：带选项的事务（超时、隔离级别）
export async function publishArticleWithNotifications(
  articleId: string,
  followerIds: string[]
) {
  return prisma.$transaction(
    async (tx) => {
      // 发布文章
      const article = await tx.article.update({
        where: { id: articleId },
        data: {
          published: true,
          publishedAt: new Date(),
        },
      });

      // 为所有关注者创建通知
      if (followerIds.length > 0) {
        await tx.notification.createMany({
          data: followerIds.map((followerId) => ({
            userId: followerId,
            type: 'NEW_ARTICLE',
            title: `${article.title} 已发布`,
            link: `/articles/${article.slug}`,
          })),
        });
      }

      return article;
    },
    {
      maxWait: 5000,      // 等待获取事务锁的最大时间（ms）
      timeout: 10000,     // 事务超时时间（ms）
      isolationLevel: 'ReadCommitted', // 隔离级别
    }
  );
}

npm install @prisma/extension-accelerate

// lib/prisma.ts

import { PrismaClient } from '@prisma/client';
import { withAccelerate } from '@prisma/extension-accelerate';

const globalForPrisma = globalThis as unknown as {
  prisma: ReturnType<typeof createPrismaClient> | undefined;
};

function createPrismaClient() {
  return new PrismaClient()
    .$extends(withAccelerate());
}

export const prisma = globalForPrisma.prisma ?? createPrismaClient();

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

# docker-compose.yml

services:
  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      DATABASE_URL: "postgresql://user:pass@postgres:5432/mydb"
      POOL_MODE: "transaction"
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 20
    ports:
      - "6432:6432"

  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"

# .env — 连接 PgBouncer 而非直连 PostgreSQL
DATABASE_URL="postgresql://user:pass@localhost:6432/mydb?pgbouncer=true&connection_limit=1"

// Neon 原生支持 Serverless，无需额外配置连接池
// .env
// DATABASE_URL="postgresql://user:pass@ep-xxx.us-east-2.aws.neon.tech/mydb?sslmode=require"

// lib/prisma.ts 保持标准写法即可

// prisma/seed.ts

import { PrismaClient } from '@prisma/client';
import { hash } from 'bcryptjs';

const prisma = new PrismaClient();

async function main() {
  console.log('🌱 Starting seed...');

  // 1. 创建管理员用户
  const adminPassword = await hash('admin123', 12);
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: '管理员',
      password: adminPassword,
      role: 'ADMIN',
      emailVerified: new Date(),
    },
  });

  console.log('✅ Admin created:', admin.email);

  // 2. 创建测试用户
  const userPassword = await hash('user123', 12);
  const user = await prisma.user.upsert({
    where: { email: 'user@example.com' },
    update: {},
    create: {
      email: 'user@example.com',
      name: '张三',
      password: userPassword,
      role: 'USER',
      emailVerified: new Date(),
      bio: '一个热爱编程的开发者',
    },
  });

  console.log('✅ User created:', user.email);

  // 3. 创建示例文章
  const articles = [
    {
      title: 'Next.js App Router 完全入门',
      slug: 'nextjs-app-router-getting-started',
      content: '# Next.js App Router\n\n这是一篇关于 Next.js App Router 的入门教程...',
      excerpt: '从零开始学习 Next.js App Router，掌握最新的路由系统和渲染策略。',
      category: 'frontend',
      tags: ['nextjs', 'react', 'app-router'],
      published: true,
      publishedAt: new Date(),
      authorId: admin.id,
    },
    {
      title: 'TypeScript 高级类型技巧',
      slug: 'typescript-advanced-types',
      content: '# TypeScript 高级类型\n\n本文介绍 TypeScript 中的高级类型用法...',
      excerpt: '深入理解 TypeScript 的条件类型、映射类型和模板字面量类型。',
      category: 'frontend',
      tags: ['typescript', 'javascript'],
      published: true,
      publishedAt: new Date(),
      authorId: admin.id,
    },
    {
      title: 'PostgreSQL 性能优化指南',
      slug: 'postgresql-performance-optimization',
      content: '# PostgreSQL 性能优化\n\n数据库性能是 Web 应用的关键...',
      excerpt: '从索引设计到查询优化，全面提升 PostgreSQL 性能。',
      category: 'backend',
      tags: ['postgresql', 'database', 'performance'],
      published: true,
      publishedAt: new Date(),
      authorId: user.id,
    },
  ];

  for (const article of articles) {
    await prisma.article.upsert({
      where: { slug: article.slug },
      update: {},
      create: article,
    });
  }

  console.log(`✅ ${articles.length} articles created`);

  // 4. 创建示例评论
  const firstArticle = await prisma.article.findFirst();
  if (firstArticle) {
    await prisma.comment.createMany({
      data: [
        {
          content: '写得很清楚，学到了很多！',
          authorId: user.id,
          postId: firstArticle.id,
        },
        {
          content: '期待后续章节的内容。',
          authorId: admin.id,
          postId: firstArticle.id,
        },
      ],
      skipDuplicates: true,
    });
    console.log('✅ Comments created');
  }

  console.log('🎉 Seed completed!');
}

main()
  .catch((e) => {
    console.error('❌ Seed failed:', e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

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

# 运行 seed
npx prisma db seed

# 重置数据库并 seed
npx prisma migrate reset

# 启动可视化数据浏览器
npx prisma studio

// lib/prisma.ts

export const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' },
  ],
});

// 监听查询事件（开发环境）
if (process.env.NODE_ENV === 'development') {
  prisma.$on('query', (e) => {
    console.log(`Query: ${e.query}`);
    console.log(`Duration: ${e.duration}ms`);
    console.log('---');
  });
}

// ❌ N+1 问题：每个文章单独查询作者
const articles = await prisma.article.findMany();
for (const article of articles) {
  const author = await prisma.user.findUnique({
    where: { id: article.authorId },
  });
  console.log(`${article.title} by ${author?.name}`);
}
// 生成 N+1 条 SQL 查询

// ✅ 使用 include 一次性加载
const articles = await prisma.article.findMany({
  include: {
    author: {
      select: { id: true, name: true, avatar: true },
    },
  },
});
// 只生成 2 条 SQL 查询（SELECT articles + SELECT users）

// 如果你更偏好 SQL 风格，Drizzle 是另一个优秀选择

// drizzle/schema.ts
import { pgTable, text, boolean, timestamp, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: text('email').notNull().unique(),
  name: text('name'),
  role: text('role').default('USER').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const articles = pgTable('articles', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  content: text('content').notNull(),
  published: boolean('published').default(false),
  views: integer('views').default(0),
  authorId: text('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// 查询示例
import { eq, desc, like, and } from 'drizzle-orm';

const articles = await db
  .select()
  .from(articles)
  .where(and(
    eq(articles.published, true),
    like(articles.title, '%Next.js%')
  ))
  .orderBy(desc(articles.createdAt))
  .limit(10);

// lib/services/analytics.ts

import { prisma } from '@/lib/prisma';
import { cache } from 'react';

// 仪表盘统计数据
export const getDashboardStats = cache(async () => {
  const [
    totalArticles,
    publishedArticles,
    totalComments,
    totalUsers,
    totalViews,
    recentArticles,
    topArticles,
    categoryStats,
  ] = await Promise.all([
    prisma.article.count(),
    prisma.article.count({ where: { published: true } }),
    prisma.comment.count(),
    prisma.user.count(),
    prisma.article.aggregate({ _sum: { views: true } }),
    prisma.article.findMany({
      take: 5,
      orderBy: { createdAt: 'desc' },
      select: {
        id: true,
        title: true,
        published: true,
        views: true,
        createdAt: true,
      },
    }),
    prisma.article.findMany({
      where: { published: true },
      take: 5,
      orderBy: { views: 'desc' },
      select: {
        id: true,
        title: true,
        slug: true,
        views: true,
      },
    }),
    prisma.article.groupBy({
      by: ['category'],
      _count: { id: true },
      where: { published: true },
    }),
  ]);

  return {
    overview: {
      totalArticles,
      publishedArticles,
      draftArticles: totalArticles - publishedArticles,
      totalComments,
      totalUsers,
      totalViews: totalViews._sum.views ?? 0,
    },
    recentArticles,
    topArticles,
    categoryStats,
  };
});

// 文章阅读趋势（最近 30 天）
export const getArticleViewsTrend = cache(async (slug: string) => {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  // 注意：这需要 ArticleView 模型来记录每日浏览量
  // 简化版本：返回总浏览量
  const article = await prisma.article.findUnique({
    where: { slug },
    select: { views: true },
  });

  return {
    totalViews: article?.views ?? 0,
    slug,
  };
});

// lib/services/search.ts

import { prisma } from '@/lib/prisma';
import { cache } from 'react';

export const searchContent = cache(async (query: string, options: {
  type?: 'article' | 'comment' | 'user' | 'all';
  page?: number;
  limit?: number;
} = {}) => {
  const { type = 'all', page = 1, limit = 10 } = options;
  const skip = (page - 1) * limit;

  const results: {
    articles?: any[];
    comments?: any[];
    users?: any[];
  } = {};

  const searchCondition = {
    contains: query,
    mode: 'insensitive' as const,
  };

  if (type === 'article' || type === 'all') {
    results.articles = await prisma.article.findMany({
      where: {
        published: true,
        OR: [
          { title: searchCondition },
          { content: searchCondition },
          { excerpt: searchCondition },
        ],
      },
      skip: type === 'article' ? skip : 0,
      take: type === 'article' ? limit : 5,
      orderBy: { createdAt: 'desc' },
      select: {
        id: true,
        title: true,
        slug: true,
        excerpt: true,
        createdAt: true,
        author: { select: { name: true } },
      },
    });
  }

  if (type === 'user' || type === 'all') {
    results.users = await prisma.user.findMany({
      where: {
        OR: [
          { name: searchCondition },
          { email: searchCondition },
        ],
      },
      skip: type === 'user' ? skip : 0,
      take: type === 'user' ? limit : 5,
      select: {
        id: true,
        name: true,
        avatar: true,
        bio: true,
      },
    });
  }

  return results;
});

## 数据库生产环境检查清单

### Schema
- [ ] 所有表都有主键（推荐 cuid/uuid）
- [ ] 所有表都有 createdAt / updatedAt
- [ ] 频繁查询的字段已添加索引
- [ ] 外键约束和级联删除已正确配置
- [ ] 敏感字段（密码）已加密存储

### 连接
- [ ] 使用连接池（PgBouncer / Accelerate）
- [ ] Prisma Client 使用单例模式
- [ ] 生产环境日志级别设为 error
- [ ] 数据库连接使用环境变量

### 迁移
- [ ] 迁移文件已提交到版本控制
- [ ] 生产环境使用 `prisma migrate deploy`
- [ ] 大表变更考虑零停机迁移策略
- [ ] 数据迁移脚本已测试

### 安全
- [ ] 数据库用户权限最小化
- [ ] 连接使用 SSL
- [ ] 敏感数据不记录到日志
- [ ] 输入验证在应用层完成

### 性能
- [ ] N+1 查询已消除
- [ ] 大数据集使用分页
- [ ] 批量操作使用 createMany / updateMany
- [ ] 只 select 需要的字段
- [ ] 热路径使用 React cache 去重

### 监控
- [ ] 慢查询日志已开启
- [ ] 连接数监控已配置
- [ ] 磁盘空间告警已设置
- [ ] 定期备份已配置

# 场景：将 articles.content 从 Text 改为 VarChar(50000)

# 步骤 1：添加新列（不删除旧列）
# migration.sql
ALTER TABLE "articles" ADD COLUMN "content_new" VARCHAR(50000);

# 步骤 2：数据迁移
UPDATE "articles" SET "content_new" = "content"::VARCHAR(50000);

# 步骤 3：部署新代码（同时读写两列）

# 步骤 4：确认所有数据迁移完成后
# migration.sql
ALTER TABLE "articles" DROP COLUMN "content";
ALTER TABLE "articles" RENAME COLUMN "content_new" TO "content";
ALTER TABLE "articles" ALTER COLUMN "content" SET NOT NULL;