database-layer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Resources

资源

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

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

Database Layer Implementation

数据库层实现

This skill guides you through implementing database layers in applications, from initial schema design to query optimization. It leverages GoodVibes precision tools and project analysis tools for type-safe, production-ready database implementations.

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

When to Use This Skill

何时使用此技能

Use this skill when you need to:

Set up a new database connection and ORM
Design and implement database schemas
Create and run migrations
Generate type-safe database clients
Write queries and handle relationships
Optimize database performance
Integrate with existing database infrastructure

在以下场景中使用此技能：

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

Workflow

工作流

Follow this sequence for database layer implementation:

按照以下步骤实现数据库层：

1. Discover Existing Database Infrastructure

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

Before implementing any database changes, understand the current state using the

detect_stack

analysis tool:

yaml

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

This identifies:

Existing database technology (PostgreSQL, MySQL, MongoDB, SQLite)
ORM/query builder in use (Prisma, Drizzle, Kysely, Mongoose)
Schema definition files
Migration tooling
Connection management patterns

Check project memory for database decisions:

yaml

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

Look for:

Previous database technology choices ("Use Prisma for type safety")
Migration strategies ("Always use reversible migrations")
Performance patterns ("Add indexes for foreign keys")
Known issues ("Avoid N+1 queries in user endpoints")

If database already exists, map the current schema:

yaml

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

This returns:

Table/collection definitions
Column types and constraints
Relationships (foreign keys, references)
Indexes and unique constraints
Enums and custom types

在进行任何数据库变更前，使用

detect_stack

分析工具了解当前状态：

yaml

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

该工具将识别：

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

检查项目内存中的数据库决策：

yaml

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

重点查找：

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

若数据库已存在，映射当前模式：

yaml

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

该工具将返回：

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

2. Choose Database Technology

2. 选择数据库技术

If starting fresh, consult the ORM comparison reference to select the appropriate technology stack.

See: references/orm-comparison.md for decision trees.

Key decision factors:

Factor	Recommendation
Type safety priority	Prisma or Drizzle
Maximum SQL control	Kysely or Drizzle
Document database	Mongoose (MongoDB)
Serverless/edge	Drizzle with libSQL/Turso
Existing PostgreSQL	Prisma or Drizzle
Learning curve	Prisma (best DX)

Record your decision in memory:

After choosing, document the decision in

.goodvibes/memory/decisions.json

for future reference.

若从零开始，参考ORM对比文档选择合适的技术栈。

查看：references/orm-comparison.md 获取决策树。

关键决策因素：

因素	推荐方案
类型安全优先级	Prisma或Drizzle
最大SQL控制权	Kysely或Drizzle
文档型数据库	Mongoose（MongoDB）
无服务器/边缘环境	Drizzle搭配libSQL/Turso
已有PostgreSQL	Prisma或Drizzle
学习曲线	Prisma（最佳开发体验）

将决策记录到内存中：

选择完成后，将决策记录到

.goodvibes/memory/decisions.json

，以备后续参考。

3. Design Schema

3. 设计模式

Identify entities and relationships first:

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)

Create schema files using precision_write:

For Prisma:

yaml

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

For Drizzle:

yaml

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

Schema best practices:

Use appropriate ID strategy:
- CUID/UUID for distributed systems
- Auto-increment for simple apps
- Composite keys for join tables
Add timestamps:
- Always include
```
createdAt
```
- Include
```
updatedAt
```
  for mutable entities
- Consider
```
deletedAt
```
  for soft deletes
Index strategically:
- Foreign keys (for joins)
- Frequently queried fields
- Composite indexes for multi-column filters
- Unique constraints where applicable
Plan for scale:
- Text vs VARCHAR limits
- JSONB for flexible data (PostgreSQL)
- Separate tables for large text/blobs

首先识别实体与关联关系：

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：

yaml

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：

yaml

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

模式最佳实践：

使用合适的ID策略：
- 分布式系统使用CUID/UUID
- 简单应用使用自增ID
- 关联表使用复合键
添加时间戳：
- 始终包含
```
createdAt
```
- 可变实体包含
```
updatedAt
```
- 软删除场景考虑
```
deletedAt
```
合理添加索引：
- 外键（用于关联查询）
- 频繁查询的字段
- 多列过滤场景使用复合索引
- 适用场景添加唯一约束
为扩展做规划：
- Text与VARCHAR的限制
- PostgreSQL使用JSONB存储灵活数据
- 大文本/二进制数据使用独立表

4. Configure Database Connection

4. 配置数据库连接

Create environment configuration:

yaml

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

Create database client module:

For Prisma:

yaml

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

For Drizzle:

yaml

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

创建环境配置：

yaml

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：

yaml

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：

yaml

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. Run Migrations

5. 运行迁移

Use

precision_exec

to run migration commands with expectations:

For Prisma:

yaml

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

For Drizzle:

yaml

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

Migration best practices:

Always review generated migrations before applying
Use reversible migrations (include both up and down)
Test migrations on dev database before production
Backup production data before running migrations
Use transactions for multi-step migrations

使用

precision_exec

执行迁移命令并验证结果：

对于Prisma：

yaml

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：

yaml

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

迁移最佳实践：

应用前务必检查生成的迁移文件
使用可逆迁移（包含up和down步骤）
在开发数据库测试迁移后再部署到生产环境
运行生产迁移前备份数据
多步骤迁移使用事务

6. Generate Type-Safe Client

6. 生成类型安全客户端

Use the

generate_types

project tool to generate TypeScript types from your schema:

yaml

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

This creates type definitions for:

Table/collection types
Insert types (omitting auto-generated fields)
Update types (all fields optional)
Select types (with relations)

Verify type generation:

yaml

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

使用

generate_types

项目工具从模式生成TypeScript类型：

yaml

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

该工具将创建以下类型定义：

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

验证类型生成：

yaml

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

7. Implement Queries

7. 实现查询

Start with basic CRUD operations:

yaml

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

Check for N+1 query patterns using project tools:

yaml

get_prisma_operations:
  project_root: "."
  analyze_performance: true

This identifies:

N+1 query opportunities (missing
```
include
```
or
```
select
```
)
Missing indexes on frequently queried fields
Inefficient relationship loading

Optimize queries:

Use select to limit fields:

typescript

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

Eager load relationships:

typescript

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

Use pagination:

typescript

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

Add database-level constraints:

prisma

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

从基础CRUD操作开始：

yaml

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查询模式：

yaml

get_prisma_operations:
  project_root: "."
  analyze_performance: true

该工具将识别：

N+1查询风险（缺少
```
include
```
或
```
select
```
）
频繁查询字段缺少索引
低效的关联关系加载

优化查询：

使用select限制返回字段：

typescript

db.user.findMany({
  select: { id: true, email: true }, // 不获取未使用的字段
});

预加载关联关系：

typescript

db.post.findMany({
  include: { author: true }, // 避免N+1查询
});

使用分页：

typescript

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

添加数据库级约束：

prisma

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

8. Implement Transactions

8. 实现事务

For multi-step operations, use transactions:

Prisma:

typescript

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:

typescript

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

多步骤操作使用事务：

Prisma：

typescript

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：

typescript

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. Seed Development Data

9. 填充开发数据

Create seed script for local development:

yaml

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: 'alice@example.com',
              name: 'Alice',
            },
          });

          const bob = await prisma.user.create({
            data: {
              email: 'bob@example.com',
              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

Update package.json:

yaml

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

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

yaml

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: 'alice@example.com',
              name: 'Alice',
            },
          });

          const bob = await prisma.user.create({
            data: {
              email: 'bob@example.com',
              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：

yaml

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

10. Validate Implementation

10. 验证实现

Run the database checklist script:

bash

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

This validates:

Schema file exists and is valid
Migration directory present
Database URL documented in .env.example
Type generation configured
No SQL injection vulnerabilities (string concatenation)
Connection pooling configured
Indexes on foreign keys

Run type checking and tests:

yaml

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

Use query_database to verify data integrity:

yaml

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

运行数据库检查脚本：

bash

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

该脚本将验证：

模式文件存在且有效
迁移目录已创建
.env.example中已记录数据库URL
已配置类型生成
无SQL注入漏洞（字符串拼接）
已配置连接池
外键已添加索引

运行类型检查与测试：

yaml

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

使用query_database验证数据完整性：

yaml

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

Common Patterns

常见模式

Soft Deletes

软删除

Add

deletedAt

field and filter in queries:

prisma

model Post {
  id        String    @id
  deletedAt DateTime?
}

typescript

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

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

添加

deletedAt

字段并在查询中过滤：

prisma

model Post {
  id        String    @id
  deletedAt DateTime?
}

typescript

// 软删除
await db.post.update({
  where: { id },
  data: { deletedAt: new Date() },
});

// 仅查询活跃记录
await db.post.findMany({
  where: { deletedAt: null },
});

Optimistic Locking

乐观锁

Use version field to prevent concurrent updates:

prisma

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

typescript

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

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

prisma

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

typescript

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

Connection Pooling

连接池

For serverless environments, use connection pooling:

env

undefined

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

env

undefined

PgBouncer

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


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

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


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

Full-Text Search

全文搜索

PostgreSQL:

prisma

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

typescript

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

PostgreSQL：

prisma

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

typescript

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

Security Checklist

安全检查清单

Performance Checklist

性能检查清单

Troubleshooting

故障排除

Migration fails with constraint violation

迁移因约束冲突失败

Check existing data conflicts with new constraints
Add data migration before schema migration
Use multi-step migrations (add column nullable, populate, make required)

检查现有数据是否与新约束冲突
模式迁移前先执行数据迁移
使用多步骤迁移（先添加可为空的列，填充数据，再设为必填）

N+1 query detected

检测到N+1查询

Use
```
get_prisma_operations
```
to identify location
Add
```
include
```
or
```
select
```
with relations
Consider using
```
dataloader
```
for complex cases

使用
```
get_prisma_operations
```
定位问题位置
添加
```
include
```
或
```
select
```
包含关联关系
复杂场景考虑使用
```
dataloader
```

Connection pool exhausted

连接池耗尽

Increase pool size in connection string (
```
?pool_timeout=10
```
)
Check for missing
```
await
```
(connections not released)
Use connection pooler (PgBouncer, Prisma Accelerate)

在连接字符串中增大池大小（如
```
?pool_timeout=10
```
）
检查是否遗漏
```
await
```
（连接未释放）
使用连接池器（PgBouncer、Prisma Accelerate）

Type generation fails

类型生成失败

Verify schema syntax with
```
npx prisma validate
```
Clear generated files and regenerate
Check for circular dependencies in relations

使用
```
npx prisma validate
```
验证模式语法
清除生成文件后重新生成
检查关联关系是否存在循环依赖

Next Steps

后续步骤

After implementing the database layer:

Add caching - Use Redis for frequently accessed data
Implement search - Add full-text search or Elasticsearch
Add monitoring - Track query performance and slow queries
Write tests - Unit tests for queries, integration tests for transactions
Document schema - Add comments to schema for team reference
Plan backups - Set up automated database backups

For additional reference material and decision trees, see:

references/orm-comparison.md - ORM selection guide
scripts/database-checklist.sh - Validation script

数据库层实现完成后：

添加缓存 - 使用Redis缓存频繁访问的数据
实现搜索 - 添加全文搜索或集成Elasticsearch
添加监控 - 跟踪查询性能与慢查询
编写测试 - 查询单元测试、事务集成测试
文档化模式 - 为模式添加注释供团队参考
规划备份 - 设置自动数据库备份

更多参考资料与决策树，请查看：

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