Prisma Query Insights - Embedder Guide

This document is for developers building observability tools, database monitoring solutions, or query analyzers that want to parse and utilize the prismaQuery SQL comment tags.

Overview

The @prisma/sqlcommenter-query-insights plugin adds a prismaQuery comment tag to SQL queries. This tag contains structured information about the Prisma operation that generated the query, encoded in a compact format suitable for SQL comments.

Comment Format

The comment follows the sqlcommenter specification:

SELECT ... FROM "User" /*prismaQuery='User.findMany:eyJ3aGVyZSI6eyJhY3RpdmUiOnsiJHR5cGUiOiJQYXJhbSJ9fSwiaW5jbHVkZSI6eyJwb3N0cyI6dHJ1ZX19'*/

Tag Structure

The prismaQuery value has the following format:

[ModelName.]Action[:Base64UrlEncodedPayload]

Components

^* Raw queries (queryRaw, executeRaw) have no model name or payload.

Examples

Actions

The following Prisma actions may appear:

Decoding the Payload

Step 1: Parse the Tag Value

// Helper to decode base64url
function fromBase64Url(data) {
  // Node.js
  if (typeof Buffer !== 'undefined') {
    return Buffer.from(data, 'base64url').toString('utf-8')
  }
  // Browser: convert base64url to base64
  let base64 = data.replace(/-/g, '+').replace(/_/g, '/')
  // Add padding if needed
  const padding = (4 - (base64.length % 4)) % 4
  base64 += '='.repeat(padding)
  return atob(base64)
}

function parsePrismaQueryTag(value) {
  // URL-decode the value (sqlcommenter spec)
  const decoded = decodeURIComponent(value)

  const colonIndex = decoded.indexOf(':')

  if (colonIndex === -1) {
    // Raw query - no payload
    return {
      modelName: undefined,
      action: decoded,
      payload: undefined,
      isRaw: true,
    }
  }

  const prefix = decoded.slice(0, colonIndex)
  const base64UrlPayload = decoded.slice(colonIndex + 1)

  const dotIndex = prefix.indexOf('.')
  const modelName = prefix.slice(0, dotIndex)
  const action = prefix.slice(dotIndex + 1)

  // Decode base64url
  const jsonString = fromBase64Url(base64UrlPayload)
  const payload = JSON.parse(jsonString)

  return {
    modelName,
    action,
    payload,
    isRaw: false,
  }
}

Step 2: Understand the Payload Structure

The payload is a JSON object representing the Prisma query in a format similar to the Prisma Client API. For single queries:

interface QueryPayload {
  where?: FilterObject
  data?: DataObject
  orderBy?: OrderByObject
  take?: number
  skip?: number
  cursor?: CursorObject
  distinct?: string[]
  select?: SelectObject
  include?: IncludeObject
  // ... other arguments
}

For compacted (batched) queries, the payload is an array:

type CompactedPayload = QueryPayload[]

Query Shape Transformation

The plugin transforms the internal JSON protocol format into a Prisma-like query format for better readability.

Parameterized Values

Important: All user data values are replaced with placeholder objects to ensure no sensitive data appears in SQL comments.

Placeholder Format

{ "$type": "Param" }

Future Extension

In future versions, the placeholder may include additional metadata (field names are for illustration purposes only):

{
  "$type": "Param",
  "name": "p1",
  "valueType": "String"
}

Your parser should handle both the current simple format and potential future extensions by checking for the $type field.

Example: Parameterized Query

Original Prisma query:

prisma.user.findMany({
  where: {
    email: { contains: '[email protected]' },
    age: { gte: 18 },
  },
  take: 10,
})

Decoded payload:

{
  "where": {
    "email": { "contains": { "$type": "Param" } },
    "age": { "gte": { "$type": "Param" } }
  },
  "take": 10
}

Note: Structural values like take and skip are preserved, while user data values are parameterized.

Preserved vs. Parameterized Values

Preserved (Structural)

These values are part of the query shape and are NOT parameterized:

Parameterized (User Data)

These values are replaced with { "$type": "Param" }:

Best Practices

1. Handle Unknown Actions Gracefully

New Prisma versions may introduce new actions. Parse unknown actions without failing:

const KNOWN_ACTIONS = new Set([
  'findUnique',
  'findFirst',
  'findMany',
  'createOne',
  'updateOne',
  'deleteOne',
  // ... etc
])

function parseAction(action) {
  return {
    action,
    isKnown: KNOWN_ACTIONS.has(action),
  }
}

2. Handle Compacted Batches

When Prisma batches multiple queries into one SQL statement, the payload is an array. Check for this:

function isCompactedBatch(payload) {
  return Array.isArray(payload)
}

function getQueryCount(parsed) {
  if (!parsed.payload) return 1
  if (Array.isArray(parsed.payload)) return parsed.payload.length
  return 1
}

Version Compatibility

Additive changes (new action types, new fields in special objects like params, etc) are not considered breaking changes.

Troubleshooting

Invalid Base64url

If payload decoding fails:

1. Make sure you are decoding it as base64url and not standard base64
2. Convert base64url to standard base64 if your decoder doesn't support base64url natively (replace - with +, _ with /, add padding)

Unrecognized Payload Structure

If the payload structure doesn't match expectations:

1. Check the Prisma version — newer versions may have additional fields
2. Parse defensively with optional chaining
3. Report unrecognized fields rather than failing

Support

For issues or questions:

• GitHub
• Documentation