How to build an LLM-powered SQL query assistant

The natural-language SQL problem in production

Translating natural language to SQL sounds straightforward until you try to productionize it. The demos are easy: ask "how many users signed up last month?" and get a clean SELECT COUNT(*) FROM users WHERE created_at > .... The reality of production systems is messier: schemas have dozens of tables with cryptic names, column types are ambiguous, queries can delete data, users phrase things in ways that match multiple interpretations, and the model sometimes confidently generates invalid SQL.

A production SQL assistant needs to handle all of this. This article builds one: schema-aware query generation, safety controls, ambiguity resolution, result formatting, and the operational scaffolding that makes it usable in a real product.

The stack is TypeScript, PostgreSQL (via node-postgres), and the Anthropic API, but the patterns apply to any relational database and LLM.

Schema extraction: giving the model what it needs

The model can only generate correct SQL if it knows your schema. Injecting the full schema into every prompt is the most reliable approach. For large schemas (100+ tables), you'll want to filter to relevant tables first, but start with full injection and optimize later.

import { Pool } from "pg";

interface ColumnInfo {
  columnName: string;
  dataType: string;
  isNullable: boolean;
  defaultValue: string | null;
  comment: string | null;
}

interface TableInfo {
  tableName: string;
  rowCount: number;
  columns: ColumnInfo[];
  foreignKeys: Array<{
    columnName: string;
    referencedTable: string;
    referencedColumn: string;
  }>;
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function extractSchema(
  schemaName = "public"
): Promise<TableInfo[]> {
  const tablesResult = await pool.query<{ table_name: string }>(
    `SELECT table_name 
     FROM information_schema.tables 
     WHERE table_schema = $1 AND table_type = 'BASE TABLE'
     ORDER BY table_name`,
    [schemaName]
  );

  const tables: TableInfo[] = [];

  for (const { table_name } of tablesResult.rows) {
    const [columnsResult, fkResult, countResult] = await Promise.all([
      pool.query<{
        column_name: string;
        data_type: string;
        is_nullable: string;
        column_default: string | null;
        description: string | null;
      }>(
        `SELECT 
           c.column_name, 
           c.data_type, 
           c.is_nullable, 
           c.column_default,
           pgd.description
         FROM information_schema.columns c
         LEFT JOIN pg_catalog.pg_statio_all_tables st 
           ON st.relname = c.table_name
         LEFT JOIN pg_catalog.pg_description pgd 
           ON pgd.objoid = st.relid 
           AND pgd.objsubid = c.ordinal_position
         WHERE c.table_schema = $1 AND c.table_name = $2
         ORDER BY c.ordinal_position`,
        [schemaName, table_name]
      ),
      pool.query<{
        column_name: string;
        foreign_table_name: string;
        foreign_column_name: string;
      }>(
        `SELECT 
           kcu.column_name,
           ccu.table_name AS foreign_table_name,
           ccu.column_name AS foreign_column_name
         FROM information_schema.table_constraints tc
         JOIN information_schema.key_column_usage kcu 
           ON tc.constraint_name = kcu.constraint_name
         JOIN information_schema.constraint_column_usage ccu 
           ON ccu.constraint_name = tc.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY' 
           AND tc.table_name = $1`,
        [table_name]
      ),
      pool.query<{ count: string }>(
        `SELECT reltuples::bigint AS count FROM pg_class WHERE relname = $1`,
        [table_name]
      ),
    ]);

    tables.push({
      tableName: table_name,
      rowCount: parseInt(countResult.rows[0]?.count ?? "0"),
      columns: columnsResult.rows.map((row) => ({
        columnName: row.column_name,
        dataType: row.data_type,
        isNullable: row.is_nullable === "YES",
        defaultValue: row.column_default,
        comment: row.description,
      })),
      foreignKeys: fkResult.rows.map((row) => ({
        columnName: row.column_name,
        referencedTable: row.foreign_table_name,
        referencedColumn: row.foreign_column_name,
      })),
    });
  }

  return tables;
}

function schemaToPrompt(tables: TableInfo[]): string {
  return tables
    .map((table) => {
      const columns = table.columns
        .map((col) => {
          const parts = [
            `  ${col.columnName} ${col.dataType}`,
            col.isNullable ? "NULL" : "NOT NULL",
          ];
          if (col.comment) parts.push(`-- ${col.comment}`);
          return parts.join(" ");
        })
        .join("\n");

      const fks = table.foreignKeys
        .map(
          (fk) =>
            `  -- ${fk.columnName} references ${fk.referencedTable}(${fk.referencedColumn})`
        )
        .join("\n");

      return `TABLE ${table.tableName} (~${table.rowCount.toLocaleString()} rows)\n(\n${columns}${fks ? "\n" + fks : ""}\n)`;
    })
    .join("\n\n");
}

Extracting row counts alongside the schema helps the model write more efficient queries — it knows whether to worry about full table scans.

Safety controls: the non-negotiable layer

Before generating any SQL, you need to decide what operations are permitted. A read-only assistant should never generate UPDATE, DELETE, DROP, or TRUNCATE. These checks belong in two places: the system prompt (instruction to the model) and a post-generation validator (guarantee).

The model will generally follow instructions, but don't rely on that alone. The validator catches edge cases like /* comment */ DELETE or CTEs that modify data.

const FORBIDDEN_OPERATIONS = [
  "DELETE",
  "UPDATE",
  "INSERT",
  "DROP",
  "TRUNCATE",
  "ALTER",
  "CREATE",
  "GRANT",
  "REVOKE",
  "EXECUTE",
];

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

function validateSQL(sql: string): ValidationResult {
  const errors: string[] = [];
  const warnings: string[] = [];

  // Strip comments before checking
  const stripped = sql
    .replace(/--[^\n]*/g, "")
    .replace(/\/\*[\s\S]*?\*\//g, "")
    .toUpperCase();

  // Check for forbidden operations
  for (const op of FORBIDDEN_OPERATIONS) {
    // Use word boundary check
    const regex = new RegExp(`\\b${op}\\b`);
    if (regex.test(stripped)) {
      errors.push(`Forbidden operation: ${op}`);
    }
  }

  // Check for semicolons indicating multiple statements
  const statements = sql.split(";").filter((s) => s.trim().length > 0);
  if (statements.length > 1) {
    errors.push("Multiple SQL statements are not allowed");
  }

  // Warn about expensive operations
  if (!stripped.includes("WHERE") && !stripped.includes("LIMIT")) {
    warnings.push(
      "Query has no WHERE clause or LIMIT — may return large result sets"
    );
  }

  if (stripped.includes("SELECT *")) {
    warnings.push("SELECT * — consider selecting specific columns");
  }

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

For production systems, also enforce at the database level: create a read-only role and connect using it for all assistant queries. Even if your validator has a gap, the database will reject the mutation.

-- Create read-only role
CREATE ROLE assistant_readonly;
GRANT CONNECT ON DATABASE your_db TO assistant_readonly;
GRANT USAGE ON SCHEMA public TO assistant_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO assistant_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO assistant_readonly;

SQL generation with context and examples

The system prompt for SQL generation needs to be precise. Vague instructions produce vague SQL:

import Anthropic from "@anthropic-ai/sdk";

const client = new Anthropic();

interface GenerationOptions {
  maxRows?: number;
  dialect?: "postgresql" | "mysql" | "sqlite";
}

async function generateSQL(
  naturalLanguageQuery: string,
  schema: TableInfo[],
  options: GenerationOptions = {}
): Promise<{ sql: string; explanation: string; confidence: "high" | "medium" | "low" }> {
  const { maxRows = 1000, dialect = "postgresql" } = options;
  const schemaText = schemaToPrompt(schema);

  const systemPrompt = `You are a SQL query generator for ${dialect}. Generate ONLY SELECT queries — never INSERT, UPDATE, DELETE, DROP, or any data-modifying statements.

DATABASE SCHEMA:
${schemaText}

RULES:
1. Generate valid ${dialect} SQL only
2. Always add LIMIT ${maxRows} unless the user explicitly asks for all records
3. Use table aliases for readability
4. For date arithmetic, use ${dialect === "postgresql" ? "NOW() - INTERVAL '...' " : "DATE_SUB(NOW(), INTERVAL ...)"} syntax
5. If a request is ambiguous, generate the most conservative interpretation and note the ambiguity in your explanation
6. If a request cannot be fulfilled with the available schema, say so explicitly rather than hallucinating columns or tables

OUTPUT FORMAT (JSON):
{
  "sql": "the SQL query",
  "explanation": "plain-English explanation of what the query does",
  "confidence": "high|medium|low",
  "ambiguities": ["list any ambiguous parts of the request"],
  "assumptions": ["list any assumptions made"]
}`;

  const response = await client.messages.create({
    model: "claude-opus-4-5",
    max_tokens: 1024,
    system: systemPrompt,
    messages: [{ role: "user", content: naturalLanguageQuery }],
  });

  const text =
    response.content[0].type === "text" ? response.content[0].text : "";
  const jsonMatch = text.match(/\{[\s\S]*\}/);
  if (!jsonMatch) throw new Error("Failed to parse SQL generation output");

  const parsed = JSON.parse(jsonMatch[0]);

  return {
    sql: parsed.sql,
    explanation: parsed.explanation,
    confidence: parsed.confidence ?? "medium",
  };
}

Ambiguity resolution: asking before assuming

Some natural language queries are genuinely ambiguous with respect to the schema. "Show me recent orders" — what's "recent"? Last day? Last week? Last month? Getting this wrong wastes user time and erodes trust.

Build an ambiguity detection step that identifies unclear requests before generating SQL:

interface AmbiguityCheck {
  isAmbiguous: boolean;
  questions: string[];
  suggestedClarifications: string[];
}

async function checkAmbiguity(
  query: string,
  schema: TableInfo[]
): Promise<AmbiguityCheck> {
  const schemaText = schemaToPrompt(schema);

  const response = await client.messages.create({
    model: "claude-haiku-4-5",
    max_tokens: 512,
    system: `You check if natural language database queries are ambiguous given a schema.

DATABASE SCHEMA:
${schemaText}

Identify ambiguities that would require making assumptions to generate SQL. Consider:
- Undefined time periods ("recent", "latest", "old")
- Unclear filtering criteria ("active users", "important orders")  
- Multiple possible interpretations of join conditions
- Vague aggregations ("summarise", "group by something")

Don't flag things that have obvious defaults (e.g. "count" clearly means COUNT(*)).

Return JSON:
{
  "isAmbiguous": boolean,
  "questions": ["question to ask the user"],
  "suggestedClarifications": ["suggested answer 1", "suggested answer 2"]
}`,
    messages: [{ role: "user", content: query }],
  });

  const text =
    response.content[0].type === "text" ? response.content[0].text : "";
  const jsonMatch = text.match(/\{[\s\S]*\}/);
  if (!jsonMatch) return { isAmbiguous: false, questions: [], suggestedClarifications: [] };

  return JSON.parse(jsonMatch[0]);
}

Use this in a pre-flight check: if the query is ambiguous, present the clarifying questions to the user before generating SQL. This catches the most common source of incorrect queries.

Execution with result limiting and formatting

Once you have validated SQL, execute it with safeguards against runaway queries:

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

async function executeQuery(
  sql: string,
  maxRows = 1000
): Promise<QueryResult> {
  const validation = validateSQL(sql);
  if (!validation.isValid) {
    throw new Error(`SQL validation failed: ${validation.errors.join(", ")}`);
  }

  const start = Date.now();

  // Set statement timeout to prevent runaway queries (5 seconds)
  const client = await pool.connect();
  try {
    await client.query("SET statement_timeout = 5000");

    const result = await client.query(sql);
    const executionTimeMs = Date.now() - start;

    const truncated = result.rows.length >= maxRows;
    const rows = result.rows.slice(0, maxRows);

    return {
      columns: result.fields.map((f) => f.name),
      rows,
      rowCount: result.rows.length,
      executionTimeMs,
      truncated,
    };
  } finally {
    client.release();
  }
}

Natural language result summaries

Raw query results are useful for technical users, but for business users you want a natural language summary. Pipe the results back through the LLM:

async function summariseResults(
  originalQuery: string,
  sql: string,
  results: QueryResult
): Promise<string> {
  if (results.rows.length === 0) {
    return "The query returned no results.";
  }

  // For large result sets, summarise stats rather than all rows
  const resultSample =
    results.rows.length > 20
      ? JSON.stringify(results.rows.slice(0, 10))
      : JSON.stringify(results.rows);

  const response = await client.messages.create({
    model: "claude-haiku-4-5",
    max_tokens: 512,
    messages: [
      {
        role: "user",
        content: `The user asked: "${originalQuery}"

SQL executed: ${sql}

Results (${results.rowCount} rows${results.truncated ? ", showing first 10" : ""}):
${resultSample}

Summarise the results in plain English, directly answering the user's original question. Include specific numbers where relevant. Be concise.`,
      },
    ],
  });

  return response.content[0].type === "text" ? response.content[0].text : "";
}

The complete assistant: putting it together

interface AssistantResponse {
  status: "success" | "clarification_needed" | "error" | "unsafe";
  sql?: string;
  results?: QueryResult;
  summary?: string;
  questions?: string[];
  explanation?: string;
  warnings?: string[];
  error?: string;
}

class SQLAssistant {
  private schema: TableInfo[] = [];

  async initialize(): Promise<void> {
    this.schema = await extractSchema();
    console.log(
      `Loaded schema: ${this.schema.length} tables, ${this.schema.reduce((sum, t) => sum + t.columns.length, 0)} columns`
    );
  }

  async query(naturalLanguage: string): Promise<AssistantResponse> {
    try {
      // 1. Check for ambiguity
      const ambiguity = await checkAmbiguity(naturalLanguage, this.schema);
      if (ambiguity.isAmbiguous) {
        return {
          status: "clarification_needed",
          questions: ambiguity.questions,
        };
      }

      // 2. Generate SQL
      const generated = await generateSQL(naturalLanguage, this.schema);

      // 3. Validate
      const validation = validateSQL(generated.sql);
      if (!validation.isValid) {
        return {
          status: "unsafe",
          error: `Query cannot be executed: ${validation.errors.join(", ")}`,
          sql: generated.sql,
        };
      }

      // 4. Execute
      const results = await executeQuery(generated.sql);

      // 5. Summarise
      const summary = await summariseResults(
        naturalLanguage,
        generated.sql,
        results
      );

      return {
        status: "success",
        sql: generated.sql,
        results,
        summary,
        explanation: generated.explanation,
        warnings: validation.warnings,
      };
    } catch (err: any) {
      return {
        status: "error",
        error: err.message,
      };
    }
  }
}

// Usage
const assistant = new SQLAssistant();
await assistant.initialize();

const response = await assistant.query(
  "How many new users signed up each month this year?"
);

if (response.status === "success") {
  console.log("Summary:", response.summary);
  console.log("SQL:", response.sql);
  console.log("Rows:", response.results?.rowCount);
} else if (response.status === "clarification_needed") {
  console.log("Need clarification:", response.questions);
}

Schema caching and handling schema changes

Extracting schema on every query is expensive. Cache it in memory with a TTL and a manual invalidation hook:

class SchemaCache {
  private schema: TableInfo[] | null = null;
  private lastFetched: number = 0;
  private ttlMs: number;

  constructor(ttlMs = 5 * 60 * 1000) {
    // 5 minutes default
    this.ttlMs = ttlMs;
  }

  async get(): Promise<TableInfo[]> {
    const now = Date.now();
    if (this.schema && now - this.lastFetched < this.ttlMs) {
      return this.schema;
    }

    this.schema = await extractSchema();
    this.lastFetched = now;
    return this.schema;
  }

  invalidate(): void {
    this.schema = null;
    this.lastFetched = 0;
  }
}

For applications where schema changes are infrequent (most production systems), a 5-minute TTL provides a good balance between freshness and performance. If you use migrations, trigger cache.invalidate() after each migration completes.

Handling multi-turn conversations

A complete assistant supports follow-up questions. "Show me the top 10 customers by revenue" followed by "now filter by Europe" requires context from the previous exchange:

interface ConversationTurn {
  userQuery: string;
  generatedSQL?: string;
  results?: QueryResult;
}

async function generateSQLWithHistory(
  query: string,
  history: ConversationTurn[],
  schema: TableInfo[]
): Promise<{ sql: string; explanation: string }> {
  const historyContext = history
    .map(
      (turn, i) =>
        `Turn ${i + 1}: "${turn.userQuery}" → ${turn.generatedSQL ?? "no SQL"}`
    )
    .join("\n");

  const schemaText = schemaToPrompt(schema);

  const response = await client.messages.create({
    model: "claude-opus-4-5",
    max_tokens: 1024,
    system: `You generate SQL queries. You have access to conversation history to resolve references like "that", "those", "them", "now filter by...".

DATABASE SCHEMA:
${schemaText}

CONVERSATION HISTORY:
${historyContext}

Generate ONLY SELECT queries. Return JSON with "sql" and "explanation" fields.`,
    messages: [{ role: "user", content: query }],
  });

  const text =
    response.content[0].type === "text" ? response.content[0].text : "";
  const jsonMatch = text.match(/\{[\s\S]*\}/);
  if (!jsonMatch) throw new Error("Failed to parse response");
  return JSON.parse(jsonMatch[0]);
}

The most significant production investment in a SQL assistant is not the SQL generation — models are quite capable of that. It's the safety layer, the ambiguity handling, and the result formatting that determine whether users can actually trust and use it. Build those first.