The request from the client was simple: "I want to ask questions about our data in plain English and get answers." Text-to-SQL. The demo took an afternoon. The production system took six weeks, because the difference between "generate a SQL query" and "generate a SQL query that will not destroy the database" is enormous.
Here is what I learned about giving LLMs safe, production-grade access to relational databases.
Why Text-to-SQL Is Harder Than It Looks
In a demo, text-to-SQL is magical. "Show me total revenue by product category last quarter" becomes a perfect SQL query. In production, users ask questions like:
- "Show me everything" (generates
SELECT *on a 50-million-row table) - "Delete the test records" (generates a
DELETEstatement) - "What is the admin password?" (attempts to query the users table)
- "Show me John's salary" (requests data the user is not authorized to see)
Every one of these is a real query I intercepted during the first month of production. The LLM will happily generate SQL for all of them, because the model does not understand authorization, resource limits, or business rules. It understands SQL syntax.
Layer 1: Read-Only Access
The most important architectural decision: the LLM should never have write access to your database. Never. Create a read-only database user specifically for AI-generated queries.
-- Create a read-only role for AI queries
CREATE ROLE ai_reader WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO ai_reader;
GRANT USAGE ON SCHEMA public TO ai_reader;
-- Grant SELECT only on specific tables
GRANT SELECT ON orders, products, categories TO ai_reader;
-- Explicitly deny access to sensitive tables
-- (The absence of a GRANT is sufficient, but being explicit is clearer)
REVOKE ALL ON users, credentials, api_keys FROM ai_reader;
Even if a prompt injection convinces the LLM to generate DROP TABLE users, the query fails with a permission error. The database enforces the boundary that the application cannot reliably enforce.
Layer 2: Schema Exposure Control
The LLM needs to know your database schema to generate correct queries. But you should not expose your entire schema. Create a curated view of the schema that includes only the tables and columns the AI should know about.
const aiVisibleSchema = {
tables: [
{
name: 'orders',
description: 'Customer orders with line items and totals',
columns: [
{ name: 'id', type: 'integer', description: 'Order ID' },
{ name: 'customer_name', type: 'text', description: 'Customer name' },
{ name: 'total_amount', type: 'numeric', description: 'Order total in USD' },
{ name: 'status', type: 'text', description: 'pending, shipped, delivered, cancelled' },
{ name: 'created_at', type: 'timestamp', description: 'When the order was placed' },
],
},
{
name: 'products',
description: 'Product catalog',
columns: [
{ name: 'id', type: 'integer', description: 'Product ID' },
{ name: 'name', type: 'text', description: 'Product name' },
{ name: 'category', type: 'text', description: 'Product category' },
{ name: 'price', type: 'numeric', description: 'Unit price in USD' },
],
},
],
};
Notice what is missing: the users table, internal IDs, cost data, margin calculations, and anything else the end user should not query. The LLM cannot reference tables it does not know about.
Layer 3: Query Validation
Before executing any AI-generated SQL, validate it. Parse the SQL, check for disallowed operations, and verify the tables and columns are within the allowed set.
import { parse } from 'pgsql-ast-parser';
interface QueryValidation {
valid: boolean;
errors: string[];
sanitizedQuery?: string;
}
function validateQuery(sql: string, allowedTables: string[]): QueryValidation {
const errors: string[] = [];
try {
const ast = parse(sql);
for (const statement of ast) {
// Only allow SELECT statements
if (statement.type !== 'select') {
errors.push(`Disallowed statement type: ${statement.type}. Only SELECT is permitted.`);
continue;
}
// Check for subqueries that might access restricted tables
const referencedTables = extractTableNames(statement);
for (const table of referencedTables) {
if (!allowedTables.includes(table)) {
errors.push(`Table "${table}" is not accessible.`);
}
}
// Prevent unbounded queries
if (!hasLimit(statement)) {
errors.push('Query must include a LIMIT clause (max 1000 rows).');
}
}
} catch (parseError) {
errors.push('Failed to parse SQL. The query may contain syntax errors.');
}
return {
valid: errors.length === 0,
errors,
sanitizedQuery: errors.length === 0 ? sql : undefined,
};
}
This catches the obvious issues: non-SELECT statements, access to restricted tables, and unbounded queries. SQL parsing is not perfect — there are edge cases with CTEs, subqueries, and database-specific syntax — but it catches 95% of problematic queries.
Layer 4: Resource Limits
Even a valid SELECT query can be dangerous if it is computationally expensive. A JOIN across three 50-million-row tables with no index usage can lock the database for minutes.
async function executeWithLimits(
query: string,
connection: PoolClient
): Promise<QueryResult> {
// Set statement timeout (5 seconds max)
await connection.query('SET statement_timeout = 5000');
// Set row limit at the connection level
await connection.query('SET work_mem = \'64MB\'');
try {
// EXPLAIN first to check estimated cost
const plan = await connection.query(`EXPLAIN (FORMAT JSON) ${query}`);
const estimatedCost = plan.rows[0]['QUERY PLAN'][0]['Plan']['Total Cost'];
if (estimatedCost > 100000) {
throw new Error('Query too expensive. Please narrow your search criteria.');
}
// Execute the actual query
const result = await connection.query(query);
return {
rows: result.rows,
rowCount: result.rowCount,
executionTime: result.duration,
};
} catch (error) {
if ((error as Error).message.includes('statement timeout')) {
throw new Error('Query took too long. Try a more specific question.');
}
throw error;
} finally {
// Reset connection settings
await connection.query('RESET statement_timeout');
await connection.query('RESET work_mem');
}
}
The EXPLAIN pre-check is a valuable safeguard. If the planner estimates a high cost, reject the query before it runs. This prevents the class of queries that are syntactically valid and table-safe but computationally catastrophic.
Layer 5: Row-Level Security
For multi-tenant applications, the LLM should only return data the current user is authorized to see. PostgreSQL's Row-Level Security (RLS) is the cleanest way to enforce this.
-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Create a policy that restricts access by organization
CREATE POLICY org_isolation ON orders
FOR SELECT
USING (organization_id = current_setting('app.current_org_id')::integer);
// Set the organization context before executing AI-generated queries
async function executeAsUser(
query: string,
organizationId: number
): Promise<QueryResult> {
const connection = await pool.connect();
try {
// Set the RLS context
await connection.query('SET app.current_org_id = $1', [organizationId]);
await connection.query(`SET ROLE ai_reader`);
// Execute the query — RLS automatically filters results
return await executeWithLimits(query, connection);
} finally {
await connection.query('RESET ROLE');
connection.release();
}
}
With RLS, even if the LLM generates a SELECT * FROM orders, the results only include orders belonging to the current user's organization. The database enforces the boundary, not the application code.
The Full Pipeline
Putting it all together:
- User asks a question in natural language.
- LLM generates SQL based on the curated schema.
- SQL is parsed and validated — only SELECT, only allowed tables, must have LIMIT.
- Query cost is estimated via EXPLAIN — reject if too expensive.
- RLS context is set — the query can only see the user's data.
- Query executes with a 5-second timeout.
- Results are returned to the LLM for natural language summarization.
- Everything is logged — the question, the generated SQL, the results, and the execution time.
Each layer catches different failure modes. Together, they make text-to-SQL safe enough for production.
The LLM generates the query. The database enforces the boundaries. Never trust the model to enforce security — it is a text generator, not a security system. Put the controls where they cannot be bypassed.
Text-to-SQL is one of the most useful AI features you can build. It is also one of the most dangerous if done carelessly. Start with read-only access. Add validation. Enforce limits. Use RLS. And always, always log the generated SQL. You will need it.