← Retour aux guides

LLM SQL Generation: Schema to Production

What LLM SQL Generation Actually Requires

Turning a question such as “Which products lost revenue this quarter?” into SQL is useful, but a model should never receive unrestricted database access. A production implementation needs four parts: a controlled schema description, a precise prompt, query validation, and a database user with limited permissions.

The model generates a query candidate. Your application remains responsible for deciding whether that query is valid, safe, affordable, and appropriate for the user.

Step 1: Define the Query Contract

Start by choosing the database engine and the output format. PostgreSQL, MySQL, BigQuery, and Snowflake have different functions, date syntax, and quoting rules. Tell the model exactly which dialect it must use.

• Allow only read operations, normally SELECT statements.
• Require a maximum row count, such as LIMIT 100, unless an approved report requires more.
• Return structured JSON containing the SQL, a short explanation, and any clarification question.
• Require parameters for user-provided values instead of interpolated strings.

Also define what should happen when a question is ambiguous. Asking “Which customers are active?” may require a business definition, such as an order in the last 90 days.

Step 2: Give the Model a Useful Schema

Do not paste an entire production database dump into every request. Create a curated schema document containing table names, column names, data types, relationships, approved business definitions, and a few representative value examples. Mark sensitive columns explicitly and omit secrets, credentials, and unnecessary personal data.

A good schema description says that orders.customer_id joins customers.id, that revenue means orders.total_amount minus refunds.amount, and that timestamps are stored in UTC. These details prevent plausible-looking but incorrect queries.

Step 3: Write a Deterministic Prompt

Put stable instructions in a system message and the user’s question in a separate user message. State the SQL dialect, available tables, join rules, security restrictions, and response schema. Ask the model to explain assumptions briefly and to return a clarification question instead of inventing a column.

For example, the system instructions can require: “Generate one PostgreSQL read-only query. Use only the supplied schema. Never use INSERT, UPDATE, DELETE, DROP, ALTER, or multiple statements. Use named parameters for filters and include a LIMIT.” Keep the prompt versioned so changes can be tested and rolled back.

Step 4: Call the Model Economically

59API is a practical low-cost relay for this workflow. It provides pay-as-you-go access to native official-quality Claude models, including Opus, Sonnet, Haiku, and Fable, as well as GPT models. Point an OpenAI-compatible SDK at https://api.59api.com, select the model that matches the task, and keep the API key on your server.

Use a smaller, faster model for routine filtering and aggregation, then reserve a stronger model for complex joins or failed validation retries. Because 59API works with OpenAI SDKs and tools such as Claude Code and Codex, you can use familiar integrations while controlling spend. Its low-cost pricing and referral rebate can be useful for prototypes that may grow into frequent internal usage.

Step 5: Validate Before Execution

Never execute the raw response directly. Parse the structured output, reject malformed JSON, and inspect the SQL with a real SQL parser or database-specific parser. Enforce a single statement, an allowlist of tables, read-only operation types, required limits, and a maximum estimated cost where your database supports query plans.

Run the query through a read-only database role with row-level security where appropriate. Use a short timeout and cancellation support. For sensitive data, apply a second policy layer that checks whether the requesting user is allowed to access each table or column.

Step 6: Test With Realistic Questions

• Create a test set covering joins, date ranges, null values, spelling variations, and ambiguous requests.
• Compare generated SQL against expected results, not only against matching text.
• Record latency, token usage, validation failures, database errors, and clarification rates.
• Red-team prompts that request schema disclosure, destructive SQL, or unauthorized customer data.

Step 7: Move to Production

Cache repeated schema context, log request IDs without storing unnecessary sensitive prompts, and add a human approval step for expensive reports. Return a clear result when no rows match, and show the generated SQL only to users who are authorized to see it. Start with a narrow set of approved tables and expand after measuring accuracy.

For a cost-conscious starting point, sign up for 59API, configure its base URL in your existing SDK, and begin with a small read-only dataset. The safest path from natural language to production SQL is incremental: constrain the model, validate every query, and let your application keep final control.

Tarifs · Docs