Prisma ORM 高级模式：事务、中间件与原始查询

Prisma 基础之上

大多数指南只介绍基本的 Prisma CRUD。以下是生产级应用所需的模式。

交互式事务

// 确保多个操作的原子性
const [order, payment] = await prisma.$transaction(async (tx) => {
  // 检查库存
  const product = await tx.product.findUniqueOrThrow({
    where: { id: productId },
  });
  
  if (product.stock < quantity) {
    throw new Error('Insufficient stock');
  }
  
  // 扣减库存
  await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } },
  });
  
  // 创建订单
  const order = await tx.order.create({
    data: { userId, productId, quantity, total: product.price * quantity },
  });
  
  // 创建支付记录
  const payment = await tx.payment.create({
    data: { orderId: order.id, amount: order.total, status: 'PENDING' },
  });
  
  return [order, payment];
}, {
  maxWait: 5000, // 等待连接的最大时间
  timeout: 10000, // 事务的最大执行时间
  isolationLevel: 'Serializable',
});

软删除中间件

// prisma/middleware.ts
export function addSoftDeleteMiddleware(prisma: PrismaClient) {
  // 拦截删除操作
  prisma.$use(async (params, next) => {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (params.action === 'deleteMany') {
      params.action = 'updateMany';
      if (params.args.data !== undefined) {
        params.args.data.deletedAt = new Date();
      } else {
        params.args.data = { deletedAt: new Date() };
      }
    }
    return next(params);
  });
  
  // 从查询中过滤掉软删除的记录
  prisma.$use(async (params, next) => {
    const softDeleteModels = ['User', 'Post', 'Comment'];
    
    if (softDeleteModels.includes(params.model ?? '')) {
      if (params.action === 'findUnique' || params.action === 'findFirst') {
        params.action = 'findFirst';
        params.args.where = { ...params.args.where, deletedAt: null };
      }
      if (params.action === 'findMany') {
        params.args.where = { ...params.args.where, deletedAt: null };
      }
    }
    return next(params);
  });
}

Prisma Schema 设计模式

// 多态关联
model Notification {
  id          String   @id @default(cuid())
  userId      String
  user        User     @relation(fields: [userId], references: [id])
  type        String
  
  // 多态引用
  resourceId   String
  resourceType String   // 'Post' | 'Comment' | 'Order'
  
  read        Boolean  @default(false)
  createdAt   DateTime @default(now())
  
  @@index([userId, read])
  @@index([resourceId, resourceType])
}

// 全文搜索（PostgreSQL）
model Post {
  id      String @id @default(cuid())
  title   String
  content String
  
  @@index([title, content], type: Gin)  // PostgreSQL GIN 索引
}

全文搜索

// PostgreSQL 全文搜索与 Prisma
const posts = await prisma.$queryRaw\`
  SELECT id, title, content,
    ts_rank(search_vector, query) AS rank
  FROM posts,
    to_tsquery('english', \${searchTerm}) query
  WHERE search_vector @@ query
  ORDER BY rank DESC
  LIMIT 20
\`;

// 或使用 Prisma 内置功能（MySQL/PostgreSQL）
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { search: searchTerm } },
      { content: { search: searchTerm } },
    ],
  },
});

使用游标高效分页

async function paginatePosts(cursor?: string, take = 20) {
  const posts = await prisma.post.findMany({
    take: take + 1,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
    where: { published: true },
  });
  
  const hasMore = posts.length > take;
  const items = hasMore ? posts.slice(0, -1) : posts;
  
  return {
    items,
    nextCursor: hasMore ? items[items.length - 1].id : undefined,
  };
}

复杂操作的原始查询

// 类型安全的原始查询
const result = await prisma.$queryRaw<Array<{
  id: string;
  name: string;
  orderCount: bigint;
}>>\`
  SELECT u.id, u.name, COUNT(o.id) as "orderCount"
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.created_at > \${new Date('2026-01-01')}
  GROUP BY u.id, u.name
  HAVING COUNT(o.id) > 5
  ORDER BY "orderCount" DESC
\`;

// 注意：COUNT 返回 BigInt — 如有需要可转换
const users = result.map(r => ({
  ...r,
  orderCount: Number(r.orderCount),
}));

连接池配置

// 带连接池设置的 DATABASE_URL
const url = 'postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20';

// 或通过 PrismaClient
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'info', 'warn', 'error']
    : ['error'],
});

使用 Include 避免 N+1

// 糟糕：N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } }); // N 次查询！
}

// 良好：单次查询使用 include
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    _count: { select: { posts: true } },
  },
});

// 复杂选择使用 select
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    posts: {
      select: { id: true, title: true, publishedAt: true },
      where: { published: true },
    },
  },
});

迁移最佳实践

# 开发工作流
npx prisma migrate dev --name add_user_role

# 生产环境 — 先审查 SQL
npx prisma migrate diff --from-migrations ./prisma/migrations --to-schema-datamodel ./prisma/schema.prisma

# 在生产环境应用
npx prisma migrate deploy