# SQL Expert

> Expert guidance for generating accurate, performant, and secure SQL queries. Focuses on schema-first prompting, index awareness, and handling complex joins/aggregations.

- Skill: `dox69/sql-expert` (Agent Skill)
- Install (CLI): `npx skillmds@latest add dox69/sql-expert`
- Raw SKILL.md: https://api.skillmd.com/api/skills/dox69/sql-expert/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: dox69 (https://skillmd.com/u/dox69)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/dox69/sql-expert

---


# SQL Expert Patterns

## Overview

This skill provides professional mental models and patterns for **Text-to-SQL generation**. It ensures that AI agents generate SQL that is not only syntactically correct but also performant, secure, and aligned with the actual database schema.

## When to Use

- **Data Retrieval Agents**: When building agents that answer questions from a database.
- **Complex Reporting**: Generating queries involving multiple joins, window functions, or CTEs.
- **Schema Migration**: Writing DDL statements to modify database structure.
- **Performance Tuning**: Optimizing slow queries generated by ORMs or other tools.

## The Mental Shift: Natural Language vs. Relational Logic

| Feature | Natural Language | Relational Logic (SQL) |
| :--- | :--- | :--- |
| **Ambiguity** | High ("Get the best users") | Zero (Specific columns, specific criteria) |
| **Context** | Implicit | **Explicit** (Must know table/column names) |
| **Operations** | Descriptive | **Set-based** (Joins, Unions, Intersections) |
| **Order** | Chronological | **Logical** (FROM -> WHERE -> GROUP BY -> SELECT) |

## Core Design Patterns

### 1. Schema-First Context

An agent cannot guess your schema. You must provide it explicitly.

**Pattern:**
Always include a compact schema definition in the prompt.
-   **Table Names** & **Column Names** (Crucial)
-   **Data Types** (Helps with quoting and casting)
-   **Foreign Keys** (Crucial for correct JOINs)

**Example Prompt Fragment:**
```text
Context:
You are an expert SQL agent. Use the following schema:
- users (id INT PK, email VARCHAR, created_at TIMESTAMP)
- orders (id INT PK, user_id INT FK, total DECIMAL, status VARCHAR)
- order_items (id INT PK, order_id INT FK, product_id INT)
```

### 2. Constraint-Based Prompting

Prevent common LLM mistakes by setting strict constraints.

**Pattern:**
-   **No Markdown:** "Return only the raw SQL query. No markdown formatting."
-   **No Hallucination:** "Do not use columns that are not in the provided schema."
-   **Standard SQL:** "Use standard ANSI SQL unless specified otherwise (e.g., PostgreSQL specific)."

### 3. Performance Awareness (Index Usage)

Agents often write "correct" SQL that kills database performance.

**Guidelines:**
-   **Avoid `SELECT *`**: Always specify columns. `SELECT *` breaks index coverage and wastes bandwidth.
-   **SARGable Queries**: Ensure `WHERE` clauses can use indexes.
    -   *Bad:* `WHERE YEAR(created_at) = 2023` (Function on column prevents index use)
    -   *Good:* `WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'`
-   **JOIN Efficiency**: Prefer `INNER JOIN` over `LEFT JOIN` unless missing data is explicitly required.

### 4. Advanced Features (CTEs & Window Functions)

For complex logic, avoid deep nesting of subqueries.

**Pattern:** Use Common Table Expressions (CTEs) for readability.

*Bad (Nested):*
```sql
SELECT * FROM (SELECT user_id, count(*) as c FROM orders GROUP BY user_id) WHERE c > 5
```

*Good (CTE):*
```sql
WITH user_order_counts AS (
    SELECT user_id, count(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_order_counts WHERE order_count > 5
```

### 5. Self-Correction Loop

If an executed query fails, the agent must treat the error message as feedback.

**Pattern:**
1.  **Execute** generated SQL.
2.  **Catch** Error (e.g., "Column 'usr_id' does not exist").
3.  **Re-prompt** with: "The previous query failed with error: [Error]. Fix the query using the schema provided."

## Expert Checklist

Before executing generated SQL:

-   [ ] **Schema Validation:** Do all tables and columns exist in the context?
-   [ ] **Type Safety:** Are strings quoted? Are dates in the correct format?
-   [ ] **Injection Safety:** Are user inputs parameterized (if running in an app) or sanitized?
-   [ ] **Logic Check:** Does the `GROUP BY` include all non-aggregated columns?

## Common Anti-Patterns

❌ **Ambiguous Joins:** Joining tables without specifying the ON clause (implicit cross join).
❌ **Recursive CTEs without Limit:** Causing infinite loops.
❌ **Mixing Dialects:** Using `ILIKE` (Postgres) on MySQL (requires `LIKE`).