跳到主要内容

数据库开发实战

今天用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查询和迁移
  • 智能优化建议和问题诊断
  • 生成数据迁移脚本
  • 提供最佳实践指导

现在,你可以用这些技能构建任何规模的数据库系统!

下一步​