sql-insight

Files (3)

• LICENSE
• SKILL.md
• sql_query_helper.py · scripts/

SKILL.md (first lines)

# 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

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

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

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

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

From zebbern/claude-code-guide, MIT.

More from zebbern/claude-code-guide