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](https://google.github.io/sqlcommenter/) developed by Google, which is widely supported by database monitoring tools.

SQL comments are useful for:

- **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:

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

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

```terminal
npm install @prisma/sqlcommenter
```

## Basic usage

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

```ts
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()],
})
```

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

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

## First-party plugins

Prisma provides two official SQL commenter plugins:

### Query tags

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

```ts
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()
)
```

The resulting SQL includes the tags as comments:

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

#### Multiple queries in one scope

All queries within the callback share the same tags:

```ts
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

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

```ts
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

Use `withMergedQueryTags` to merge tags with the outer scope:

```ts
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()
  })
})
```

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

```ts
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

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

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

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

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

```sql
SELECT * FROM "User" /*traceparent='00-0af7651916cd43dd8448eb211c80319c-b9c7c989f97918e1-01'*/
```

:::info

The trace context plugin requires [`@prisma/instrumentation`](/orm/prisma-client/observability-and-logging/opentelemetry-tracing) to be configured. The `traceparent` is only added when tracing is active and the span is sampled.

:::

The `traceparent` header follows the [W3C Trace Context](https://www.w3.org/TR/trace-context/) specification:

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

Where:

- `version`: Always `00` for the current spec
- `trace-id`: 32 hexadecimal characters representing the trace ID
- `parent-id`: 16 hexadecimal characters representing the parent span ID
- `trace-flags`: 2 hexadecimal characters; `01` indicates sampled

## Creating custom plugins

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

### Plugin structure

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

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

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

### Using custom plugins

Pass your custom plugins to the `comments` option:

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

### Conditional keys

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

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

### Query context

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

```ts
interface SqlCommenterContext {
  query: SqlCommenterQueryInfo
  sql?: string
}
```

The `query` property provides information about the Prisma operation:

| Property    | Type                                  | Description                                                                      |
| ----------- | ------------------------------------- | -------------------------------------------------------------------------------- |
| `type`      | `'single'` \| `'compacted'`           | Whether this is a single query or a batched query                                |
| `modelName` | `string` \| `undefined`               | The model being queried (e.g., `"User"`). Undefined for raw queries.             |
| `action`    | `string`                              | The Prisma operation (e.g., `"findMany"`, `"createOne"`, `"queryRaw"`)           |
| `query`     | `unknown` (single) or `queries: unknown[]` (compacted) | The full query object(s). Structure is not part of the public API.  |

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

- **Single queries** (`type: 'single'`): A single Prisma query is being executed
- **Compacted queries** (`type: 'compacted'`): Multiple queries have been batched into a single SQL statement (e.g., automatic `findUnique` batching)

### Example: Application metadata

```ts
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

Use `AsyncLocalStorage` to propagate context through your application:

```ts
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:

```ts
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's middleware properly awaits downstream handlers:

```ts
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's middleware properly awaits downstream handlers:

```ts
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:

```ts
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 middleware uses callbacks, so wrap route handlers directly:

```ts
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:

```ts
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

Plugin outputs are merged, sorted alphabetically by key, URL-encoded, and formatted according to the [sqlcommenter specification](https://google.github.io/sqlcommenter/spec/):

```sql
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
- Keys with `undefined` values are filtered out (they do not remove keys set by earlier plugins)
- Keys and values are URL-encoded per the sqlcommenter spec
- Single quotes in values are escaped as `\'`
- Comments are appended to the end of SQL queries

## API reference

### `SqlCommenterTags`

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

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

### `SqlCommenterPlugin`

```ts
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`

```ts
interface SqlCommenterContext {
  query: SqlCommenterQueryInfo
  sql?: string
}
```

Context provided to plugins containing information about the query.

- **`query`**: Information about the Prisma query being executed. See [`SqlCommenterQueryInfo`](#sqlcommenterqueryinfo).
- **`sql`**: The SQL query being executed. It is only available when using driver adapters but not when using Accelerate.


### `SqlCommenterQueryInfo`

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

Information about the query or queries being executed.

### `SqlCommenterSingleQueryInfo`

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

Information about a single Prisma query.

### `SqlCommenterCompactedQueryInfo`

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

Information about a compacted batch query.
---

# Observability & logging