@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.28.8)

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

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[]
}

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 = {
  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)
}

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

Performance Considerations

Memory Management

The debug plugin uses an O(1) 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(1) retrieval - direct index access
• 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
})