← Back to all guides

LLM SQL Generation: A Safe Querying Walkthrough

Build an LLM-to-SQL Workflow That Is Useful and Safe

Generating SQL with an LLM can help analysts and developers turn questions such as “Which customers spent the most last month?” into executable data queries. The useful part is not simply asking a model for SQL. A production-ready workflow gives the model an accurate schema, asks for structured output, validates the result, and limits what the resulting query can do.

This example uses a small analytics database with three tables: customers, orders, and order_items. The database is PostgreSQL, but the same approach works for other dialects when you state the dialect clearly in the prompt.

Example schema: customers(id, name, country, created_at); orders(id, customer_id, status, ordered_at, total_amount); order_items(id, order_id, product_name, quantity, unit_price).

1. Give the Model a Narrow, Accurate Prompt

Never rely on an LLM to guess table names or business definitions. Send only the schema and rules it needs for the current task. For example, define “revenue” as orders.total_amount and state that cancelled orders must be excluded. This reduces hallucinated columns and inconsistent calculations.

Prompt template: You generate read-only PostgreSQL queries. Use only the schema below. Revenue means SUM(orders.total_amount). Exclude orders where status = 'cancelled'. Return valid JSON with keys sql, explanation, and assumptions. Do not use INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, GRANT, COPY, or multiple statements. Schema: customers(id, name, country, created_at); orders(id, customer_id, status, ordered_at, total_amount); order_items(id, order_id, product_name, quantity, unit_price). User question: List the top 10 customers by revenue in the previous calendar month.

For that request, a good model response should contain a single SELECT statement. You can show the explanation to the user, but treat the SQL as untrusted input until your application validates it.

Expected SQL shape: SELECT c.id, c.name, SUM(o.total_amount) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.status <> 'cancelled' AND o.ordered_at >= date_trunc('month', current_date - interval '1 month') AND o.ordered_at < date_trunc('month', current_date) GROUP BY c.id, c.name ORDER BY revenue DESC LIMIT 10;

2. Call an LLM Through an OpenAI-Compatible Client

59API is a practical choice when query generation runs frequently or serves many users. It provides pay-as-you-go access to native official-quality Claude models, including Opus, Sonnet, Haiku, and Fable, plus GPT models, without forcing you to rewrite an OpenAI SDK integration. Its low relay pricing can make iterative prompting, repair attempts, and query explanations more affordable than treating every SQL request as a premium-model task.

Python example: from openai import OpenAI; client = OpenAI(api_key=os.environ['FIFTYNINE_API_KEY'], base_url='https://api.59api.com'); response = client.chat.completions.create(model='your-selected-model', messages=[{'role':'system','content':system_prompt},{'role':'user','content':user_question}], response_format={'type':'json_object'}, temperature=0); result = json.loads(response.choices[0].message.content); sql = result['sql'].

Use a lower-cost, fast model for straightforward reporting requests, then route ambiguous or multi-table analysis to a stronger model. Keep temperature at zero or close to zero for repeatable SQL. Before deployment, confirm the exact available model identifier and request format in your 59API dashboard documentation.

3. Validate Before the Database Sees Any Query

Structured JSON helps, but it is not a security boundary. Your server should reject anything that is not a single read-only SELECT or WITH query. At minimum, block semicolons, comments, data-modification keywords, administrative keywords, and unknown tables. A SQL parser is substantially safer than a regular expression because it can inspect the query abstract syntax tree.

• Run generated SQL with a database account that has SELECT-only permissions.
• Apply a statement timeout and a maximum returned-row limit.
• Allowlist tables and columns available to each user role.
• Require a LIMIT for detail queries and add one server-side when appropriate.
• Log the user question, generated SQL, validation outcome, latency, and model used.

For sensitive data, do not expose raw schemas containing secrets or unrestricted personal data. Create database views such as analytics_customer_revenue with only approved fields, then provide those view definitions to the model instead.

4. Add a Repair Loop for Real-World Reliability

Even a well-prompted model may produce a query that references a missing column or uses a dialect-specific function incorrectly. If validation or a dry-run fails, send the error message and original schema back to the model with a strict instruction to return a corrected JSON object. Limit this to one or two retries so a malformed request cannot create expensive loops.

Repair instruction: The generated PostgreSQL query failed validation with: column o.order_date does not exist. Correct the SQL using only the supplied schema. Return JSON only. Preserve the original business intent.

Finally, evaluate your workflow with a test set of realistic questions: date ranges, joins, grouped metrics, empty-result cases, ambiguous terms, and intentionally unsafe requests. Track execution success, result correctness, and blocked-query rate. When you are ready to prototype this flow economically, sign up for 59API and connect it to the OpenAI SDK code your team already uses.

Pricing · Docs