Kysely SqlCommenter

SqlCommenter plugin for Kysely

Getting started

npm install kysely-sqlcommenter

Requires Kysely >=0.28.17 <0.30.0.

SqlCommenterPlugin does not change the API of Kysely. You only provide it a callback for getting the metadata for the comment. AsyncLocalStorage or any alternative is needed.

Initialize the AsyncLocalStorage:

import { AsyncLocalStorage } from 'node:async_hooks'
import { SqlCommentLike } from 'kysely-sqlcommenter'

const asyncLocalStorage = new AsyncLocalStorage<SqlCommentLike>()

Register the SqlCommenterPlugin using the asyncLocalStorage in callback:

import { SqlCommenterPlugin } from 'kysely-sqlcommenter'

const db = new Kysely<DB>({
  // ... kysely config
  plugins: [
    // Provide callback
    new SqlCommenterPlugin(() => asyncLocalStorage.getStore()),
  ],
})

Create a root middleware, register the root span with storage via asyncLocalStorage.run. Everything in the callstack of this next will have access to a shared copy of the storage (new calls will have exclusive storage). You can initialize it with a value.

app.use((req, res, next) => {
  asyncLocalStorage.run({ controller: req.path }, next)
})

Any supported DML query will have the appropriate SqlComment. Supported query types: select, update, insert, delete, and merge.

db.selectFrom('cats').select(['id', 'name'])
// select "id", "name" from "cats" /*controller='cats'*/

Cloud SQL Query Insights

Sqlcommenter spec wraps values in single quotes: /*action='list'*/. Cloud SQL Query Insights currently displays those delimiters as part of the tag value. Keep the default for spec-compliant output, or disable value quoting if you only target Cloud SQL display:

const db = new Kysely<DB>({
  // ... kysely config
  plugins: [
    new SqlCommenterPlugin(() => asyncLocalStorage.getStore(), {
      quoteValues: false,
    }),
  ],
})

This emits /*action=list*/, which is intentionally not sqlcommenter-spec compliant. The explicit sqlCommenter helper accepts the same option as its third argument.

Per-query

For explicit per-query comments, use Kysely's $call helper API:

import { sqlCommenter } from 'kysely-sqlcommenter'

db.selectFrom('cats')
  .$call((qb) => sqlCommenter(qb, { controller: 'cats', action: 'list' }))
  .select(['id', 'name'])
// select "id", "name" from "cats" /*action='list',controller='cats'*/

See the full working example for express here, including concurrency demo and adjusting the comment in other middleware.

Features

• SqlCommenter spec serialization tests
• Callback API with AsyncLocalStorage examples
• Explicit helper API via Kysely $call
• DML query support: select, update, insert, delete, and merge
• Package smoke tests and CI

References

• SqlCommenter
• Kysely
• Support for sqlcommenter? #384