Skip to main content

SQL 注释

SQL 注释允许你向数据库查询附加元数据，从而更容易将查询与应用上下文关联起来。Prisma ORM 支持 Google 开发的 sqlcommenter 格式，该对象被数据库监控工具广泛支持。

¥SQL comments allow you to append metadata to your database queries, making it easier to correlate queries with application context. Prisma ORM supports the sqlcommenter format developed by Google, which is widely supported by database monitoring tools.

SQL 注释的用途：

¥SQL comments are useful for:

  • 可观测性：使用 traceparent 将数据库查询与应用跟踪关联起来。

    ¥Observability: Correlate database queries with application traces using traceparent

  • 查询洞察：使用元数据标记查询，以便在数据库监控工具中进行分析

    ¥Query insights: Tag queries with metadata for analysis in database monitoring tools

  • 调试：为查询添加自定义上下文以便于故障排除

    ¥Debugging: Add custom context to queries for easier troubleshooting

安装​

¥Installation

根据你的用例安装一个或多个官方插件：

¥Install one or more first-party plugins depending on your use case:

npm install @prisma/sqlcommenter-query-tags
npm install @prisma/sqlcommenter-trace-context

安装核心 SQL 注释类型包以创建你自己的插件：

¥Install the core SQL commenter types package to create your own plugin:

npm install @prisma/sqlcommenter

基本用法​

¥Basic usage

创建 PrismaClient 时，将 SQL 注释插件数组传递给 comments 选项。实例：

¥Pass an array of SQL commenter plugins to the comments option when creating a PrismaClient instance:

import { PrismaClient } from '../prisma/generated/client'
import { PrismaPg } from '@prisma/adapter-pg'
import { queryTags } from '@prisma/sqlcommenter-query-tags'
import { traceContext } from '@prisma/sqlcommenter-trace-context'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })

const prisma = new PrismaClient({
  adapter,
  comments: [queryTags(), traceContext()],
})

通过此配置，你的 SQL 查询将包含元数据作为注释：

¥With this configuration, your SQL queries will include metadata as comments:

SELECT "id", "name" FROM "User" /*application='my-app',traceparent='00-abc123...-01'*/

第一方插件​

¥First-party plugins

Prisma 提供两个官方的 SQL 注释插件：

¥Prisma provides two official SQL commenter plugins:

查询标签​

¥Query tags

@prisma/sqlcommenter-query-tags 包允许你使用 AsyncLocalStorage 在异步上下文中向查询添加任意标签。

¥The @prisma/sqlcommenter-query-tags package allows you to add arbitrary tags to queries within an async context using AsyncLocalStorage.

import { queryTags, withQueryTags } from '@prisma/sqlcommenter-query-tags'
import { PrismaClient } from '../prisma/generated/client'

const prisma = new PrismaClient({
  adapter,
  comments: [queryTags()],
})

// Wrap your queries to add tags
const users = await withQueryTags(
  { route: '/api/users', requestId: 'abc-123' },
  () => prisma.user.findMany()
)

生成的 SQL 包含作为注释的标签：

¥The resulting SQL includes the tags as comments:

SELECT ... FROM "User" /*requestId='abc-123',route='/api/users'*/

在一个作用域中执行多个查询​

¥Multiple queries in one scope

回调中的所有查询共享相同的标签：

¥All queries within the callback share the same tags:

const result = await withQueryTags({ traceId: 'trace-456' }, async () => {
  const users = await prisma.user.findMany()
  const posts = await prisma.post.findMany()
  return { users, posts }
})

使用标签嵌套作用域替换​

¥Nested scopes with tag replacement

默认情况下，嵌套的 withQueryTags 调用会完全替换外部标签：

¥By default, nested withQueryTags calls replace the outer tags entirely:

await withQueryTags({ requestId: 'req-123' }, async () => {
  // Queries here have: requestId='req-123'

  await withQueryTags({ userId: 'user-456' }, async () => {
    // Queries here only have: userId='user-456'
    // requestId is NOT included
    await prisma.user.findMany()
  })
})

使用标签合并嵌套作用域​

¥Nested scopes with tag merging

使用 withMergedQueryTags 将标签与外部作用域合并：

¥Use withMergedQueryTags to merge tags with the outer scope:

import { withQueryTags, withMergedQueryTags } from '@prisma/sqlcommenter-query-tags'

await withQueryTags({ requestId: 'req-123', source: 'api' }, async () => {
  await withMergedQueryTags({ userId: 'user-456', source: 'handler' }, async () => {
    // Queries here have: requestId='req-123', userId='user-456', source='handler'
    await prisma.user.findMany()
  })
})

你还可以通过将嵌套作用域中的标签设置为 undefined 来删除它们：

¥You can also remove tags in nested scopes by setting them to undefined:

await withQueryTags({ requestId: 'req-123', debug: 'true' }, async () => {
  await withMergedQueryTags({ userId: 'user-456', debug: undefined }, async () => {
    // Queries here have: requestId='req-123', userId='user-456'
    // debug is removed
    await prisma.user.findMany()
  })
})

跟踪上下文​

¥Trace context

@prisma/sqlcommenter-trace-context 包会将 W3C 跟踪上下文 (traceparent) 标头添加到你的查询中，从而实现分布式跟踪和数据库查询之间的关联。

¥The @prisma/sqlcommenter-trace-context package adds W3C Trace Context (traceparent) headers to your queries, enabling correlation between distributed traces and database queries.

import { traceContext } from '@prisma/sqlcommenter-trace-context'
import { PrismaClient } from '../prisma/generated/client'

const prisma = new PrismaClient({
  adapter,
  comments: [traceContext()],
})

启用跟踪并对当前 span 进行采样后，查询将包含 traceparent：

¥When tracing is enabled and the current span is sampled, queries include the traceparent:

SELECT * FROM "User" /*traceparent='00-0af7651916cd43dd8448eb211c80319c-b9c7c989f97918e1-01'*/
信息

跟踪上下文插件需要配置 @prisma/instrumentation。仅当跟踪处于活动状态且跨度已采样时，才会添加 traceparent。

¥The trace context plugin requires @prisma/instrumentation to be configured. The traceparent is only added when tracing is active and the span is sampled.

traceparent 标头遵循 W3C 跟踪上下文 规范：

¥The traceparent header follows the W3C Trace Context specification:

{version}-{trace-id}-{parent-id}-{trace-flags}

位置：

¥Where:

  • version：始终使用 00 以符合当前规范

    ¥version: Always 00 for the current spec

  • trace-id：32 个十六进制字符，表示跟踪 ID

    ¥trace-id: 32 hexadecimal characters representing the trace ID

  • parent-id：16 个十六进制字符，表示父级 span ID

    ¥parent-id: 16 hexadecimal characters representing the parent span ID

  • trace-flags：2 个十六进制字符；01 表示已采样

    ¥trace-flags: 2 hexadecimal characters; 01 indicates sampled

创建自定义插件​

¥Creating custom plugins

你可以创建自己的 SQL 注释插件，以便向查询添加自定义元数据。

¥You can create your own SQL commenter plugins to add custom metadata to queries.

插件结构​

¥Plugin structure

SQL 注释插件是一个接收查询上下文并返回键值对的函数：

¥A SQL commenter plugin is a function that receives query context and returns key-value pairs:

import type { SqlCommenterPlugin, SqlCommenterContext } from '@prisma/sqlcommenter'

const myPlugin: SqlCommenterPlugin = (context: SqlCommenterContext) => {
  return {
    application: 'my-app',
    version: '1.0.0',
  }
}

使用自定义插件​

¥Using custom plugins

将你的自定义插件传递给 comments 选项：

¥Pass your custom plugins to the comments option:

const prisma = new PrismaClient({
  adapter,
  comments: [myPlugin],
})

条件键​

¥Conditional keys

返回要从注释中排除的键的 undefined 对象。值为 undefined 的键会被自动过滤掉：

¥Return undefined for keys you want to exclude from the comment. Keys with undefined values are automatically filtered out:

const conditionalPlugin: SqlCommenterPlugin = (context) => ({
  model: context.query.modelName, // undefined for raw queries, automatically omitted
  action: context.query.action,
})

查询上下文​

¥Query context

插件接收一个包含查询信息的 SqlCommenterContext 对象：

¥Plugins receive a SqlCommenterContext object containing information about the query:

interface SqlCommenterContext {
  query: SqlCommenterQueryInfo
  sql?: string
}

query 属性提供有关 Prisma 操作的信息：

¥The query property provides information about the Prisma operation:

属性类型描述
type'single' | 'compacted'无论是单个查询还是批量查询
modelNamestring | undefined正在查询的模型（例如，"User"）。原始查询未定义。
actionstringPrisma 操作（例如，"findMany"、"createOne"、"queryRaw"）
queryunknown（单个）或 queries: unknown[]（压缩）完整的查询对象。结构体不属于公共 API 的一部分。

sql 属性是此 Prisma 查询生成的原始 SQL 查询。当 PrismaClient 连接到数据库并直接渲染 SQL 查询时，它始终可用。使用 Prisma Accelerate 时，SQL 渲染在 Accelerate 端进行，因此当 SQL 注释插件在 PrismaClient 端执行时，原始 SQL 字符串不可用。

¥The sql property is the raw SQL query generated from this Prisma query. It is always available when PrismaClient connects to the database and renders SQL queries directly. When using Prisma Accelerate, SQL rendering happens on Accelerate side and the raw SQL strings are not available when SQL commenter plugins are executed on the PrismaClient side.

单查询与压缩查询​

¥Single vs. compacted queries

  • 单个查询 (type: 'single')：正在执行一条 Prisma 查询

    ¥Single queries (type: 'single'): A single Prisma query is being executed

  • 压缩查询 (type: 'compacted')：多个查询已被批处理成单个 SQL 语句（例如，自动 findUnique 批处理）。

    ¥Compacted queries (type: 'compacted'): Multiple queries have been batched into a single SQL statement (e.g., automatic findUnique batching)

示例：应用元数据​

¥Example: Application metadata

import type { SqlCommenterPlugin } from '@prisma/sqlcommenter'

const applicationTags: SqlCommenterPlugin = (context) => ({
  application: 'my-service',
  environment: process.env.NODE_ENV ?? 'development',
  operation: context.query.action,
  model: context.query.modelName,
})

示例：异步上下文传播​

¥Example: Async context propagation

使用 AsyncLocalStorage 在应用中传播上下文：

¥Use AsyncLocalStorage to propagate context through your application:

import { AsyncLocalStorage } from 'node:async_hooks'
import type { SqlCommenterPlugin } from '@prisma/sqlcommenter'

interface RequestContext {
  route: string
  userId?: string
}

const requestStorage = new AsyncLocalStorage<RequestContext>()

const requestContextPlugin: SqlCommenterPlugin = () => {
  const context = requestStorage.getStore()
  return {
    route: context?.route,
    userId: context?.userId,
  }
}

// Usage in a request handler
requestStorage.run({ route: '/api/users', userId: 'user-123' }, async () => {
  await prisma.user.findMany()
})

合并多个插件​

¥Combining multiple plugins

插件按数组顺序调用，其输出将被合并。后续插件可以覆盖之前插件设置的键：

¥Plugins are called in array order, and their outputs are merged. Later plugins can override keys from earlier plugins:

import type { SqlCommenterPlugin } from '@prisma/sqlcommenter'
import { queryTags } from '@prisma/sqlcommenter-query-tags'
import { traceContext } from '@prisma/sqlcommenter-trace-context'

const appPlugin: SqlCommenterPlugin = () => ({
  application: 'my-app',
  version: '1.0.0',
})

const prisma = new PrismaClient({
  adapter,
  comments: [appPlugin, queryTags(), traceContext()],
})

框架集成​

¥Framework integration

Hono​

Hono 的中间件会正确等待下游处理程序：

¥Hono's middleware properly awaits downstream handlers:

import { createMiddleware } from 'hono/factory'
import { withQueryTags } from '@prisma/sqlcommenter-query-tags'

app.use(
  createMiddleware(async (c, next) => {
    await withQueryTags(
      {
        route: c.req.path,
        method: c.req.method,
        requestId: c.req.header('x-request-id') ?? crypto.randomUUID(),
      },
      () => next(),
    )
  }),
)

Koa​

Koa 的中间件会正确等待下游处理程序：

¥Koa's middleware properly awaits downstream handlers:

import { withQueryTags } from '@prisma/sqlcommenter-query-tags'

app.use(async (ctx, next) => {
  await withQueryTags(
    {
      route: ctx.path,
      method: ctx.method,
      requestId: ctx.get('x-request-id') || crypto.randomUUID(),
    },
    () => next(),
  )
})

Fastify​

封装各个路由处理程序：

¥Wrap individual route handlers:

import { withQueryTags } from '@prisma/sqlcommenter-query-tags'

fastify.get('/users', (request, reply) => {
  return withQueryTags(
    {
      route: '/users',
      method: 'GET',
      requestId: request.id,
    },
    () => prisma.user.findMany(),
  )
})

Express​

Express 中间件使用回调函数，因此请直接封装路由处理程序：

¥Express middleware uses callbacks, so wrap route handlers directly:

import { withQueryTags } from '@prisma/sqlcommenter-query-tags'

app.get('/users', (req, res, next) => {
  withQueryTags(
    {
      route: req.path,
      method: req.method,
      requestId: req.header('x-request-id') ?? crypto.randomUUID(),
    },
    () => prisma.user.findMany(),
  )
    .then((users) => res.json(users))
    .catch(next)
})

NestJS​

使用拦截器封装处理程序执行：

¥Use an interceptor to wrap handler execution:

import { Injectable, NestInterceptor, ExecutionContext, CallHandler } from '@nestjs/common'
import { Observable, from, lastValueFrom } from 'rxjs'
import { withQueryTags } from '@prisma/sqlcommenter-query-tags'

@Injectable()
export class QueryTagsInterceptor implements NestInterceptor {
  intercept(context: ExecutionContext, next: CallHandler): Observable<unknown> {
    const request = context.switchToHttp().getRequest<Request>()
    return from(
      withQueryTags(
        {
          route: request.url,
          method: request.method,
          requestId: request.headers.get('x-request-id') ?? crypto.randomUUID(),
        },
        () => lastValueFrom(next.handle()),
      ),
    )
  }
}

// Apply globally in main.ts
app.useGlobalInterceptors(new QueryTagsInterceptor())

输出格式​

¥Output format

插件输出将被合并，按键按字母顺序排序，进行 URL 编码，并根据 sqlcommenter 规范 进行格式化：

¥Plugin outputs are merged, sorted alphabetically by key, URL-encoded, and formatted according to the sqlcommenter specification:

SELECT "id", "name" FROM "User" /*application='my-app',environment='production',model='User'*/

关键行为：

¥Key behaviors:

  • 插件按数组顺序同步调用。

    ¥Plugins are called synchronously in array order

  • 如果后续插件返回相同的键，则会覆盖之前的插件。

    ¥Later plugins override earlier ones if they return the same key

  • 值为 undefined 的键会被过滤掉（不会移除之前插件设置的键）

    ¥Keys with undefined values are filtered out (they do not remove keys set by earlier plugins)

  • 键和值根据 sqlcommenter 规范进行 URL 编码。

    ¥Keys and values are URL-encoded per the sqlcommenter spec

  • 值中的单引号会被转义为 \'

    ¥Single quotes in values are escaped as \'

  • 注释会附加到 SQL 查询的末尾。

    ¥Comments are appended to the end of SQL queries

API 参考​

¥API reference

SqlCommenterTags​

type SqlCommenterTags = { readonly [key: string]: string | undefined }

要添加为 SQL 注释的键值对。具有 undefined 值的键将被自动过滤掉。

¥Key-value pairs to add as SQL comments. Keys with undefined values are automatically filtered out.

SqlCommenterPlugin​

interface SqlCommenterPlugin {
  (context: SqlCommenterContext): SqlCommenterTags
}

一个接收查询上下文并返回键值对的函数。返回一个空对象，表示不对特定查询添加任何注释。

¥A function that receives query context and returns key-value pairs. Return an empty object to add no comments for a particular query.

SqlCommenterContext​

interface SqlCommenterContext {
  query: SqlCommenterQueryInfo
  sql?: string
}

提供给插件的上下文包含有关查询的信息。

¥Context provided to plugins containing information about the query.

  • query：有关正在执行的 Prisma 查询的信息。参见 SqlCommenterQueryInfo。

    ¥query: Information about the Prisma query being executed. See SqlCommenterQueryInfo.

  • sql：正在执行的 SQL 查询。仅在使用驱动程序适配器时可用，而使用 Accelerate 时不可用。

    ¥sql: The SQL query being executed. It is only available when using driver adapters but not when using Accelerate.

SqlCommenterQueryInfo​

type SqlCommenterQueryInfo =
  | ({ type: 'single' } & SqlCommenterSingleQueryInfo)
  | ({ type: 'compacted' } & SqlCommenterCompactedQueryInfo)

有关正在执行的查询的信息。

¥Information about the query or queries being executed.

SqlCommenterSingleQueryInfo​

interface SqlCommenterSingleQueryInfo {
  modelName?: string
  action: string
  query: unknown
}

有关单个 Prisma 查询的信息。

¥Information about a single Prisma query.

SqlCommenterCompactedQueryInfo​

interface SqlCommenterCompactedQueryInfo {
  modelName?: string
  action: string
  queries: unknown[]
}

有关压缩批处理查询的信息。

¥Information about a compacted batch query.