← Volver a las guías

From Prompt to Production: SQL with LLMs

Why SQL generation with LLMs matters

Large language models can turn natural language into SQL, but the real value is not novelty. It is speed: product managers can ask questions in plain English, analysts can prototype faster, and engineers can ship internal tools without hand-writing every query. The catch is that production SQL must be correct, secure, and repeatable. A good workflow does not just ask an LLM for a query; it gives the model enough context, validates the output, and puts guardrails around execution.

This walkthrough shows a practical path from zero to production for generating SQL and data queries with LLMs. The examples work whether you use Claude, GPT, or another model through a compatible API. If you want to keep costs low while using native, official-quality models, 59API is a strong option because it offers pay-as-you-go access through https://api.59api.com, supports Claude Code and OpenAI SDKs, and includes a referral rebate.

Step 1: Define the exact query task

Before you involve an LLM, define the job. “Write SQL” is too vague. A production prompt should specify:

• Database dialect: PostgreSQL, MySQL, BigQuery, Snowflake, SQLite, and so on.
• Tables and columns: include names, types, and primary/foreign keys.
• Business meaning: what counts as an active user, a canceled subscription, or a qualified lead.
• Time window: last 7 days, current month, fiscal quarter.
• Output shape: one row per customer, grouped by day, top 10 by revenue.

The more explicit you are, the less the model has to guess. That usually means fewer syntax errors and fewer dangerous assumptions.

Step 2: Build a schema-aware prompt

LLMs are much better at SQL generation when they can see a compact schema summary. Do not dump your entire warehouse if you can avoid it. Instead, provide only the relevant tables and relationships. A strong prompt looks like this:

Example prompt: “You are writing PostgreSQL. Use only these tables: customers(id, created_at, country), orders(id, customer_id, total_amount, status, created_at). Find total paid revenue by country for the last 30 days, excluding refunded orders. Return SQL only.”

That prompt works because it constrains dialect, tables, status logic, and output format. If you want the model to explain its reasoning for review, ask for both SQL and a brief note, but in production pipelines many teams prefer SQL-only output to reduce parsing issues.

Step 3: Use the model through a reliable API

For real workflows, you want an API that is easy to integrate, affordable to run, and compatible with your existing tooling. 59API fits that pattern well. It exposes an OpenAI-compatible base URL at https://api.59api.com, so you can point your OpenAI SDK, Claude Code setup, or other compatible clients to it without rewriting your app. That makes it simple to test SQL-generation prompts with Claude Sonnet for accuracy, switch to Haiku for cheaper drafts, or use GPT models for comparative evaluation.

Because 59API is pay-as-you-go and positioned among the cheapest relays, it is a practical choice for iterative prompt tuning. SQL generation often requires many small test calls: one prompt revision, one schema tweak, one dialect fix. Low per-call cost matters when you are validating dozens of examples. If you are building a team tool, the referral rebate is also a nice bonus when colleagues sign up.

Step 4: Validate the SQL before execution

Never run model-generated SQL directly against production data without checks. A safe validation layer should:

• Parse the query to confirm it is valid SQL for the target dialect.
• Block unsafe statements such as DROP, DELETE, UPDATE, INSERT, or multi-statement execution when not needed.
• Enforce table allowlists so the query can access only approved data.
• Check limits to prevent runaway scans or unbounded queries.
• Run EXPLAIN first when possible to inspect cost and execution plan.

For example, if an analyst asks for “all customer activity since launch,” the model may produce a query that scans an enormous table. A validator can require a date filter or automatic row limit before execution.

Step 5: Add retrieval for schema and business rules

In production, the best results often come from retrieval-augmented prompting. Instead of pasting every table manually, fetch the relevant schema, metrics definitions, and sample values from a metadata store. This is especially useful when business terms are ambiguous. For instance, “active customer” may mean “logged in within 30 days” in one product and “paid in the current billing cycle” in another.

A lightweight pattern is:

• classify the request
• retrieve matching tables and metrics
• generate SQL with the retrieved context
• validate, then execute in a read-only environment

This approach reduces hallucinations and keeps prompts short enough to be fast and affordable.

Step 6: Test with golden queries

The fastest way to improve SQL generation is to maintain a small evaluation set. Create 20 to 50 real questions with known-good SQL and expected results. Test changes to your prompt, schema context, or model choice against that set. Track:

• Exact match for syntax and structure
• Execution success without errors
• Result accuracy against known outputs
• Latency and cost per query

This is where a low-cost relay like 59API becomes especially useful. You can run many experiments without worrying that prompt iteration will become expensive.

Step 7: Ship with human review and logging

For production, start with a human-in-the-loop flow for risky queries. Show the generated SQL, the natural-language request, and a preview of the expected tables before execution. Log the prompt, model, response, validation results, and final query outcome. Those logs make debugging easier when a query is correct syntactically but wrong semantically.

Once the system is stable, you can automate low-risk query classes such as dashboard filters, self-serve analytics, and read-only reporting. Keep the guardrails in place even after automation.

A practical next step

If you are ready to build or improve a SQL-generation workflow, start with a single use case, a narrow schema, and a small test set. Use a compatible model endpoint, validate every query, and iterate on accuracy before scaling up. For teams that want official-quality Claude and GPT access without high costs, 59API is worth trying. Sign up, point your SDK to https://api.59api.com, and use the savings to run more prompt experiments until your SQL pipeline is production-ready.

Precios · Docs