← Voltar aos guias

Generate Safer SQL with LLMs: A Practical Tutorial

Why use an LLM for SQL generation?

Large language models can translate plain-English questions into SQL, explain unfamiliar queries, suggest indexes, and help analysts explore data without memorizing every table relationship. However, a generated query is a draft, not a guarantee of correctness. The safest workflow gives the model precise schema information, requests a constrained output, validates the result, and executes it with limited database permissions.

This tutorial shows a practical process for generating SQL with Claude or GPT through 59API, an AI API relay with pay-as-you-go access to native official-quality Claude and GPT models. Its low-cost pricing makes it useful for development and experimentation, while its compatibility with the OpenAI SDK, Claude Code, and Codex reduces integration work.

Step 1: Prepare a safe schema summary

Do not send an entire production database dump to a model. Create a compact schema document containing table names, columns, data types, primary keys, foreign keys, and a few representative values with sensitive information removed. Include business definitions when names are ambiguous. For example, explain that orders.created_at records checkout time, while orders.shipped_at records fulfillment time.

• Include only tables relevant to the task.
• Replace customer names, emails, and payment details with masked examples.
• State the SQL dialect, such as PostgreSQL, MySQL, BigQuery, or SQLite.
• Document timezone, currency, and status-code meanings.

Step 2: Write a constrained prompt

A strong prompt defines the model's role, supplies the schema, states the user's question, and specifies the exact response format. Ask for SQL only when your application needs machine-readable output. Otherwise, request SQL plus a short explanation and assumptions.

Useful prompt pattern: You are a PostgreSQL analytics assistant. Use only the tables and columns listed below. Convert the question into one read-only query. Do not invent fields. Return the SQL, required parameters, assumptions, and possible edge cases. User question: Which product categories had more than 1,000 completed orders last quarter?

Tell the model to use named parameters such as :start_date and :end_date rather than inserting user-provided values directly into the query.

Step 3: Generate SQL through 59API

59API uses the base URL https://api.59api.com and works with OpenAI-compatible SDKs. This lets you keep a familiar client while selecting an appropriate model for the job: a fast, inexpensive model for simple filters, or a stronger Claude or GPT model for complex joins and analytical reasoning. Send the schema and prompt in the request, then store the model response for review.

For production use, keep the API key in an environment variable, set request timeouts, log request IDs rather than private data, and define a maximum token limit. Because 59API is among the cheapest relays and charges based on usage, you can test prompts economically instead of committing to a large fixed plan. Developers can also check its referral rebate when inviting teammates.

Step 4: Validate the generated query

Never execute generated SQL immediately against a writable production connection. First check that it is syntactically valid for your database dialect and that every referenced table and column exists. Reject statements containing write operations such as INSERT, UPDATE, DELETE, DROP, ALTER, or TRUNCATE when the task is read-only.

• Parse the SQL with a dialect-aware parser where possible.
• Require a LIMIT for exploratory queries unless the result is an aggregate.
• Run EXPLAIN or an equivalent plan check before expensive execution.
• Enforce a database statement timeout and maximum returned rows.
• Compare the result with known totals or a manually written test query.

Step 5: Execute with parameters and least privilege

Use a read-only database role that can access only approved schemas. Bind parameters through your database driver; never concatenate values into the generated SQL. For example, the model may return a query with :start_date, but your application should provide the date through a prepared statement. Apply row-level security and column masking if different users have different data permissions.

Step 6: Improve accuracy with a review loop

If validation fails, send the error message and the original query back to the model, along with a clear instruction to fix only the reported problem. If results look suspicious, ask the model to list assumptions and produce a verification query. For recurring questions, save approved SQL templates and use the LLM only to fill safe filters or explain results.

Step 7: Measure quality before going live

Create a test set of real business questions with expected tables, metrics, filters, and answer ranges. Track execution success, latency, token cost, incorrect joins, and unsafe-query rejections. Review difficult cases with a data engineer. Start with a small internal pilot, then expand access after the generated queries consistently match your acceptance criteria.

To build this workflow quickly, sign up for 59API and test a few Claude or GPT models with your existing OpenAI-compatible tooling. The combination of pay-as-you-go access, low relay costs, and model choice makes it a practical starting point for reliable AI-assisted data querying.

Preços · Docs