数据库功能

使用 Prisma ORM 管理 Codofly Template 的数据库

数据库功能

Codofly Template 使用 Prisma ORM 来管理数据库操作，提供类型安全的数据访问。

Prisma Schema 结构

核心数据模型

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)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // 关联关系
  chats     Chat[]
  teams     TeamMember[]
  messages  Message[]

  @@map("users")
}

model Team {
  id          String   @id @default(cuid())
  name        String
  description String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // 关联关系
  members TeamMember[]
  chats   Chat[]

  @@map("teams")
}

model Chat {
  id        String   @id @default(cuid())
  title     String
  userId    String
  teamId    String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // 关联关系
  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  team     Team?     @relation(fields: [teamId], references: [id])
  messages Message[]

  @@map("chats")
}

model Message {
  id        String      @id @default(cuid())
  content   String
  role      MessageRole
  chatId    String
  userId    String
  createdAt DateTime    @default(now())

  // 关联关系
  chat Chat @relation(fields: [chatId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id])

  @@map("messages")
}

enum Role {
  USER
  ADMIN
}

enum MessageRole {
  USER
  ASSISTANT
}

基础数据操作

用户操作

lib/db/users.ts

import { prisma } from '@/lib/prisma'

// 创建用户
export async function createUser(data: {
  email: string
  name: string
}) {
  return await prisma.user.create({
    data
  })
}

// 查询用户
export async function getUser(id: string) {
  return await prisma.user.findUnique({
    where: { id },
    include: {
      chats: true,
      teams: true
    }
  })
}

// 更新用户
export async function updateUser(id: string, data: any) {
  return await prisma.user.update({
    where: { id },
    data
  })
}

// 删除用户
export async function deleteUser(id: string) {
  return await prisma.user.delete({
    where: { id }
  })
}

聊天操作

lib/db/chats.ts

// 创建聊天
export async function createChat(data: {
  title: string
  userId: string
  teamId?: string
}) {
  return await prisma.chat.create({
    data,
    include: {
      messages: true
    }
  })
}

// 获取用户聊天列表
export async function getUserChats(userId: string) {
  return await prisma.chat.findMany({
    where: { userId },
    include: {
      messages: {
        orderBy: { createdAt: 'asc' }
      }
    },
    orderBy: { updatedAt: 'desc' }
  })
}

// 添加消息
export async function addMessage(data: {
  content: string
  role: 'USER' | 'ASSISTANT'
  chatId: string
  userId: string
}) {
  return await prisma.message.create({
    data
  })
}

关系查询

复杂查询示例

lib/db/advanced-queries.ts

// 获取团队成员及其聊天
export async function getTeamWithChats(teamId: string) {
  return await prisma.team.findUnique({
    where: { id: teamId },
    include: {
      members: {
        include: {
          user: true
        }
      },
      chats: {
        include: {
          messages: {
            take: 10,
            orderBy: { createdAt: 'desc' }
          }
        }
      }
    }
  })
}

// 搜索聊天记录
export async function searchMessages(userId: string, query: string) {
  return await prisma.message.findMany({
    where: {
      AND: [
        { userId },
        {
          content: {
            contains: query,
            mode: 'insensitive'
          }
        }
      ]
    },
    include: {
      chat: true
    },
    orderBy: { createdAt: 'desc' }
  })
}

事务操作

lib/db/transactions.ts

// 创建聊天并添加首条消息
export async function createChatWithMessage(data: {
  title: string
  userId: string
  message: string
}) {
  return await prisma.$transaction(async (tx) => {
    const chat = await tx.chat.create({
      data: {
        title: data.title,
        userId: data.userId
      }
    })

    await tx.message.create({
      data: {
        content: data.message,
        role: 'USER',
        chatId: chat.id,
        userId: data.userId
      }
    })

    return chat
  })
}

数据迁移

创建迁移

# 创建新迁移
npx prisma migrate dev --name add_team_feature

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

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

迁移文件示例

migrations/001_initial/migration.sql

-- CreateEnum
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN');

-- CreateTable
CREATE TABLE "users" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "role" "Role" NOT NULL DEFAULT 'USER',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

数据种子

种子文件

prisma/seed.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // 创建测试用户
  const user = await prisma.user.upsert({
    where: { email: '[e-mail]' },
    update: {},
    create: {
      email: '[e-mail]',
      name: 'Admin User',
      role: 'ADMIN'
    }
  })

  // 创建测试聊天
  await prisma.chat.create({
    data: {
      title: '第一个聊天',
      userId: user.id,
      messages: {
        create: [
          {
            content: '你好！',
            role: 'USER',
            userId: user.id
          },
          {
            content: '你好！我是 AI 助手。',
            role: 'ASSISTANT',
            userId: user.id
          }
        ]
      }
    }
  })

  console.log('数据种子创建完成')
}

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

运行种子

# 运行种子文件
npx prisma db seed

# 或者在 package.json 中配置
{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

最佳实践

1. 连接管理

lib/prisma.ts

import { PrismaClient } from '@prisma/client'

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient()

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

2. 错误处理

try {
  const user = await prisma.user.create({ data })
} catch (error) {
  if (error.code === 'P2002') {
    // 唯一约束违反
    throw new Error('邮箱已存在')
  }
  throw error
}

3. 性能优化

// 使用选择字段
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true
  }
})

// 分页查询
const users = await prisma.user.findMany({
  skip: (page - 1) * limit,
  take: limit
})

查看 Prisma 官方文档 了解更多高级功能。