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