prisma-database-querying

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Prisma 7 Database Querying (PostgreSQL)

Prisma 7 数据库查询（PostgreSQL）

You are an expert in efficient querying with Prisma 7 for Postgres.

你是Prisma 7操作PostgreSQL的高效查询专家。

Activation cues

触发场景

Use this skill when the user asks about:

Prisma

findMany/findFirst/findUnique

include/select

, filtering, ordering, pagination

transactions, concurrency, batching
performance optimization, N+1 issues, large reads
when/how to use
```
$queryRaw
```
/
```
$executeRaw
```
counts/aggregations/grouping where ORM becomes awkward or slow

当用户询问以下内容时使用本技能：

Prisma

findMany/findFirst/findUnique

、

include/select

、过滤、排序、分页

事务、并发、批处理
性能优化、N+1问题、大规模读取
何时/如何使用
```
$queryRaw
```
/
```
$executeRaw
```
当ORM实现起来繁琐或性能低下时的计数/聚合/分组操作

Default policy (important)

默认策略（重要）

Mutations (create/update/delete/upsert): use Prisma Client ORM by default.
Reads (SELECT/COUNT/analytics):
- Use Prisma Client first.
- Switch to raw SQL when Prisma cannot express the query cleanly, or when SQL can significantly improve performance (CTEs, window functions, custom joins, partial indexes usage, advanced grouping).

变更操作（create/update/delete/upsert）： 默认使用Prisma Client ORM。
读取操作（SELECT/COUNT/分析）：
- 优先使用Prisma Client。
- 当Prisma无法简洁表达查询，或者SQL能显著提升性能时（如CTE、窗口函数、自定义关联、部分索引使用、高级分组），切换到原生SQL。

Read patterns to prefer in Prisma Client

Prisma Client优先推荐的读取模式

Always scope fields using
```
select
```
(or narrowly scoped
```
include
```
) to avoid overfetching.
Use cursor-based pagination for large tables:
- ```
take
```
  ,
```
skip
```
  only for small datasets; cursor for high-scale.
Use
```
distinct
```
,
```
groupBy
```
, aggregates where they fit.
Avoid N+1: query relations with
```
include
```
or two-step queries with
```
in
```
filters.

始终使用
```
select
```
（或范围狭窄的
```
include
```
）来限定字段，避免过度获取数据。
对大型表使用基于游标（cursor）的分页：
- ```
take
```
  、
```
skip
```
  仅适用于小型数据集；高并发场景下使用游标。
在适用场景下使用
```
distinct
```
、
```
groupBy
```
、聚合函数。
避免N+1问题：通过
```
include
```
查询关联数据，或使用带
```
in
```
过滤器的两步查询。

Raw SQL rules

原生SQL规则

Use Prisma’s parameterized raw queries:

```
$queryRaw
```
for SELECT-like reads.
```
$executeRaw
```
for commands that return affected rows (never for SELECT).

Never build SQL strings from untrusted input. If you must do dynamic SQL, build the structure from safe enums/whitelists and pass user data as parameters.

(See Prisma raw SQL docs in

references/PRISMA7_CORE_REFERENCES.md

使用Prisma的参数化原生查询：

```
$queryRaw
```
用于类SELECT的读取操作。
```
$executeRaw
```
用于返回受影响行数的命令（绝不能用于SELECT）。

绝不要从不可信输入构建SQL字符串。如果必须使用动态SQL，请通过安全的枚举/白名单构建结构，并将用户数据作为参数传递。

（详见

references/PRISMA7_CORE_REFERENCES.md

中的Prisma原生SQL文档。）

Transaction guidance

事务指导

Use
```
$transaction
```
for multi-step writes that must be atomic.
Prefer short transactions; avoid long-running SELECTs inside write transactions unless required.

对于必须原子执行的多步写入操作，使用
```
$transaction
```
。
优先使用短事务；除非必要，否则避免在写入事务中执行长时间运行的SELECT操作。

Output format

输出格式

When the user asks for a query, provide:

The recommended Prisma Client query (or raw SQL if justified)
Notes on indexes and expected query plan assumptions
Pagination strategy if results can be large

当用户请求查询示例时，请提供：

推荐的Prisma Client查询（或在合理情况下使用原生SQL）
关于索引和预期查询计划的说明
如果结果集可能很大，提供分页策略

Examples

示例

Example: efficient list endpoint with cursor pagination

示例：基于游标分页的高效列表接口

// Input: { workspaceId, cursorId?: string, take?: number }
const take = Math.min(input.take ?? 50, 200);

const items = await prisma.project.findMany({
  where: { workspaceId: input.workspaceId },
  orderBy: { createdAt: "desc" },
  take: take + 1,
  ...(input.cursorId
    ? { cursor: { id: input.cursorId }, skip: 1 }
    : {}),
  select: {
    id: true,
    name: true,
    slug: true,
    createdAt: true,
  },
});

const hasNextPage = items.length > take;
const page = hasNextPage ? items.slice(0, take) : items;
const nextCursor = hasNextPage ? page[page.length - 1]!.id : null;

// Input: { workspaceId, cursorId?: string, take?: number }
const take = Math.min(input.take ?? 50, 200);

const items = await prisma.project.findMany({
  where: { workspaceId: input.workspaceId },
  orderBy: { createdAt: "desc" },
  take: take + 1,
  ...(input.cursorId
    ? { cursor: { id: input.cursorId }, skip: 1 }
    : {}),
  select: {
    id: true,
    name: true,
    slug: true,
    createdAt: true,
  },
});

const hasNextPage = items.length > take;
const page = hasNextPage ? items.slice(0, take) : items;
const nextCursor = hasNextPage ? page[page.length - 1]!.id : null;

Example: COUNT with complex join via raw SQL (read path)

示例：通过原生SQL实现带复杂关联的COUNT（读取场景）

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

const rows = await prisma.$queryRaw<{ total: bigint }[]>`
  SELECT COUNT(*)::bigint AS total
  FROM "Project" p
  JOIN "Workspace" w ON w.id = p."workspaceId"
  WHERE w.id = ${input.workspaceId}
    AND p."createdAt" >= ${input.since}
`;

const total = Number(rows[0]?.total ?? 0n);

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

const rows = await prisma.$queryRaw<{ total: bigint }[]>`
  SELECT COUNT(*)::bigint AS total
  FROM "Project" p
  JOIN "Workspace" w ON w.id = p."workspaceId"
  WHERE w.id = ${input.workspaceId}
    AND p."createdAt" >= ${input.since}
`;

const total = Number(rows[0]?.total ?? 0n);

Example: mutation stays in Prisma Client (write path)

示例：变更操作保留在Prisma Client中（写入场景）

await prisma.project.update({
  where: { id: input.projectId },
  data: { name: input.name, slug: input.slug },
});

await prisma.project.update({
  where: { id: input.projectId },
  data: { name: input.name, slug: input.slug },
});

Common pitfalls to warn about

需要提醒的常见陷阱

Mixing
```
select
```
and
```
include
```
incorrectly: choose one strategy; if you need relations and partial scalars, structure the query accordingly.
Using
```
$executeRaw
```
for SELECT: it returns affected rows, not data.
Using
```
skip
```
/
```
take
```
offsets on large tables: can become slow; use cursor.

错误混用
```
select
```
和
```
include
```
：选择一种策略；如果需要关联数据和部分标量字段，请合理构造查询。
使用
```
$executeRaw
```
执行SELECT：它返回的是受影响行数，而非数据。
在大型表上使用
```
skip
```
/
```
take
```
偏移分页：性能会变慢；请使用游标分页。

Additional resources

额外资源

For complete Prisma docs details, see reference.md

如需完整的Prisma文档细节，请查看reference.md