跳至主要内容

資料庫開發實戰

今天用Claude Code完成一個完整的資料庫開發專案——從Schema設計到效能最佳化,從資料遷移到生產部署。

這個案例會教你:

  • Schema設計和建模
  • 生成和執行遷移檔案
  • 編寫複雜查詢
  • 效能分析和最佳化
  • 資料遷移和版本管理

專案概述​

專案背景:部落格系統資料庫​

為部落格平臺設計完整的資料庫架構,系統需要:

  • 使用者管理: 使用者註冊、登入、許可權控制
  • 文章管理: 文章釋出、編輯、分類、標籤
  • 評論系統: 多級評論、點贊、回覆
  • 社交功能: 關注、粉絲、動態
  • 內容稽覈: 敏感詞過濾、舉報處理
  • 統計分析: 閱讀量、點贊量、使用者活躍度

技術棧選擇​

  • 資料庫: PostgreSQL 15+ (關係型,支援複雜查詢)
  • ORM: Prisma (型別安全,優秀的遷移系統)
  • 快取: Redis (會話、熱點資料)
  • 連線池: PgBouncer (生產環境連線管理)
  • 開發工具: Claude Code (主要開發助手)

為什麼選擇PostgreSQL?​

✓ 支持复杂查询和JOIN操作
✓ 原生JSON类型,灵活存储
✓ 全文搜索功能强大
✓ 事务处理可靠
✓ 拥有丰富的索引类型
✓ 开源免费,社区活跃

準備工作​

環境搭建​

# 检查PostgreSQL版本
psql --version
# 需要PostgreSQL 15或更高版本

# 检查Node.js版本
node --version
# 需要Node.js 18+

# 创建项目目录
mkdir blog-database-dev
cd blog-database-dev

# 初始化项目
npm init -y

# 初始化Git仓库
git init
git branch -M main

安裝依賴​

# 安装Prisma CLI
npm install prisma @prisma/client --save-dev

# 安装PostgreSQL客户端
npm install pg --save

# 安装Redis客户端
npm install redis --save

# 安装其他工具库
npm install dotenv uuid bcryptjs --save

啟動Claude Code​

# 在项目目录启动
claude

歡迎介面:

╔════════════════════════════════════════════╗
║                                            ║
║     Welcome to Claude Code (Beta)          ║
║                                            ║
╚════════════════════════════════════════════╝

Connected to: claude-sonnet-4.5
Working directory: /Users/username/blog-database-dev

How can I help you code today?

第一階段:Schema設計​

第一輪對話​

開始設計資料庫Schema:

我要为博客系统设计数据库Schema,系统需要:

1. User(用户表)
   - id, username, email, password(哈希)
   - avatar, bio, role(admin/author/user)
   - createdAt, updatedAt, lastLoginAt
   - 索引:email(unique), username(unique)

2. Post(文章表)
   - id, title, slug, content(summary), body
   - author(外键->User), status(draft/published/archived)
   - views, likesCount, commentsCount
   - publishedAt, createdAt, updatedAt
   - 索引:slug(unique), author, status, publishedAt

3. Category(分类表)
   - id, name, slug, description
   - parentId(自关联), order
   - 索引:slug(unique), parentId

4. Tag(标签表)
   - id, name, slug, color
   - 索引:name(unique), slug(unique)

5. Comment(评论表)
   - id, content, author(外键->User)
   - post(外键->Post), parentComment(自关联)
   - likesCount, status(pending/approved/rejected)
   - createdAt, updatedAt
   - 索引:post, author, parentComment, status

6. Follow(关注关系表)
   - follower(外键->User), following(外键->User)
   - createdAt
   - 唯一索引:follower+following

请使用Prisma设计这些模型,包含所有关系。

Claude Code會建立完整的Schema檔案:

Prisma Schema生成​

prisma/schema.prisma:

// Prisma Schema for Blog System

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 用户模型
model User {
  id           String    @id @default(uuid())
  username     String    @unique
  email        String    @unique
  password     String
  avatar       String?
  bio          String?   @db.Text
  role         UserRole  @default(USER)

  // 关系
  posts        Post[]
  comments     Comment[]
  followers    Follow[]  @relation("UserFollowers")
  following    Follow[]  @relation("UserFollowing")
  likes        Like[]
  activities   Activity[]

  // 时间戳
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt
  lastLoginAt  DateTime?

  @@index([email])
  @@index([username])
  @@map("users")
}

enum UserRole {
  ADMIN
  AUTHOR
  USER
}

// 文章模型
model Post {
  id             String       @id @default(uuid())
  title          String
  slug           String       @unique
  content        String?      @db.Text
  body           String       @db.Text
  excerpt        String?      @db.VarChar(500)

  // 关系
  author         User         @relation(fields: [authorId], references: [id])
  authorId       String
  category       Category?    @relation(fields: [categoryId], references: [id])
  categoryId     String?
  tags           PostTag[]

  // 状态和统计
  status         PostStatus   @default(DRAFT)
  views          Int          @default(0)
  likesCount     Int          @default(0)
  commentsCount  Int          @default(0)
  featured       Boolean      @default(false)
  pinned         Boolean      @default(false)

  // SEO
  metaTitle      String?
  metaDescription String?     @db.VarChar(160)
  keywords       String[]

  // 时间戳
  publishedAt    DateTime?
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt

  @@index([authorId])
  @@index([categoryId])
  @@index([status])
  @@index([publishedAt(sort: Desc)])
  @@index([slug])
  @@map("posts")
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

// 分类模型
model Category {
  id          String     @id @default(uuid())
  name        String     @unique
  slug        String     @unique
  description String?    @db.Text
  icon        String?
  color       String?
  order       Int        @default(0)

  // 关系
  parent      Category?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
  parentId    String?
  children    Category[] @relation("CategoryHierarchy")
  posts       Post[]

  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([parentId])
  @@index([slug])
  @@map("categories")
}

// 标签模型
model Tag {
  id        String     @id @default(uuid())
  name      String     @unique
  slug      String     @unique
  color     String?
  posts     PostTag[]

  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt

  @@index([name])
  @@index([slug])
  @@map("tags")
}

// 文章-标签关联表
model PostTag {
  post    Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId  String
  tag     Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId   String

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

// 评论模型
model Comment {
  id          String        @id @default(uuid())
  content     String        @db.Text

  // 关系
  author      User          @relation(fields: [authorId], references: [id])
  authorId    String
  post        Post          @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId      String
  parent      Comment?      @relation("CommentReplies", fields: [parentId], references: [id])
  parentId    String?
  replies     Comment[]     @relation("CommentReplies")

  // 状态和统计
  status      CommentStatus @default(PENDING)
  likesCount  Int           @default(0)

  // 时间戳
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

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

enum CommentStatus {
  PENDING
  APPROVED
  REJECTED
  SPAM
}

// 关注关系模型
model Follow {
  id          String   @id @default(uuid())
  follower    User     @relation("UserFollowing", fields: [followerId], references: [id], onDelete: Cascade)
  followerId  String
  following   User     @relation("UserFollowers", fields: [followingId], references: [id], onDelete: Cascade)
  followingId String

  createdAt   DateTime @default(now())

  @@unique([followerId, followingId])
  @@index([followerId])
  @@index([followingId])
  @@map("follows")
}

// 点赞模型
model Like {
  id        String   @id @default(uuid())
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String
  comment   Comment? @relation(fields: [commentId], references: [id], onDelete: Cascade)
  commentId String?
  createdAt DateTime @default(now())

  @@unique([userId, commentId])
  @@index([userId])
  @@index([commentId])
  @@map("likes")
}

// 用户动态模型
model Activity {
  id          String       @id @default(uuid())
  type        ActivityType
  user        User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId      String
  data        Json?

  createdAt   DateTime     @default(now())

  @@index([userId])
  @@index([createdAt(sort: Desc)])
  @@map("activities")
}

enum ActivityType {
  POST_CREATED
  POST_PUBLISHED
  COMMENT_CREATED
  USER_FOLLOWED
  USER_LIKED
}

Schema設計亮點​

Claude Code在Schema設計中展示了這些專業能力:

  1. 關係對映: 正確使用了一對一、一對多、多對多關係
  2. 索引最佳化: 在常用查詢欄位上新增索引
  3. 列舉型別: 使用列舉限制欄位值,提高資料完整性
  4. 時間戳: 自動管理createdAt和updatedAt
  5. 級聯刪除: 正確配置onDelete行為
  6. 唯一約束: 防止資料重複
  7. JSON欄位: 靈活儲存動態資料

第二階段:資料庫遷移​

初始化Prisma​

执行以下操作:
1. 初始化Prisma配置
2. 生成Prisma Client
3. 创建环境变量文件(.env.example)
4. 生成初始迁移
5. 显示迁移SQL语句

Claude Code會執行這些步驟:

環境配置​

.env.example:

# Database
DATABASE_URL="postgresql://user:password@localhost:5432/blog_db?schema=public"

# Redis
REDIS_URL="redis://localhost:6379"

# App
NODE_ENV="development"
PORT=3000

# JWT
JWT_SECRET="your-super-secret-jwt-key"
JWT_EXPIRES_IN="7d"

建立資料庫​

# 在PostgreSQL中创建数据库
createdb blog_db

# 或者使用psql
psql -U postgres
CREATE DATABASE blog_db;
\q

生成遷移檔案​

# 生成初始迁移
npx prisma migrate dev --name init

# 输出:
# ✔ Generated Prisma Client
# ✔ The following migration has been created and applied from new schema changes:
#
# migrations/
#   └─ 20240115120000_init/
#     └─ migration.sql

檢視生成的SQL​

migrations/20240115120000_init/migration.sql:

-- CreateUser
CREATE TABLE "users" (
    "id" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "avatar" TEXT,
    "bio" TEXT,
    "role" "UserRole" NOT NULL DEFAULT 'USER',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "lastLoginAt" TIMESTAMP(3),

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE UNIQUE INDEX "users_username_key" ON "users"("username");

-- CreateCategory
CREATE TABLE "categories" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "slug" TEXT NOT NULL,
    "description" TEXT,
    "icon" TEXT,
    "color" TEXT,
    "order" INTEGER NOT NULL DEFAULT 0,
    "parentId" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "categories_slug_key" ON "categories"("slug");

-- CreateTag
CREATE TABLE "tags" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "slug" TEXT NOT NULL,
    "color" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "tags_pkey" PRIMARY KEY ("id")
);

-- CreatePost
CREATE TABLE "posts" (
    "id" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "slug" TEXT NOT NULL,
    "content" TEXT,
    "body" TEXT NOT NULL,
    "excerpt" TEXT,
    "authorId" TEXT NOT NULL,
    "categoryId" TEXT,
    "status" "PostStatus" NOT NULL DEFAULT 'DRAFT',
    "views" INTEGER NOT NULL DEFAULT 0,
    "likesCount" INTEGER NOT NULL DEFAULT 0,
    "commentsCount" INTEGER NOT NULL DEFAULT 0,
    "featured" BOOLEAN NOT NULL DEFAULT false,
    "pinned" BOOLEAN NOT NULL DEFAULT false,
    "metaTitle" TEXT,
    "metaDescription" TEXT,
    "keywords" TEXT[],
    "publishedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "posts_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "posts_authorId_idx" ON "posts"("authorId");

-- CreateIndex
CREATE INDEX "posts_categoryId_idx" ON "posts"("categoryId");

-- CreateIndex
CREATE INDEX "posts_status_idx" ON "posts"("status");

-- CreateIndex
CREATE INDEX "posts_publishedAt_idx" ON "posts"("publishedAt" DESC);

-- CreateIndex
CREATE UNIQUE INDEX "posts_slug_key" ON "posts"("slug");

-- CreateComment
CREATE TABLE "comments" (
    "id" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "authorId" TEXT NOT NULL,
    "postId" TEXT NOT NULL,
    "parentId" TEXT,
    "status" "CommentStatus" NOT NULL DEFAULT 'PENDING',
    "likesCount" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "comments_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "comments_postId_idx" ON "comments"("postId");

-- CreateIndex
CREATE INDEX "comments_authorId_idx" ON "comments"("authorId");

-- CreateIndex
CREATE INDEX "comments_parentId_idx" ON "comments"("parentId");

-- CreateIndex
CREATE INDEX "comments_status_idx" ON "comments"("status");

-- CreateFollow
CREATE TABLE "follows" (
    "id" TEXT NOT NULL,
    "followerId" TEXT NOT NULL,
    "followingId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "follows_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "follows_followerId_followingId_key" ON "follows"("followerId", "followingId");

-- CreateLike
CREATE TABLE "likes" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "commentId" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "likes_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "likes_userId_commentId_key" ON "likes"("userId", "commentId");

-- CreateActivity
CREATE TABLE "activities" (
    "id" TEXT NOT NULL,
    "type" "ActivityType" NOT NULL,
    "userId" TEXT NOT NULL,
    "data" JSONB,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "activities_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "activities_userId_idx" ON "activities"("userId");

-- CreateIndex
CREATE INDEX "activities_createdAt_idx" ON "activities"("createdAt" DESC);

-- CreatePostTag
CREATE TABLE "post_tags" (
    "postId" TEXT NOT NULL,
    "tagId" TEXT NOT NULL,

    CONSTRAINT "post_tags_pkey" PRIMARY KEY ("postId", "tagId")
);

-- CreateEnum
CREATE TYPE "UserRole" AS ENUM ('ADMIN', 'AUTHOR', 'USER');

-- CreateEnum
CREATE "PostStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED');

-- CreateEnum
CREATE TYPE "CommentStatus" AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'SPAM');

-- CreateEnum
CREATE TYPE "ActivityType" AS ENUM ('POST_CREATED', 'POST_PUBLISHED', 'COMMENT_CREATED', 'USER_FOLLOWED', 'USER_LIKED');

-- AddForeignKey
ALTER TABLE "posts" ADD CONSTRAINT "posts_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "posts" ADD CONSTRAINT "posts_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "categories"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "categories" ADD CONSTRAINT "categories_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "categories"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "comments" ADD CONSTRAINT "comments_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "comments" ADD CONSTRAINT "comments_postId_fkey" FOREIGN KEY ("postId") REFERENCES "posts"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "comments" ADD CONSTRAINT "comments_parentId_fkey" FOREIGN KEY ("parentCommentId") REFERENCES "comments"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "follows" ADD CONSTRAINT "follows_followerId_fkey" FOREIGN KEY ("followerId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "follows" ADD CONSTRAINT "follows_followingId_fkey" FOREIGN KEY ("followingId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "likes" ADD CONSTRAINT "likes_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "likes" ADD CONSTRAINT "likes_commentId_fkey" FOREIGN KEY ("commentId") REFERENCES "comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "activities" ADD CONSTRAINT "activities_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "post_tags" ADD CONSTRAINT "post_tags_postId_fkey" FOREIGN KEY ("postId") REFERENCES "posts"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "post_tags" ADD CONSTRAINT "post_tags_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "tags"("id") ON DELETE CASCADE ON UPDATE CASCADE;

驗證遷移​

# 查看迁移状态
npx prisma migrate status

# 输出:
# ✔ migrations are up to date
#
# No pending migrations.

第三階段:種子資料​

建立種子指令碼​

创建种子数据脚本,包含:
1. 3个用户(1个admin, 2个author)
2. 5个分类(包含父子关系)
3. 10个标签
4. 20篇文章(不同状态)
5. 50条评论(多级回复)
6. 随机的关注关系
7. 一些点赞记录

使用Faker库生成真实的数据。

prisma/seed.ts:

import { PrismaClient, UserRole, PostStatus, CommentStatus } from '@prisma/client';
import * as faker from 'faker';
import * as bcrypt from 'bcryptjs';

const prisma = new PrismaClient();

async function main() {
  console.log('🌱 开始种子数据生成...');

  // 清空现有数据
  await prisma.like.deleteMany();
  await prisma.activity.deleteMany();
  await prisma.postTag.deleteMany();
  await prisma.comment.deleteMany();
  await prisma.follow.deleteMany();
  await prisma.post.deleteMany();
  await prisma.tag.deleteMany();
  await prisma.category.deleteMany();
  await prisma.user.deleteMany();

  console.log('✓ 已清空现有数据');

  // 1. 创建用户
  const hashedPassword = await bcrypt.hash('password123', 10);

  const admin = await prisma.user.create({
    data: {
      username: 'admin',
      email: 'admin@blog.com',
      password: hashedPassword,
      role: UserRole.ADMIN,
      bio: '系统管理员',
      avatar: faker.internet.avatar(),
    },
  });

  const author1 = await prisma.user.create({
    data: {
      username: 'alice_writes',
      email: 'alice@blog.com',
      password: hashedPassword,
      role: UserRole.AUTHOR,
      bio: '热爱写作的技术博主',
      avatar: faker.internet.avatar(),
    },
  });

  const author2 = await prisma.user.create({
    data: {
      username: 'bob_dev',
      email: 'bob@blog.com',
      password: hashedPassword,
      role: UserRole.AUTHOR,
      bio: '全栈开发工程师',
      avatar: faker.internet.avatar(),
    },
  });

  const users = [admin, author1, author2];
  console.log(`✓ 创建了 ${users.length} 个用户`);

  // 2. 创建分类(带层级关系)
  const techCategory = await prisma.category.create({
    data: {
      name: '技术',
      slug: 'tech',
      description: '技术相关文章',
      icon: '💻',
      color: '#3B82F6',
      order: 1,
    },
  });

  const webDevCategory = await prisma.category.create({
    data: {
      name: 'Web开发',
      slug: 'web-dev',
      description: 'Web前端和后端开发',
      icon: '🌐',
      color: '#10B981',
      parent: {
        connect: { id: techCategory.id },
      },
      order: 1,
    },
  });

  const aiCategory = await prisma.category.create({
    data: {
      name: '人工智能',
      slug: 'ai',
      description: 'AI和机器学习',
      icon: '🤖',
      color: '#8B5CF6',
      parent: {
        connect: { id: techCategory.id },
      },
      order: 2,
    },
  });

  const lifeCategory = await prisma.category.create({
    data: {
      name: '生活',
      slug: 'life',
      description: '生活随笔',
      icon: '🌱',
      color: '#F59E0B',
      order: 2,
    },
  });

  const travelCategory = await prisma.category.create({
    data: {
      name: '旅行',
      slug: 'travel',
      description: '旅行见闻',
      icon: '✈️',
      color: '#EF4444',
      parent: {
        connect: { id: lifeCategory.id },
      },
      order: 1,
    },
  });

  const categories = [techCategory, webDevCategory, aiCategory, lifeCategory, travelCategory];
  console.log(`✓ 创建了 ${categories.length} 个分类`);

  // 3. 创建标签
  const tagNames = ['JavaScript', 'Python', 'React', 'Vue', 'Node.js', 'Docker', 'Kubernetes', 'GraphQL', 'TypeScript', 'Git'];
  const tags = await Promise.all(
    tagNames.map((name) =>
      prisma.tag.create({
        data: {
          name,
          slug: name.toLowerCase(),
          color: faker.random.arrayElement(['#3B82F6', '#10B981', '#F59E0B', '#EF4444', '#8B5CF6']),
        },
      })
    )
  );
  console.log(`✓ 创建了 ${tags.length} 个标签`);

  // 4. 创建文章
  const posts = [];
  for (let i = 0; i < 20; i++) {
    const author = faker.random.arrayElement(users);
    const category = faker.random.arrayElement(categories);
    const status = faker.random.arrayElement([PostStatus.DRAFT, PostStatus.PUBLISHED, PostStatus.PUBLISHED, PostStatus.PUBLISHED]);
    const publishedAt = status === PostStatus.PUBLISHED ? faker.date.past(1) : null;

    const post = await prisma.post.create({
      data: {
        title: faker.lorem.sentence(faker.datatype.number({ min: 5, max: 10 })),
        slug: faker.lorem.slug(faker.datatype.number({ min: 3, max: 6 })) + `-${i}`,
        content: faker.lorem.paragraphs(3),
        body: faker.lorem.paragraphs(faker.datatype.number({ min: 5, max: 15 })),
        excerpt: faker.lorem.paragraph(),
        author: {
          connect: { id: author.id },
        },
        category: {
          connect: { id: category.id },
        },
        status,
        publishedAt,
        featured: faker.datatype.boolean(),
        pinned: faker.datatype.boolean(),
        metaTitle: faker.lorem.sentence(),
        metaDescription: faker.lorem.sentence(),
        keywords: faker.random.arrayElements(['tech', 'programming', 'tutorial', 'tips'], faker.datatype.number({ min: 1, max: 4 })),
        views: faker.datatype.number({ min: 0, max: 10000 }),
        likesCount: faker.datatype.number({ min: 0, max: 500 }),
        commentsCount: faker.datatype.number({ min: 0, max: 100 }),
        tags: {
          connect: faker.random.arrayElements(tags, faker.datatype.number({ min: 1, max: 4 })).map((t) => ({ id: t.id })),
        },
      },
    });
    posts.push(post);
  }
  console.log(`✓ 创建了 ${posts.length} 篇文章`);

  // 5. 创建评论(多级)
  const comments = [];
  for (let i = 0; i < 50; i++) {
    const post = faker.random.arrayElement(posts);
    const author = faker.random.arrayElement(users);
    const parentComment = faker.datatype.boolean() ? faker.random.arrayElement(comments.filter((c) => c.postId === post.id && !c.parentId)) : null;

    const comment = await prisma.comment.create({
      data: {
        content: faker.lorem.paragraph(),
        author: {
          connect: { id: author.id },
        },
        post: {
          connect: { id: post.id },
        },
        parent: parentComment
          ? {
              connect: { id: parentComment.id },
            }
          : undefined,
        status: faker.random.arrayElement([CommentStatus.APPROVED, CommentStatus.APPROVED, CommentStatus.APPROVED, CommentStatus.PENDING]),
        likesCount: faker.datatype.number({ min: 0, max: 50 }),
      },
    });
    comments.push(comment);
  }
  console.log(`✓ 创建了 ${comments.length} 条评论`);

  // 6. 创建关注关系
  await prisma.follow.create({
    data: {
      follower: {
        connect: { id: author1.id },
      },
      following: {
        connect: { id: author2.id },
      },
    },
  });

  await prisma.follow.create({
    data: {
      follower: {
        connect: { id: author2.id },
      },
      following: {
        connect: { id: author1.id },
      },
    },
  });
  console.log('✓ 创建了关注关系');

  // 7. 创建点赞记录
  const commentsToLike = faker.random.arrayElements(comments, 20);
  for (const comment of commentsToLike) {
    const user = faker.random.arrayElement(users.filter((u) => u.id !== comment.authorId));
    try {
      await prisma.like.create({
        data: {
          user: {
            connect: { id: user.id },
          },
          comment: {
            connect: { id: comment.id },
          },
        },
      });
    } catch (e) {
      // 忽略重复点赞错误
    }
  }
  console.log('✓ 创建了点赞记录');

  // 8. 创建用户动态
  await prisma.activity.createMany({
    data: [
      {
        type: 'POST_PUBLISHED',
        userId: author1.id,
        data: { postId: posts[0].id, title: posts[0].title },
      },
      {
        type: 'USER_FOLLOWED',
        userId: author1.id,
        data: { followingId: author2.id, username: author2.username },
      },
    ],
  });
  console.log('✓ 创建了用户动态');

  console.log('🎉 种子数据生成完成!');
  console.log('\n📝 登录凭据:');
  console.log('Admin: admin@blog.com / password123');
  console.log('Author: alice@blog.com / password123');
  console.log('Author: bob@blog.com / password123');
}

main()
  .catch((e) => {
    console.error('❌ 种子数据生成失败:', e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

配置和執行種子指令碼​

package.json:

{
  "name": "blog-database-dev",
  "version": "1.0.0",
  "scripts": {
    "seed": "ts-node prisma/seed.ts",
    "db:push": "prisma db push",
    "db:migrate": "prisma migrate dev",
    "db:studio": "prisma studio"
  },
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

執行種子指令碼:

# 安装开发依赖
npm install -D ts-node typescript @types/node faker bcryptjs @types/bcryptjs @types/faker

# 运行种子脚本
npm run seed

# 输出:
# 🌱 开始种子数据生成...
# ✓ 已清空现有数据
# ✓ 创建了 3 个用户
# ✓ 创建了 5 个分类
# ✓ 创建了 10 个标签
# ✓ 创建了 20 篇文章
# ✓ 创建了 50 条评论
# ✓ 创建了关注关系
# ✓ 创建了点赞记录
# ✓ 创建了用户动态
# 🎉 种子数据生成完成!
#
# 📝 登录凭据:
# Admin: admin@blog.com / password123
# Author: alice@blog.com / password123
# Author: bob@blog.com / password123

第四階段:複雜查詢​

建立查詢示例​

创建queries.js文件,包含:

1. 获取热门文章(按阅读量、点赞量、评论数综合排序)
2. 获取用户的关注者列表(分页)
3. 搜索文章(全文搜索,支持标题、内容、标签)
4. 获取文章详情(包含作者、分类、标签、评论)
5. 获取用户动态时间线(包含所有关注用户的活动)
6. 统计分析(用户增长、文章发布趋势、热门标签)

lib/queries.js:

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

/**
 * 获取热门文章
 * 综合考虑阅读量、点赞量、评论数
 */
async function getPopularPosts(limit = 10, days = 30) {
  const startDate = new Date();
  startDate.setDate(startDate.getDate() - days);

  const posts = await prisma.post.findMany({
    where: {
      status: 'PUBLISHED',
      publishedAt: {
        gte: startDate,
      },
    },
    include: {
      author: {
        select: {
          id: true,
          username: true,
          avatar: true,
        },
      },
      category: {
        select: {
          name: true,
          slug: true,
        },
      },
      tags: {
        select: {
          name: true,
          slug: true,
        },
      },
    },
    orderBy: [
      { views: 'desc' },
      { likesCount: 'desc' },
      { commentsCount: 'desc' },
      { publishedAt: 'desc' },
    ],
    take: limit,
  });

  // 计算热度分数
  return posts.map((post) => ({
    ...post,
    hotScore: post.views * 0.5 + post.likesCount * 2 + post.commentsCount * 3,
  }));
}

/**
 * 获取用户的关注者列表(分页)
 */
async function getUserFollowers(userId, page = 1, pageSize = 20) {
  const skip = (page - 1) * pageSize;

  const [followers, total] = await Promise.all([
    prisma.follow.findMany({
      where: { followingId: userId },
      include: {
        follower: {
          select: {
            id: true,
            username: true,
            avatar: true,
            bio: true,
            _count: {
              select: {
                followers: true,
                posts: true,
              },
            },
          },
        },
      },
      orderBy: { createdAt: 'desc' },
      skip,
      take: pageSize,
    }),
    prisma.follow.count({
      where: { followingId: userId },
    }),
  ]);

  return {
    followers: followers.map((f) => f.follower),
    pagination: {
      page,
      pageSize,
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  };
}

/**
 * 全文搜索文章
 */
async function searchPosts(query, filters = {}) {
  const { category, tags, status = 'PUBLISHED', page = 1, pageSize = 20 } = filters;
  const skip = (page - 1) * pageSize;

  const where = {
    status,
    OR: [
      { title: { contains: query, mode: 'insensitive' } },
      { content: { contains: query, mode: 'insensitive' } },
      { body: { contains: query, mode: 'insensitive' } },
    ],
  };

  if (category) {
    where.category = { slug: category };
  }

  if (tags && tags.length > 0) {
    where.tags = {
      some: {
        slug: { in: tags },
      },
    };
  }

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      include: {
        author: {
          select: {
            id: true,
            username: true,
            avatar: true,
          },
        },
        category: true,
        tags: true,
      },
      orderBy: { publishedAt: 'desc' },
      skip,
      take: pageSize,
    }),
    prisma.post.count({ where }),
  ]);

  return {
    posts,
    pagination: {
      page,
      pageSize,
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  };
}

/**
 * 获取文章详情(包含所有关联数据)
 */
async function getPostDetail(slug) {
  const post = await prisma.post.findUnique({
    where: { slug },
    include: {
      author: {
        select: {
          id: true,
          username: true,
          avatar: true,
          bio: true,
          _count: {
            select: {
              posts: true,
              followers: true,
            },
          },
        },
      },
      category: {
        include: {
          parent: true,
          children: true,
        },
      },
      tags: true,
      comments: {
        where: {
          status: 'APPROVED',
          parentId: null, // 只获取顶级评论
        },
        include: {
          author: {
            select: {
              id: true,
              username: true,
              avatar: true,
            },
          },
          replies: {
            include: {
              author: {
                select: {
                  id: true,
                  username: true,
                  avatar: true,
                },
              },
            },
            orderBy: { createdAt: 'asc' },
          },
        },
        orderBy: { createdAt: 'desc' },
      },
    },
  });

  if (!post) {
    return null;
  }

  // 增加阅读量
  await prisma.post.update({
    where: { id: post.id },
    data: {
      views: { increment: 1 },
    },
  });

  return { ...post, views: post.views + 1 };
}

/**
 * 获取用户动态时间线
 */
async function getUserTimeline(userId, page = 1, pageSize = 20) {
  const skip = (page - 1) * pageSize;

  // 获取用户关注的所有用户
  const following = await prisma.follow.findMany({
    where: { followerId: userId },
    select: { followingId: true },
  });

  const followingIds = [...following.map((f) => f.followingId), userId];

  const [activities, total] = await Promise.all([
    prisma.activity.findMany({
      where: {
        userId: { in: followingIds },
      },
      include: {
        user: {
          select: {
            id: true,
            username: true,
            avatar: true,
          },
        },
      },
      orderBy: { createdAt: 'desc' },
      skip,
      take: pageSize,
    }),
    prisma.activity.count({
      where: {
        userId: { in: followingIds },
      },
    }),
  ]);

  return {
    activities,
    pagination: {
      page,
      pageSize,
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  };
}

/**
 * 统计分析数据
 */
async function getAnalytics() {
  const [userStats, postStats, commentStats, popularTags, recentActivity] = await Promise.all([
    // 用户增长趋势
    prisma.$queryRaw`
      SELECT
        DATE_TRUNC('day', "createdAt") as date,
        COUNT(*) as count
      FROM users
      WHERE "createdAt" >= NOW() - INTERVAL '30 days'
      GROUP BY DATE_TRUNC('day', "createdAt")
      ORDER BY date DESC
    `,
    // 文章发布趋势
    prisma.$queryRaw`
      SELECT
        DATE_TRUNC('day', "publishedAt") as date,
        COUNT(*) as count,
        SUM("views") as total_views
      FROM posts
      WHERE "publishedAt" >= NOW() - INTERVAL '30 days'
        AND status = 'PUBLISHED'
      GROUP BY DATE_TRUNC('day', "publishedAt")
      ORDER BY date DESC
    `,
    // 评论统计
    prisma.comment.groupBy({
      by: ['status'],
      _count: true,
    }),
    // 热门标签
    prisma.tag.findMany({
      include: {
        _count: {
          select: { posts: true },
        },
      },
      orderBy: {
        posts: {
          _count: 'desc',
        },
      },
      take: 10,
    }),
    // 最近活动
    prisma.activity.findMany({
      take: 10,
      include: {
        user: {
          select: {
            username: true,
            avatar: true,
          },
        },
      },
      orderBy: { createdAt: 'desc' },
    }),
  ]);

  return {
    userGrowth: userStats,
    postTrends: postStats,
    commentStats,
    popularTags,
    recentActivity,
  };
}

/**
 * 获取推荐文章(基于标签相似度)
 */
async function getRecommendedPosts(postId, limit = 5) {
  const post = await prisma.post.findUnique({
    where: { id: postId },
    include: { tags: true },
  });

  if (!post) return [];

  const tagIds = post.tags.map((t) => t.id);

  return prisma.post.findMany({
    where: {
      id: { not: postId },
      status: 'PUBLISHED',
      tags: {
        some: {
          id: { in: tagIds },
        },
      },
    },
    include: {
      author: {
        select: {
          username: true,
          avatar: true,
        },
      },
      category: {
        select: {
          name: true,
        },
      },
      tags: true,
    },
    orderBy: { publishedAt: 'desc' },
    take: limit,
  });
}

module.exports = {
  getPopularPosts,
  getUserFollowers,
  searchPosts,
  getPostDetail,
  getUserTimeline,
  getAnalytics,
  getRecommendedPosts,
};

第五階段:效能最佳化​

效能分析​

分析当前数据库性能,创建以下优化:

1. 为常用查询添加复合索引
2. 创建全文搜索索引
3. 优化N+1查询
4. 添加查询结果缓存
5. 创建物化视图用于统计

新增複合索引​

prisma/migrations/20240115120001_add_performance_indexes/migration.sql:

-- 复合索引:文章列表查询
CREATE INDEX "posts_author_status_published_idx" ON "posts"("authorId", "status", "publishedAt" DESC);

-- 复合索引:分类文章查询
CREATE INDEX "posts_category_status_idx" ON "posts"("categoryId", "status", "publishedAt" DESC);

-- 复合索引:热门文章查询
CREATE INDEX "posts_status_views_likes_idx" ON "posts"("status", "views" DESC, "likesCount" DESC);

-- 复合索引:用户评论查询
CREATE INDEX "comments_post_status_created_idx" ON "comments"("postId", "status", "createdAt" DESC);

-- 复合索引:用户时间线查询
CREATE INDEX "activities_user_created_idx" ON "activities"("userId", "createdAt" DESC);

-- 全文搜索索引
CREATE INDEX "posts_title_fulltext_idx" ON "posts" USING gin(to_tsvector('english', "title"));
CREATE INDEX "posts_body_fulltext_idx" ON "posts" USING gin(to_tsvector('english', "body"));

-- 覆盖索引:包含常用字段
CREATE INDEX "posts_list_covering_idx" ON "posts"("status", "publishedAt" DESC)
  INCLUDE ("title", "slug", "excerpt", "views", "likesCount");

-- 部分索引:只索引已发布的文章
CREATE INDEX "posts_published_idx" ON "posts"("publishedAt" DESC)
  WHERE "status" = 'PUBLISHED';

建立物化檢視​

prisma/migrations/20240115120002_create_materialized_views/migration.sql:

-- 文章统计物化视图
CREATE MATERIALIZED VIEW "post_stats" AS
SELECT
  p.id,
  p.title,
  p.slug,
  p.status,
  p.views,
  p.likesCount,
  p.commentsCount,
  p.publishedAt,
  u.username as author_username,
  c.name as category_name,
  COUNT(DISTINCT pt."tagId") as tags_count
FROM posts p
LEFT JOIN users u ON p."authorId" = u.id
LEFT JOIN categories c ON p."categoryId" = c.id
LEFT JOIN post_tags pt ON p.id = pt."postId"
GROUP BY p.id, u.username, c.name;

-- 创建索引
CREATE UNIQUE INDEX "post_stats_id_idx" ON "post_stats"(id);
CREATE INDEX "post_stats_status_idx" ON "post_stats"("status");
CREATE INDEX "post_stats_published_idx" ON "post_stats"("publishedAt" DESC);

-- 用户统计物化视图
CREATE MATERIALIZED VIEW "user_stats" AS
SELECT
  u.id,
  u.username,
  u.role,
  COUNT(DISTINCT p.id) as posts_count,
  COUNT(DISTINCT c.id) as comments_count,
  COUNT(DISTINCT f1.id) as followers_count,
  COUNT(DISTINCT f2.id) as following_count,
  SUM(p.views) as total_views
FROM users u
LEFT JOIN posts p ON u.id = p."authorId" AND p.status = 'PUBLISHED'
LEFT JOIN comments c ON u.id = c."authorId"
LEFT JOIN follows f1 ON u.id = f1."followingId"
LEFT JOIN follows f2 ON u.id = f2."followerId"
GROUP BY u.id;

CREATE UNIQUE INDEX "user_stats_id_idx" ON "user_stats"(id);

-- 创建刷新函数
CREATE OR REPLACE FUNCTION refresh_stats()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;
  REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
END;
$$ LANGUAGE plpgsql;

建立快取層​

lib/cache.js:

const redis = require('redis');
const { promisify } = require('util');

class CacheManager {
  constructor() {
    this.client = redis.createClient({
      url: process.env.REDIS_URL || 'redis://localhost:6379',
    });

    this.client.get = promisify(this.client.get).bind(this.client);
    this.client.set = promisify(this.client.set).bind(this.client);
    this.client.del = promisify(this.client.del).bind(this.client);
    this.client.keys = promisify(this.client.keys).bind(this.client);

    this.defaultTTL = 3600; // 1小时
  }

  async connect() {
    await this.client.connect();
  }

  /**
   * 生成缓存键
   */
  generateKey(prefix, params) {
    const paramString = Object.keys(params)
      .sort()
      .map((key) => `${key}:${params[key]}`)
      .join(':');
    return `${prefix}:${paramString}`;
  }

  /**
   * 获取缓存
   */
  async get(prefix, params) {
    const key = this.generateKey(prefix, params);
    const data = await this.client.get(key);

    if (data) {
      return JSON.parse(data);
    }
    return null;
  }

  /**
   * 设置缓存
   */
  async set(prefix, params, data, ttl = this.defaultTTL) {
    const key = this.generateKey(prefix, params);
    await this.client.set(key, JSON.stringify(data), 'EX', ttl);
  }

  /**
   * 删除缓存
   */
  async delete(pattern) {
    const keys = await this.client.keys(pattern);
    if (keys.length > 0) {
      await this.client.del(...keys);
    }
  }

  /**
   * 清除所有缓存
   */
  async clear() {
    await this.client.flushdb();
  }

  /**
   * 带缓存的查询装饰器
   */
  cached(prefix, ttl = this.defaultTTL) {
    return (target, propertyName, descriptor) => {
      const originalMethod = descriptor.value;

      descriptor.value = async function (...args) {
        // 尝试从缓存获取
        const cacheKey = Array.isArray(args[0]) ? { params: args[0] } : args[0] || {};
        const cached = await this.cache.get(prefix, cacheKey);

        if (cached) {
          return cached;
        }

        // 执行原始查询
        const result = await originalMethod.apply(this, args);

        // 存入缓存
        await this.cache.set(prefix, cacheKey, result, ttl);

        return result;
      };

      return descriptor;
    };
  }
}

module.exports = CacheManager;

應用快取到查詢​

lib/cachedQueries.js:

const { PrismaClient } = require('@prisma/client');
const CacheManager = require('./cache');

const prisma = new PrismaClient();
const cache = new CacheManager();

class CachedQueries {
  constructor() {
    this.cache = cache;
  }

  /**
   * 获取热门文章(带缓存)
   */
  async getPopularPosts(limit = 10, days = 30) {
    const cacheKey = { limit, days };
    const cached = await cache.get('popular_posts', cacheKey);

    if (cached) {
      return cached;
    }

    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);

    const posts = await prisma.post.findMany({
      where: {
        status: 'PUBLISHED',
        publishedAt: {
          gte: startDate,
        },
      },
      include: {
        author: {
          select: {
            id: true,
            username: true,
            avatar: true,
          },
        },
        category: {
          select: {
            name: true,
            slug: true,
          },
        },
        tags: {
          select: {
            name: true,
            slug: true,
          },
        },
      },
      orderBy: [
        { views: 'desc' },
        { likesCount: 'desc' },
        { commentsCount: 'desc' },
      ],
      take: limit,
    });

    const result = posts.map((post) => ({
      ...post,
      hotScore: post.views * 0.5 + post.likesCount * 2 + post.commentsCount * 3,
    }));

    await cache.set('popular_posts', cacheKey, result, 1800); // 30分钟缓存

    return result;
  }

  /**
   * 获取文章详情(带缓存)
   */
  async getPostDetail(slug) {
    const cacheKey = { slug };
    const cached = await cache.get('post_detail', cacheKey);

    if (cached) {
      return cached;
    }

    const post = await prisma.post.findUnique({
      where: { slug },
      include: {
        author: {
          select: {
            id: true,
            username: true,
            avatar: true,
            bio: true,
          },
        },
        category: true,
        tags: true,
        comments: {
          where: {
            status: 'APPROVED',
            parentId: null,
          },
          include: {
            author: {
              select: {
                id: true,
                username: true,
                avatar: true,
              },
            },
            replies: {
              include: {
                author: {
                  select: {
                    id: true,
                    username: true,
                    avatar: true,
                  },
                },
              },
              orderBy: { createdAt: 'asc' },
            },
          },
          orderBy: { createdAt: 'desc' },
        },
      },
    });

    if (!post) {
      return null;
    }

    // 增加阅读量(不经过缓存)
    await prisma.post.update({
      where: { id: post.id },
      data: { views: { increment: 1 } },
    });

    const result = { ...post, views: post.views + 1 };

    await cache.set('post_detail', cacheKey, result, 3600); // 1小时缓存

    return result;
  }

  /**
   * 清除文章相关缓存
   */
  async clearPostCache(postId, slug) {
    // 删除文章详情缓存
    await cache.delete(`post_detail:*:slug:${slug}`);

    // 删除热门文章缓存
    await cache.delete('popular_posts:*');

    // 删除搜索结果缓存
    await cache.delete('search_posts:*');
  }

  /**
   * 刷新物化视图
   */
  async refreshMaterializedViews() {
    await prisma.$executeRaw`REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats`;
    await prisma.$executeRaw`REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats`;
  }
}

module.exports = CachedQueries;

第六階段:資料遷移​

版本管理​

创建数据迁移系统,用于:
1. 将旧系统的数据导入新数据库
2. 数据格式转换
3. 数据验证和清洗
4. 错误处理和回滚

遷移指令碼模板​

migrations/20240115120003_migrate_legacy_data.js:

const { PrismaClient } = require('@prisma/client');
const bcrypt = require('bcryptjs');

const prisma = new PrismaClient();

class DataMigration {
  constructor() {
    this.stats = {
      success: 0,
      failed: 0,
      skipped: 0,
      errors: [],
    };
  }

  /**
   * 执行迁移
   */
  async run() {
    console.log('🚀 开始数据迁移...');

    try {
      // 1. 迁移用户
      await this.migrateUsers();

      // 2. 迁移文章
      await this.migratePosts();

      // 3. 迁移评论
      await this.migrateComments();

      // 4. 迁移标签
      await this.migrateTags();

      // 5. 打印统计
      this.printStats();

      console.log('✅ 迁移完成!');
    } catch (error) {
      console.error('❌ 迁移失败:', error);
      throw error;
    }
  }

  /**
   * 迁移用户
   */
  async migrateUsers() {
    console.log('\n📦 迁移用户...');

    // 假设从旧数据库读取
    const legacyUsers = await this.getLegacyUsers();

    for (const legacyUser of legacyUsers) {
      try {
        // 数据清洗
        const cleanedData = this.cleanUserData(legacyUser);

        // 检查是否已存在
        const existing = await prisma.user.findFirst({
          where: {
            OR: [{ email: cleanedData.email }, { username: cleanedData.username }],
          },
        });

        if (existing) {
          console.log(`⏭️  用户 ${cleanedData.username} 已存在,跳过`);
          this.stats.skipped++;
          continue;
        }

        // 创建用户
        await prisma.user.create({
          data: cleanedData,
        });

        console.log(`✓ 用户 ${cleanedData.username} 迁移成功`);
        this.stats.success++;
      } catch (error) {
        console.error(`✗ 用户 ${legacyUser.username} 迁移失败:`, error.message);
        this.stats.failed++;
        this.stats.errors.push({
          entity: 'user',
          id: legacyUser.id,
          error: error.message,
        });
      }
    }
  }

  /**
   * 清洗用户数据
   */
  cleanUserData(legacyUser) {
    // 密码加密
    const hashedPassword = legacyUser.password.includes('$2a$')
      ? legacyUser.password
      : bcrypt.hashSync(legacyUser.password, 10);

    return {
      username: legacyUser.username.trim(),
      email: legacyUser.email.toLowerCase().trim(),
      password: hashedPassword,
      avatar: legacyUser.avatar || null,
      bio: legacyUser.bio?.substring(0, 500) || null,
      role: this.mapRole(legacyUser.role),
      createdAt: new Date(legacyUser.created_at),
      updatedAt: new Date(legacyUser.updated_at),
    };
  }

  /**
   * 映射角色
   */
  mapRole(legacyRole) {
    const roleMap = {
      admin: 'ADMIN',
      editor: 'AUTHOR',
      user: 'USER',
    };
    return roleMap[legacyRole] || 'USER';
  }

  /**
   * 从旧数据库获取用户
   */
  async getLegacyUsers() {
    // 这里应该是从旧数据库查询
    // 示例数据:
    return [
      {
        id: 1,
        username: 'olduser1',
        email: 'olduser1@example.com',
        password: 'plainpassword',
        avatar: 'https://example.com/avatar1.jpg',
        bio: 'This is a long biography that needs to be truncated...',
        role: 'admin',
        created_at: '2023-01-01 00:00:00',
        updated_at: '2023-12-01 00:00:00',
      },
      // ... 更多用户
    ];
  }

  /**
   * 迁移文章
   */
  async migratePosts() {
    console.log('\n📦 迁移文章...');

    const legacyPosts = await this.getLegacyPosts();

    for (const legacyPost of legacyPosts) {
      try {
        const cleanedData = this.cleanPostData(legacyPost);

        // 查找作者
        const author = await prisma.user.findUnique({
          where: { email: cleanedData.authorEmail },
        });

        if (!author) {
          console.log(`⏭️  文章 ${legacyPost.title} 的作者不存在,跳过`);
          this.stats.skipped++;
          continue;
        }

        // 创建文章
        await prisma.post.create({
          data: {
            ...cleanedData,
            authorId: author.id,
          },
        });

        console.log(`✓ 文章 ${cleanedData.title} 迁移成功`);
        this.stats.success++;
      } catch (error) {
        console.error(`✗ 文章 ${legacyPost.title} 迁移失败:`, error.message);
        this.stats.failed++;
        this.stats.errors.push({
          entity: 'post',
          id: legacyPost.id,
          error: error.message,
        });
      }
    }
  }

  /**
   * 清洗文章数据
   */
  cleanPostData(legacyPost) {
    return {
      title: legacyPost.title.trim(),
      slug: this.generateSlug(legacyPost.title),
      content: legacyPost.excerpt?.substring(0, 500) || null,
      body: legacyPost.content,
      excerpt: legacyPost.excerpt?.substring(0, 200) || null,
      status: this.mapPostStatus(legacyPost.status),
      publishedAt: legacyPost.published_at ? new Date(legacyPost.published_at) : null,
      views: parseInt(legacyPost.views) || 0,
      authorEmail: legacyPost.author_email,
      createdAt: new Date(legacyPost.created_at),
      updatedAt: new Date(legacyPost.updated_at),
    };
  }

  /**
   * 生成唯一slug
   */
  async generateSlug(title) {
    const baseSlug = title
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, '-')
      .replace(/(^-|-$)/g, '');

    let slug = baseSlug;
    let counter = 1;

    while (await prisma.post.findUnique({ where: { slug } })) {
      slug = `${baseSlug}-${counter}`;
      counter++;
    }

    return slug;
  }

  /**
   * 映射文章状态
   */
  mapPostStatus(legacyStatus) {
    const statusMap = {
      draft: 'DRAFT',
      published: 'PUBLISHED',
      archived: 'ARCHIVED',
    };
    return statusMap[legacyStatus] || 'DRAFT';
  }

  /**
   * 打印统计信息
   */
  printStats() {
    console.log('\n📊 迁移统计:');
    console.log(`✓ 成功: ${this.stats.success}`);
    console.log(`✗ 失败: ${this.stats.failed}`);
    console.log(`⏭️  跳过: ${this.stats.skipped}`);

    if (this.stats.errors.length > 0) {
      console.log('\n❌ 错误详情:');
      this.stats.errors.forEach((err) => {
        console.log(`  ${err.entity}#${err.id}: ${err.error}`);
      });
    }
  }

  /**
   * 回滚迁移
   */
  async rollback() {
    console.log('⚠️  开始回滚迁移...');

    // 按照依赖关系倒序删除
    await prisma.comment.deleteMany({});
    await prisma.postTag.deleteMany({});
    await prisma.tag.deleteMany({});
    await prisma.post.deleteMany({});
    await prisma.user.deleteMany({});

    console.log('✅ 回滚完成');
  }
}

// 执行迁移
const migration = new DataMigration();

if (require.main === module) {
  const args = process.argv.slice(2);

  if (args.includes('--rollback')) {
    migration.rollback().catch(console.error);
  } else {
    migration.run().catch(console.error);
  }
}

module.exports = DataMigration;

第七階段:生產部署​

部署清單​

DEPLOYMENT_CHECKLIST.md:

# 数据库部署清单

## 准备工作

- [ ] 确认PostgreSQL版本(15+)
- [ ] 配置生产环境变量
- [ ] 设置数据库备份策略
- [ ] 配置SSL连接
- [ ] 设置防火墙规则

## 数据库配置

- [ ] 优化postgresql.conf
- [ ] 配置pg_hba.conf
- [ ] 设置连接池(PgBouncer)
- [ ] 配置WAL归档
- [ ] 启用查询日志

## 性能优化

- [ ] 运行VACUUM ANALYZE
- [ ] 更新表统计信息
- [ ] 检查索引使用情况
- [ ] 优化慢查询
- [ ] 配置自动VACUUM

## 安全设置

- [ ] 创建只读用户
- [ ] 设置行级安全策略
- [ ] 启用审计日志
- [ ] 配置密码策略
- [ ] 限制超级用户访问

## 监控告警

- [ ] 配置性能监控
- [ ] 设置磁盘空间告警
- [ ] 配置连接数监控
- [ ] 设置慢查询告警
- [ ] 配置复制延迟监控

## 备份恢复

- [ ] 设置自动备份
- [ ] 测试备份恢复流程
- [ ] 配置异地备份
- [ ] 文档化恢复步骤

生產環境配置​

postgresql.conf最佳化:

# 连接设置
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
work_mem = 16MB

# WAL设置
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.9

# 查询规划
random_page_cost = 1.1
effective_io_concurrency = 200

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# 自动清理
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

資料庫健康檢查​

lib/healthCheck.js:

const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient();

class HealthCheck {
  async check() {
    const checks = {
      database: await this.checkDatabase(),
      connections: await this.checkConnections(),
      performance: await this.checkPerformance(),
      tables: await this.checkTables(),
      indexes: await this.checkIndexes(),
    };

    const isHealthy = Object.values(checks).every((check) => check.healthy);

    return {
      healthy: isHealthy,
      checks,
      timestamp: new Date().toISOString(),
    };
  }

  async checkDatabase() {
    try {
      await prisma.$queryRaw`SELECT 1`;
      return { healthy: true, message: 'Database connection OK' };
    } catch (error) {
      return { healthy: false, message: error.message };
    }
  }

  async checkConnections() {
    try {
      const result = await prisma.$queryRaw`
        SELECT
          count(*) as total,
          count(*) FILTER (WHERE state = 'active') as active,
          count(*) FILTER (WHERE state = 'idle') as idle
        FROM pg_stat_activity
        WHERE datname = current_database()
      `;

      const [{ total, active, idle }] = result;

      const healthy = total < 100; // 连接数阈值

      return {
        healthy,
        message: healthy ? 'Connection count OK' : 'Too many connections',
        data: { total, active, idle },
      };
    } catch (error) {
      return { healthy: false, message: error.message };
    }
  }

  async checkPerformance() {
    try {
      const result = await prisma.$queryRaw`
        SELECT
          schemaname,
          relname,
          seq_scan,
          idx_scan,
          seq_scan / (NULLIF(idx_scan, 0) + seq_scan) as idx_scan_ratio
        FROM pg_stat_user_tables
        WHERE seq_scan > 1000
        ORDER BY idx_scan_ratio DESC
        LIMIT 5
      `;

      const hasIssues = result.length > 0;

      return {
        healthy: !hasIssues,
        message: hasIssues ? 'Some tables need index optimization' : 'Performance OK',
        data: result,
      };
    } catch (error) {
      return { healthy: false, message: error.message };
    }
  }

  async checkTables() {
    try {
      const tables = await prisma.$queryRaw`
        SELECT
          schemaname,
          relname,
          n_live_tup,
          n_dead_tup,
          last_vacuum,
          last_autovacuum
        FROM pg_stat_user_tables
        ORDER BY n_dead_tup DESC
      `;

      const needsVacuum = tables.filter((t) => t.n_dead_tup > 1000);

      return {
        healthy: needsVacuum.length === 0,
        message: needsVacuum.length === 0 ? 'Table stats OK' : 'Some tables need VACUUM',
        data: tables,
      };
    } catch (error) {
      return { healthy: false, message: error.message };
    }
  }

  async checkIndexes() {
    try {
      const indexes = await prisma.$queryRaw`
        SELECT
          schemaname,
          tablename,
          indexname,
          idx_scan,
          idx_tup_read,
          idx_tup_fetch
        FROM pg_stat_user_indexes
        ORDER BY idx_scan ASC
        LIMIT 10
      `;

      const unusedIndexes = indexes.filter((idx) => idx.idx_scan === 0);

      return {
        healthy: unusedIndexes.length < 5,
        message: unusedIndexes.length === 0 ? 'All indexes used' : 'Some indexes unused',
        data: indexes,
      };
    } catch (error) {
      return { healthy: false, message: error.message };
    }
  }
}

module.exports = HealthCheck;

實戰技巧總結​

1. Schema設計最佳實踐​

✓ 使用列舉型別限制欄位值

enum UserRole {
  ADMIN
  AUTHOR
  USER
}

✓ 新增預設值和約束

views Int @default(0)
createdAt DateTime @default(now())

✓ 合理使用索引

@@index([email])
@@index([status, publishedAt(sort: Desc)])

✓ 使用JSON欄位儲存靈活資料

metadata Json?

2. 查詢最佳化技巧​

✓ 使用select限制返回欄位

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

✓ 使用include最佳化關聯查詢

const posts = await prisma.post.findMany({
  include: {
    author: { select: { username: true } },
    category: true,
  },
});

✓ 使用分頁避免大資料集

const posts = await prisma.post.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
});

✓ 使用事務保證資料一致性

await prisma.$transaction([
  prisma.post.update({...}),
  prisma.activity.create({...}),
]);

3. 遷移管理技巧​

✓ 使用有意義的遷移名稱

npx prisma migrate dev --name add_user_indexes

✓ 生產環境使用先檢查後應用

npx prisma migrate deploy --preview-feature

✓ 保持遷移的冪等性

const existing = await prisma.user.findUnique({...});
if (existing) return;

4. 效能監控清單​

  • 定期檢查慢查詢日誌
  • 監控連線數使用情況
  • 檢查索引使用率
  • 分析表膨脹情況
  • 監控快取命中率
  • 追蹤查詢響應時間

5. 安全建議​

✓ 使用環境變數儲存敏感資訊

DATABASE_URL="postgresql://user:password@host:5432/db"

✓ 使用連線池

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  log: ['query', 'error', 'warn'],
});

✓ 啟用SSL連線

DATABASE_URL="postgresql://user:password@host:5432/db?sslmode=require"

常見問題解決​

Q1: 如何處理大量資料遷移?​

A: 分批處理,使用遊標:

const BATCH_SIZE = 1000;
let offset = 0;

while (true) {
  const batch = await legacyDb.query(`
    SELECT * FROM users
    LIMIT ${BATCH_SIZE}
    OFFSET ${offset}
  `);

  if (batch.length === 0) break;

  await migrateBatch(batch);
  offset += BATCH_SIZE;

  console.log(`已迁移 ${offset} 条记录`);
}

Q2: 如何最佳化全文搜尋效能?​

A: 建立GIN索引:

CREATE INDEX posts_body_gin_idx ON posts
USING gin(to_tsvector('english', body));

使用時:

const posts = await prisma.$queryRaw`
  SELECT * FROM posts
  WHERE to_tsvector('english', body) @@ to_tsquery('english', ${query})
`;

Q3: 如何處理併發更新衝突?​

A: 使用樂觀鎖:

const post = await prisma.post.findUnique({
  where: { id: postId },
});

const updated = await prisma.post.updateMany({
  where: {
    id: postId,
    updatedAt: post.updatedAt, // 版本检查
  },
  data: {
    views: { increment: 1 },
  },
});

if (updated.count === 0) {
  throw new Error('记录已被其他用户修改');
}

Q4: 如何監控資料庫效能?​

A: 使用Prisma中介軟體:

prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();

  console.log(`Query ${params.model}.${params.action} took ${after - before}ms`);

  if (after - before > 1000) {
    // 记录慢查询
    logger.warn('Slow query', { params, duration: after - before });
  }

  return result;
});

總結​

這個資料庫開發案例教你掌握:

✅ Schema設計: 使用Prisma設計複雜的關係模型 ✅ 遷移管理: 建立和應用資料庫遷移 ✅ 複雜查詢: 編寫高效的查詢和聚合操作 ✅ 效能最佳化: 索引、快取、物化檢視 ✅ 資料遷移: 安全的資料匯入和轉換 ✅ 生產部署: 監控、備份、安全配置

關鍵要點:

  1. 規劃先行: 花時間設計好的Schema,後續會省很多麻煩
  2. 索引為王: 合理的索引能帶來10-100倍效能提升
  3. 快取優先: 熱資料使用Redis快取,減輕資料庫壓力
  4. 監控重要: 建立完善的監控體系,及時發現效能問題
  5. 備份必須: 定期備份,並測試恢復流程

Claude Code在資料庫開發中的價值:

  • 快速生成複雜Schema設計
  • 自動編寫SQL查詢和遷移
  • 智慧最佳化建議和問題診斷
  • 生成資料遷移指令碼
  • 提供最佳實踐指導

現在,你可以用這些技能構建任何規模的資料庫系統!

下一步​