sql-expert

name sql-expert description Expert guidance for generating accurate, performant, and secure SQL queries. Focuses on schema-first prompting, index awareness, and handling complex joins/aggregations. 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: 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): SELECT * FROM ( SELECT user_id, count ( * ) as c FROM orders GROUP BY user_id) WHERE c > 5 Good (CTE): 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: Execute generated SQL. Catch Error (e.g., "Column 'usr_id' does not exist"). 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 ).