DLH.io SQL Optimizer

AI-powered SQL formatter, optimizer, and explainer for VS Code. Format across 20+ dialects, rewrite with your favourite LLM, and read plain-English explanations — all without leaving the editor.

Highlights

Format SQL across 20+ dialects with full control over casing, indentation, comma position, expression width, and more.
AI Optimize — rewrite queries for performance and clarity. Choose between Anthropic, OpenAI, Google Gemini, local Ollama, or a rule-based fallback. Results open in a side-by-side diff with Apply / Show Explanation / Dismiss.
AI Explain — plain-English explanation of any query, rendered as Markdown beside the editor.
DLH default optimization prompt (v1.2.0+) — opinionated warehouse-aware system prompt (predicate pushdown, CTEs over correlated subqueries, explicit JOINs, partition/cluster-key awareness, etc.) baked in as the default. Override or extend it per-workspace from the sidebar Settings tab, and quick-toggle it on/off from the Quick tab or the Formatter Panel.
Dedicated sidebar (activity bar icon) with Quick / Explain / History / Settings tabs.
Schema context — paste your CREATE TABLE statements once per workspace; every AI prompt silently ships them as context so the model knows your tables.
Per-provider model memory — pick claude-sonnet-4 for Anthropic, gpt-4o-mini for OpenAI, llama3.1 for Ollama; each is remembered per provider.
CodeLens actions above every query — Format, AI Optimize, AI Explain. Customisable (style, order, which actions show).
Works on embedded SQL in TypeScript, JavaScript, Python, and Java strings — keeps your template-literal / triple-quote / backtick wrappers intact when applying changes.
VS Code theme-aware — respects light, dark, and high-contrast themes everywhere.

SQL Formatter Panel

Features

AI Optimize

Rewrite a query with an LLM. Click $(sparkle) AI Optimize above any SQL statement, and:

The query is formatted locally first (so the AI sees clean input).
The formatted SQL is sent to your configured provider with features: ['rewrite'].
A side-by-side diff opens: original on the left, AI rewrite on the right.
A toast offers Apply / Show Explanation / Dismiss. The diff editor's title bar also has a persistent $(check) Apply and $(x) Dismiss button, so you don't have to catch the toast.
Apply writes the rewrite back to the original file — inside template literals / triple-quotes / backtick strings for embedded SQL.

Works on standalone .sql files and embedded SQL in other languages (TypeScript, JavaScript, Python, Java).

AI Explain

Get a Markdown explanation of what a query does.

Click $(comment-discussion) AI Explain above any SQL statement. A Markdown preview opens beside the editor with:

The original query in a fenced code block.
A plain-English explanation.
Any optimization notes the model flagged.

Click the DLH SQL icon in the activity bar (left rail) to open the AI Assistant view. Four tabs:

Tab	What it does
Quick	Paste a query (multi-line + multiple queries supported, with a "N queries detected" hint), pick a dialect, run Format / AI Optimize / AI Explain inline. `Ctrl+Enter` triggers the primary action. ↗ button opens the Formatter Panel prefilled with the current query. Pill toggle quick-flips the custom optimization prompt on/off.
Explain	The latest AI output auto-focuses here on every new AI run. A "Showing: latest result · View history →" / "Showing: history entry · Back to latest →" strip keeps context clear. Provider, model, dialect, and timestamp metadata included.
History	Last 20 AI calls — click any entry to load it back into the Explain tab. Cleared via a button or persisted across sessions automatically.
Settings	AI provider, per-provider model, API key (stored in `SecretStorage`), Ollama base URL, workspace schema context, and the optimization prompt override.

The sidebar is narrow-width aware: tabs collapse to icons below ~260 px, button rows stack on very narrow widths, long labels truncate with ellipsis.

Schema context

Under Settings → Schema context (in the sidebar), paste your schema — CREATE TABLE statements, column lists, foreign keys, whatever the AI needs to produce meaningful rewrites. It's workspace-scoped and automatically prepended as a commented block to every AI Optimize / AI Explain prompt:

-- DATABASE SCHEMA (context for this query):
-- CREATE TABLE users (id INT PRIMARY KEY, email TEXT, ...);
-- CREATE TABLE orders (id INT, user_id INT, ...);

SELECT u.email, COUNT(*) FROM orders o JOIN users u ON u.id = o.user_id GROUP BY u.email;

Badge in the Quick tab shows $(check) schema on or schema off at a glance.

Optimization prompt (DLH default + override)

Every AI Optimize / AI Explain call uses DLH's opinionated rewrite system prompt by default. It encodes warehouse-aware heuristics — push predicates down, project only the columns you need, prefer CTEs over correlated subqueries, use explicit JOIN syntax, leverage partition / cluster / sort keys (Snowflake clustering, BigQuery _PARTITIONTIME, Databricks Z-ORDER, Redshift DISTKEY/SORTKEY), and only suggest semantics-preserving rewrites.

The prompt lives in the @dlh.io/dlh-sql-formatter library and can be overridden per workspace:

Sidebar → Settings → Optimization prompt card

Expand View DLH default prompt to see the full baked-in prompt.
Check Override default prompt to enable override:
- Extend DLH default — your text is appended under "Additional guidance:\n" (your rules layer on top of the DLH philosophy).
- Replace entirely — your text becomes the full system prompt. You own the JSON response contract ({ sql, explanation, optimizations[] }).
Reset to default wipes your custom text and returns to the DLH default.

Quick on/off toggle — once you've saved a custom prompt, a pill toggle shows up in two places:

Sidebar Quick tab toolbar, next to the schema badge.
Formatter Panel (Ctrl+Shift+Q), in the AI Actions row.

Flipping either toggle switches the override on/off without wiping the saved text or mode — handy for A/B testing your own prompt against the DLH default on the same query.

CodeLens actions

Above each detected SQL statement:

CodeLens Buttons

$(file-code) Format — format locally, no network call.
$(sparkle) AI Optimize — rewrite with the configured AI provider.
$(comment-discussion) AI Explain — explain in plain English.

Customisable:

dlh-sql-formatter.codeLens.style — icon (default, VSCode codicons), emoji, or text.
dlh-sql-formatter.codeLens.actions — array of "format" | "optimize" | "explain". Reorder to change display order, remove entries to hide actions.
dlh-sql-formatter.enableCodeLens — master on/off switch.

Works on:

SQL files: .sql, .pgsql, .psql, .mysql, .ddl, .dml, .hql, plus language IDs like sql, mysql, postgresql, snowflake, clickhouse, databricks, etc.
Embedded SQL in .ts, .js, .py, .java — inside template literals, triple-quoted strings, and regular string literals.

Formatter Panel

Open with Ctrl+Shift+Q (Cmd+Shift+Q on Mac). A full-width panel with:

Input — paste SQL here.
Formatted tab — the formatted output + Copy / Insert into Editor buttons.
AI Output tab — rich rendering of the latest AI call's explanation and optimizations.
AI Actions row — AI Optimize, AI Explain, Configure.
Format Options — collapsible card with all 14 formatting toggles.

Configure your AI provider

Three routes, all wired to the same SecretStorage:

Sidebar → Settings tab (recommended).
Command palette → DLH SQL: Configure AI Provider (QuickPick — provider, then key prompt).
VS Code settings directly — dlh-sql-formatter.ai.provider and related keys.

Providers

Provider	Needs API key	Default model (if none set)
Anthropic (Claude)	yes	`claude-sonnet-4-20250514`
OpenAI (GPT)	yes	per library — GPT-4 family
Gemini (Google)	yes	`gemini-2.0-flash`
Ollama (local)	no	`llama3`
Rule-based (local, static analysis)	no	—

API keys are stored in VS Code SecretStorage, never in settings.json.

For Ollama: make sure ollama serve is running locally. Configure the endpoint with dlh-sql-formatter.ai.ollamaBaseUrl (default http://localhost:11434).

Per-provider model memory

Pick a model once per provider; it sticks. Switch providers and the model input repopulates with that provider's saved choice (or the library's default as placeholder text). Stored under dlh-sql-formatter.ai.models as a map:

{
  "dlh-sql-formatter.ai.models": {
    "anthropic": "claude-opus-4-20250514",
    "openai": "gpt-4o-mini",
    "ollama": "llama3.1"
  }
}

The legacy dlh-sql-formatter.ai.model (single global string) is deprecated but still read as a fallback.

Supported SQL dialects

BigQuery, ClickHouse, Databricks, DB2, DB2i, DuckDB, Hive, MariaDB, MySQL, N1QL (Couchbase), PL/SQL (Oracle), PostgreSQL, Amazon Redshift, SingleStoreDB, Snowflake, Spark SQL, SQLite, Standard SQL, TiDB, Transact-SQL (SQL Server), Trino.

Dialect auto-detection kicks in from file extensions and VS Code's languageId. You can override via the Dialect dropdown in the sidebar Quick tab or dlh-sql-formatter.language setting.

Keyboard shortcuts

Shortcut	Command
`Ctrl+Shift+Q` / `Cmd+Shift+Q`	Open SQL Formatter Panel
`Ctrl+Alt+D` / `Cmd+Alt+D`	Open SQL Formatter Panel (alias, handy from any file)
`Ctrl+Shift+F` / `Cmd+Shift+F`	Format SQL (in SQL files)
`Ctrl+Enter` in the sidebar Quick input	Run primary action — AI Optimize if a provider is configured, otherwise local Format

Commands

Command	Category
`DLH SQL: Open SQL Formatter Panel`	Panel
`DLH SQL: Open AI Assistant in Editor Panel`	Panel (alias — same target as Open SQL Formatter Panel)
`DLH SQL: Format SQL Query`	Format
`DLH SQL: Format Each SQL Query in File`	Format
`DLH SQL: Convert SQL to Multi-line`	Format
`DLH SQL: Convert SQL to Single-line`	Format
`DLH SQL: Optimize SQL Query`	AI
`DLH SQL: Explain SQL Query`	AI
`DLH SQL: Configure AI Provider`	AI
`DLH SQL: Apply Optimization`	AI (while a diff is open)
`DLH SQL: Dismiss Optimization`	AI (while a diff is open)

Settings reference

Most-used settings (all prefixed dlh-sql-formatter.):

Setting	Default	Description
`language`	`sql`	Default SQL dialect
`enableCodeLens`	`true`	Show CodeLens buttons above queries
`codeLens.style`	`icon`	CodeLens icon style: `icon` \| `emoji` \| `text`
`codeLens.actions`	all three	Which CodeLens actions to render; reorderable
`ai.provider`	`rule-based`	`anthropic` \| `openai` \| `gemini` \| `ollama` \| `rule-based`
`ai.models`	`{}`	Per-provider model override map
`ai.ollamaBaseUrl`	`http://localhost:11434`	Ollama server endpoint
`tabWidth`	`2`	Spaces per indent
`useTabs`	`false`	Use tabs instead of spaces
`keywordCase`	`preserve`	`preserve` \| `upper` \| `lower`
`identifierCase`	`preserve`	Same options
`dataTypeCase`	`preserve`	Same options
`functionCase`	`preserve`	Same options
`commaPosition`	`trailing`	`trailing` \| `leading` \| `leadingWithSpace`
`logicalOperatorNewline`	`before`	Place `AND`/`OR` before or after newline
`expressionWidth`	`50`	Max characters before wrapping
`linesBetweenQueries`	`1`	Blank lines between queries
`denseOperators`	`false`	Remove spaces around operators
`newlineBeforeSemicolon`	`false`	Put `;` on its own line
`autoDetectLanguage`	`true`	Auto-detect dialect from file

See Preferences → Settings → Extensions → DLH SQL Optimizer for the full list.

Typical workflows

Format a query

Put the cursor on a query → Ctrl+Shift+F, or click the $(file-code) Format CodeLens.

Ask the AI to rewrite

(First time only) Open the sidebar → Settings → pick a provider, paste your API key (or just pick rule-based / ollama — no key needed).
Click $(sparkle) AI Optimize above the query.
Side-by-side diff opens. Click the $(check) in the diff title bar or pick Apply in the toast.

Explain a query

Same flow, click $(comment-discussion) AI Explain instead. Markdown preview opens beside the editor.

Use local Ollama

ollama serve running, with a model pulled (e.g. ollama pull llama3.1).
Sidebar Settings → Provider = Ollama → set Model override to llama3.1 (or leave blank for llama3).
Click any AI CodeLens.

Sidebar Settings → Schema context → paste your CREATE TABLE statements → Save. Every future AI call includes this as a prompt prefix.

Customize the optimization prompt

Sidebar Settings → Optimization prompt → check Override default prompt → pick Extend (append to DLH default) or Replace (full takeover) → save. Toggle the override on/off any time via the pill switch on the Quick tab or in the Formatter Panel — your saved text persists across toggles.

Installation

From the VS Code Marketplace

Search for "DLH SQL Optimizer" in the Extensions view, or install from marketplace.visualstudio.com/items?itemName=DLH.dlh-sql-optimizer.

From a `.vsix` file

code --install-extension dlh-sql-optimizer-1.1.0.vsix

From source

git clone https://github.com/datalakehouse/dlh-sql-optimizer-vscode.git
cd dlh-sql-optimizer-vscode
npm install

Press F5 in VS Code to launch the Extension Development Host. To build a .vsix:

npm run clean-compile
npx vsce package

Requirements

VS Code 1.75.0 or higher.
Node.js 20+ for development.

Releasing

Maintainers: see docs/RELEASING.md for the release workflow (tag-triggered GitHub Actions build + Marketplace publish).

Support

License

MIT

DLH.io SQL Optimizer

DLH.io

DLH.io SQL Optimizer

Highlights

Features

AI Optimize

AI Explain

Sidebar (activity bar)

Schema context

Optimization prompt (DLH default + override)

CodeLens actions

Formatter Panel

Configure your AI provider

Providers

Per-provider model memory

Supported SQL dialects

Keyboard shortcuts

Commands

Settings reference

Typical workflows

Format a query

Ask the AI to rewrite

Explain a query

Use local Ollama

Share schema with the AI

Customize the optimization prompt

Installation

From the VS Code Marketplace

From a .vsix file

From source

Requirements

Releasing

Support

License

From a `.vsix` file