Prisma + PostgreSQL 实战：Schema 设计、Migration、类型安全查询与 Seed 数据

# 初始化 Schema 并生成第一个 Migration
npx prisma migrate dev --name init

# Model 变更后生成新 Migration
npx prisma migrate dev --name add_user_role

# 部署到生产环境
npx prisma migrate deploy

# 生成客户端（类型安全）
npx prisma generate

# 可视化数据库
npx prisma studio

# CI/CD 步骤（Docker 容器内）
npx prisma generate               # 先编译出 Prisma Client
dotenv -e .env.production npx prisma migrate deploy  # 只 apply 待执行迁移
npm run build                     # 构建应用
npm start                         # 启动

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  // 创建种子数据
  const alice = await prisma.user.upsert({
    where: { email: 'alice@example.com' },
    update: {},
    create: {
      email: 'alice@example.com',
      name: 'Alice',
      role: 'USER',
      posts: {
        create: [
          {
            title: 'Getting Started with Prisma',
            slug: 'getting-started-prisma',
            published: true,
            tags: { create: [{ name: 'prisma' }, { name: 'database' }] },
          },
        ],
      },
      profile: { create: { bio: 'Full-stack developer' } },
    },
  });

  console.log({ alice });
}

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

// package.json
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  },
  "scripts": {
    "db:seed": "prisma db seed"
  }
}

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'], // 开发时开启
});

// Create（含关联嵌套创建）
const user = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    name: 'Bob',
    role: 'ADMIN',
    profile: { create: { bio: 'Administrator' } },
  },
  include: { profile: true }, // 返回关联数据
});

// Read（条件 + 关联 + 分页）
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: { email: { contains: '@example.com' } },
  },
  include: { author: { select: { name: true, email: true } }, tags: true },
  orderBy: [{ createdAt: 'desc' }],
  take: 10,
  skip: 0, // offset for pagination
});

// Update（计数器原子性）
const updated = await prisma.post.update({
  where: { id: 1 },
  data: { views: { increment: 1 } }, // 原子自增
});

// Delete（级联删除由 Schema 中 onDelete: Cascade 控制）
await prisma.user.delete({ where: { id: 1 } });

// 1. Filter 多条件组合
const users = await prisma.user.findMany({
  where: {
    AND: [
      { role: 'USER' },
      { createdAt: { gte: new Date('2025-01-01') } },
      { posts: { some: { published: true } } }, // 至少有一篇已发布文章
    ],
  },
});

// 2. Aggregation（聚合）
const stats = await prisma.post.groupBy({
  by: ['published'],
  _count: { id: true },
  _avg: { views: true },
  _sum: { views: true },
  _max: { createdAt: true },
});

// 3. DISTINCT 查询
const distinctTags = await prisma.post.findMany({
  distinct: ['authorId'],
});

// 4. SELECT 指定字段（减少返回数据量）
const slimPosts = await prisma.post.findMany({
  select: { id: true, title: true, slug: true },
  take: 5,
});

// 方式 1：交互式事务（推荐大多数场景）
const [user, post] = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'charlie@example.com', name: 'Charlie' },
  });
  const post = await tx.post.create({
    data: {
      title: "Charlie's Post",
      slug: 'charlies-post',
      authorId: user.id,
    },
  });
  return [user, post];
});

// 方式 2：批量事务（串行执行，一步失败全回滚）
const result = await prisma.$transaction([
  prisma.user.update({ where: { id: 1 }, data: { name: 'Updated' } }),
  prisma.post.update({ where: { id: 1 }, data: { views: { increment: 1 } } }),
]);

// 方式 3：事务选项（隔离级别 + 重试）
await prisma.$transaction(
  async (tx) => {
    const user = await tx.user.create({ data: { email: 'dave@example.com' } });
    return user;
  },
  {
    isolationLevel: 'Serializable',
    maxWait: 5000,
    timeout: 10000,
  }
);

// 简单 Raw Query（返回动态类型，不安全）
const result = await prisma.$queryRaw`
  SELECT * FROM posts WHERE published = true ORDER BY views DESC LIMIT 5
`;

// 参数化 Raw Query（防 SQL 注入）
const email = 'alice@example.com';
const user = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

// Prisma 类型安全的 Raw Query（typed-sql 功能）
// 先运行 prisma db generate 生成类型，然后：
import { getActiveUsers } from '@prisma/client/sql';
const users = await prisma.$queryRawTyped(getActiveUsers());

// Raw Execute（INSERT/UPDATE/DELETE）
await prisma.$executeRaw`
  INSERT INTO tags (name) VALUES ('new-tag') ON CONFLICT DO NOTHING
`;

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

const prisma = new PrismaClient();

// 全局 Middleware：记录慢查询
prisma.$use(async (params, next) => {
  const start = performance.now();
  const result = await next(params);
  const end = performance.now();
  const duration = end - start;

  if (duration > 1000) {
    console.warn(`[SLOW QUERY] ${params.model}.${params.action} took ${duration.toFixed(2)}ms`);
  }

  return result;
});

// Middleware 2：自动注入用户上下文
prisma.$use(async (params, next) => {
  if (params.model === 'Post' && params.action === 'create') {
    params.args.data.slug = params.args.data.slug ||
      params.args.data.title.toLowerCase().replace(/\s+/g, '-');
  }
  return next(params);
});

export default prisma;

// 避免 Next.js 开发热重载产生多个连接
const globalForPrisma = global as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

// 连接池池大小需在连接字符串中配置
// DATABASE_URL=postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=5
// production 推荐 via PgBouncer:`DATABASE_URL=postgresql://user:pass@pgbouncer:6432/db?pgbouncer=true`

npx prisma migrate reset --force  # 清空数据库 + 应用全部迁移 + 运行 seed
# CI 中 --force 跳过确认提示