prisma

Prisma ORM 模式

用途

完整介绍如何高效使用 Prisma ORM，包括查询优化、事务处理，以及用于实现清晰数据访问的仓库模式。

何时使用此技能

• 使用 Prisma Client 查询数据库
• 创建用于数据访问的仓库
• 使用事务
• 优化查询并预防 N+1 问题
• 处理 Prisma 错误

---

Prisma 基本用法

核心查询模式

import { PrismaService } from '@project-lifecycle-portal/database';

// Always use PrismaService.main
if (!PrismaService.isAvailable) {
    throw new Error('Prisma client not initialized');
}

// Find one
const user = await PrismaService.main.user.findUnique({
    where: { id: userId },
});

// Find many with filters
const users = await PrismaService.main.user.findMany({
    where: { isActive: true },
    orderBy: { createdAt: 'desc' },
    take: 10,
});

// Create
const newUser = await PrismaService.main.user.create({
    data: {
        email: '[email protected]',
        name: 'John Doe',
    },
});

// Update
const updated = await PrismaService.main.user.update({
    where: { id: userId },
    data: { name: 'Jane Doe' },
});

// Delete
await PrismaService.main.user.delete({
    where: { id: userId },
});

复杂筛选

// Multiple conditions
const users = await PrismaService.main.user.findMany({
    where: {
        email: { contains: '@example.com' },
        isActive: true,
        createdAt: { gte: new Date('2024-01-01') },
    },
});

// AND/OR conditions
const posts = await PrismaService.main.post.findMany({
    where: {
        AND: [
            { published: true },
            { author: { isActive: true } },
        ],
        OR: [
            { title: { contains: 'prisma' } },
            { content: { contains: 'prisma' } },
        ],
    },
});

---

仓库模式

何时使用仓库

✅ 在以下情况使用仓库：

• 包含联接或关联查询的复杂查询
• 多处使用的查询
• 需要在测试中模拟

❌ 以下情况无需使用仓库：

• 简单的一次性查询
• 原型开发

仓库模板

import { PrismaService } from '@project-lifecycle-portal/database';
import type { User, Prisma } from '@prisma/client';

export class UserRepository {
    async findById(id: string): Promise<User | null> {
        return PrismaService.main.user.findUnique({
            where: { id },
            include: { profile: true },
        });
    }

    async findByEmail(email: string): Promise<User | null> {
        return PrismaService.main.user.findUnique({
            where: { email },
        });
    }

    async findActive(): Promise<User[]> {
        return PrismaService.main.user.findMany({
            where: { isActive: true },
            orderBy: { createdAt: 'desc' },
        });
    }

    async create(data: Prisma.UserCreateInput): Promise<User> {
        return PrismaService.main.user.create({ data });
    }

    async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
        return PrismaService.main.user.update({ where: { id }, data });
    }

    async delete(id: string): Promise<void> {
        await PrismaService.main.user.delete({ where: { id } });
    }
}

在服务中使用

export class UserService {
    private userRepository: UserRepository;

    constructor() {
        this.userRepository = new UserRepository();
    }

    async getById(id: string): Promise<User> {
        const user = await this.userRepository.findById(id);
        if (!user) {
            throw new Error('User not found');
        }
        return user;
    }
}

---

事务模式

简单事务

const result = await PrismaService.main.$transaction(async (tx) => {
    const user = await tx.user.create({
        data: { email: '[email protected]', name: 'John' },
    });

    const profile = await tx.userProfile.create({
        data: { userId: user.id, bio: 'Developer' },
    });

    return { user, profile };
});

交互式事务

const result = await PrismaService.main.$transaction(
    async (tx) => {
        const user = await tx.user.findUnique({ where: { id: userId } });
        if (!user) throw new Error('User not found');

        const updated = await tx.user.update({
            where: { id: userId },
            data: { lastLogin: new Date() },
        });

        await tx.auditLog.create({
            data: { userId, action: 'LOGIN', timestamp: new Date() },
        });

        return updated;
    },
    {
        maxWait: 5000,   // Wait max 5s to start
        timeout: 10000,  // Timeout after 10s
    }
);

---

查询优化

使用 select 限制字段

// ❌ Fetches all fields
const users = await PrismaService.main.user.findMany();

// ✅ Only fetch needed fields
const users = await PrismaService.main.user.findMany({
    select: {
        id: true,
        email: true,
        name: true,
    },
});

// ✅ Select with relations
const users = await PrismaService.main.user.findMany({
    select: {
        id: true,
        email: true,
        profile: {
            select: { firstName: true, lastName: true },
        },
    },
});

谨慎使用 include

// ❌ Excessive includes
const user = await PrismaService.main.user.findUnique({
    where: { id },
    include: {
        posts: { include: { comments: true } },
        workflows: { include: { steps: { include: { actions: true } } } },
    },
});

// ✅ Only include what you need
const user = await PrismaService.main.user.findUnique({
    where: { id },
    include: { profile: true },
});

---

预防 N+1 查询

问题

// ❌ N+1 Query Problem
const users = await PrismaService.main.user.findMany(); // 1 query

for (const user of users) {
    // N additional queries
    const profile = await PrismaService.main.userProfile.findUnique({
        where: { userId: user.id },
    });
}

方案 1：使用 include

// ✅ Single query with include
const users = await PrismaService.main.user.findMany({
    include: { profile: true },
});

for (const user of users) {
    console.log(user.profile.bio);
}

方案 2：批量查询

// ✅ Batch query
const users = await PrismaService.main.user.findMany();
const userIds = users.map(u => u.id);

const profiles = await PrismaService.main.userProfile.findMany({
    where: { userId: { in: userIds } },
});

const profileMap = new Map(profiles.map(p => [p.userId, p]));

---

关系

一对多

// Get user with posts
const user = await PrismaService.main.user.findUnique({
    where: { id: userId },
    include: {
        posts: {
            where: { published: true },
            orderBy: { createdAt: 'desc' },
            take: 10,
        },
    },
});

嵌套写入

// Create user with profile
const user = await PrismaService.main.user.create({
    data: {
        email: '[email protected]',
        name: 'John Doe',
        profile: {
            create: {
                bio: 'Developer',
                avatar: 'avatar.jpg',
            },
        },
    },
    include: { profile: true },
});

// Update with nested updates
const user = await PrismaService.main.user.update({
    where: { id: userId },
    data: {
        name: 'Jane Doe',
        profile: {
            update: { bio: 'Senior developer' },
        },
    },
});

---

错误处理

Prisma 错误代码

import { Prisma } from '@prisma/client';

try {
    await PrismaService.main.user.create({
        data: { email: '[email protected]' },
    });
} catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
        // P2002: Unique constraint violation
        if (error.code === 'P2002') {
            throw new ConflictError('Email already exists');
        }

        // P2003: Foreign key constraint failed
        if (error.code === 'P2003') {
            throw new ValidationError('Invalid reference');
        }

        // P2025: Record not found
        if (error.code === 'P2025') {
            throw new NotFoundError('Record not found');
        }
    }

    Sentry.captureException(error);
    throw error;
}

常见错误代码

代码	含义
P2002	唯一约束冲突
P2003	外键约束失败
P2025	未找到记录
P2014	关系约束冲突

---

高级模式

聚合

// Count
const count = await PrismaService.main.user.count({
    where: { isActive: true },
});

// Aggregate
const stats = await PrismaService.main.post.aggregate({
    _count: true,
    _avg: { views: true },
    _sum: { likes: true },
    where: { published: true },
});

// Group by
const postsByAuthor = await PrismaService.main.post.groupBy({
    by: ['authorId'],
    _count: { id: true },
});

Upsert

// Update if exists, create if not
const user = await PrismaService.main.user.upsert({
    where: { email: '[email protected]' },
    update: { lastLogin: new Date() },
    create: {
        email: '[email protected]',
        name: 'John Doe',
    },
});

---

TypeScript 模式

import type { User, Prisma } from '@prisma/client';

// Create input type
const createUser = async (data: Prisma.UserCreateInput): Promise<User> => {
    return PrismaService.main.user.create({ data });
};

// Include type
type UserWithProfile = Prisma.UserGetPayload<{
    include: { profile: true };
}>;

const user: UserWithProfile = await PrismaService.main.user.findUnique({
    where: { id },
    include: { profile: true },
});

---

最佳实践

1. 始终使用 PrismaService.main - 切勿创建新的 PrismaClient 实例
2. 复杂查询使用仓库 - 让数据访问保持有条理
3. 仅选择所需字段 - 使用 select 提升性能
4. 预防 N+1 查询 - 使用 include 或批量查询
5. 使用事务 - 确保多步操作的原子性
6. 处理错误 - 检查特定的 Prisma 错误代码

---

相关技能：

• backend-dev-guidelines - 完整的后端架构指南
• nodejs - Node.js 核心模式与异步处理
• express - Express.js 路由与中间件