Sql Expert

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:

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