database-layer

资源

scripts/
  database-checklist.sh
references/
  orm-comparison.md

数据库层实现

本技能将指导你在应用中实现数据库层，从初始架构设计到查询优化。它借助 GoodVibes 精准工具和项目分析工具，帮助实现类型安全、可用于生产环境的数据库。

何时使用此技能

在以下情况下使用此技能：

• 设置新的数据库连接和 ORM
• 设计并实现数据库架构
• 创建并运行迁移
• 生成类型安全的数据库客户端
• 编写查询并处理关系
• 优化数据库性能
• 集成现有数据库基础设施

工作流

按照以下顺序实现数据库层：

1. 发现现有数据库基础设施

在实现任何数据库变更之前，使用 detect_stack 分析工具了解当前状态：

detect_stack:
  project_root: "."
  categories: ["database", "orm"]

此工具会识别：

• 现有数据库技术（PostgreSQL、MySQL、MongoDB、SQLite）
• 正在使用的 ORM／查询构建器（Prisma、Drizzle、Kysely、Mongoose）
• 架构定义文件
• 迁移工具
• 连接管理模式

检查项目记忆中的数据库决策：

precision_read:
  files:
    - path: ".goodvibes/memory/decisions.json"
    - path: ".goodvibes/memory/patterns.json"
  verbosity: minimal

查找以下内容：

• 先前选择的数据库技术（“使用 Prisma 确保类型安全”）
• 迁移策略（“始终使用可回滚的迁移”）
• 性能模式（“为外键添加索引”）
• 已知问题（“避免在用户端点中出现 N+1 查询”）

如果数据库已存在，请梳理当前架构：

get_database_schema:
  project_root: "."
  include_relations: true
  include_indexes: true

此工具会返回：

• 表／集合定义
• 列类型和约束
• 关系（外键、引用）
• 索引和唯一约束
• 枚举和自定义类型

2. 选择数据库技术

如果从头开始，请参考 ORM 对比文档，选择合适的技术栈。

参见：references/orm-comparison.md，了解决策流程。

关键决策因素：

因素	建议
类型安全优先	Prisma 或 Drizzle
最大程度控制 SQL	Kysely 或 Drizzle
文档数据库	Mongoose（MongoDB）
无服务器／边缘环境	Drizzle 搭配 libSQL/Turso
现有 PostgreSQL	Prisma 或 Drizzle
学习曲线	Prisma（开发体验最佳）

在记忆中记录你的决策：

选择完成后，将该决策记录到 .goodvibes/memory/decisions.json，以供将来参考。

3. 设计架构

先确定实体和关系：

Entities: User, Post, Comment, Category

Relationships:
- User 1:N Post (author)
- Post N:M Category (through PostCategory)
- Post 1:N Comment
- User 1:N Comment (author)

使用 precision_write 创建架构文件：

对于 Prisma：

precision_write:
  files:
    - path: "prisma/schema.prisma"
      content: |
        generator client {
          provider = "prisma-client-js"
        }

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

        model User {
          id        String   @id @default(cuid())
          email     String   @unique
          name      String?
          posts     Post[]
          comments  Comment[]
          createdAt DateTime @default(now())
          updatedAt DateTime @updatedAt
        }

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

          @@index([authorId])
          @@index([published, createdAt])
        }

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

        model Comment {
          id        String   @id @default(cuid())
          content   String
          post      Post     @relation(fields: [postId], references: [id])
          postId    String
          author    User     @relation(fields: [authorId], references: [id])
          authorId  String
          createdAt DateTime @default(now())

          @@index([postId])
          @@index([authorId])
        }
  verbosity: minimal

对于 Drizzle：

precision_write:
  files:
    - path: "src/db/schema.ts"
      content: |
        import { pgTable, text, timestamp, boolean, index } from 'drizzle-orm/pg-core';
        import { relations } from 'drizzle-orm';

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

        export const posts = pgTable('posts', {
          id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
          title: text('title').notNull(),
          content: text('content').notNull(),
          published: boolean('published').default(false).notNull(),
          authorId: text('author_id').notNull().references(() => users.id),
          createdAt: timestamp('created_at').defaultNow().notNull(),
          updatedAt: timestamp('updated_at').defaultNow().notNull(),
        }, (table) => ({
          authorIdx: index('author_idx').on(table.authorId),
          publishedCreatedIdx: index('published_created_idx').on(table.published, table.createdAt),
        }));

        export const usersRelations = relations(users, ({ many }) => ({
          posts: many(posts),
        }));

        export const postsRelations = relations(posts, ({ one }) => ({
          author: one(users, {
            fields: [posts.authorId],
            references: [users.id],
          }),
        }));
  verbosity: minimal

架构最佳实践：

1. 使用合适的 ID 策略：

   • 分布式系统使用 CUID／UUID
   • 简单应用使用自增 ID
   • 联接表使用复合主键

2. 添加时间戳：

   • 始终包含 createdAt
   • 对可变实体包含 updatedAt
   • 可考虑为软删除添加 deletedAt

3. 合理建立索引：

   • 外键（用于联接）
   • 经常查询的字段
   • 为多列筛选建立复合索引
   • 适用时添加唯一约束

4. 为扩展做好规划：

   • 文本与 VARCHAR 的长度限制
   • 使用 JSONB 存储灵活数据（PostgreSQL）
   • 为大型文本／二进制对象使用独立表

4. 配置数据库连接

创建环境配置：

precision_write:
  files:
    - path: ".env.example"
      content: |
        # Database
        DATABASE_URL="postgresql://user:password@localhost:5432/dbname"
        # For Prisma with connection pooling
        # DATABASE_URL="postgresql://user:password@localhost:5432/dbname?pgbouncer=true"
        # DIRECT_URL="postgresql://user:password@localhost:5432/dbname"
      mode: overwrite
  verbosity: minimal

创建数据库客户端模块：

对于 Prisma：

precision_write:
  files:
    - path: "src/lib/db.ts"
      content: |
        import { PrismaClient } from '@prisma/client';

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

        export const db =
          globalForPrisma.prisma ??
          new PrismaClient({
            log:
              process.env.NODE_ENV === 'development'
                ? ['query', 'error', 'warn']
                : ['error'],
          });

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

对于 Drizzle：

precision_write:
  files:
    - path: "src/lib/db.ts"
      content: |
        import { drizzle } from 'drizzle-orm/postgres-js';
        import postgres from 'postgres';
        import * as schema from '@/db/schema';

        const connectionString = process.env.DATABASE_URL!;

        const client = postgres(connectionString, {
          max: process.env.NODE_ENV === 'production' ? 10 : 1,
        });

        export const db = drizzle(client, { schema });
  verbosity: minimal

5. 运行迁移

使用 precision_exec 运行迁移命令并设置预期结果：

对于 Prisma：

precision_exec:
  commands:
    - cmd: "npx prisma migrate dev --name init"
      timeout_ms: 60000
      expect:
        exit_code: 0
      # Note: Prisma outputs progress to stderr; this is expected behavior
    - cmd: "npx prisma generate"
      expect:
        exit_code: 0
  verbosity: standard

对于 Drizzle：

precision_exec:
  commands:
    - cmd: "npx drizzle-kit generate"
      expect:
        exit_code: 0
    - cmd: "npx drizzle-kit push"
      timeout_ms: 60000
      expect:
        exit_code: 0
  verbosity: standard

迁移最佳实践：

1. 应用前始终检查生成的迁移
2. 使用可回滚的迁移（同时包含 up 和 down）
3. 在开发数据库上测试迁移，再应用到生产环境
4. 运行迁移前备份生产数据
5. 对多步骤迁移使用事务

6. 生成类型安全的客户端

使用 generate_types 项目工具根据架构生成 TypeScript 类型：

generate_types:
  project_root: "."
  source: "database"
  output_path: "src/types/db.ts"

这会创建以下类型定义：

• 表/集合类型
• 插入类型（省略自动生成的字段）
• 更新类型（所有字段均为可选）
• 查询类型（包含关联关系）

验证类型生成：

precision_exec:
  commands:
    - cmd: "npm run typecheck"
      expect:
        exit_code: 0
  verbosity: minimal

7. 实现查询

从基本的 CRUD 操作开始：

precision_write:
  files:
    - path: "src/db/queries/users.ts"
      content: |
        import { db } from '@/lib/db';

        export async function createUser(data: { email: string; name?: string }) {
          return db.user.create({
            data,
          });
        }

        export async function getUserById(id: string) {
          return db.user.findUnique({
            where: { id },
            include: {
              posts: true,
            },
          });
        }

        export async function updateUser(
          id: string,
          data: { email?: string; name?: string }
        ) {
          return db.user.update({
            where: { id },
            data,
          });
        }

        export async function deleteUser(id: string) {
          return db.user.delete({
            where: { id },
          });
        }
  verbosity: minimal

使用项目工具检查 N+1 查询模式：

get_prisma_operations:
  project_root: "."
  analyze_performance: true

这会识别：

• N+1 查询机会（缺少 include 或 select）
• 经常查询的字段缺少索引
• 低效的关联关系加载

优化查询：

1. 使用 select 限制字段：

   db.user.findMany({
     select: { id: true, email: true }, // Don't fetch unused fields
   });
   

2. 预加载关联关系：

   db.post.findMany({
     include: { author: true }, // Prevents N+1
   });
   

3. 使用分页：

   db.post.findMany({
     take: 20,
     skip: (page - 1) * 20,
   });
   

4. 添加数据库级约束：

   @@index([userId, createdAt(sort: Desc)])
   

8. 实现事务

对于多步骤操作，请使用事务：

Prisma：

export async function createPostWithCategories(
  postData: { title: string; content: string; authorId: string },
  categoryIds: string[]
) {
  return db.$transaction(async (tx) => {
    const post = await tx.post.create({
      data: {
        ...postData,
        categories: {
          connect: categoryIds.map((id) => ({ id })),
        },
      },
    });

    await tx.user.update({
      where: { id: postData.authorId },
      data: { updatedAt: new Date() },
    });

    return post;
  });
}

Drizzle：

export async function createPostWithCategories(
  postData: { title: string; content: string; authorId: string },
  categoryIds: string[]
) {
  return db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values(postData).returning();

    await tx.insert(postCategories).values(
      categoryIds.map((categoryId) => ({
        postId: post.id,
        categoryId,
      }))
    );

    return post;
  });
}

9. 填充开发数据

为本地开发创建种子脚本：

precision_write:
  files:
    - path: "prisma/seed.ts"
      content: |
        import { PrismaClient } from '@prisma/client';

        const prisma = new PrismaClient();

        async function main() {
          // Clear existing data
          await prisma.comment.deleteMany();
          await prisma.post.deleteMany();
          await prisma.user.deleteMany();
          await prisma.category.deleteMany();

          // Create users
          const alice = await prisma.user.create({
            data: {
              email: '[email protected]',
              name: 'Alice',
            },
          });

          const bob = await prisma.user.create({
            data: {
              email: '[email protected]',
              name: 'Bob',
            },
          });

          // Create categories
          const tech = await prisma.category.create({
            data: { name: 'Technology' },
          });

          const news = await prisma.category.create({
            data: { name: 'News' },
          });

          // Create posts
          await prisma.post.create({
            data: {
              title: 'First Post',
              content: 'This is the first post',
              published: true,
              authorId: alice.id,
              categories: {
                connect: [{ id: tech.id }],
              },
            },
          });

          console.log('Database seeded successfully');
        }

        main()
          .catch((e) => {
            console.error(e);
            process.exit(1);
          })
          .finally(async () => {
            await prisma.$disconnect();
          });
  verbosity: minimal

更新 package.json：

precision_edit:
  edits:
    - path: "package.json"
      find: '"scripts": {'
      hints:
        near_line: 2
      replace: |
        "prisma": {
          "seed": "tsx prisma/seed.ts"
        },
        "scripts": {
  verbosity: minimal

10. 验证实现

运行数据库检查清单脚本：

./plugins/goodvibes/skills/outcome/database-layer/scripts/database-checklist.sh .

此脚本会验证：

• 架构文件存在且有效
• 迁移目录存在
• .env.example 中记录了数据库 URL
• 已配置类型生成
• 不存在 SQL 注入漏洞（字符串拼接）
• 已配置连接池
• 外键上有索引

运行类型检查和测试：

precision_exec:
  commands:
    - cmd: "npm run typecheck"
      expect:
        exit_code: 0
    - cmd: "npm run test -- db"
      expect:
        exit_code: 0
  verbosity: minimal

使用 query_database 验证数据完整性：

query_database:
  project_root: "."
  query: "SELECT COUNT(*) FROM users;"

常见模式

软删除

添加 deletedAt 字段，并在查询中过滤：

model Post {
  id        String    @id
  deletedAt DateTime?
}

// Soft delete
await db.post.update({
  where: { id },
  data: { deletedAt: new Date() },
});

// Query only active records
await db.post.findMany({
  where: { deletedAt: null },
});

乐观锁

使用版本字段防止并发更新：

model Post {
  id      String @id
  version Int    @default(0)
}

await db.post.update({
  where: {
    id: postId,
    version: currentVersion,
  },
  data: {
    title: newTitle,
    version: { increment: 1 },
  },
});

连接池

在无服务器环境中使用连接池：

# PgBouncer
DATABASE_URL="postgresql://user:password@localhost:6543/db?pgbouncer=true"
DIRECT_URL="postgresql://user:password@localhost:5432/db"

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

全文搜索

PostgreSQL：

@@index([content(ops: raw("gin_trgm_ops"))], type: Gin)

await db.$queryRaw`
  SELECT * FROM posts
  WHERE to_tsvector('english', content) @@ to_tsquery('search terms')
`;

安全检查清单

• 数据库凭据存放在环境变量中（不提交到版本库）
• 验证所有用户提供的数据
• 使用参数化查询（不进行字符串拼接）
• 为多租户应用启用行级安全
• 对开销较大的查询实施速率限制
• 对敏感操作进行审计日志记录
• 数据库用户权限遵循最小权限原则
• 在生产环境中为数据库连接启用 SSL/TLS

性能检查清单

• 在外键上建立索引
• 为多列筛选建立复合索引
• 已配置连接池
• 对查询结果进行分页
• 预加载以避免 N+1 查询
• 在开发环境中记录数据库查询日志
• 对慢查询执行 Explain/Analyze
• 缓存频繁访问的数据

故障排除

迁移因约束冲突而失败

1. 检查现有数据是否与新约束冲突
2. 在架构迁移前添加数据迁移
3. 使用多步骤迁移（添加可空列、填充数据、设为必填）

检测到 N+1 查询

1. 使用 get_prisma_operations 确定问题位置
2. 使用关联关系添加 include 或 select
3. 对于复杂情况，可考虑使用 dataloader

连接池耗尽

1. 增大连接字符串中的连接池大小（?pool_timeout=10）
2. 检查是否缺少 await（连接未释放）
3. 使用连接池代理（PgBouncer、Prisma Accelerate）

类型生成失败

1. 使用 npx prisma validate 验证架构语法
2. 清除生成的文件并重新生成
3. 检查关联关系中是否存在循环依赖

后续步骤

实现数据库层后：

1. 添加缓存 - 使用 Redis 缓存频繁访问的数据
2. 实现搜索 - 添加全文搜索或 Elasticsearch
3. 添加监控 - 跟踪查询性能和慢查询
4. 编写测试 - 为查询编写单元测试，为事务编写集成测试
5. 记录架构 - 为架构添加注释，供团队参考
6. 规划备份 - 设置自动数据库备份

如需更多参考资料和决策树，请参阅：

• references/orm-comparison.md - ORM 选择指南
• scripts/database-checklist.sh - 验证脚本