/sql-insight

name: sql-insight
description: "Translate natural language to SQL, optimize query performance, and interpret EXPLAIN plans for SQLite and PostgreSQL. Triggered when users ask to convert questions into SQL, improve slow queries, tune indexes, analyze execution plans, or mention keywords like NL2SQL, query tuning, or full table scan."
license: MIT

sql-insight

SQL query assistant — natural language to SQL translation, query optimization analysis, and EXPLAIN plan interpretation.

Capabilities

| Feature | Description | |---------|-------------| | Schema Extraction | Extracts database table structure (columns, types, indexes, foreign keys, sample data) to provide context for NL→SQL | | Natural Language → SQL | Translates natural language descriptions into SQL queries using schema context | | Query Optimization Analysis | Detects SQL anti-patterns based on 13 rules and provides optimization suggestions | | EXPLAIN Interpretation | Runs EXPLAIN and interprets the query plan, identifying full table scans, missing indexes, and more |

Workflow

Natural Language → SQL

1. Use the `schema` command to extract the database table structure 2. Use the schema as context to translate the user's natural language request into SQL 3. Use the `optimize` command to check if the generated SQL can be improved 4. Use the `explain` command to verify the query execution plan

# Step 1: Extract schema (compact mode, suitable for LLM context)
python3 scripts/sql_query_helper.py --db-path data.db schema --compact

# Step 2: Analyze SQL optimization suggestions
python3 scripts/sql_query_helper.py optimize "SELECT * FROM orders WHERE user_id = 100"

# Step 3: View EXPLAIN execution plan
python3 scripts/sql_query_helper.py --db-path data.db explain "SELECT * FROM orders WHERE user_id = 100"

Quick Start

Schema Extraction

# Extract full schema (JSON format, with sample data)
python3 scripts/sql_query_helper.py --db-path data.db schema

# Compact mode (plain text, suitable for embedding in prompts)
python3 scripts/sql_query_helper.py --db-path data.db schema --compact

# Skip data sampling
python3 scripts/sql_query_helper.py --db-path data.db schema --sample-rows 0

# PostgreSQL
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" schema --compact

Query Optimization Analysis

# Analyze SQL query (no database connection required, pure rule-based detection)
python3 scripts/sql_query_helper.py optimize "SELECT * FROM orders o, users u WHERE o.user_id = u.id"

python3 scripts/sql_query_helper.py optimize "SELECT name FROM users WHERE UPPER(email) LIKE '%@GMAIL.COM'"

python3 scripts/sql_query_helper.py optimize "SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u"

EXPLAIN Interpretation

# SQLite EXPLAIN
python3 scripts/sql_query_helper.py --db-path data.db explain "SELECT * FROM orders WHERE user_id = 100"

# PostgreSQL EXPLAIN
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb" explain "SELECT * FROM orders WHERE user_id = 100"

# PostgreSQL EXPLAIN ANALYZE (actually executes the query for real-world data)
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb" explain --analyze "SELECT * FROM orders WHERE user_id = 100"

Detailed Usage

Global Parameters

| Parameter | Required | Default | Description | |-----------|----------|---------|-------------| | `--db-type` | No | sqlite | Database type: sqlite or postgres | | `--db-path` | For schema/explain (SQLite) | — | SQLite database file path | | `--dsn` | For schema/explain (PostgreSQL) | — | PostgreSQL connection string |

Subcommands

| Command | Requires Database | Description | |---------|-------------------|-------------| | `schema` | Yes | Extract database table structure | | `optimize <sql>` | No | SQL query optimization analysis (pure rule-based detection) | | `explain <sql>` | Yes | Run EXPLAIN and interpret the plan |

schema Parameters

| Parameter | Default | Description | |-----------|---------|-------------| | `--sample-rows, -n` | 3 | Number of sample rows per table (0 to skip sampling) | | `--compact` | false | Compact text output (suitable for embedding in prompts) |

explain Parameters

| Parameter | Default | Description | |-----------|---------|-------------| | `--analyze` | false | Use EXPLAIN ANALYZE (PostgreSQL only; actually executes the query) |

Optimization Rules

The `optimize` command detects the following 13 SQL anti-patterns:

| Rule | Severity | Description | |------|----------|-------------| | avoid-select-star | warning | Avoid SELECT *; explicitly list column names | | unbounded-query | info | Missing WHERE and LIMIT clauses | | leading-wildcard-like | warning | LIKE '%...' causes index to be bypassed | | or-condition | info | OR conditions may prevent index usage | | not-in-subquery | warning | NOT IN (subquery) has poor performance | | scalar-subquery | warning | Scalar subqueries in SELECT execute row-by-row | | function-on-column | warning | Functions on columns in WHERE prevent index usage | | implicit-join | info | Implicit joins (comma-separated tables) are less readable | | distinct-usage | info | DISTINCT may mask JOIN duplication issues | | order-without-limit | info | ORDER BY without LIMIT | | deep-nesting | warning | Deeply nested subqueries | | having-without-group | warning | HAVING without GROUP BY | | not-equal-filter | info | != conditions cannot effectively use indexes |

EXPLAIN Interpretation Items

| Check | Applicable Database | Description | |-------|---------------------|-------------| | Full table scan | SQLite / PostgreSQL | Detects Seq Scan / SCAN TABLE | | Auto temporary index | SQLite | SQLite auto-creates a temporary index, indicating a missing permanent index | | Covering index | SQLite / PostgreSQL | Index contains all queried columns; no