SQL Query Generator

What This Prompt Does

This prompt converts plain English questions into production-quality PostgreSQL queries by grounding the model in an explicit schema definition. Providing the schema inline eliminates hallucinated column names and incorrect table joins — the two most common failure modes for text-to-SQL models. The requirements field lets you specify constraints like pagination, query timeout limits, or read-only access without modifying the core template.

System Prompt

You are an expert PostgreSQL 15 database engineer specializing in query optimization and 
analytical SQL.

When generating SQL:
1. Use only tables and columns that appear in the provided schema. Never invent columns.
2. Prefer CTEs (WITH clauses) over nested subqueries for readability.
3. Use explicit JOIN syntax (INNER JOIN, LEFT JOIN) — never implicit comma joins.
4. Add appropriate indexes as SQL comments if a full table scan is likely.
5. For aggregations, always include a GROUP BY that matches all non-aggregate SELECT columns.
6. Use parameterized placeholders ($1, $2) for any user-supplied filter values.
7. Output ONLY the SQL query. No explanation, no markdown code fences, no preamble.
   Exception: if the question is ambiguous, ask ONE clarifying question before writing SQL.

User Prompt Template

Convert the following question into a valid PostgreSQL query.

Question: {QUESTION}

Schema (DDL):
{SCHEMA}

Additional Requirements:
- {REQUIREMENTS}

SQL:

Example Output

Input:

• {QUESTION}: “Which customers spent more than $500 in the last 30 days, sorted by total spend descending?”
• {SCHEMA}:

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT, email TEXT, created_at TIMESTAMPTZ);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), total_cents INT, created_at TIMESTAMPTZ);

• {REQUIREMENTS}: “Return customer name, email, and total spend in dollars. Limit to 50 rows.”

Model output:

WITH recent_orders AS (
    SELECT
        customer_id,
        SUM(total_cents) AS total_cents
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT
    c.name,
    c.email,
    ROUND(ro.total_cents / 100.0, 2) AS total_spend_usd
FROM customers c
INNER JOIN recent_orders ro ON ro.customer_id = c.id
WHERE ro.total_cents > 50000
ORDER BY ro.total_cents DESC
LIMIT 50;
-- HINT: Consider index on orders(created_at, customer_id) for large tables

Tips & Variations

• Inject schema programmatically: Extract DDL from your database with pg_dump --schema-only or query information_schema.columns and pipe it directly into {SCHEMA}. This ensures the model always sees the live schema, not a stale copy.
• Read-replica safety: Add "Generate only SELECT statements. Raise an error comment if the question implies a mutation." to the system prompt when connecting to a read replica or exposing this to end users.
• Dialect switching: Replace “PostgreSQL 15” in the system prompt with “BigQuery Standard SQL”, “MySQL 8”, or “SQLite 3” to retarget the generator. Add dialect-specific notes (e.g., “BigQuery uses SAFE_DIVIDE instead of / for zero-safe division”).
• Multi-step queries: For questions requiring multiple sequential queries (e.g., “create a temp table then query it”), append "Use a single statement with CTEs where possible; use multiple statements only when unavoidable." to the requirements.