npm package: @gabrudj/sql-engine
Description: Core SQL analysis engine with deterministic parsing, heuristics, and optimization suggestions
Author: SQL Playground Contributors
Latest version: 0.1.2

# SQL Playground Engine

[![CI](https://github.com/sql-playground/sql-playground-engine/workflows/CI/badge.svg)](https://github.com/sql-playground/sql-playground-engine/actions)

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

```bash
npm install @sql-playground/engine
```

## Quick Start

```typescript
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 = 'john@example.com'");
console.log(sanitized.sanitized);
// => "SELECT * FROM users WHERE email = <str_0>"
```

## API

### Core Functions

#### `normalizeSQL(sql, options?)`

Normalize SQL to canonical form.

```typescript
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.

```typescript
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).

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

### Sanitization

#### `sanitizeSQL(sql, config?)`

Replace literals with tokens for privacy.

```typescript
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.

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

#### `extractLiterals(sql)`

Extract all literals without sanitizing.

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

### EXPLAIN Parsing

#### PostgreSQL

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

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

#### MySQL

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

#### SQLite

```typescript
function parseSqliteExplainQueryPlan(explainText: string): ExplainNode;
```

### Heuristics

#### `analyzeExplainPlan(plan, tableName?)`

Analyze EXPLAIN plan for performance issues.

```typescript
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.

```typescript
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:

```typescript
// 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

```bash
npm install
```

### Build

```bash
npm run build
```

### Test

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

### Lint & Format

```bash
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

```typescript
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](./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