Prisma Schema 与关联关系指南

Prisma 是最流行的 TypeScript ORM，它的 schema 优先方法使数据库建模变得直观且类型安全。无论你是在构建新项目还是从原始 SQL 迁移，理解 Prisma schema 语法、关系和查询都至关重要。本综合指南涵盖了从基本模型定义到软删除和多租户等高级模式的所有内容。

使用我们的免费工具即时将 SQL 转换为 Prisma Schema →

什么是 Prisma？

Prisma 是面向 Node.js 和 TypeScript 的下一代 ORM。与传统 ORM 将类映射到表不同，Prisma 使用 schema 优先方法，你在 .prisma 文件中定义数据模型，Prisma 从中生成完全类型安全的客户端。

Prisma 消除了应用程序代码和数据库之间的阻抗不匹配。你可以获得完整的自动补全、编译时错误检查，不再需要为基本的 CRUD 操作编写原始 SQL。

// Install Prisma
npm install prisma --save-dev
npm install @prisma/client

// Initialize a new Prisma project
npx prisma init

// This creates:
// prisma/schema.prisma  — your data model
// .env                  — database connection URL

Schema 基础

每个 Prisma 项目都从 schema.prisma 文件开始。它包含三个主要块：datasource（数据源）、generator（生成器）和 model（模型）定义。

// prisma/schema.prisma

// 1. Datasource — where your data lives
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// 2. Generator — what client to generate
generator client {
  provider = "prisma-client-js"
}

// 3. Model — your database tables
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

datasource 块配置数据库连接。generator 块告诉 Prisma 生成什么客户端。model 定义数据库表。

字段类型

Prisma 支持丰富的标量类型，映射到原生数据库列类型。理解这些类型对于正确设计 schema 至关重要。

Prisma 还支持 枚举（enum），特别适用于状态字段、角色和其他固定值列：

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

model Order {
  id        Int         @id @default(autoincrement())
  status    OrderStatus @default(PENDING)
  total     Decimal
  items     Json        // flexible JSON column
  metadata  Bytes?      // binary data
  createdAt DateTime    @default(now())
}

// Using cuid() or uuid() for string IDs
model Product {
  id    String @id @default(cuid())
  // or: id String @id @default(uuid())
  name  String
  price Float
}

字段修饰符与属性

字段修饰符和属性控制字段在数据库层面的行为。它们是良好 schema 设计的基础。

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?                        // optional field
  bio       String?  @db.Text              // native type mapping
  tags      String[]                       // array field (PostgreSQL)
  role      Role     @default(USER)
  score     Float    @default(0)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt      @map("updated_at")

  @@map("users")                           // table name in DB
  @@index([email])
}

关系

关系是任何关系型数据库的核心，Prisma 使它们变得声明式且类型安全。有三种基本关系类型。

一对一

一个模型中的每条记录对应另一个模型中的恰好一条记录。外键端必须在关系标量字段上包含 @unique。

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique  // @unique makes it one-to-one
}

// Query with one-to-one relation
const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true },
});
// userWithProfile.profile?.bio

一对多

最常见的关系类型。一条记录可以与另一个模型中的多条记录关联。"多"的一方持有外键。

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]                        // one user has many posts
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  content  String?
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int                        // foreign key
}

model Comment {
  id      Int    @id @default(autoincrement())
  text    String
  post    Post   @relation(fields: [postId], references: [id])
  postId  Int
  author  User   @relation(fields: [authorId], references: [id])
  authorId Int
}

多对多

Prisma 支持隐式和显式多对多关系。隐式关系让 Prisma 管理连接表；显式关系让你完全控制。

隐式多对多（Prisma 管理连接表）：

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]  // implicit many-to-many
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]                        // implicit many-to-many
}

// Prisma auto-creates a _CategoryToPost join table

显式多对多（你定义连接表）：

model Post {
  id   Int       @id @default(autoincrement())
  title String
  tags  PostTag[]
}

model Tag {
  id    Int       @id @default(autoincrement())
  name  String    @unique
  posts PostTag[]
}

// Explicit join table — you control extra fields
model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  assignedAt DateTime @default(now())
  assignedBy String?

  @@id([postId, tagId])               // composite primary key
}

自关系

自关系发生在模型引用自身时。它们在树结构、社交图和组织层级中很常见。

树结构（父子关系）

一个分类可以有多个子分类，每个子分类属于一个父分类：

model Category {
  id       Int        @id @default(autoincrement())
  name     String
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  parentId Int?
  children Category[] @relation("CategoryTree")
}

// Query: Get category with all children
const category = await prisma.category.findUnique({
  where: { id: 1 },
  include: {
    children: {
      include: { children: true },  // nested children
    },
  },
});

关注者/关注中

多对多自关系，用户可以关注其他用户：

model User {
  id         Int    @id @default(autoincrement())
  name       String
  followers  User[] @relation("UserFollows")
  following  User[] @relation("UserFollows")
}

// Follow a user
await prisma.user.update({
  where: { id: 1 },
  data: {
    following: { connect: { id: 2 } },
  },
});

// Get user with followers and following
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    followers: true,
    following: true,
  },
});

索引

索引可以显著提升查询性能。Prisma 通过模型级属性提供多种索引类型。

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  tenantId String
  name     String

  @@unique([tenantId, email])           // composite unique
  @@index([tenantId])                   // index for tenant queries
  @@index([name])                       // index for name searches
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  authorId  Int
  published Boolean  @default(false)
  createdAt DateTime @default(now())

  @@index([authorId])                   // index for author lookups
  @@index([published, createdAt])       // composite for published feed
  @@fulltext([title, content])          // full-text search (MySQL)
}

始终为出现在 WHERE 子句、JOIN 条件和 ORDER BY 中的列添加索引。监控慢查询并相应添加索引。

迁移

Prisma Migrate 从 schema 变更生成 SQL 迁移文件。这为你提供了每次数据库变更的版本化历史。

迁移工作流

关键命令

# Create and apply a migration (development)
npx prisma migrate dev --name add_user_profile

# Apply pending migrations (production)
npx prisma migrate deploy

# Reset database and re-apply all migrations
npx prisma migrate reset

# Push schema without creating migration files (prototyping)
npx prisma db push

# Pull existing database schema into Prisma
npx prisma db pull

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio (GUI)
npx prisma studio

# Seed the database
npx prisma db seed

prisma db push 非常适合原型开发——它同步 schema 而不创建迁移文件。在需要迁移历史的生产工作流中使用 prisma migrate dev。

// Example migration file: prisma/migrations/20240101_add_user_profile/migration.sql
-- CreateTable
CREATE TABLE "Profile" (
    "id" SERIAL NOT NULL,
    "bio" TEXT NOT NULL,
    "avatar" TEXT,
    "userId" INTEGER NOT NULL,

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

-- CreateIndex
CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");

-- AddForeignKey
ALTER TABLE "Profile" ADD CONSTRAINT "Profile_userId_fkey"
    FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Prisma Client 查询

Prisma Client 为所有数据库操作提供直观、类型安全的 API。每个查询都在编译时验证，因此字段名拼写错误或类型不正确会在代码运行前被捕获。

读取数据

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

// Find one by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});

// Find one or throw
const user = await prisma.user.findUniqueOrThrow({
  where: { id: 1 },
});

// Find first matching
const admin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
});

// Find many with pagination
const users = await prisma.user.findMany({
  where: { role: 'USER' },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 20,
  select: {                           // select specific fields
    id: true,
    email: true,
    name: true,
  },
});

// Count records
const userCount = await prisma.user.count({
  where: { role: 'USER' },
});

// Aggregate
const stats = await prisma.order.aggregate({
  _avg: { total: true },
  _sum: { total: true },
  _count: true,
});

创建数据

// Create a single record
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    role: 'USER',
  },
});

// Create with nested relation
const userWithPosts = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    name: 'Bob',
    posts: {
      create: [
        { title: 'First Post', content: 'Hello World' },
        { title: 'Second Post', content: 'Prisma is great' },
      ],
    },
  },
  include: { posts: true },
});

// Create many records
const result = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
    { email: 'user3@example.com', name: 'User 3' },
  ],
  skipDuplicates: true,               // skip records with unique conflicts
});

更新数据

// Update a single record
const user = await prisma.user.update({
  where: { email: 'alice@example.com' },
  data: { name: 'Alice Smith' },
});

// Update many
const result = await prisma.user.updateMany({
  where: { role: 'USER' },
  data: { role: 'MEMBER' },
});

// Upsert (create or update)
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: {
    email: 'alice@example.com',
    name: 'Alice New',
  },
});

// Delete
await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
await prisma.post.deleteMany({
  where: { published: false },
});

过滤与关系

Prisma 提供强大的过滤 API，支持逻辑运算符、关系查询和聚合：

// Complex filtering
const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      {
        OR: [
          { title: { contains: 'prisma', mode: 'insensitive' } },
          { content: { contains: 'typescript', mode: 'insensitive' } },
        ],
      },
    ],
    author: {
      role: 'ADMIN',                  // filter by relation
    },
    createdAt: {
      gte: new Date('2024-01-01'),    // greater than or equal
    },
  },
  include: {
    author: {
      select: { name: true, email: true },
    },
    _count: {
      select: { comments: true },     // count related comments
    },
  },
  orderBy: [
    { createdAt: 'desc' },
  ],
  take: 10,
});

// Transaction
const [newUser, updatedPost] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'new@example.com', name: 'New' } }),
  prisma.post.update({ where: { id: 1 }, data: { published: true } }),
]);

高级模式

中间件

Prisma 中间件让你在查询执行前后拦截查询。这非常适合日志记录、软删除和访问控制：

// Logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  console.log(
    `${params.model}.${params.action} took ${after - before}ms`
  );
  return result;
});

软删除

不要永久删除记录，而是用时间戳标记为已删除。使用中间件自动过滤它们：

// Schema
model Post {
  id        Int       @id @default(autoincrement())
  title     String
  deletedAt DateTime? // null = not deleted
  // ...
}

// Soft delete middleware
prisma.$use(async (params, next) => {
  // Intercept delete -> update with deletedAt
  if (params.model === 'Post' && params.action === 'delete') {
    params.action = 'update';
    params.args['data'] = { deletedAt: new Date() };
  }

  // Intercept deleteMany -> updateMany
  if (params.model === 'Post' && params.action === 'deleteMany') {
    params.action = 'updateMany';
    if (params.args.data) {
      params.args.data['deletedAt'] = new Date();
    } else {
      params.args['data'] = { deletedAt: new Date() };
    }
  }

  // Filter out soft-deleted records on find
  if (params.model === 'Post' && params.action === 'findMany') {
    if (!params.args.where) params.args.where = {};
    params.args.where['deletedAt'] = null;
  }

  return next(params);
});

多租户

在每个模型中添加 tenantId 字段，并使用 Prisma 中间件自动将查询范围限定到当前租户：

// Schema
model User {
  id       Int    @id @default(autoincrement())
  email    String
  tenantId String

  @@unique([tenantId, email])
  @@index([tenantId])
}

// Multi-tenancy middleware
function createTenantMiddleware(tenantId: string) {
  return prisma.$use(async (params, next) => {
    // Automatically inject tenantId on create
    if (params.action === 'create') {
      params.args.data.tenantId = tenantId;
    }

    // Automatically scope queries to tenant
    if (['findMany', 'findFirst', 'updateMany', 'deleteMany'].includes(params.action)) {
      if (!params.args.where) params.args.where = {};
      params.args.where.tenantId = tenantId;
    }

    return next(params);
  });
}

原始 SQL

当你需要完全控制或 Prisma Client 无法表达的复杂查询时，使用原始 SQL：

// Tagged template for safe parameterized queries
const email = 'alice@example.com';
const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`;

// Unparameterized raw query (use with caution)
const result = await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE id = $1',
  1
);

// Execute raw SQL (INSERT, UPDATE, DELETE)
const affected = await prisma.$executeRaw`
  UPDATE "User" SET name = ${'Alice Smith'}
  WHERE email = ${email}
`;

// Raw SQL with type safety using Prisma.sql
import { Prisma } from '@prisma/client';

const orderBy = Prisma.sql`ORDER BY "createdAt" DESC`;
const users = await prisma.$queryRaw`
  SELECT id, email, name FROM "User"
  ${orderBy}
  LIMIT 10
`;

Prisma vs TypeORM vs Drizzle

选择合适的 ORM 取决于你的项目需求。以下是三个最流行的 TypeScript ORM 的比较：

常见问题