@kysera/debug

Debug utilities for Kysera - query logging, profiling, SQL formatting, and performance analysis.

Installation

npm install @kysera/debug

Overview

Dependencies: @kysera/core (peer: kysely >=0.29.0)

:::info Package Type This is a utility package providing debug and monitoring features. It's not part of the Repository/DAL pattern - it wraps Kysely instances directly. :::

Key Features

• Query Logging - Automatic logging of SQL queries and execution times
• Performance Metrics - Collect and analyze query performance data
• Slow Query Detection - Identify and alert on slow database queries
• SQL Formatting - Format and highlight SQL for better readability
• Query Profiling - Detailed performance analysis with statistics
• Circular Buffer - Memory-efficient metrics storage

Quick Start

import { Kysely } from 'kysely'
import { withDebug } from '@kysera/debug'

// Wrap your database with debug capabilities
const debugDb = withDebug(db)

// Execute queries - they're automatically logged and timed
await debugDb.selectFrom('users').selectAll().execute()

// Get collected metrics
const metrics = debugDb.getMetrics()
console.log(`Executed ${metrics.length} queries`)

withDebug()

Wraps a Kysely database instance with debug capabilities.

const debugDb = withDebug(db, {
  logQuery: true,
  logParams: true,
  slowQueryThreshold: 100,
  maxMetrics: 1000,
  onSlowQuery: (sql, duration) => {
    console.warn(`Slow query detected: ${duration}ms`)
  }
})

DebugOptions

interface DebugOptions {
  logQuery?: boolean // Log query SQL (default: true)
  logParams?: boolean // Log query parameters (default: false)
  slowQueryThreshold?: number // Threshold in ms (default: 100)
  onSlowQuery?: (sql: string, duration: number) => void
  logger?: KyseraLogger // Custom logger
  maxMetrics?: number // Max metrics in buffer (default: 1000)
}

DebugDatabase Methods

interface DebugDatabase<DB> extends Kysely<DB> {
  getMetrics(): QueryMetrics[] // Get all collected metrics
  clearMetrics(): void // Clear collected metrics
}

QueryMetrics

interface QueryMetrics {
  sql: string // SQL query string
  params?: unknown[] // Query parameters
  duration: number // Execution time in ms
  timestamp: number // When query was executed
}

QueryProfiler

Advanced profiler for detailed performance analysis.

import { QueryProfiler } from '@kysera/debug'

const profiler = new QueryProfiler({ maxQueries: 500 })

// Record queries
profiler.record({
  sql: 'SELECT * FROM users WHERE id = $1',
  params: [123],
  duration: 10.5,
  timestamp: Date.now()
})

// Get summary
const summary = profiler.getSummary()
console.log(`Total: ${summary.totalQueries}`)
console.log(`Average: ${summary.averageDuration.toFixed(2)}ms`)
console.log(`Slowest: ${summary.slowestQuery?.duration.toFixed(2)}ms`)

// Get slow queries
const slowQueries = profiler.getSlowQueries(50)

// Get top 10 slowest
const top10 = profiler.getSlowestQueries(10)

// Get count of recorded queries
console.log(`Recorded: ${profiler.count} queries`)

// Clear all recorded queries
profiler.clear()

ProfilerOptions

interface ProfilerOptions {
  maxQueries?: number // Max queries kept in memory (default: 1000)
}

When the limit is reached, the oldest recorded queries are overwritten (backed by a CircularBuffer).

QueryProfiler Methods

Method	Returns	Description
record(metrics)	void	Record a query execution
getSummary()	ProfilerSummary	Get aggregated statistics
getSlowQueries(threshold)	QueryMetrics[]	Get queries slower than threshold (ms)
getSlowestQueries(n)	QueryMetrics[]	Get top N slowest queries
clear()	void	Clear all recorded queries
count (getter)	number	Get the number of recorded queries

ProfilerSummary

interface ProfilerSummary {
  totalQueries: number
  totalDuration: number
  averageDuration: number
  slowestQuery: QueryMetrics | null
  fastestQuery: QueryMetrics | null
  queries: QueryMetrics[]
}

CircularBuffer

The fixed-size ring buffer backing metrics storage is exported for standalone use. When full, the oldest entry is overwritten.

import { CircularBuffer } from '@kysera/debug'

const buffer = new CircularBuffer<number>(3)

buffer.add(1)
buffer.add(2)
buffer.add(3)
buffer.add(4) // Overwrites oldest (1)

buffer.getOrdered() // [2, 3, 4] - chronological order
buffer.getRaw() // Storage order (not chronological)
buffer.size // 3 (current item count)
buffer.capacity // 3 (maximum size)
buffer.isFull // true
buffer.isEmpty // false
buffer.clear() // Remove all items

CircularBuffer API

Member	Returns	Description
add(item)	void	Add an item, overwriting the oldest when full - O(1)
getOrdered()	T[]	Copy of items in chronological order - O(n)
getRaw()	T[]	Copy of items in storage order - O(n)
clear()	void	Remove all items
size (getter)	number	Current number of items
capacity (getter)	number	Maximum number of items
isFull (getter)	boolean	Whether the buffer is at capacity
isEmpty (getter)	boolean	Whether the buffer has no items

The constructor throws if maxSize is not a positive integer.

SQL Formatting Functions

formatSQL()

Format SQL with newlines before major keywords.

import { formatSQL } from '@kysera/debug'

const sql = 'SELECT id, name FROM users WHERE active = true ORDER BY name'
console.log(formatSQL(sql))
// SELECT id, name
// FROM users
// WHERE active = true
// ORDER BY name

formatSQLPretty()

Format SQL with indentation for nested queries.

import { formatSQLPretty } from '@kysera/debug'

const sql = 'SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)'
console.log(formatSQLPretty(sql, 2)) // 2 spaces indent

minifySQL()

Remove unnecessary whitespace.

import { minifySQL } from '@kysera/debug'

const sql = `
  SELECT id, name
  FROM users
`
console.log(minifySQL(sql))
// SELECT id, name FROM users

highlightSQL()

Highlight SQL keywords with ANSI color codes for terminal.

import { highlightSQL } from '@kysera/debug'

console.log(highlightSQL('SELECT * FROM users'))
// Keywords highlighted in blue

Usage Examples

Slow Query Detection

const debugDb = withDebug(db, {
  slowQueryThreshold: 50,
  onSlowQuery: (sql, duration) => {
    monitoring.recordSlowQuery({ sql, duration })
    logger.warn(`Slow query: ${duration.toFixed(2)}ms`, { sql })
  }
})

Production Monitoring

const debugDb = withDebug(db, {
  logQuery: false, // Don't log in production
  slowQueryThreshold: 200,
  maxMetrics: 100,
  onSlowQuery: (sql, duration) => {
    apm.recordTransaction({
      type: 'db.query',
      duration,
      metadata: { sql }
    })

    if (duration > 1000) {
      alerting.critical('Query exceeded 1 second', { sql, duration })
    }
  }
})

// Periodic metrics reporting
setInterval(() => {
  const metrics = debugDb.getMetrics()
  if (metrics.length > 0) {
    const avgDuration = metrics.reduce((sum, m) => sum + m.duration, 0) / metrics.length
    monitoring.gauge('db.query.avg_duration', avgDuration)
    debugDb.clearMetrics()
  }
}, 60000)

Custom Logger

import type { KyseraLogger } from '@kysera/core'

const customLogger: KyseraLogger = {
  trace: message => loggingService.trace('db-query', message),
  debug: message => loggingService.debug('db-query', message),
  info: message => loggingService.info('db-query', message),
  warn: message => loggingService.warn('db-query', message),
  error: message => loggingService.error('db-query', message),
  fatal: message => loggingService.fatal('db-query', message)
}

const debugDb = withDebug(db, { logger: customLogger })

Performance Considerations

Memory Management

The debug plugin uses a circular buffer for efficient metrics storage:

• Default limit: 1000 metrics
• Oldest metrics automatically overwritten when limit reached
• Configure via maxMetrics option
• O(1) insertion - no array shifting or reallocation
• O(n) retrieval - getMetrics() copies entries into chronological order (handling wrap-around)
• Minimal memory overhead - fixed-size array
• No garbage collection pressure from array resizing

Production Usage

// Disable verbose logging
const debugDb = withDebug(db, {
  logQuery: false,
  logParams: false
})

// Use slow query detection only
const debugDb = withDebug(db, {
  logQuery: false,
  slowQueryThreshold: 500,
  onSlowQuery: (sql, duration) => monitoring.recordSlowQuery({ sql, duration })
})

// Smaller buffer for production
const debugDb = withDebug(db, {
  maxMetrics: 100
})