SQL Playground Engine

Core deterministic SQL analysis engine. Provides SQL parsing, normalization, sanitization, fingerprinting, EXPLAIN plan analysis, and optimization suggestions.

Features

• SQL Normalization: Canonicalize queries for deduplication
• Fingerprinting: Generate deterministic hashes with metadata (tables, joins, WHERE complexity)
• Sanitization: Privacy-mode support to strip literals before sending to LLM
• EXPLAIN Parsing: Parse Postgres, MySQL, SQLite EXPLAIN output (text and JSON)
• Heuristics: Detect sequential scans, missing indexes, N+1 patterns, complex joins
• Index Suggestions: Recommend indexes based on EXPLAIN analysis
• Type-Safe: Full TypeScript with strict mode, JSDoc, and exported types

Installation

npm install @sql-playground/engine

Quick Start

import { normalizeSQL, fingerprint, sanitizeSQL } from '@sql-playground/engine';

// Normalize SQL
const normalized = normalizeSQL('select id from users where id=1');
console.log(normalized.normalized);
// => "SELECT id FROM users WHERE id = 1"

// Generate fingerprint
const fp = fingerprint('SELECT id FROM users WHERE id = 1');
console.log(fp.hash, fp.tables, fp.joinCount);
// => "a1b2c3d4e5f6g7h8", ["users"], 0

// Sanitize for privacy
const sanitized = sanitizeSQL("SELECT * FROM users WHERE email = '[email protected]'");
console.log(sanitized.sanitized);
// => "SELECT * FROM users WHERE email = <str_0>"

API

Core Functions

normalizeSQL(sql, options?)

Normalize SQL to canonical form.

interface NormalizationOptions {
  collapseWhitespace?: boolean; // Default: true
  normalizeKeywords?: boolean;  // Default: true
  normalizeIdentifiers?: boolean; // Default: false
}

function normalizeSQL(
  sql: string,
  options?: NormalizationOptions
): NormalizationResult;

interface NormalizationResult {
  original: string;
  normalized: string;
}

fingerprint(sql)

Generate deterministic query fingerprint.

interface QueryFingerprint {
  hash: string; // 16-char hex hash
  pattern: string; // Normalized pattern with placeholders
  tables: string[]; // Extracted table names
  joinCount: number; // Number of JOINs
  whereClauseComplexity: number; // AND/OR count
}

function fingerprint(sql: string): QueryFingerprint;

compareFingerprints(fp1, fp2)

Compare two fingerprints for similarity (0-1 score).

function compareFingerprints(
  fp1: QueryFingerprint,
  fp2: QueryFingerprint
): number;

Sanitization

sanitizeSQL(sql, config?)

Replace literals with tokens for privacy.

interface PrivacyConfig {
  enabled: boolean; // Default: true
  replaceStrings: boolean;
  replaceNumbers: boolean;
}

interface SanitizationResult {
  original: string;
  sanitized: string;
  literals: SQLLiteral[]; // Detected literals
  literalMap: Record<string, string>; // Token -> value mapping
}

function sanitizeSQL(
  sql: string,
  config?: Partial<PrivacyConfig>
): SanitizationResult;

restoreSQL(sanitized, literalMap)

Restore original literals from sanitized SQL.

function restoreSQL(
  sanitized: string,
  literalMap: Record<string, string>
): string;

extractLiterals(sql)

Extract all literals without sanitizing.

function extractLiterals(sql: string): SQLLiteral[];

EXPLAIN Parsing

PostgreSQL

// Parse EXPLAIN (FORMAT JSON)
function parsePostgresExplainJson(explainJson: unknown[]): ExplainNode;

// Parse text format
function parsePostgresExplainText(explainText: string): ExplainNode;

MySQL

function parseMysqlExplainJson(explainJson: unknown): ExplainNode;
function parseMysqlExplainTable(rows: Record<string, unknown>[]): ExplainNode;

SQLite

function parseSqliteExplainQueryPlan(explainText: string): ExplainNode;

Heuristics

analyzeExplainPlan(plan, tableName?)

Analyze EXPLAIN plan for performance issues.

interface HeuristicsResult {
  hotnessScore: number; // 0-100 performance concern score
  isSequentialScan: boolean;
  hasComplexJoin: boolean;
  hasMissingIndex: boolean;
  hasN1Problem: boolean;
  estimatedRowsDiscrepancy: number | null;
  recommendations: IndexRecommendation[];
}

function analyzeExplainPlan(
  plan: ExplainNode,
  tableName?: string
): HeuristicsResult;

suggestIndexes(plan, existingIndexes?)

Get index recommendations from analysis.

interface IndexRecommendation {
  table: string;
  columns: string[];
  type: 'btree' | 'hash' | 'brin' | 'gin';
  reason: string;
}

function suggestIndexes(
  plan: ExplainNode,
  existingIndexes?: string[]
): IndexRecommendation[];

Exported Types

All types are exported for SDK/API consumers:

// Core analysis types
export type SQLDialect = 'postgres' | 'mysql' | 'sqlite';
export interface ExplainNode;
export interface HeuristicsResult;
export interface QueryFingerprint;
export interface SanitizationResult;
export interface AnalysisRequest;
export interface ExplanationResult;
export interface PlanNode;
export interface Optimization;
export interface AntiPattern;
export interface SchemaSummary;

Development

Setup

npm install

Build

npm run build

Test

npm test              # Run once
npm run test:watch   # Watch mode
npm run test:coverage # With coverage report

Lint & Format

npm run lint        # Check
npm run lint:fix    # Fix
npm run format      # Apply prettier
npm run format:check # Check formatting

Architecture

The engine is organized into modules:

• types.ts — Shared TypeScript interfaces
• sanitizer.ts — Privacy mode literal replacement
• normalization.ts — SQL normalization and fingerprinting
• heuristics.ts — EXPLAIN plan analysis
• parsers/postgres.ts, parsers/mysql.ts, parsers/sqlite.ts — Dialect-specific EXPLAIN parsing
• index.ts — Main exports

Privacy & Security

• Privacy Mode (default): Literals are replaced with tokens before analysis
• Opt-in Raw SQL: To send unmodified SQL to LLM, set privacy_mode: false
• No Execution: Engine never executes SQL; it only parses and analyzes
• Deterministic: All operations produce consistent output for same input

Usage in API/SDK

import { normalizeSQL, sanitizeSQL, fingerprint } from '@sql-playground/engine';

// In API request handler:
const { sql } = req.body;

// Optional: sanitize before LLM
const sanitized = sanitizeSQL(sql, { enabled: true });

// Generate cache key
const fp = fingerprint(sql);
const cacheKey = `explain:${fp.hash}`;

// Proceed with analysis...

Contributing

See CONTRIBUTING.md for guidelines on adding new dialects or features.

License

MIT

Support

Issues and PRs welcome at: https://github.com/sql-playground/sql-playground-engine