← Voltar aos guias

LLM SQL Generation: Fix Bad Queries and Wrong Results

Generating SQL with LLMs can speed up reporting, but a query that runs can still answer the wrong question. Reliable results depend on explicit business definitions, accurate schema context, and validation outside the model. Use this troubleshooting and FAQ guide to turn natural language requests into queries you can check.

What should you include in a SQL generation prompt?

Provide the database engine and version, relevant tables, column types, primary and foreign keys, and the intended output. Describe business rules that column names cannot explain: whether revenue includes refunds, which statuses qualify, and which timezone defines a reporting day.

• Limit scope: Send the relevant schema subset, including relationships needed for joins.
• Define the grain: Specify whether each output row represents a customer, order, product, or day.
• Expose uncertainty: Tell the model to ask about missing definitions before generating SQL.
• Separate outputs: Request SQL, parameter names, and assumptions as distinct fields when your API supports structured output.

What does a useful prompt and query look like?

For example: “Use PostgreSQL. Tables: customers(id primary key, country text); orders(id primary key, customer_id references customers.id, created_at timestamptz, status text); order_items(order_id references orders.id, quantity integer, unit_price numeric). All prices are USD. Return the top 10 countries by gross item revenue for paid orders created in January 2026, using UTC boundaries. Revenue means quantity multiplied by unit_price, before refunds, tax, and shipping.”

A suitable query is:

SELECT c.country, SUM(oi.quantity * oi.unit_price) AS gross_item_revenue_usd FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id WHERE o.status = 'paid' AND o.created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00' AND o.created_at < TIMESTAMPTZ '2026-02-01 00:00:00+00' GROUP BY c.country ORDER BY gross_item_revenue_usd DESC LIMIT 10;

The exclusive February boundary includes every January timestamp without depending on fractional-second precision. The inner joins also mean orders without matching customers or items contribute nothing; confirm that matches your reporting rules.

Why does generated SQL reference nonexistent columns?

The model may be filling gaps with familiar names such as customer_name or total_amount. Supply authoritative schema metadata and explicitly prohibit inventing identifiers. When a query fails, return the database error alongside the relevant schema. Ask for a corrected query and an explanation of the changed identifiers; cap repair attempts so an automated loop cannot run indefinitely.

Why are totals inflated even though the query runs?

Check join cardinality. Joining an order with three items and two payment records produces six rows. Summing item revenue then doubles it. Aggregate each child table to one row per order before joining, or use EXISTS when you only need to test whether a related record exists. Compare totals before and after each join. SUM(DISTINCT amount) is usually an incorrect repair because different purchases can share the same amount.

How do you validate and execute queries safely?

Parse generated SQL with a dialect-aware parser, restrict accessible tables and functions, and execute through a database role with narrowly scoped read permissions. A SELECT prefix alone is insufficient protection. Bind user-supplied filter values as parameters. Review EXPLAIN first, set statement timeouts, and test against small fixtures with known results. LIMIT restricts returned rows but may still require an expensive scan or aggregation.

Which model and API setup should you use?

Evaluate GPT-6 Astra and DeepSeek V4 Pro against the same representative questions. Score execution success, result correctness, latency, and cost per accepted answer; include retries in the cost calculation.

59API offers low-cost, pay-as-you-go access to native official-quality models without downgrades, with an API base URL of https://api.59api.com. Its compatibility with OpenAI SDKs, Claude Code, Codex, and Gemini CLI makes it convenient to test models within existing workflows. Use its documented client configuration and exact API model identifiers. If you are building a text-to-SQL prototype, consider signing up for 59API and benchmarking a small question set before scaling usage.

Does this approach work for other data queries?

Yes. For document databases, search engines, or dataframe expressions, supply the target query language, field types, and representative document structure. Keep the same checks: valid syntax, correct business meaning, bounded execution, and results verified against known examples.

Preços · Docs