prisma-orm
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChinesePrisma ORM - Type-Safe Database Toolkit
Prisma ORM - 类型安全的数据库工具包
Modern database toolkit for TypeScript with schema-first development, auto-generated type-safe client, and powerful migration system.
适用于TypeScript的现代化数据库工具包,采用Schema优先的开发模式,支持自动生成类型安全的客户端,以及强大的迁移系统。
Quick Reference
快速参考
Installation
安装
bash
npm install prisma @prisma/client
npx prisma initbash
npm install prisma @prisma/client
npx prisma initBasic Workflow
基础工作流
bash
undefinedbash
undefined1. Define schema
1. 定义Schema
Edit prisma/schema.prisma
编辑 prisma/schema.prisma
2. Create migration
2. 创建迁移
npx prisma migrate dev --name init
npx prisma migrate dev --name init
3. Generate client
3. 生成客户端
npx prisma generate
npx prisma generate
4. Open Studio
4. 打开Studio
npx prisma studio
undefinednpx prisma studio
undefinedCore Schema Pattern
核心Schema示例
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?
posts Post[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Post {
id String @id @default(cuid())
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([authorId])
}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?
posts Post[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Post {
id String @id @default(cuid())
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([authorId])
}Type-Safe CRUD
类型安全的CRUD操作
typescript
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// Create
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice',
posts: {
create: { title: 'First Post', content: 'Hello World' }
}
},
include: { posts: true }
});
// Read with filters
const users = await prisma.user.findMany({
where: { email: { contains: '@example.com' } },
include: { posts: { where: { published: true } } },
orderBy: { createdAt: 'desc' },
take: 10
});
// Update
await prisma.user.update({
where: { id: userId },
data: { name: 'Bob' }
});
// Delete
await prisma.user.delete({ where: { id: userId } });typescript
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// 创建
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice',
posts: {
create: { title: 'First Post', content: 'Hello World' }
}
},
include: { posts: true }
});
// 带过滤条件的查询
const users = await prisma.user.findMany({
where: { email: { contains: '@example.com' } },
include: { posts: { where: { published: true } } },
orderBy: { createdAt: 'desc' },
take: 10
});
// 更新
await prisma.user.update({
where: { id: userId },
data: { name: 'Bob' }
});
// 删除
await prisma.user.delete({ where: { id: userId } });Schema Design Patterns
Schema设计模式
Field Types and Attributes
字段类型与属性
prisma
model Product {
id Int @id @default(autoincrement())
sku String @unique
name String
description String? // Optional field
price Decimal @db.Decimal(10, 2)
inStock Boolean @default(true)
quantity Int @default(0)
tags String[] // Array field (PostgreSQL)
metadata Json? // JSON field
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([sku])
@@index([name, inStock])
}prisma
model Product {
id Int @id @default(autoincrement())
sku String @unique
name String
description String? // 可选字段
price Decimal @db.Decimal(10, 2)
inStock Boolean @default(true)
quantity Int @default(0)
tags String[] // 数组字段(PostgreSQL)
metadata Json? // JSON字段
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([sku])
@@index([name, inStock])
}Relations
关系管理
One-to-Many:
prisma
model User {
id String @id @default(cuid())
posts Post[]
}
model Post {
id String @id @default(cuid())
author User @relation(fields: [authorId], references: [id])
authorId String
@@index([authorId])
}Many-to-Many:
prisma
model Post {
id String @id @default(cuid())
categories Category[] @relation("PostCategories")
}
model Category {
id String @id @default(cuid())
name String @unique
posts Post[] @relation("PostCategories")
}One-to-One:
prisma
model User {
id String @id @default(cuid())
profile Profile?
}
model Profile {
id String @id @default(cuid())
bio String
user User @relation(fields: [userId], references: [id])
userId String @unique
}Self-Relations:
prisma
model User {
id String @id @default(cuid())
following User[] @relation("UserFollows")
followers User[] @relation("UserFollows")
}一对多:
prisma
model User {
id String @id @default(cuid())
posts Post[]
}
model Post {
id String @id @default(cuid())
author User @relation(fields: [authorId], references: [id])
authorId String
@@index([authorId])
}多对多:
prisma
model Post {
id String @id @default(cuid())
categories Category[] @relation("PostCategories")
}
model Category {
id String @id @default(cuid())
name String @unique
posts Post[] @relation("PostCategories")
}一对一:
prisma
model User {
id String @id @default(cuid())
profile Profile?
}
model Profile {
id String @id @default(cuid())
bio String
user User @relation(fields: [userId], references: [id])
userId String @unique
}自关联:
prisma
model User {
id String @id @default(cuid())
following User[] @relation("UserFollows")
followers User[] @relation("UserFollows")
}Client Operations
客户端操作
Nested Writes
嵌套写入
typescript
// Create with nested relations
const user = await prisma.user.create({
data: {
email: 'bob@example.com',
profile: {
create: { bio: 'Software Engineer' }
},
posts: {
create: [
{ title: 'Post 1', content: 'Content 1' },
{ title: 'Post 2', content: 'Content 2' }
]
}
}
});
// Update with nested operations
await prisma.user.update({
where: { id: userId },
data: {
posts: {
create: { title: 'New Post' },
update: {
where: { id: postId },
data: { published: true }
},
delete: { id: oldPostId }
}
}
});typescript
// 带嵌套关系的创建
const user = await prisma.user.create({
data: {
email: 'bob@example.com',
profile: {
create: { bio: 'Software Engineer' }
},
posts: {
create: [
{ title: 'Post 1', content: 'Content 1' },
{ title: 'Post 2', content: 'Content 2' }
]
}
}
});
// 带嵌套操作的更新
await prisma.user.update({
where: { id: userId },
data: {
posts: {
create: { title: 'New Post' },
update: {
where: { id: postId },
data: { published: true }
},
delete: { id: oldPostId }
}
}
});Transactions
事务
Sequential (Interactive):
typescript
await prisma.$transaction(async (tx) => {
const user = await tx.user.create({
data: { email: 'alice@example.com' }
});
await tx.post.create({
data: { title: 'Post', authorId: user.id }
});
// Rollback if error thrown
if (someCondition) {
throw new Error('Rollback transaction');
}
});Batch (Parallel):
typescript
const [deletedPosts, updatedUser] = await prisma.$transaction([
prisma.post.deleteMany({ where: { published: false } }),
prisma.user.update({
where: { id: userId },
data: { name: 'Updated' }
})
]);交互式事务:
typescript
await prisma.$transaction(async (tx) => {
const user = await tx.user.create({
data: { email: 'alice@example.com' }
});
await tx.post.create({
data: { title: 'Post', authorId: user.id }
});
// 抛出错误将回滚事务
if (someCondition) {
throw new Error('Rollback transaction');
}
});批量事务:
typescript
const [deletedPosts, updatedUser] = await prisma.$transaction([
prisma.post.deleteMany({ where: { published: false } }),
prisma.user.update({
where: { id: userId },
data: { name: 'Updated' }
})
]);Advanced Queries
高级查询
Aggregations:
typescript
const result = await prisma.post.aggregate({
_count: { id: true },
_avg: { views: true },
_sum: { likes: true },
_max: { createdAt: true },
where: { published: true }
});
const grouped = await prisma.post.groupBy({
by: ['authorId'],
_count: { id: true },
_avg: { views: true },
having: { views: { _avg: { gt: 100 } } }
});Raw SQL:
typescript
// Raw query
const users = await prisma.$queryRaw<User[]>`
SELECT * FROM "User" WHERE email LIKE ${`%${search}%`}
`;
// Execute
await prisma.$executeRaw`
UPDATE "Post" SET views = views + 1 WHERE id = ${postId}
`;聚合查询:
typescript
const result = await prisma.post.aggregate({
_count: { id: true },
_avg: { views: true },
_sum: { likes: true },
_max: { createdAt: true },
where: { published: true }
});
const grouped = await prisma.post.groupBy({
by: ['authorId'],
_count: { id: true },
_avg: { views: true },
having: { views: { _avg: { gt: 100 } } }
});原生SQL:
typescript
// 原生查询
const users = await prisma.$queryRaw<User[]>`
SELECT * FROM "User" WHERE email LIKE ${`%${search}%`}
`;
// 执行原生SQL
await prisma.$executeRaw`
UPDATE "Post" SET views = views + 1 WHERE id = ${postId}
`;Migrations
迁移
Development Workflow
开发工作流
bash
undefinedbash
undefinedCreate and apply migration
创建并应用迁移
npx prisma migrate dev --name add_user_role
npx prisma migrate dev --name add_user_role
Reset database (WARNING: deletes all data)
重置数据库(警告:会删除所有数据)
npx prisma migrate reset
npx prisma migrate reset
View migration status
查看迁移状态
npx prisma migrate status
undefinednpx prisma migrate status
undefinedProduction Deployment
生产部署
bash
undefinedbash
undefinedApply pending migrations
应用待处理的迁移
npx prisma migrate deploy
npx prisma migrate deploy
Generate client (in CI/CD)
生成客户端(在CI/CD中执行)
npx prisma generate
undefinednpx prisma generate
undefinedSchema Prototyping
Schema原型设计
bash
undefinedbash
undefinedPush schema without migrations (dev only)
推送Schema而不生成迁移(仅开发环境)
npx prisma db push
npx prisma db push
Pull schema from existing database
从现有数据库拉取Schema
npx prisma db pull
undefinednpx prisma db pull
undefinedIntegration Patterns
集成模式
Next.js App Router
Next.js App Router
typescript
// 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;
}Server Component:
typescript
// app/users/page.tsx
import { prisma } from '@/lib/prisma';
export default async function UsersPage() {
const users = await prisma.user.findMany({
include: { posts: { take: 5 } }
});
return (
<ul>
{users.map(u => (
<li key={u.id}>{u.name} - {u.posts.length} posts</li>
))}
</ul>
);
}Server Action:
typescript
// app/actions.ts
'use server';
import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';
export async function createPost(formData: FormData) {
const title = formData.get('title') as string;
const authorId = formData.get('authorId') as string;
await prisma.post.create({
data: { title, authorId }
});
revalidatePath('/posts');
}typescript
// 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;
}服务端组件:
typescript
// app/users/page.tsx
import { prisma } from '@/lib/prisma';
export default async function UsersPage() {
const users = await prisma.user.findMany({
include: { posts: { take: 5 } }
});
return (
<ul>
{users.map(u => (
<li key={u.id}>{u.name} - {u.posts.length} posts</li>
))}
</ul>
);
}服务端操作:
typescript
// app/actions.ts
'use server';
import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';
export async function createPost(formData: FormData) {
const title = formData.get('title') as string;
const authorId = formData.get('authorId') as string;
await prisma.post.create({
data: { title, authorId }
});
revalidatePath('/posts');
}Node.js Middleware
Node.js中间件
typescript
import { PrismaClient } from '@prisma/client';
import express from 'express';
const app = express();
const prisma = new PrismaClient();
app.get('/users/:id', async (req, res) => {
const user = await prisma.user.findUnique({
where: { id: req.params.id },
include: { posts: true }
});
if (!user) return res.status(404).json({ error: 'Not found' });
res.json(user);
});
app.listen(3000);typescript
import { PrismaClient } from '@prisma/client';
import express from 'express';
const app = express();
const prisma = new PrismaClient();
app.get('/users/:id', async (req, res) => {
const user = await prisma.user.findUnique({
where: { id: req.params.id },
include: { posts: true }
});
if (!user) return res.status(404).json({ error: 'Not found' });
res.json(user);
});
app.listen(3000);Performance Optimization
性能优化
Query Optimization
查询优化
typescript
// ❌ N+1 queries
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
});
}
// ✅ Single query with include
const users = await prisma.user.findMany({
include: { posts: true }
});
// ✅ Select specific fields
const users = await prisma.user.findMany({
select: { id: true, email: true, posts: { select: { title: true } } }
});typescript
// ❌ N+1查询问题
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
});
}
// ✅ 使用include的单查询
const users = await prisma.user.findMany({
include: { posts: true }
});
// ✅ 选择特定字段
const users = await prisma.user.findMany({
select: { id: true, email: true, posts: { select: { title: true } } }
});Pagination
分页
typescript
// Cursor-based (recommended for large datasets)
const posts = await prisma.post.findMany({
take: 10,
cursor: lastPostId ? { id: lastPostId } : undefined,
skip: lastPostId ? 1 : 0,
orderBy: { createdAt: 'desc' }
});
// Offset-based (simple but slower)
const posts = await prisma.post.findMany({
skip: (page - 1) * pageSize,
take: pageSize
});typescript
// 基于游标(推荐用于大数据集)
const posts = await prisma.post.findMany({
take: 10,
cursor: lastPostId ? { id: lastPostId } : undefined,
skip: lastPostId ? 1 : 0,
orderBy: { createdAt: 'desc' }
});
// 基于偏移(简单但速度较慢)
const posts = await prisma.post.findMany({
skip: (page - 1) * pageSize,
take: pageSize
});Connection Pooling
连接池
prisma
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// Connection pool settings
directUrl = env("DIRECT_URL")
// Serverless connection limit
relationMode = "prisma" // For PlanetScale, Neon
}bash
undefinedprisma
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// 连接池设置
directUrl = env("DIRECT_URL")
// 无服务器环境连接限制
relationMode = "prisma" // 适用于PlanetScale、Neon
}bash
undefined.env
.env
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20"
undefinedDATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20"
undefinedPrisma Studio
Prisma Studio
bash
undefinedbash
undefinedLaunch visual database browser
启动可视化数据库浏览器
npx prisma studio
**Features:**
- Visual data browser and editor
- Create, read, update, delete records
- Filter and search data
- View relations visually
- Runs on `localhost:5555`npx prisma studio
**功能特性:**
- 可视化数据浏览与编辑器
- 创建、读取、更新、删除记录
- 过滤与搜索数据
- 可视化查看关系
- 运行在 `localhost:5555`Prisma vs Drizzle
Prisma vs Drizzle
| Feature | Prisma | Drizzle |
|---|---|---|
| Schema Definition | Custom DSL | TypeScript code |
| Type Safety | Generated types | Inferred types |
| Migrations | Built-in (migrate) | drizzle-kit |
| Query Builder | Fluent API | SQL-like builders |
| Relations | Automatic | Manual joins |
| Studio | Built-in GUI | No GUI |
| Bundle Size | ~300kB | ~50kB |
| Raw SQL | Supported | First-class |
| Edge Runtime | Limited | Full support |
| Learning Curve | Moderate | Steeper |
| Best For | Full-stack apps, rapid development, teams | Edge functions, SQL experts, bundle-sensitive |
Choose Prisma when:
- Team prefers schema-first development
- Need visual database tools (Studio)
- Want automatic relation handling
- Building full-stack monoliths
- Rapid prototyping and migrations
Choose Drizzle when:
- Need minimal bundle size (edge functions)
- Prefer SQL-like syntax
- Edge runtime deployment (Cloudflare Workers)
- Want full control over SQL generation
- Team has strong SQL expertise
| 特性 | Prisma | Drizzle |
|---|---|---|
| Schema定义 | 自定义DSL | TypeScript代码 |
| 类型安全 | 生成类型 | 推断类型 |
| 迁移 | 内置(migrate) | drizzle-kit |
| 查询构建器 | 流畅API类SQL构建器 | SQL风格构建器 |
| 关系管理 | 自动处理 | 手动关联 |
| Studio | 内置GUI | 无GUI |
| 包体积 | ~300kB | ~50kB |
| 原生SQL | 支持 | 一等公民 |
| 边缘运行时 | 有限支持 | 完全支持 |
| 学习曲线 | 中等 | 较陡 |
| 最佳适用场景 | 全栈应用、快速开发、团队协作 | 边缘函数、SQL专家、对包体积敏感的场景 |
选择Prisma的场景:
- 团队偏好Schema优先的开发模式
- 需要可视化数据库工具(Studio)
- 希望自动处理关系管理
- 构建全栈单体应用
- 快速原型开发与迁移
选择Drizzle的场景:
- 需要极小的包体积(边缘函数)
- 偏好SQL风格语法
- 部署到边缘运行时(Cloudflare Workers)
- 希望完全控制SQL生成
- 团队具备较强的SQL专业能力
Best Practices
最佳实践
- Singleton Pattern - Reuse instance (especially in dev)
PrismaClient - Connection Management - Configure pool size for serverless
- Select Specific Fields - Use to reduce payload size
select - Use Transactions - For multi-step operations requiring atomicity
- Index Strategically - Add on frequently queried fields
@@index - Migration Discipline - Never edit migrations after deployment
- Schema Versioning - Use descriptive migration names
- Soft Deletes - Add field instead of hard deletes
deletedAt - Validate Before Saving - Use Zod schemas before Prisma operations
- Monitor Queries - Use for logging
prisma.$on('query')
- 单例模式 - 复用实例(尤其在开发环境)
PrismaClient - 连接管理 - 为无服务器环境配置连接池大小
- 选择特定字段 - 使用减少返回数据量
select - 使用事务 - 对需要原子性的多步骤操作使用事务
- 合理添加索引 - 在频繁查询的字段上添加
@@index - 迁移规范 - 部署后切勿编辑已有的迁移文件
- Schema版本控制 - 使用描述性的迁移名称
- 软删除 - 添加字段而非硬删除
deletedAt - 保存前验证 - 在Prisma操作前使用Zod进行验证
- 监控查询 - 使用进行日志记录
prisma.$on('query')
Common Pitfalls
常见陷阱
❌ Creating multiple PrismaClient instances:
typescript
// WRONG - creates connection leak
function getUser() {
const prisma = new PrismaClient(); // New instance every call
return prisma.user.findMany();
}
// CORRECT - singleton pattern
const prisma = new PrismaClient();
function getUser() {
return prisma.user.findMany();
}❌ N+1 queries:
typescript
// WRONG - multiple queries
const users = await prisma.user.findMany();
for (const user of users) {
user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
// CORRECT - single query with include
const users = await prisma.user.findMany({ include: { posts: true } });❌ Missing transaction for multi-step operations:
typescript
// WRONG - not atomic, can leave inconsistent state
await prisma.user.delete({ where: { id: userId } });
await prisma.post.deleteMany({ where: { authorId: userId } }); // May fail
// CORRECT - atomic transaction
await prisma.$transaction([
prisma.post.deleteMany({ where: { authorId: userId } }),
prisma.user.delete({ where: { id: userId } })
]);❌ 创建多个PrismaClient实例:
typescript
// 错误 - 会导致连接泄漏
function getUser() {
const prisma = new PrismaClient(); // 每次调用创建新实例
return prisma.user.findMany();
}
// 正确 - 单例模式
const prisma = new PrismaClient();
function getUser() {
return prisma.user.findMany();
}❌ N+1查询问题:
typescript
// 错误 - 多次查询
const users = await prisma.user.findMany();
for (const user of users) {
user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
// 正确 - 使用include的单查询
const users = await prisma.user.findMany({ include: { posts: true } });❌ 多步骤操作未使用事务:
typescript
// 错误 - 不具备原子性,可能导致数据不一致
await prisma.user.delete({ where: { id: userId } });
await prisma.post.deleteMany({ where: { authorId: userId } }); // 可能执行失败
// 正确 - 原子事务
await prisma.$transaction([
prisma.post.deleteMany({ where: { authorId: userId } }),
prisma.user.delete({ where: { id: userId } })
]);Red Flags
注意事项
Stop and reconsider if:
- Creating new in request handlers
PrismaClient - Not using transactions for multi-step operations
- Missing indexes on foreign keys or frequently queried fields
- Using without pagination on large tables
findMany - Fetching entire objects when only specific fields needed
- Not handling connection errors in production
- Using in production (use
migrate dev)migrate deploy
出现以下情况请立即检查:
- 在请求处理器中创建新的实例
PrismaClient - 多步骤操作未使用事务
- 外键或频繁查询的字段缺少索引
- 对大表使用而不进行分页
findMany - 仅需要特定字段时却获取整个对象
- 生产环境未处理连接错误
- 在生产环境使用(应使用
migrate dev)migrate deploy
Integration with Other Skills
与其他技能的集成
- typescript-core: Zod validation, type safety patterns
- nextjs-core: Server Actions, Server Components integration
- nextjs-v16: App Router data fetching, caching
- database-migration: Safe schema evolution patterns
- typescript-core: Zod验证、类型安全模式
- nextjs-core: 服务端操作、服务端组件集成
- nextjs-v16: App Router数据获取、缓存
- database-migration: 安全的Schema演进模式
Resources
资源
- Documentation: https://www.prisma.io/docs
- Schema Reference: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference
- Client API: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference
- Examples: https://github.com/prisma/prisma-examples
Related Skills
相关技能
When using Prisma, these skills enhance your workflow:
- drizzle: Drizzle ORM as lightweight alternative to Prisma
- typescript: TypeScript best practices for Prisma generated types
- nextjs: Prisma with Next.js: connection pooling, edge runtime considerations
- test-driven-development: Testing Prisma models, migrations, and queries
[Full documentation available in these skills if deployed in your bundle]
使用Prisma时,以下技能可提升你的工作流:
- drizzle: 作为Prisma轻量替代方案的Drizzle ORM
- typescript: 针对Prisma生成类型的TypeScript最佳实践
- nextjs: Prisma与Next.js的集成:连接池、边缘运行时注意事项
- test-driven-development: 测试Prisma模型、迁移与查询
[若你的技能包中包含这些技能,可查看完整文档]