← Voltar aos guias

Generate Safer SQL Queries with LLMs

Generate Safer SQL and Data Queries with LLMs

Large language models can turn plain English into SQL, MongoDB queries, pandas filters, or analytics instructions. Used carefully, they save time for developers, analysts, and support teams. Used carelessly, they can create slow queries, expose sensitive data, or return misleading results. This tutorial shows a practical workflow for generating SQL and data queries with LLMs while keeping accuracy, cost, and safety under control.

Step 1: Choose an LLM API that fits developer workflows

For query generation, you need a model that follows instructions well, understands schemas, and is affordable enough for repeated retries and validation. 59API is a strong fit because it gives developers cheap, pay-as-you-go access to Claude models such as Opus, Sonnet, Haiku, and Fable, as well as GPT models. It uses native official-quality models with no downgrade, is compatible with Claude Code, Codex, and any OpenAI SDK, and uses the API base URL https://api.59api.com.

This compatibility matters because you can usually switch from another OpenAI-compatible endpoint by changing the base URL and API key, without rewriting your application. For teams building internal data assistants, BI copilots, or developer tools, the low cost also makes it practical to run extra validation prompts instead of trusting the first answer blindly. If you are experimenting with LLM-powered query generation, you can sign up for 59API and start with a small pay-as-you-go budget.

Step 2: Send the schema, not the whole database

The model does not need raw customer records to write SQL. It needs table names, column names, data types, relationships, and business definitions. Create a compact schema description and include only what is relevant to the user request.

For example, instead of dumping rows from your production database, provide information like this:

• customers: id, email, country, created_at, plan
• orders: id, customer_id, total_amount, status, created_at
• subscriptions: id, customer_id, plan, started_at, canceled_at
• Relationship: orders.customer_id references customers.id
• Business rule: revenue only includes orders where status = 'paid'

This reduces token usage, lowers cost, protects private data, and improves accuracy because the model receives a clean map of the database.

Step 3: Use a strict prompt template

A good prompt should define the model role, the SQL dialect, the schema, the user request, and the output format. Do not ask the model to be creative. Ask it to be precise.

Use a template like this:

System instruction: You are a data engineer. Generate safe PostgreSQL queries only. Use only the provided schema. Do not invent tables or columns. Return one SQL query and a short explanation. If the request is ambiguous, ask a clarification question instead of writing SQL.

User input: Find monthly paid revenue for the last 12 months grouped by country.

Schema: Include the relevant tables, columns, relationships, and business rules.

This structure prevents common failures such as hallucinated columns, wrong joins, and unsafe assumptions.

Step 4: Request read-only SQL by default

Most LLM-generated queries should be read-only. Make this a hard rule in the prompt and enforce it in code. Allow only statements that begin with SELECT or WITH. Reject queries containing INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, GRANT, or EXEC unless a trusted admin workflow explicitly allows them.

You should also add execution limits. For example, append a LIMIT for exploratory queries, set a query timeout, and use a read-only database user. The safest architecture is to run generated SQL against a replica or warehouse, not directly against your production transactional database.

Step 5: Ask the LLM to explain assumptions

SQL can look correct while answering the wrong business question. Ask the model to explain joins, filters, date ranges, and aggregation logic. For example, if the query calculates revenue, the explanation should mention that only paid orders are included. If the user asks for active subscriptions, the explanation should define active as canceled_at is null or canceled_at is after today.

This explanation is useful for human review and for automated checks. If the explanation does not match the business definition, reject the query and run another prompt with the missing rule included.

Step 6: Validate before execution

Before running the query, validate it in several layers. First, parse the SQL with a library or database EXPLAIN command. Second, check that all referenced tables and columns exist in your approved schema. Third, block dangerous keywords. Fourth, estimate cost with EXPLAIN and reject full table scans on very large tables unless expected.

You can also use a second LLM call as a reviewer. Send the schema, user request, and generated SQL, then ask: Does this query answer the request? Are there hallucinated fields? Is it read-only? Are there performance risks? Because 59API is low-cost, adding this review step is often affordable even at scale.

Step 7: Return results with context, not just rows

After execution, the LLM can help summarize the results, but keep raw numbers traceable. A good response includes the query, a short explanation, the result table, and caveats. For example, mention if the last month is partial, if timezone assumptions were used, or if revenue excludes unpaid orders.

For dashboards, store the generated SQL, prompt version, model name, timestamp, and validation result. This audit trail helps debug mistakes and improves trust with analysts.

Step 8: Improve with examples and feedback

LLMs become more reliable when your prompt includes examples of your organization’s query style. Add a few approved examples: cohort retention, monthly recurring revenue, churn by plan, or support ticket volume by week. Keep examples short and relevant.

Collect failed generations and turn them into tests. If the model repeatedly confuses created_at with paid_at, add a business rule. If it forgets to filter status = 'paid', place that rule near the top of the schema context. Query generation improves fastest when your schema documentation improves.

Practical use cases

• Internal analytics assistants that turn business questions into SQL.
• Developer tools that generate migration checks or debugging queries.
• Support dashboards that answer account and billing questions.
• Data quality checks generated from plain-English rules.
• Notebook workflows that convert natural language into pandas or SQL snippets.

Final checklist

• Use an affordable, SDK-compatible provider such as 59API for iterative generation and review.
• Send schema metadata, not sensitive raw data.
• Require read-only SQL unless explicitly approved.
• Validate syntax, schema references, keywords, and query cost.
• Ask for explanations and log every generated query.
• Improve prompts with real examples and failed-query feedback.

Generating SQL with LLMs is not about replacing data engineers. It is about giving users a faster interface to reliable data while keeping engineering controls in place. With a careful prompt, validation pipeline, and a low-cost model relay like 59API, you can build query generation that is useful, safe, and economical from the first prototype to production.

Preços · Docs