Most teams sit on valuable data but can't easily access it without writing SQL. What if your users could ask questions in plain English and get instant answers? That's the promise of natural language to SQL—and it's now practical enough to ship to production. This guide walks you through building it the right way: starting with schema context, locking down permissions, validating every query, and showing users the SQL behind their results.
Why natural language to SQL matters now
For years, the gap between business users and data was wide. A product manager wanting to know "how many trial users upgraded last month" would email an analyst, wait two days, get a CSV, and move on. Today, large language models are good enough at SQL generation that we can close that gap—but only if we build the system correctly.
The risk is real: a poorly implemented natural language interface can expose your entire database, run expensive queries that crash your system, or return nonsensical results that erode trust. We're going to solve each problem head-on.
Give the model the schema — table and column descriptions improve accuracy
The first rule of AI analytics is this: your model is only as good as the context you give it. When you send raw SQL schema to an LLM, you get mediocre results. When you send schema plus business context, accuracy jumps dramatically.
Instead of sending:
CREATE TABLE users (
id INT,
created_at TIMESTAMP,
subscription_status VARCHAR
);Send something like this to your LLM:
CREATE TABLE users (
id INT PRIMARY KEY,
created_at TIMESTAMP COMMENT 'Account creation date; used to identify when a user first joined',
subscription_status VARCHAR COMMENT 'One of: trial, active, paused, cancelled. Trials auto-expire after 14 days unless manually upgraded.',
trial_ends_at TIMESTAMP COMMENT 'When this trial expires; NULL if not a trial'
);That context—especially the business rules embedded in the comments—dramatically improves query generation. A model now knows that "trial users" means subscription_status = 'trial' and can filter on trial_ends_at when relevant.
Here's a Python pattern we use to build this enriched schema dynamically:
import psycopg2
from typing import Dict, List
def fetch_schema_with_descriptions(conn) -> str:
"""
Fetch table and column descriptions from PostgreSQL.
Returns a text representation suitable for LLM consumption.
"""
cursor = conn.cursor()
schema_parts = []
# Get all tables
cursor.execute("""
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name
""")
for (table_name,) in cursor.fetchall():
schema_parts.append(f"\n-- Table: {table_name}")
# Get columns and their comments
cursor.execute("""
SELECT
column_name,
data_type,
(SELECT pg_catalog.col_description(t.oid, c.ordinal_position)
FROM pg_catalog.pg_class t
WHERE t.relname = %s) as col_comment
FROM information_schema.columns c
WHERE table_name = %s
ORDER BY ordinal_position
""", (table_name, table_name))
schema_parts.append(f"CREATE TABLE {table_name} (")
for col_name, data_type, comment in cursor.fetchall():
line = f" {col_name} {data_type}"
if comment:
line += f" -- {comment}"
schema_parts.append(line)
schema_parts.append(");")
return "\n".join(schema_parts)
# Usage
conn = psycopg2.connect("dbname=analytics user=reader")
schema = fetch_schema_with_descriptions(conn)
print(schema)Feed this enriched schema into your prompt. Most LLM APIs let you include it as part of the system message or as additional context. The cost is negligible; the accuracy gain is substantial.
Read-only access — run generated SQL with a restricted database user
Never run user-generated queries as your application's main database user. Create a dedicated, read-only account that has access only to tables and columns users should query.
Here's the pattern:
-- Create a restricted role for AI analytics queries
CREATE ROLE analytics_reader WITH LOGIN PASSWORD 'secure_password';
-- Grant SELECT on specific tables only
GRANT USAGE ON SCHEMA public TO analytics_reader;
GRANT SELECT ON users, orders, events TO analytics_reader;
-- Explicitly deny all other operations
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_reader;
REVOKE INSERT, UPDATE, DELETE ON users, orders, events FROM analytics_reader;
REVOKE ALL ON information_schema TO analytics_reader;Why this matters: if a model hallucinates a query like DROP TABLE users; or tries to write data, the database itself becomes your firewall. The query fails at the permission layer, not after damage is done.
In your application, maintain two connections:
- Primary connection (full permissions) for your app logic only
- Analytics connection (read-only) for all generated queries
import psycopg2
class DatabaseConnections:
def __init__(self, primary_dsn: str, analytics_dsn: str):
self.primary = psycopg2.connect(primary_dsn)
self.analytics = psycopg2.connect(analytics_dsn)
def execute_user_query(self, sql: str):
"""Execute a user-generated query with restricted permissions."""
cursor = self.analytics.cursor()
try:
cursor.execute(sql)
return cursor.fetchall()
finally:
cursor.close()
# Usage
db = DatabaseConnections(
primary_dsn="postgresql://app_user:pwd@localhost/db",
analytics_dsn="postgresql://analytics_reader:pwd@localhost/db"
)
results = db.execute_user_query("SELECT COUNT(*) FROM users WHERE created_at > NOW() - INTERVAL '30 days';")Validate queries — parse and check SQL before execution
Even with permission-based protection, you want to catch malformed or suspicious queries before they hit your database. Parsing SQL at the application layer buys you early feedback and prevents wasteful queries from executing.
Use a SQL parser library to extract table and column names, then validate them against your allow-list:
from sqlparse import parse
from sqlparse.sql import IdentifierList, Identifier, Where, Token
from typing import Set
ALLOWED_TABLES = {'users', 'orders', 'events', 'subscriptions'}
ALLOWED_COLUMNS = {
'users': {'id', 'email', 'created_at', 'subscription_status'},
'orders': {'id', 'user_id', 'amount', 'created_at', 'status'},
'events': {'id', 'user_id', 'event_type', 'created_at'},
}
def validate_sql(sql: str) -> tuple[bool, str]:
"""
Validate generated SQL.
Returns (is_valid, error_message)
"""
try:
parsed = parse(sql)
except Exception as e:
return False, f"SQL parse error: {str(e)}"
# Check for dangerous keywords
sql_upper = sql.upper()
forbidden = ['DROP', 'DELETE', 'INSERT', 'UPDATE', 'TRUNCATE', 'ALTER', 'CREATE']
for keyword in forbidden:
if keyword in sql_upper:
return False, f"Query contains forbidden operation: {keyword}"
# Extract table and column references (simplified)
# In production, use sqlparse's token traversal more thoroughly
# Check statement count (no multiple statements)
if len(parsed) > 1 or any(';' in str(p) for p in parsed if ';' in str(p)):
return False, "Multiple statements not allowed"
return True, ""
# Usage
sql = "SELECT email FROM users WHERE created_at > NOW() - INTERVAL '30 days'"
is_valid, error = validate_sql(sql)
if not is_valid:
print(f"Query rejected: {error}")
else:
print("Query approved for execution")This catches obvious issues early. A query with a syntax error won't waste database resources; a query referencing unauthorized tables gets rejected before it runs.
Show the query — let users see and verify the SQL behind answers
Transparency is critical. Never hide the SQL from users. Always show them what query was executed. This does three things:
- Builds trust ("I can see why the answer is what it is")
- Catches model mistakes ("That query looks wrong")
- Lets users refine further ("Can you modify this to also include…")
Your response should look something like this:
| User Question | Generated SQL | Results |
|---|---|---|
| "How many active users signed up in the last 30 days?" | SELECT COUNT(*) FROM users WHERE subscription_status = 'active' AND created_at > NOW() - INTERVAL '30 days'; |
1,247 |
In your API response, include all three components:
from pydantic import BaseModel
from typing import Any, List
class AnalyticsResponse(BaseModel):
question: str
sql_generated: str
results: List[dict]
row_count: int
execution_time_ms: float
def to_api(self):
return {
"question": self.question,
"sql": self.sql_generated, # Always include
"results": self.results,
"rows": self.row_count,
"execution_ms": self.execution_time_ms
}
# Usage in your endpoint
response = AnalyticsResponse(
question="How many active users signed up in the last 30 days?",
sql_generated="SELECT COUNT(*) as count FROM users WHERE subscription_status = 'active' AND created_at > NOW() - INTERVAL '30 days';",
results=[{"count": 1247}],
row_count=1,
execution_time_ms=45.3
)
return response.to_api()Some teams also let users edit the query directly and re-run it, turning your natural language interface into a gateway to ad-hoc analytics. That's a nice refinement once the basics are solid.
Common mistakes
- Sending raw schema without context: Models guess poorly without business meaning. Always include column comments and table descriptions.
- Running queries as the app user: Permission boundaries are your defense layer. Use a restricted read-only account without exception.
- Skipping SQL validation: Don't assume the model always generates safe queries. Parse and check before execution, every time.
- Hiding the generated SQL: If you hide the query, users can't verify results or catch errors. Transparency matters for adoption and correctness.
- Not setting query timeouts: A model might generate an expensive join on million-row tables. Always set a max execution time to prevent resource exhaustion.
- Ignoring the model's confidence: Some LLM APIs let you request reasoning or confidence scores. Use them to flag uncertain queries to users.
Key takeaways
- Enrich your database schema with business context and column descriptions before sending it to the model. This single step improves accuracy significantly.
- Run all generated queries through a dedicated, read-only database user with strict permission boundaries. Let the database be your firewall.
- Parse and validate every generated query for syntax errors and forbidden operations before execution. Catch problems early.
- Always show users the SQL that was generated and executed. Transparency builds trust and lets teams refine queries together.
- Set execution timeouts, log all queries, and monitor for patterns of errors or misuse. Treat this like any other production system.
Comments
Loading comments…