数据库集成：Prisma

// prisma/schema.prisma
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
  avatar    String?
  role      Role     @default(USER)
  posts     Post[]
  comments  Comment[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

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

  @@index([authorId])
  @@index([published, createdAt])
  @@map("posts")
}

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])
  @@map("comments")
}

enum Role {
  USER
  ADMIN
}

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

// CRUD操作
// 创建
const user = await prisma.user.create({
  data: {
    email: 'zhang@test.com',
    name: '张三',
    posts: {
      create: {
        title: '第一篇文章',
        slug: 'first-post',
        content: '这是内容...',
        published: true,
      },
    },
  },
  include: { posts: true },
});

// 查询
const users = await prisma.user.findMany({
  where: { role: 'USER' },
  select: { id: true, name: true, email: true, _count: { select: { posts: true } } },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

// 分页查询
const paginatedPosts = await prisma.post.findMany({
  where: { published: true },
  include: { author: { select: { name: true, avatar: true } } },
  orderBy: { createdAt: 'desc' },
  skip: (page - 1) * 10,
  take: 10,
});

const total = await prisma.post.count({ where: { published: true } });

// 更新
await prisma.user.update({
  where: { id: userId },
  data: { name: '新名字', avatar: '/new-avatar.png' },
});

// 删除
await prisma.post.delete({ where: { id: postId } });

// 事务：转账操作
await prisma.$transaction(async (tx) => {
  const sender = await tx.user.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  if (sender.balance < 0) throw new Error('余额不足');

  await tx.user.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });

  await tx.transactionLog.create({
    data: { from: senderId, to: receiverId, amount },
  });
});

// 原始SQL查询（需要时）
const result = await prisma.$queryRaw\`
  SELECT date_trunc('day', "createdAt") as date, count(*) as count
  FROM "posts"
  WHERE "published" = true
  GROUP BY date
  ORDER BY date DESC
  LIMIT 30
\`;

// 软删除扩展
const prisma = new PrismaClient().$extends({
  name: 'softDelete',
  query: {
    $allModels: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
      async delete({ args, query, model }) {
        return (prisma as any)[model].update({
          where: args.where,
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

// 自动时间戳扩展
const prisma = new PrismaClient().$extends({
  name: 'timestamps',
  query: {
    $allModels: {
      async create({ args, query }) {
        args.data = { ...args.data, createdAt: new Date(), updatedAt: new Date() };
        return query(args);
      },
      async update({ args, query }) {
        args.data = { ...args.data, updatedAt: new Date() };
        return query(args);
      },
    },
  },
});