← Retour aux guides

LLM-to-SQL Quick Start: Generate Queries Safely

Generating SQL with LLMs can turn a reporting request into a useful query in seconds. The hard part is not producing SQL-shaped text: it is getting the right business definition, database dialect, and execution boundaries. This quick-start guide builds a small, reviewable workflow rather than giving a chatbot unrestricted database access.

1. Start with one dialect and a narrow schema

Pick a database before writing your prompt. PostgreSQL, MySQL, BigQuery, and SQL Server differ in date functions, quoting, and parameter syntax. For this example, use PostgreSQL and expose only two approved tables:

• customers: id BIGINT PRIMARY KEY, country TEXT.
• orders: id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers(id), created_at TIMESTAMPTZ, status TEXT, total_cents BIGINT.

Add semantic notes: total_cents stores integer USD cents; paid orders have status = 'paid'; created_at determines the reporting period. Include keys and relationships, not just column names. Do not send customer records, credentials, or your entire production catalog.

2. Give the model an explicit query contract

A useful prompt contains the dialect, schema, business rules, question, and output format. Ask for clarification when missing information would change the result. For a first test, use this request:

Prompt: Using the PostgreSQL schema above, return the ten countries with the highest paid-order revenue during January 2026, using UTC boundaries. Revenue means SUM(total_cents), without refund adjustments. Use SQLAlchemy-style named parameters :start_at and :end_at. Return JSON containing sql, params, and assumptions. Generate one read-only SELECT statement against the approved tables only. Do not invent columns or execute anything.

Specify these parameter values: start_at = 2026-01-01T00:00:00Z and end_at = 2026-02-01T00:00:00Z. Half-open intervals include the starting instant and exclude the next period, avoiding end-of-day precision bugs.

3. Connect through an OpenAI-compatible client

59API offers low-cost, pay-as-you-go access through an OpenAI-compatible API, making it convenient to compare models without maintaining separate provider integrations. Install the Python client with pip install openai, then keep your API key in an environment variable.

Initialize the client with OpenAI(api_key=os.environ['API59_KEY'], base_url='https://api.59api.com') after importing os and OpenAI. Call client.chat.completions.create with the model identifier from your 59API dashboard and a messages array containing your query contract. Read the generated text from response.choices[0].message.content; parse and validate it before using it.

Try DeepSeek V4 Flash as an initial candidate, then compare it with Claude Sonnet 5.5 on your own difficult joins and business rules. Use the dashboard's API identifier rather than assuming a display name is the request identifier. 59API provides native models without downgrades, so you can evaluate cost and accuracy on the actual models you intend to use.

4. Inspect the generated SQL

A suitable query for this example is:

SELECT c.country, SUM(o.total_cents) AS revenue_cents FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.status = 'paid' AND o.created_at >= :start_at AND o.created_at < :end_at GROUP BY c.country ORDER BY revenue_cents DESC, c.country ASC LIMIT 10;

Keep revenue in cents until presentation. Bind the timestamps as typed values through your database library; never substitute user text into SQL strings. Review assumptions such as how unknown countries or historical customer-country changes should be handled.

5. Validate before execution

• Validate structure: Parse the response JSON and SQL with dialect-aware tooling. Reject multiple statements, unapproved relations, write operations, and unsafe function calls. A SELECT prefix check is insufficient.
• Enforce permissions: Use a dedicated read-only role, approved views, and database-enforced tenant isolation. Prompt instructions are not authorization.
• Bound resource use: Set statement timeouts and result limits. Inspect EXPLAIN first; LIMIT does not prevent expensive scans. EXPLAIN ANALYZE actually executes the query.
• Test correctness: Check known fixtures, empty periods, timestamp boundaries, duplicate joins, and NULL values before production use.

6. Measure reliability, then expand

Save a small evaluation set of questions, expected results, latency, and token cost. Cache approved query templates and regenerate only when necessary. For other data-query languages, replace the SQL-specific schema and validator with equivalent controls. If you want an inexpensive place to test this workflow, consider signing up for 59API and comparing a small batch before scaling.

Tarifs · Docs