⬡ API & SDK/2026-03-28Advanced

Building a Text-to-SQL Intelligent Agent with Claude API — Schema Inference, Query Optimization, and Secure Execution for Production

Learn how to build an intelligent agent that converts natural language to SQL using the Claude API. Covers schema inference, query optimization, security hardening, and production-grade implementation patterns.

Claude API¹¹⁵ Text-to-SQL agent¹³ database² natural language production¹¹¹

✦ Premium Article

Why You Need a Text-to-SQL Agent

"Show me last month's revenue by region." "Which subscription plan has the highest average session time?" — If non-engineers could get instant answers to questions like these, data-driven decision making would accelerate dramatically.

A Text-to-SQL agent converts natural language questions into SQL queries, safely executes them against a database, and returns the results in plain language. By combining the Claude API's powerful language understanding with its Tool Use capability, you can build an intelligent data analysis agent that goes far beyond simple SQL generation.

In this article, we'll walk through building a production-quality Text-to-SQL agent from architecture to implementation, covering:

Automatic schema inference and context understanding
Safe SQL generation and validation
Natural language summarization of query results
Automatic error correction loops
Production-grade security hardening

For foundational knowledge on the Claude API, see Claude API Data Analysis Intro Guide. For defending Tool Use output with layered validation, see Structured Responses and Layered JSON Schema Validation, and to scale this into a larger autonomous analysis agent, see Building an Autonomous Data Analysis Agent.

Prerequisites

Required Environment

Node.js 20 or later (TypeScript recommended)
PostgreSQL 15 or later (sample DB)
Anthropic API key

Package Installation

npm install @anthropic-ai/sdk pg zod dotenv
npm install -D typescript @types/pg @types/node

Sample Database

We'll use an e-commerce schema throughout this article:

-- Sample schema (PostgreSQL)
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  plan VARCHAR(20) DEFAULT 'free',  -- free / pro / enterprise
  created_at TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total_amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',  -- pending / completed / refunded
  region VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  category VARCHAR(100),
  price DECIMAL(10, 2) NOT NULL
);
 
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

✦

Thank you for reading this far.

Continue Reading

What follows includes implementation code, benchmarks, and practical content we hope you'll find useful. This site runs without ads — server and development costs are supported entirely by members like you. If it's been helpful, we'd be truly grateful for your support.

WHAT YOU'LL LEARN

✦Dynamic table selection that cuts input tokens by ~92%, and how to measure the gain

✦Why the error-correction loop should cap at 3 attempts (success rate plateaus after that)

✦An AST check that prevents broken LIMIT injection into aggregate queries — not in the docs

Secure payment via Stripe · Cancel anytime

✦

Unlock This Article

Get full access to the rest of this article. Buy once, read anytime. This site is ad-free — your support goes directly toward keeping it running.

Unlock all articles with Membership →

Architecture Design — The Agent Pipeline

The Text-to-SQL agent operates through five stages:

Stage 1: Schema Inference Automatically retrieve database metadata — table names, column names, types, and foreign key relationships — and convert it into a format Claude can understand.

Stage 2: Query Generation Claude API generates an SQL query based on the user's natural language question and the schema information.

Stage 3: Validation Parse the generated SQL and verify it contains no dangerous operations (DELETE, DROP, etc.) and doesn't exceed cost limits.

Stage 4: Execution Run the validated SQL within a read-only transaction and retrieve results.

Stage 5: Result Interpretation Pass the execution results back to Claude and generate a natural language answer to the user's question.

// Agent flow overview (conceptual code)
async function textToSqlAgent(question: string): Promise<AgentResponse> {
  // Stage 1: Retrieve schema information
  const schema = await inferSchema();
 
  // Stage 2: Generate SQL (Claude API)
  const sql = await generateSQL(question, schema);
 
  // Stage 3: Validate
  const validated = validateSQL(sql);
  if (!validated.safe) {
    return { error: validated.reason };
  }
 
  // Stage 4: Execute
  const results = await executeQuery(validated.sql);
 
  // Stage 5: Interpret results (Claude API)
  const answer = await interpretResults(question, sql, results);
 
  return { answer, sql, rowCount: results.length };
}

Implementing Schema Inference — Automatically Understanding Database Structure

The quality of schema information is the single biggest factor in Text-to-SQL accuracy. Table and column names alone aren't enough — including foreign key relationships, possible column values, and inter-table relationships dramatically improves Claude's SQL generation accuracy.

import { Pool } from "pg";
 
interface ColumnInfo {
  name: string;
  type: string;
  nullable: boolean;
  description: string | null;
}
 
interface TableInfo {
  name: string;
  columns: ColumnInfo[];
  foreignKeys: { column: string; references: string }[];
  sampleValues: Record<string, string[]>;
  rowCount: number;
}
 
async function inferSchema(pool: Pool): Promise<TableInfo[]> {
  const tables: TableInfo[] = [];
 
  // Get table list
  const tableResult = await pool.query(`
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_type = 'BASE TABLE'
    ORDER BY table_name
  `);
 
  for (const row of tableResult.rows) {
    const tableName = row.table_name;
 
    // Column information
    const columnsResult = await pool.query(`
      SELECT
        c.column_name,
        c.data_type,
        c.is_nullable,
        pgd.description
      FROM information_schema.columns c
      LEFT JOIN pg_catalog.pg_statio_all_tables st
        ON c.table_name = st.relname
      LEFT JOIN pg_catalog.pg_description pgd
        ON pgd.objoid = st.relid
        AND pgd.objsubid = c.ordinal_position
      WHERE c.table_name = $1
        AND c.table_schema = 'public'
      ORDER BY c.ordinal_position
    `, [tableName]);
 
    // Foreign key relationships
    const fkResult = await pool.query(`
      SELECT
        kcu.column_name,
        ccu.table_name || '.' || ccu.column_name AS references_column
      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 tc.constraint_name = ccu.constraint_name
      WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_name = $1
    `, [tableName]);
 
    // Sample values for enum-like columns
    const sampleValues: Record<string, string[]> = {};
    for (const col of columnsResult.rows) {
      if (col.data_type === "character varying" || col.data_type === "text") {
        const sampleResult = await pool.query(`
          SELECT DISTINCT ${col.column_name}
          FROM ${tableName}
          WHERE ${col.column_name} IS NOT NULL
          LIMIT 10
        `);
        if (sampleResult.rows.length <= 10) {
          sampleValues[col.column_name] = sampleResult.rows.map(
            (r: Record<string, string>) => r[col.column_name]
          );
        }
      }
    }
 
    // Approximate row count
    const countResult = await pool.query(`
      SELECT reltuples::bigint AS estimate
      FROM pg_class
      WHERE relname = $1
    `, [tableName]);
 
    tables.push({
      name: tableName,
      columns: columnsResult.rows.map((c) => ({
        name: c.column_name,
        type: c.data_type,
        nullable: c.is_nullable === "YES",
        description: c.description,
      })),
      foreignKeys: fkResult.rows.map((fk) => ({
        column: fk.column_name,
        references: fk.references_column,
      })),
      sampleValues,
      rowCount: Number(countResult.rows[0]?.estimate || 0),
    });
  }
 
  return tables;
}

Formatting Schema for the Prompt

Converting the retrieved schema information into a Claude-friendly format is critical for query generation quality.

function formatSchemaForPrompt(tables: TableInfo[]): string {
  return tables.map((table) => {
    const cols = table.columns
      .map((c) => {
        let desc = `  - ${c.name} (${c.type}${c.nullable ? ", nullable" : ""})`;
        if (c.description) desc += ` -- ${c.description}`;
        return desc;
      })
      .join("\n");
 
    const fks = table.foreignKeys.length > 0
      ? `  Foreign Keys:\n${table.foreignKeys
          .map((fk) => `    - ${fk.column} → ${fk.references}`)
          .join("\n")}`
      : "";
 
    const samples = Object.entries(table.sampleValues)
      .map(([col, vals]) => `    ${col}: [${vals.join(", ")}]`)
      .join("\n");
 
    const sampleSection = samples
      ? `  Sample Values:\n${samples}`
      : "";
 
    return `TABLE: ${table.name} (~${table.rowCount} rows)\n${cols}\n${fks}\n${sampleSection}`;
  }).join("\n\n");
}

The SQL Generation Engine — Claude API × Tool Use Pattern

For SQL generation, we leverage Claude API's Tool Use feature to obtain structured output. By having Claude return SQL as a tool call rather than free-form text, we make parsing straightforward and robust.

import Anthropic from "@anthropic-ai/sdk";
 
const anthropic = new Anthropic();
 
// SQL generator tool definition
const sqlGeneratorTool: Anthropic.Tool = {
  name: "execute_sql",
  description: "Execute a generated SQL query. Only SELECT queries are permitted.",
  input_schema: {
    type: "object" as const,
    properties: {
      sql: {
        type: "string",
        description: "The SQL query to execute (SELECT only)",
      },
      explanation: {
        type: "string",
        description: "Explanation of how this query answers the question",
      },
      confidence: {
        type: "number",
        description: "Confidence in query accuracy (0.0-1.0)",
      },
    },
    required: ["sql", "explanation", "confidence"],
  },
};
 
interface SQLGenerationResult {
  sql: string;
  explanation: string;
  confidence: number;
}
 
async function generateSQL(
  question: string,
  schemaText: string
): Promise<SQLGenerationResult> {
  const systemPrompt = `You are a PostgreSQL database expert.
Convert the user's natural language questions into accurate SQL queries.
 
## Rules
1. Generate SELECT statements only (INSERT/UPDATE/DELETE/DROP etc. are strictly forbidden)
2. Only use table and column names that exist in the schema
3. JOINs must be based on foreign key relationships
4. Always include appropriate GROUP BY when using aggregate functions
5. Add a LIMIT clause for large datasets (default 100 rows)
6. Reference column comments for context when available
 
## Database Schema
${schemaText}`;
 
  const response = await anthropic.messages.create({
    model: "claude-sonnet-4-6",
    max_tokens: 2048,
    system: systemPrompt,
    tools: [sqlGeneratorTool],
    messages: [
      {
        role: "user",
        content: question,
      },
    ],
  });
 
  // Extract SQL information from Tool Use response
  for (const block of response.content) {
    if (block.type === "tool_use" && block.name === "execute_sql") {
      const input = block.input as SQLGenerationResult;
      return {
        sql: input.sql,
        explanation: input.explanation,
        confidence: input.confidence,
      };
    }
  }
 
  throw new Error("Claude failed to generate an SQL query");
}

Prompt Design Principles

Several key principles drive high-accuracy SQL generation:

Always include schema in the system prompt: Placing schema information in the system prompt rather than the user message lets Claude treat database structure as "background knowledge."

Include sample values: When Claude knows that a status column can only contain ['pending', 'completed', 'refunded'], it generates WHERE status = 'completed' rather than guessing incorrectly.

Define disambiguation rules: Specify default interpretations for ambiguous terms — does "revenue" mean SUM(total_amount) or order count? Making this explicit in the system prompt prevents misinterpretation.

SQL Validation — The Most Critical Security Layer

Running Claude-generated SQL directly is unacceptable in production. We implement defense in depth with multiple validation layers.

import { z } from "zod";
 
// Validation result type
interface ValidationResult {
  safe: boolean;
  sql: string;
  reason?: string;
  warnings: string[];
}
 
// Forbidden patterns
const FORBIDDEN_PATTERNS = [
  /\b(INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|TRUNCATE|GRANT|REVOKE)\b/i,
  /\b(INTO\s+OUTFILE|LOAD_FILE|EXEC|EXECUTE)\b/i,
  /;\s*\w/,  // Statements after semicolons (SQL injection defense)
  /--\s/,     // SQL comments (prevent intentional comment-out attacks)
  /\/\*[\s\S]*?\*\//,  // Block comments
  /\bpg_\w+/i,  // PostgreSQL system table access
  /\binformation_schema\b/i,  // Metadata tables
];
 
// Cost estimation limits
const MAX_ESTIMATED_ROWS = 100000;
const MAX_JOIN_COUNT = 5;
 
function validateSQL(sql: string): ValidationResult {
  const warnings: string[] = [];
 
  // 1. Empty query check
  if (!sql || sql.trim().length === 0) {
    return { safe: false, sql, reason: "Empty query", warnings };
  }
 
  // 2. Forbidden pattern check
  for (const pattern of FORBIDDEN_PATTERNS) {
    if (pattern.test(sql)) {
      return {
        safe: false,
        sql,
        reason: `Forbidden pattern detected: ${pattern.source}`,
        warnings,
      };
    }
  }
 
  // 3. Must start with SELECT
  const trimmed = sql.trim();
  if (!/^(SELECT|WITH)\b/i.test(trimmed)) {
    return {
      safe: false,
      sql,
      reason: "Query does not start with SELECT/WITH",
      warnings,
    };
  }
 
  // 4. JOIN count limit
  const joinCount = (sql.match(/\bJOIN\b/gi) || []).length;
  if (joinCount > MAX_JOIN_COUNT) {
    return {
      safe: false,
      sql,
      reason: `JOIN count exceeds limit (${MAX_JOIN_COUNT}): ${joinCount}`,
      warnings,
    };
  }
 
  // 5. Force LIMIT clause
  let safeSql = sql;
  if (!/\bLIMIT\b/i.test(sql)) {
    safeSql = `${sql.replace(/;?\s*$/, "")} LIMIT 100`;
    warnings.push("Automatically added LIMIT 100");
  }
 
  // 6. Subquery nesting depth check
  const nestDepth = (sql.match(/\(/g) || []).length;
  if (nestDepth > 10) {
    return {
      safe: false,
      sql,
      reason: `Subquery nesting depth exceeds limit: ${nestDepth}`,
      warnings,
    };
  }
 
  return { safe: true, sql: safeSql, warnings };
}

Read-Only Transaction Execution

Beyond validation, we harden the database connection itself:

async function executeQuery(
  pool: Pool,
  sql: string,
  timeoutMs: number = 10000
): Promise<{ rows: Record<string, unknown>[]; duration: number }> {
  const client = await pool.connect();
 
  try {
    // Start a read-only transaction
    await client.query("BEGIN READ ONLY");
 
    // Set statement timeout
    await client.query(`SET statement_timeout = '${timeoutMs}'`);
 
    const start = Date.now();
    const result = await client.query(sql);
    const duration = Date.now() - start;
 
    await client.query("COMMIT");
 
    return {
      rows: result.rows,
      duration,
    };
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Three key protections here: BEGIN READ ONLY physically blocks write operations at the PostgreSQL engine level, statement_timeout kills long-running queries, and errors always trigger a ROLLBACK.

Automatic Error Correction Loop — An Agent That Learns from Failures

In production, Claude's generated SQL won't always execute correctly on the first try. Table name typos, GROUP BY mismatches, type conflicts — all manner of errors arise. Implementing an automatic correction loop dramatically improves the agent's practical utility.

interface AgentResponse {
  answer: string;
  sql: string;
  rowCount: number;
  attempts: number;
  warnings: string[];
}
 
async function textToSqlAgentWithRetry(
  pool: Pool,
  question: string,
  maxRetries: number = 3
): Promise<AgentResponse> {
  const schema = await inferSchema(pool);
  const schemaText = formatSchemaForPrompt(schema);
 
  let lastError: string | null = null;
  let allWarnings: string[] = [];
 
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      // Generate SQL with error context if available
      const prompt = lastError
        ? `${question}\n\nNote: The previous query produced an error:\n${lastError}\nPlease generate a corrected query.`
        : question;
 
      const generated = await generateSQL(prompt, schemaText);
 
      // Warn on low confidence
      if (generated.confidence < 0.7) {
        allWarnings.push(
          `Low confidence score (${generated.confidence}): ${generated.explanation}`
        );
      }
 
      // Validate
      const validated = validateSQL(generated.sql);
      if (!validated.safe) {
        lastError = `Validation failed: ${validated.reason}`;
        continue;
      }
      allWarnings = allWarnings.concat(validated.warnings);
 
      // Execute
      const result = await executeQuery(pool, validated.sql);
 
      // Interpret results
      const answer = await interpretResults(
        question,
        validated.sql,
        result.rows,
        result.duration
      );
 
      return {
        answer,
        sql: validated.sql,
        rowCount: result.rows.length,
        attempts: attempt,
        warnings: allWarnings,
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : String(error);
      lastError = errorMessage;
 
      if (attempt === maxRetries) {
        return {
          answer: `Sorry, after ${maxRetries} attempts I was unable to generate a correct query. Last error: ${errorMessage}`,
          sql: "",
          rowCount: 0,
          attempts: attempt,
          warnings: allWarnings,
        };
      }
    }
  }
 
  throw new Error("Unexpected loop exit");
}

Result Interpretation and Natural Language Answers

When returning query results to the user, we again leverage Claude API to produce clear, natural language summaries.

async function interpretResults(
  question: string,
  sql: string,
  rows: Record<string, unknown>[],
  durationMs: number
): Promise<string> {
  // Truncate large result sets
  const maxRows = 50;
  const truncated = rows.length > maxRows;
  const displayRows = truncated ? rows.slice(0, maxRows) : rows;
 
  const response = await anthropic.messages.create({
    model: "claude-sonnet-4-6",
    max_tokens: 2048,
    system: `You are a data analyst. Answer the user's question based on SQL query results.
 
## Rules
- Format numbers with appropriate units and formatting (e.g., $1,234,567)
- Highlight trends or patterns when present
- If results are empty, suggest possible reasons
- Use clear, business-friendly language — avoid jargon`,
    messages: [
      {
        role: "user",
        content: `## Question
${question}
 
## Executed SQL
\`\`\`sql
${sql}
\`\`\`
 
## Results (${rows.length} rows${truncated ? `, showing first ${maxRows}` : ""}, ${durationMs}ms)
\`\`\`json
${JSON.stringify(displayRows, null, 2)}
\`\`\`
 
Based on these results, provide a clear answer to the question.`,
      },
    ],
  });
 
  const textBlock = response.content.find((b) => b.type === "text");
  return textBlock ? textBlock.text : "Failed to interpret results";
}

Advanced Production Optimizations

Cost Reduction with Prompt Caching

Schema information rarely changes, so leveraging Claude API's Prompt Caching can dramatically reduce costs.

async function generateSQLWithCaching(
  question: string,
  schemaText: string
): Promise<SQLGenerationResult> {
  const response = await anthropic.messages.create({
    model: "claude-sonnet-4-6",
    max_tokens: 2048,
    system: [
      {
        type: "text",
        text: `You are a PostgreSQL database expert.
Convert the user's natural language questions into accurate SQL queries.`,
      },
      {
        type: "text",
        text: `## Database Schema\n${schemaText}`,
        cache_control: { type: "ephemeral" },  // Cache the schema portion
      },
    ],
    tools: [sqlGeneratorTool],
    messages: [{ role: "user", content: question }],
  });
 
  // ... response processing (same as above)
}

Adding cache_control: { type: "ephemeral" } caches the schema information. For the second request onward within the same session, input token costs drop by 90% due to cache hits. For agents processing hundreds to thousands of queries per day, this optimization has a massive impact.

Pre-Execution Query Plan Validation

Verify query execution plans before running them and block queries that are too expensive:

async function estimateQueryCost(
  pool: Pool,
  sql: string
): Promise<{ estimatedRows: number; estimatedCost: number }> {
  const client = await pool.connect();
  try {
    const result = await client.query(`EXPLAIN (FORMAT JSON) ${sql}`);
    const plan = result.rows[0]["QUERY PLAN"][0]["Plan"];
 
    return {
      estimatedRows: plan["Plan Rows"],
      estimatedCost: plan["Total Cost"],
    };
  } finally {
    client.release();
  }
}
 
// Cost limit check
const MAX_QUERY_COST = 50000;
 
async function validateWithCostCheck(
  pool: Pool,
  sql: string
): Promise<ValidationResult> {
  const basicValidation = validateSQL(sql);
  if (!basicValidation.safe) return basicValidation;
 
  try {
    const cost = await estimateQueryCost(pool, basicValidation.sql);
    if (cost.estimatedCost > MAX_QUERY_COST) {
      return {
        safe: false,
        sql: basicValidation.sql,
        reason: `Query cost exceeds limit (${MAX_QUERY_COST}): ${cost.estimatedCost}`,
        warnings: basicValidation.warnings,
      };
    }
    return basicValidation;
  } catch {
    // If EXPLAIN fails, return the basic validation result
    return basicValidation;
  }
}

Conversational Context with Session History

Support follow-up questions by maintaining conversation history across turns:

interface ConversationTurn {
  question: string;
  sql: string;
  resultSummary: string;
}
 
class TextToSqlSession {
  private history: ConversationTurn[] = [];
  private pool: Pool;
  private schemaText: string = "";
 
  constructor(pool: Pool) {
    this.pool = pool;
  }
 
  async initialize(): Promise<void> {
    const schema = await inferSchema(this.pool);
    this.schemaText = formatSchemaForPrompt(schema);
  }
 
  async ask(question: string): Promise<AgentResponse> {
    // Build context including conversation history
    const contextMessages: Anthropic.MessageParam[] = [];
 
    for (const turn of this.history.slice(-5)) {  // Last 5 turns
      contextMessages.push({
        role: "user",
        content: turn.question,
      });
      contextMessages.push({
        role: "assistant",
        content: `SQL: ${turn.sql}\nResult: ${turn.resultSummary}`,
      });
    }
 
    contextMessages.push({
      role: "user",
      content: question,
    });
 
    // Generate SQL with conversation history
    const response = await anthropic.messages.create({
      model: "claude-sonnet-4-6",
      max_tokens: 2048,
      system: [
        {
          type: "text",
          text: "You are a PostgreSQL expert. Generate SQL considering the conversation context. Resolve pronouns like 'that' or 'the previous one' from recent conversation history.",
        },
        {
          type: "text",
          text: `## Database Schema\n${this.schemaText}`,
          cache_control: { type: "ephemeral" },
        },
      ],
      tools: [sqlGeneratorTool],
      messages: contextMessages,
    });
 
    // ... response processing and history update
    // Expected behavior:
    // Q: "What was last month's revenue?" → SQL: SELECT SUM(total_amount) FROM orders WHERE ...
    // Q: "Break that down by region" → SQL: SELECT region, SUM(total_amount) FROM orders WHERE ... GROUP BY region
    return {} as AgentResponse;  // Simplified
  }
}

When a user asks "What was last month's revenue?" followed by "Break that down by region," the agent retains the "last month's revenue" context and automatically generates a region-grouped query.

Monitoring and Logging — Production Observability

In production, continuously monitoring agent behavior is essential for anomaly detection and performance improvement.

interface QueryLog {
  timestamp: string;
  question: string;
  generatedSql: string;
  executionTimeMs: number;
  rowCount: number;
  attempts: number;
  success: boolean;
  error?: string;
  tokenUsage: {
    inputTokens: number;
    outputTokens: number;
  };
}
 
class QueryLogger {
  private logs: QueryLog[] = [];
 
  log(entry: QueryLog): void {
    this.logs.push(entry);
 
    // Anomaly detection: failed after 3 retries
    if (!entry.success && entry.attempts >= 3) {
      console.warn(
        `[ALERT] Query generation failed (${entry.attempts} attempts): ${entry.question}`
      );
    }
 
    // Performance warning: over 5 seconds
    if (entry.executionTimeMs > 5000) {
      console.warn(
        `[SLOW QUERY] ${entry.executionTimeMs}ms: ${entry.generatedSql}`
      );
    }
  }
 
  getStats(): {
    totalQueries: number;
    successRate: number;
    avgExecutionTime: number;
    avgAttempts: number;
  } {
    const total = this.logs.length;
    const successful = this.logs.filter((l) => l.success).length;
    const avgTime =
      this.logs.reduce((sum, l) => sum + l.executionTimeMs, 0) / total;
    const avgAttempts =
      this.logs.reduce((sum, l) => sum + l.attempts, 0) / total;
 
    return {
      totalQueries: total,
      successRate: successful / total,
      avgExecutionTime: Math.round(avgTime),
      avgAttempts: Math.round(avgAttempts * 100) / 100,
    };
  }
}

Implementation Insights the Official Docs Don't Mention

Everything above gets you something that works. But once you connect a Text-to-SQL agent to your own data and use it daily, you discover several places where the sample code won't survive production. I run a suite of iOS/Android wallpaper and relaxation apps (six titles today) that I've been building since 2014, and I wired this agent up as an internal tool over my AdMob revenue and App Store Connect export data. Asking questions like "which app's eCPM dropped this week?" or "is the spike in 3-star reviews correlated with an update date?" in plain language surfaced a handful of small but high-leverage lessons.

1. Don't send the full schema every time — dynamic table selection cuts input tokens by ~92%

My first naive version stuffed every table definition into the prompt. My analytics DB has about 40 tables, and the full schema alone exceeded 10,000 input tokens per request. The per-question cost stopped being negligible, and latency noticeably degraded.

So I added a lightweight "narrowing pass" that first asks Claude which tables are relevant.

// Select only the tables relevant to the question before building the schema
async function selectRelevantTables(
  question: string,
  tableSummaries: { name: string; summary: string }[]
): Promise<string[]> {
  const res = await client.messages.create({
    model: "claude-haiku-4-5-20251001", // narrowing is fine on a fast model
    max_tokens: 256,
    system:
      "Return only the table names needed to answer the question as a JSON array. " +
      "When in doubt, include the table. No explanation.",
    messages: [
      {
        role: "user",
        content:
          `Question: ${question}\n\nAvailable tables:\n` +
          tableSummaries.map((t) => `- ${t.name}: ${t.summary}`).join("\n"),
      },
    ],
  });
  const text = res.content[0].type === "text" ? res.content[0].text : "[]";
  const match = text.match(/\[[\s\S]*\]/);
  return match ? JSON.parse(match[0]) : tableSummaries.map((t) => t.name);
}

With this one extra step, the schema passed to the main SQL generation shrank to roughly 3 tables and ~800 tokens on average — about a 92% reduction versus the full 10,000-token dump. The narrowing itself runs accurately on Haiku, so the added cost is tiny. Once you cross ten tables, I strongly recommend this pattern.

2. Auto-injecting LIMIT into aggregate queries silently breaks results

A common safety measure is to auto-append LIMIT 1000 to every query. I did this too at first. The problem is that it misfires on aggregate queries like SELECT COUNT(*) or SUM(revenue) GROUP BY app_id. It truncates the GROUP BY output at 1,000 rows, so asking for "total revenue across all apps" quietly returns a partial result. Because it doesn't error out, it's actually more dangerous than a crash.

The fix is to make a quick AST-style check for aggregation before deciding whether to inject LIMIT, rather than blind string concatenation.

function shouldInjectLimit(sql: string): boolean {
  const normalized = sql.toLowerCase();
  // Skip when the query aggregates, groups, or already has a LIMIT
  const hasAggregate = /\b(count|sum|avg|min|max|group\s+by)\b/.test(normalized);
  const hasLimit = /\blimit\s+\d+/.test(normalized);
  return !hasAggregate && !hasLimit;
}

The official quickstart says "add a LIMIT for safety" but never mentions the aggregate exception. In real operation, this is exactly where you trip.

3. Three attempts is the cost/benefit sweet spot for the error-correction loop

The auto-correction loop is powerful, but running it without a cap means the model keeps repeating similar mistakes under the same wrong assumption while burning tokens. Across my recent operational logs (~1,200 queries), I measured the final success rate by attempt count: about 78% succeed on the first try, ~92% within two attempts, and ~97% within three. Cases that first succeeded on the fourth attempt or later were under a few percent of the total — and most were "questions too ambiguous to answer in the first place."

In other words, raising the cap to four or five only adds cost while success rate plateaus. I fix the cap at three and, when it's exceeded, return to the human asking them to make the question more specific. I recommend three not as a defensive anti-loop guard, but as a data-backed, deliberate cutoff.

4. Absorb the ambiguity of "last month" and "revenue" with business definitions in column comments

Ambiguity is natural language's worst enemy. When someone says "last month's revenue," do they mean by billing date or by accrual date? Tax included or excluded? Humans share unspoken assumptions; an LLM fills the gap by guessing and returns plausible but subtly misaligned SQL.

What helped most was passing each column's business-definition comment alongside the schema during inference. In my wallpaper apps' review analysis, I initially left "negative review" undefined and quietly dropped 3-star reviews from the aggregation. Once I made the rule explicit as a column comment — treat rating <= 3 as negative — those omissions virtually disappeared. The point is to feed the data dictionary to the agent together with the schema, not maintain it separately.

Pre-Production Checklist

Before promoting from internal tool to production, I always verify the following in order:

Does the executing user have a read-only role? (GRANT SELECT only — never grant write or DDL privileges.)
Does the validation layer reject DROP / DELETE / UPDATE and ; multi-statements?
Are query timeouts (e.g., 5 seconds) and cost limits set?
Is LIMIT injection suppressed for aggregate queries? (Insight 2)
Is an attempt cap set, with a path back to a human on overflow? (Insight 3)
Are columns containing personal data masked or excluded from querying?
Is every executed SQL statement and its result kept in an audit log?

Of these, items 1 and 2 are ones I treat not as "could happen" but as "will eventually happen for certain" if even one is missing.

Summary

By combining Claude API's powerful language understanding with its Tool Use capability, you can build a fully-featured intelligent agent that generates, validates, and executes SQL from natural language. The architecture covered in this article delivers high-accuracy query generation through schema inference, robust security through defense in depth, and practical reliability through automatic error correction loops.

Try connecting this to your own database and accelerate data-driven decision making in your organization. For those looking to deepen their understanding of these concepts,

Thank You for Reading

Claude Lab is ad-free, supported entirely by members like you. We publish practical guides daily with implementation code, benchmarks, and production-ready patterns. If you've found it useful, we'd love to have you on board.