Text-to-SQL with LLMs: Schema Mapping, Query Validation, and Safe Database Access for AI Applications

Natural language database access is one of the few AI use cases where the output is directly executable against production state. That raises the stakes considerably compared to summarization or classification tasks. A misunderstood question generates a slow query or pulls data it should not touch. A malicious question, if your pipeline lacks defenses, can be far worse.

Text-to-SQL has real value: internal analytics tools, customer-facing data exploration, AI agents that need to answer structured questions without hardcoded query logic. But the gap between a demo that works on your test schema and a pipeline that holds up in production is wide. This article covers the full pipeline: schema description, prompt construction, query generation, validation, safe execution, and evaluation.

Why Text-to-SQL Is Harder Than It Looks

The obvious failure mode is the model generating syntactically invalid SQL. That happens, but it is not the main problem in production. The harder failures are:

• Semantic drift: the model generates valid SQL that answers a different question than the one asked. “How many users signed up last month?” returns a count of all users if the model does not understand your created_at column semantics.
• Schema ambiguity: two tables have a status column with different value semantics. The model picks the wrong one.
• Implicit assumptions: “top customers” could mean highest revenue, most orders, or most recent. The model picks one without flagging the ambiguity.
• Performance hazard: a valid query scans 200 million rows with no index and kills your database.

A well-designed pipeline catches most of these before execution. None of these problems is unique to LLMs. They are the same problems any query builder faces. The difference is that LLMs fail silently unless you build in validation and observability.

Pipeline Architecture

The pipeline has five stages:

User question
    → Schema pruning + description
    → Prompt construction
    → LLM query generation
    → Query validation
    → Safe execution + result formatting

Each stage is a place where you either add guardrails or lose control of the output. Let us build each one.

Stage 1: Schema Description

The model needs to understand your schema. A full database schema dump into the context window is both wasteful and counterproductive. A 200-table schema with all columns and constraints will confuse the model and consume tokens you need for few-shot examples.

The approach is to build a schema registry and prune it at query time based on the user’s question.

interface TableSchema {
  name: string;
  description: string;
  columns: ColumnSchema[];
  sampleValues?: Record<string, string[]>;
}

interface ColumnSchema {
  name: string;
  type: string;
  description: string;
  nullable: boolean;
  foreignKey?: { table: string; column: string };
}

const schemaRegistry: TableSchema[] = [
  {
    name: "orders",
    description: "Customer purchase orders. Each row is one order.",
    columns: [
      {
        name: "id",
        type: "uuid",
        description: "Primary key",
        nullable: false,
      },
      {
        name: "customer_id",
        type: "uuid",
        description: "References customers.id",
        nullable: false,
        foreignKey: { table: "customers", column: "id" },
      },
      {
        name: "status",
        type: "text",
        description: "Order lifecycle state: pending | confirmed | shipped | delivered | cancelled",
        nullable: false,
        sampleValues: ["pending", "confirmed", "shipped"],
      },
      {
        name: "total_cents",
        type: "integer",
        description: "Order total in cents (USD). Divide by 100 for display.",
        nullable: false,
      },
      {
        name: "created_at",
        type: "timestamptz",
        description: "When the order was placed, in UTC.",
        nullable: false,
      },
    ],
  },
];

The description fields are the most important part of schema mapping. Column names alone are rarely sufficient. total_cents, status with its allowed values, and created_at with its timezone note all prevent common errors.

Schema Pruning

Before building the prompt, select only the tables relevant to the question using embedding similarity or keyword matching:

import OpenAI from "openai";

const openai = new OpenAI();

async function pruneSchema(
  question: string,
  registry: TableSchema[],
  maxTables: number = 5
): Promise<TableSchema[]> {
  // Embed the question
  const questionEmbedding = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: question,
  });

  // Embed table descriptions (cache these in production)
  const tableEmbeddings = await Promise.all(
    registry.map((table) =>
      openai.embeddings.create({
        model: "text-embedding-3-small",
        input: `${table.name}: ${table.description} ${table.columns.map((c) => c.name).join(", ")}`,
      })
    )
  );

  // Cosine similarity
  const scores = tableEmbeddings.map((emb, i) => ({
    table: registry[i],
    score: cosineSimilarity(
      questionEmbedding.data[0].embedding,
      emb.data[0].embedding
    ),
  }));

  return scores
    .sort((a, b) => b.score - a.score)
    .slice(0, maxTables)
    .map((s) => s.table);
}

function cosineSimilarity(a: number[], b: number[]): number {
  const dot = a.reduce((sum, val, i) => sum + val * b[i], 0);
  const magA = Math.sqrt(a.reduce((sum, val) => sum + val * val, 0));
  const magB = Math.sqrt(b.reduce((sum, val) => sum + val * val, 0));
  return dot / (magA * magB);
}

In practice, caching the table embeddings at startup and re-embedding only when the schema changes is essential. Re-embedding on every request adds 100-200ms and unnecessary cost.

Stage 2: Prompt Construction

The system prompt does the heavy lifting. It tells the model what dialect to use, what constraints to respect, and how to handle ambiguity.

function buildPrompt(
  tables: TableSchema[],
  fewShotExamples: Array<{ question: string; sql: string }>,
  dialect: "postgresql" | "mysql" | "sqlite" = "postgresql"
): string {
  const schemaSection = tables
    .map((table) => {
      const columns = table.columns
        .map(
          (col) =>
            `  - ${col.name} (${col.type}${col.nullable ? "" : ", NOT NULL"}): ${col.description}`
        )
        .join("\n");
      return `Table: ${table.name}\nPurpose: ${table.description}\nColumns:\n${columns}`;
    })
    .join("\n\n");

  const examplesSection = fewShotExamples
    .map((ex) => `Question: ${ex.question}\nSQL:\n\`\`\`sql\n${ex.sql}\n\`\`\``)
    .join("\n\n");

  return `You are a SQL query generator for a ${dialect} database.

RULES:
- Generate only SELECT statements. Never INSERT, UPDATE, DELETE, DROP, or any DDL.
- Always include a LIMIT clause. Maximum 1000 rows.
- Use parameterized values where filters are applied (return a JSON object with query and params).
- If the question is ambiguous, choose the most conservative interpretation and add a comment.
- If the question cannot be answered with the available schema, return {"error": "reason"}.
- Never use SELECT *. Name every column explicitly.

SCHEMA:
${schemaSection}

EXAMPLES:
${examplesSection}

Respond with a JSON object in this exact shape:
{
  "sql": "SELECT ...",
  "params": [],
  "explanation": "one sentence description of what this query returns",
  "confidence": "high|medium|low",
  "ambiguities": []
}`;
}

The few-shot examples are the highest-leverage part of the prompt. Three to five well-chosen examples that cover your most common query patterns will improve accuracy more than any amount of schema description. They teach the model your naming conventions, your join patterns, and your preferred aggregation styles.

Stage 3: Query Generation

interface GeneratedQuery {
  sql: string;
  params: (string | number | boolean | null)[];
  explanation: string;
  confidence: "high" | "medium" | "low";
  ambiguities: string[];
}

async function generateQuery(
  question: string,
  systemPrompt: string
): Promise<GeneratedQuery | { error: string }> {
  const response = await openai.chat.completions.create({
    model: "gpt-4o",
    messages: [
      { role: "system", content: systemPrompt },
      { role: "user", content: question },
    ],
    response_format: { type: "json_object" },
    temperature: 0,
  });

  const content = response.choices[0].message.content;
  if (!content) throw new Error("Empty response from model");

  return JSON.parse(content) as GeneratedQuery | { error: string };
}

temperature: 0 is correct for SQL generation. You want deterministic output for a given prompt, not creative variation. If the model generates different queries for the same question on retries, that is a sign the prompt is ambiguous, not a problem to solve with sampling temperature.

Stage 4: Query Validation

Validation is where you stop bad queries before they reach the database. It operates at three levels: structural, semantic, and safety.

import { Parser } from "node-sql-parser";

interface ValidationResult {
  valid: boolean;
  errors: string[];
  warnings: string[];
}

const parser = new Parser();

function validateQuery(
  sql: string,
  allowedTables: Set<string>
): ValidationResult {
  const errors: string[] = [];
  const warnings: string[] = [];

  // 1. Parse check: is it valid SQL?
  let ast: ReturnType<typeof parser.astify>;
  try {
    ast = parser.astify(sql, { database: "PostgreSQL" });
  } catch (e) {
    return { valid: false, errors: [`Parse error: ${String(e)}`], warnings };
  }

  // 2. Statement type check: SELECT only
  const statements = Array.isArray(ast) ? ast : [ast];
  for (const stmt of statements) {
    if (stmt.type !== "select") {
      errors.push(
        `Rejected: statement type "${stmt.type}" is not allowed. Only SELECT is permitted.`
      );
    }
  }

  // 3. Table whitelist check
  const usedTables = extractTableNames(sql);
  for (const table of usedTables) {
    if (!allowedTables.has(table)) {
      errors.push(`Rejected: table "${table}" is not in the allowed schema.`);
    }
  }

  // 4. LIMIT check
  if (!sql.toLowerCase().includes("limit")) {
    errors.push("Rejected: query must include a LIMIT clause.");
  }

  // 5. Subquery depth check (prevent deeply nested expensive queries)
  const subqueryDepth = countSubqueryDepth(sql);
  if (subqueryDepth > 3) {
    warnings.push(
      `Warning: query has ${subqueryDepth} levels of subquery nesting. Consider simplifying.`
    );
  }

  // 6. Cartesian product detection
  if (hasCartesianProduct(sql)) {
    errors.push(
      "Rejected: query appears to produce a cartesian product (JOIN without ON condition)."
    );
  }

  return {
    valid: errors.length === 0,
    errors,
    warnings,
  };
}

function extractTableNames(sql: string): string[] {
  // Simple regex approach; replace with AST traversal for production
  const matches = sql.match(/\bFROM\b\s+(\w+)|\bJOIN\b\s+(\w+)/gi) || [];
  return matches
    .map((m) => m.replace(/\b(FROM|JOIN)\b\s+/i, "").trim().toLowerCase())
    .filter(Boolean);
}

function countSubqueryDepth(sql: string): number {
  let depth = 0;
  let max = 0;
  for (const char of sql) {
    if (char === "(") depth++;
    if (char === ")") depth--;
    max = Math.max(max, depth);
  }
  return max;
}

function hasCartesianProduct(sql: string): boolean {
  // Detects comma-separated FROM clause tables without explicit JOIN
  return /FROM\s+\w+\s*,\s*\w+/i.test(sql);
}

The AST-based approach via node-sql-parser is more reliable than regex for statement type detection. Regex can be fooled by comments, string literals, or clever formatting. For the table whitelist and limit checks, string matching is acceptable as a secondary defense since the AST check already ran.

Stage 5: Safe Execution

Even with a validated query, execution needs guardrails. Read-only database connections and hard row limits are the two non-negotiables.

import { Pool } from "pg";

const readOnlyPool = new Pool({
  connectionString: process.env.DATABASE_URL_READONLY,
  // Many Postgres providers offer read-replica connection strings.
  // If not, create a read-only role:
  // CREATE ROLE readonly_user LOGIN PASSWORD '...';
  // GRANT CONNECT ON DATABASE mydb TO readonly_user;
  // GRANT USAGE ON SCHEMA public TO readonly_user;
  // GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
});

interface QueryResult {
  rows: Record<string, unknown>[];
  rowCount: number;
  truncated: boolean;
  durationMs: number;
}

async function executeQuery(
  sql: string,
  params: (string | number | boolean | null)[],
  maxRows: number = 500
): Promise<QueryResult> {
  const client = await readOnlyPool.connect();
  const start = Date.now();

  try {
    // Enforce statement timeout at the session level
    await client.query("SET statement_timeout = '10s'");

    // Inject a hard row limit regardless of what the LLM included
    const limitedSql = injectRowLimit(sql, maxRows);

    const result = await client.query(limitedSql, params);

    return {
      rows: result.rows,
      rowCount: result.rowCount ?? 0,
      truncated: (result.rowCount ?? 0) >= maxRows,
      durationMs: Date.now() - start,
    };
  } finally {
    client.release();
  }
}

function injectRowLimit(sql: string, maxRows: number): string {
  // Replace any existing LIMIT with a capped value, or add one
  const limitMatch = sql.match(/\bLIMIT\s+(\d+)/i);
  if (limitMatch) {
    const existing = parseInt(limitMatch[1], 10);
    if (existing > maxRows) {
      return sql.replace(/\bLIMIT\s+\d+/i, `LIMIT ${maxRows}`);
    }
    return sql;
  }
  return `${sql} LIMIT ${maxRows}`;
}

statement_timeout is the safety net that the LIMIT clause does not cover. A query that returns 10 rows but joins against a 200M-row table without a useful index will still scan the full table before applying the limit. The timeout kills it after 10 seconds. Adjust based on your acceptable latency budget; 5-10 seconds is typical for interactive tools.

Improving Accuracy

Query Decomposition

Complex questions (“Which product category had the highest average order value among customers who signed up in Q4 and placed at least three orders?”) often fail in a single-shot generation pass. Decompose them into sub-questions:

async function decomposeQuestion(
  question: string
): Promise<string[]> {
  const response = await openai.chat.completions.create({
    model: "gpt-4o",
    messages: [
      {
        role: "system",
        content: `Break the user's database question into ordered sub-questions that can each be answered with a single SQL query. If the question is simple, return it unchanged as a single-item array. Return JSON: {"subquestions": ["..."]}`,
      },
      { role: "user", content: question },
    ],
    response_format: { type: "json_object" },
    temperature: 0,
  });

  const parsed = JSON.parse(response.choices[0].message.content!);
  return parsed.subquestions as string[];
}

For multi-step questions, generate each sub-query, execute it, and pass the results as context for the next step. This converts a single hard query into a chain of simpler ones where the model has real data to reason about at each step.

Schema Self-Consistency

When confidence is medium or low, generate the query twice with slight prompt variations and compare the outputs. If both produce structurally identical SQL (same tables, same aggregations, same filters), confidence increases. If they diverge, surface the ambiguity to the user rather than guessing.

Tradeoffs

Dimension	Single-shot	Decomposed multi-step	Notes
Latency	1-2s	3-8s	Multi-step chains multiple LLM calls
Accuracy on complex questions	Low-medium	High	Decomposition pays off for 3+ join queries
Cost	Low	Medium-high	One LLM call vs. 2-5 calls
Schema surface required	Full	Subset per step	Step context grows as sub-results are added
Failure blast radius	High (one wrong query)	Contained (fail at sub-step)	Easier to debug where the chain broke

Production Considerations

Parameterized queries are not optional. The model will sometimes interpolate user-provided values directly into the SQL string, especially for string filters. Your validation layer must detect this pattern and either reject the query or extract the literal values into params. An LLM prompt that says “use parameterized queries” is not a sufficient defense if the generated SQL has WHERE name = 'alice' instead of WHERE name = $1.

Log everything. Every question, every generated SQL, every validation result, every execution duration, and every row count. Text-to-SQL pipelines are notoriously hard to debug after the fact. The combination of “what did the user ask,” “what did the model generate,” and “how long did it take” is what you need to diagnose quality regressions.

Cache validated queries. If the same question (or a semantically near-identical one) has been answered before and the query passed validation and execution, cache the SQL keyed by question embedding similarity. Retrieval beats regeneration for deterministic queries on static schema sections.

Schema versioning. When you rename a column or change column semantics, your cached queries and your few-shot examples become wrong. Treat the schema registry as a versioned artifact with a change log. When the schema changes, invalidate query caches and audit your examples.

Confidence routing. Route low confidence outputs to a human review queue or to an explicit “I am not sure” response rather than executing speculatively. A query that returns wrong results is worse than a query that honestly says “I could not answer this reliably.”

Evaluation. Build a golden set: a collection of natural language questions paired with expected SQL output and expected result shapes. Run it on every model version change and every schema change. Without a golden set you are flying blind when accuracy degrades. Start with 50 questions covering your most common patterns and your known edge cases. Accuracy below 85% on the golden set should block deployment.

The Evaluation Loop

An evaluation harness for a text-to-SQL pipeline runs at three levels:

1. SQL validity: does the generated SQL parse without errors? This should be 99%+ or your prompt is broken.
2. Semantic correctness: does the query answer the right question? Compare result shapes and sample values against ground truth. Harder to automate; requires human annotation or a judge LLM.
3. Performance safety: does the query complete within the timeout? Log execution times and flag queries that approach the limit.

interface EvalCase {
  question: string;
  expectedTableNames: string[];
  expectedColumns: string[];
  resultShape: "single-row" | "multi-row" | "aggregate";
}

async function runEval(cases: EvalCase[]): Promise<void> {
  let passed = 0;
  for (const c of cases) {
    const result = await runPipeline(c.question);
    if ("error" in result) {
      console.log(`FAIL: ${c.question}: ${result.error}`);
      continue;
    }
    const tablesMatch = c.expectedTableNames.every((t) =>
      result.sql.toLowerCase().includes(t)
    );
    if (tablesMatch) {
      passed++;
      console.log(`PASS: ${c.question}`);
    } else {
      console.log(
        `FAIL: ${c.question}: expected tables ${c.expectedTableNames.join(", ")}`
      );
    }
  }
  console.log(`\n${passed}/${cases.length} passed`);
}

The table name check is a weak proxy for semantic correctness. For production evaluation, you want to compare actual query results against expected results. That requires either a deterministic test database with known state or a judge LLM that evaluates whether the result answers the original question. The judge LLM approach scales better and catches semantic errors the table-name check misses.

Summary

A production text-to-SQL pipeline has five load-bearing components: a schema registry with human-readable descriptions, a pruning step that keeps context focused, a prompt with explicit constraints and few-shot examples, a validation layer that blocks invalid and dangerous queries before execution, and a safe execution layer on a read-only connection with statement timeouts.

Accuracy comes from schema description quality, few-shot example selection, and an evaluation golden set that catches regressions. Safety comes from read-only credentials, parameterized queries enforced at validation time, row limits injected at execution time, and statement timeouts at the session level.

The models are good enough to make this work. The engineering around them is what determines whether it holds up in production.