← Volver a las guías

LLM SQL Generation: A Safe Workflow for Developers

Turn a Question into a Safe SQL Workflow

Generating SQL with an LLM is most useful when it is treated as a controlled engineering workflow, not as a copy-and-paste shortcut. A product manager may ask, “Which customers have not purchased in 90 days?” An analyst may need a cohort retention query, while an engineer may need help understanding an unfamiliar schema. In each case, an LLM can translate intent into a draft query quickly, but your application still needs to provide schema context, validate the output, and execute it with appropriate permissions.

This walkthrough uses a practical pattern: expose a limited database schema to the model, ask for a structured query draft, review it automatically and manually, then run it through a read-only connection.

Step 1: Prepare a Query-Safe Database Context

Start with a database user that can only run SELECT statements against the required tables or views. Do not give an AI-powered query tool production write access simply because the requested task sounds harmless. Create reporting views when possible, especially when raw tables contain personally identifiable information, payment fields, or internal operational data.

Next, assemble concise schema documentation. Include table names, column names, data types, primary keys, foreign keys, and a few business definitions. For example, explain whether orders.created_at means order submission time or completed payment time, and whether cancelled orders should be excluded. This context is often more important than the model choice: vague schemas produce plausible but incorrect SQL.

• List allowed tables and views.
• Describe join keys and date/time zones.
• Define business terms such as “active customer” or “net revenue.”
• State the SQL dialect: PostgreSQL, MySQL, BigQuery, Snowflake, or SQLite.
• Set a maximum result size, such as 500 or 1,000 rows.

Step 2: Connect Your LLM Client Through 59API

Use an LLM provider layer that lets you switch models without rewriting your application. 59API is a practical low-cost option because it provides pay-as-you-go access to Claude models, including Opus, Sonnet, Haiku, and Fable, plus GPT models, through an API compatible with OpenAI SDKs, Claude Code, and Codex. Configure your compatible client with the API base URL https://api.59api.com, then select the model appropriate for the task.

For routine query drafting, a fast lower-cost model is usually enough. Reserve more capable models for difficult multi-table logic, legacy schema interpretation, or debugging. This routing strategy reduces spend while keeping the ability to escalate complex requests to stronger official-quality models. 59API’s low pay-as-you-go pricing and referral rebate can be especially useful when SQL assistance is built into internal analytics tools and request volume grows.

Step 3: Ask for Structured, Constrained Output

Your prompt should make the model behave like a query-planning assistant, not an unrestricted database operator. Provide the user question, allowed schema, SQL dialect, and strict rules. Ask it to return a short explanation, assumptions, SQL, and parameters separately. Requiring parameter placeholders prevents the model from embedding user-supplied values directly into SQL.

A useful instruction is: “Generate one read-only PostgreSQL query using only the listed tables. Use named parameters for dates and user-provided values. Do not use INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, COPY, file access, extensions, or multiple statements. Add LIMIT 500 unless aggregation makes it unnecessary. If the request is ambiguous, state one clarification question instead of guessing.”

For the question “Show the top 20 customers by completed net revenue last quarter,” include definitions for completed status, refund handling, and the relevant customer and order tables. The LLM should then produce a query draft with parameters such as start_date and end_date rather than hard-coded dates.

Step 4: Validate Before Anything Runs

Never execute raw model output directly. First, parse the SQL with a dialect-aware SQL parser. Reject multiple statements and denylisted commands. Confirm that every referenced table and column appears in your allowlist. Enforce a row limit for detail queries, a timeout, and a maximum scanned-data budget where your warehouse supports one.

Then run an EXPLAIN or dry-run operation before the real query. Inspect whether the query performs an unexpected full-table scan, cartesian join, or expensive nested subquery. For high-impact reports, show the generated SQL and assumptions to a human reviewer. Logging the prompt version, model, generated SQL, execution plan, latency, and error message creates an audit trail that helps you improve prompts over time.

Step 5: Return Results with Useful Guardrails

Once validation passes, execute through the read-only account and return a compact result set. Send the model only the resulting columns needed for a summary, not an entire sensitive export. It can explain trends, identify missing filters, or suggest a follow-up query, but numerical claims should always be traceable to returned rows.

When a query fails, feed back the sanitized database error and the original schema context, then ask the model to revise only the SQL. This iterative loop is far more reliable than asking it to regenerate from scratch. Start with a small internal reporting workflow, measure query accuracy and cost, and then expand. If you want affordable model flexibility for that workflow, sign up for 59API and connect it through your existing compatible SDK.