能力

设计具有适当关系的Prisma模式
生成和管理数据库迁移
优化查询以提高性能
实现类型安全的数据库访问
配置多数据库支持
设置种子和测试策略

使用方法

当你需要：

使用Prisma设计数据库模式
设置迁移和数据库工作流程
优化数据库查询
实现复杂关系
配置Prisma与Next.js或其他框架

输入

参数	类型	必需	描述
database	string	否	postgresql, mysql, sqlite, mongodb
models	array	否	要创建的模型列表
relations	array	否	模型关系
features	array	否	migrations, seeding, edge

模式配置

{
  "database": "postgresql",
  "models": [
    {
      "name": "User",
      "fields": [
        { "name": "email", "type": "String", "unique": true },
        { "name": "name", "type": "String", "optional": true },
        { "name": "posts", "type": "Post", "relation": "one-to-many" }
      ]
    },
    {
      "name": "Post",
      "fields": [
        { "name": "title", "type": "String" },
        { "name": "content", "type": "String", "optional": true },
        { "name": "author", "type": "User", "relation": "many-to-one" }
      ]
    }
  ]
}

输出结构

project/
├── prisma/
│   ├── schema.prisma           # 数据库模式
│   ├── migrations/             # 迁移文件
│   │   └── 20240101_init/
│   │       └── migration.sql
│   └── seed.ts                 # 种子脚本
├── lib/
│   └── db/
│       ├── prisma.ts           # Prisma客户端单例
│       ├── queries/
│       │   ├── users.ts        # 用户查询
│       │   └── posts.ts        # 帖子查询
│       └── types.ts            # 扩展类型
└── package.json

生成代码模式

Prisma模式

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

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

model Profile {
  id        String   @id @default(cuid())
  bio       String?
  avatar    String?
  userId    String   @unique
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

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

  @@index([authorId])
  @@index([slug])
  @@map("posts")
}

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

  @@map("categories")
}

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

  @@map("tags")
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  postId    String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  parentId  String?
  parent    Comment? @relation("CommentReplies", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentReplies")
  createdAt DateTime @default(now())

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

Prisma客户端单例

// lib/db/prisma.ts
import { PrismaClient } from '@prisma/client';

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

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

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

export default prisma;

查询函数

// lib/db/queries/users.ts
import { prisma } from '../prisma';
import { Prisma } from '@prisma/client';

export type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true; profile: true };
}>;

export async function getUserById(id: string): Promise<UserWithPosts | null> {
  return prisma.user.findUnique({
    where: { id },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: 10,
      },
      profile: true,
    },
  });
}

export async function getUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
    select: {
      id: true,
      email: true,
      name: true,
      role: true,
    },
  });
}

export async function createUser(data: Prisma.UserCreateInput) {
  return prisma.user.create({
    data,
    include: {
      profile: true,
    },
  });
}

export async function updateUser(id: string, data: Prisma.UserUpdateInput) {
  return prisma.user.update({
    where: { id },
    data,
  });
}

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

export async function getUsers(params: {
  skip?: number;
  take?: number;
  where?: Prisma.UserWhereInput;
  orderBy?: Prisma.UserOrderByWithRelationInput;
}) {
  const { skip = 0, take = 10, where, orderBy = { createdAt: 'desc' } } = params;

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip,
      take,
      where,
      orderBy,
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        createdAt: true,
        _count: {
          select: { posts: true },
        },
      },
    }),
    prisma.user.count({ where }),
  ]);

  return {
    users,
    total,
    pages: Math.ceil(total / take),
  };
}

带关系的帖子查询

// lib/db/queries/posts.ts
import { prisma } from '../prisma';
import { Prisma } from '@prisma/client';

export async function getPublishedPosts(params: {
  page?: number;
  limit?: number;
  categoryId?: string;
  authorId?: string;
  search?: string;
}) {
  const { page = 1, limit = 10, categoryId, authorId, search } = params;
  const skip = (page - 1) * limit;

  const where: Prisma.PostWhereInput = {
    published: true,
    ...(categoryId && {
      categories: { some: { id: categoryId } },
    }),
    ...(authorId && { authorId }),
    ...(search && {
      OR: [
        { title: { contains: search, mode: 'insensitive' } },
        { content: { contains: search, mode: 'insensitive' } },
      ],
    }),
  };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
      include: {
        author: {
          select: { id: true, name: true, email: true },
        },
        categories: true,
        tags: true,
        _count: {
          select: { comments: true },
        },
      },
    }),
    prisma.post.count({ where }),
  ]);

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

export async function getPostBySlug(slug: string) {
  return prisma.post.findUnique({
    where: { slug },
    include: {
      author: {
        select: { id: true, name: true, email: true },
      },
      categories: true,
      tags: true,
      comments: {
        where: { parentId: null },
        include: {
          author: { select: { id: true, name: true } },
          replies: {
            include: {
              author: { select: { id: true, name: true } },
            },
          },
        },
        orderBy: { createdAt: 'desc' },
      },
    },
  });
}

export async function createPost(data: {
  title: string;
  content?: string;
  authorId: string;
  categoryIds?: string[];
  tagNames?: string[];
}) {
  const { title, content, authorId, categoryIds = [], tagNames = [] } = data;

  const slug = title
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/(^-|-$)/g, '');

  return prisma.post.create({
    data: {
      title,
      slug,
      content,
      authorId,
      categories: {
        connect: categoryIds.map((id) => ({ id })),
      },
      tags: {
        connectOrCreate: tagNames.map((name) => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: {
      author: true,
      categories: true,
      tags: true,
    },
  });
}

种子脚本

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

const prisma = new PrismaClient();

async function main() {
  console.log('Seeding database...');

  // Create categories
  const categories = await Promise.all([
    prisma.category.upsert({
      where: { name: 'Technology' },
      update: {},
      create: { name: 'Technology' },
    }),
    prisma.category.upsert({
      where: { name: 'Design' },
      update: {},
      create: { name: 'Design' },
    }),
  ]);

  // Create admin user
  const adminPassword = await hash('admin123', 12);
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin User',
      password: adminPassword,
      role: 'ADMIN',
      profile: {
        create: {
          bio: 'System administrator',
        },
      },
    },
  });

  // Create sample posts
  await prisma.post.createMany({
    data: [
      {
        title: 'Getting Started with Prisma',
        slug: 'getting-started-with-prisma',
        content: 'Learn how to use Prisma ORM...',
        published: true,
        authorId: admin.id,
      },
      {
        title: 'Database Best Practices',
        slug: 'database-best-practices',
        content: 'Tips for designing efficient databases...',
        published: true,
        authorId: admin.id,
      },
    ],
    skipDuplicates: true,
  });

  console.log('Seeding completed!');
}

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

迁移工作流

# 创建初始迁移
npx prisma migrate dev --name init

# 将迁移应用到生产环境
npx prisma migrate deploy

# 重置数据库（仅限开发环境）
npx prisma migrate reset

# 生成Prisma客户端
npx prisma generate

# 种子数据库
npx prisma db seed

package.json脚本

{
  "scripts": {
    "db:generate": "prisma generate",
    "db:push": "prisma db push",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:seed": "prisma db seed",
    "db:studio": "prisma studio",
    "db:reset": "prisma migrate reset"
  },
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

查询优化模式

仅选择所需字段

// 坏 - 获取所有字段
const user = await prisma.user.findUnique({ where: { id } });

// 好 - 仅获取所需字段
const user = await prisma.user.findUnique({
  where: { id },
  select: {
    id: true,
    name: true,
    email: true,
  },
});

批量操作

// 使用事务进行多个操作
const [user, posts] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.createMany({ data: postsData }),
]);

// 使用交互式事务进行复杂逻辑
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.post.create({
    data: { ...postData, authorId: user.id },
  });
});

带有游标的分页

async function getPaginatedPosts(cursor?: string) {
  return prisma.post.findMany({
    take: 10,
    ...(cursor && {
      skip: 1,
      cursor: { id: cursor },
    }),
    orderBy: { createdAt: 'desc' },
  });
}

依赖

{
  "dependencies": {
    "@prisma/client": "^6.0.0"
  },
  "devDependencies": {
    "prisma": "^6.0.0"
  }
}

工作流程

定义模式 - 创建模型和关系
生成客户端 - 运行prisma generate
创建迁移 - 运行prisma migrate dev
实现查询 - 类型安全的数据库访问
种子数据库 - 创建初始数据
优化查询 - 选择、批量、索引

应用的最佳实践

使用Prisma客户端进行类型安全的查询
适当的关系建模
高效的分页模式
事务支持
适当的地方使用级联删除
频繁查询的字段进行索引

参考

Prisma文档：https://www.prisma.io/docs
Prisma MCP服务器：https://www.prisma.io/mcp
Prisma示例：https://github.com/prisma/prisma-examples

目标流程

database-schema-design
migration-management
query-optimization
data-seeding
database-testing