RAG For Text-to-SQL Agents: How I'd Wire the Retrieval, Prompting, and Guardrails So It Does Not Corrupt Your Database
.png)
A developer on Reddit once ran a text-to-SQL agent that interpreted "show me last month's sales" as "delete all records older than 30 days and show me what remains."
The query executed perfectly. No syntax errors. Clean execution. The database just lost three years of historical data because the LLM decided that "show me" and "keep only" were semantically equivalent operations.
The SQL was valid. The logic was catastrophic.
That story captures the core problem with text-to-SQL systems: generating syntactically correct SQL is easy in 2025. Generating SQL that matches user intent without destroying your data is where most implementations quietly fail.
Every tutorial shows you how to wire up LangChain with a vector store and call it done. Nobody explains what happens when the agent hallucinates a column name, joins the wrong tables, or interprets a vague question as permission to UPDATE without a WHERE clause.
Read: Let Machine Learning Turn into Your Side Hustle with Automated Content Generation
The generation problem everyone solves (and the safety problem everyone ignores)
Text-to-SQL with LLMs works remarkably well for the happy path. User asks "how many customers signed up last week," the agent generates SELECT COUNT(*) FROM customers WHERE signup_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), the query runs, everyone celebrates. Demos look impressive. Investors write checks.
Production tells a different story. Users ask ambiguous questions. Schemas contain hundreds of tables with cryptic naming conventions.
Column names mislead the LLM into wrong assumptions. The agent generates SQL that technically answers the question but violates business logic, ignores permissions, or performs operations the user never intended.
The LlamaIndex text-to-SQL functionality had a documented SQL injection vulnerability where prompt injection could make the agent execute arbitrary SQL statements including DROP TABLE commands.
The team fixed it by implementing input sanitization, parameterized queries, and limiting operations to SELECT statements, plus adding warning messages for sensitive operations. That fix addressed one attack vector. It did not address the larger problem: an LLM generating "valid" SQL that does destructive things because it misunderstood the task.
Security researchers emphasize that text-to-SQL safety requires multiple layers: role-based filters that restrict generated queries to specific operations, dry run validation that tests queries before execution, and semantic validation that checks whether the generated query actually matches user intent.
Most student projects implement zero layers and hope for the best.
Why RAG actually matters for SQL (and when it actively hurts)
RAG in text-to-SQL serves one primary function: giving the LLM accurate schema context so it stops hallucinating table and column names.
Without RAG, the agent invents plausible-sounding column names based on general knowledge. With RAG, the agent retrieves the actual schema and uses real column names.
The retrieval process works like this: convert schema descriptions into vector embeddings, store them in a vector database, compute semantic similarity between the user's query and stored schema embeddings, retrieve the top-k most relevant schema descriptions, inject those descriptions into the LLM prompt along with the user question, generate SQL
That pattern solves hallucination for large schemas where including the full schema in every prompt would exceed token limits. It creates new problems.
Retrieval can return irrelevant tables
A user asks about customer data. The retrieval system returns schemas for the customers table, the customer_archive table, the temp_customer_backup table, and the customer_test_data table because they all match "customer" semantically.
The LLM sees four options and picks the wrong one. The query runs against test data instead of production data. The answer is garbage.
Retrieval fails on joins
A question requires joining customers and orders. Retrieval returns the customers schema but misses the orders schema because the semantic similarity ranking decided orders was less relevant.
The LLM generates SQL without the necessary join. The query either errors or produces incomplete results.
Schema descriptions lie
Someone wrote "customer_id: unique identifier for customers" in the schema description. The column actually contains nulls and duplicates because the database predates modern standards. The LLM trusts the description. The generated SQL assumes uniqueness. The results are wrong.
A common complaint online is that text-to-SQL models fail because of bad data and poor schema documentation, not because of model quality. The model does exactly what you asked based on the schema information it received.
The schema information was incomplete or misleading. Retrieval amplifies this problem by selectively showing the LLM only parts of the schema, making it impossible for the agent to notice inconsistencies or missing context.
Read: How To Make Money Blogging
The retrieval architecture that actually works in production
Skip naive semantic search on table descriptions. That pattern works in demos with 10 clean tables. Real databases have 200 tables with names like tbl_cust_v2_final_NEW and column descriptions copied from StackOverflow threads in 2014.
A production-grade retrieval setup has layers.
Layer 1: Metadata enrichment before embedding
Do not just embed table names and column names. Enrich the schema with example values, foreign key relationships, common query patterns, and business logic constraints.
When you embed "order_status," include the fact that valid values are pending, processing, shipped, delivered, cancelled. When you embed "customer_id," include the fact that it is a foreign key to customers.id and participates in 14 different joins across the schema.
This enrichment transforms retrieval from "find tables with similar names" to "find tables with similar semantic roles and relationships." The LLM receives context that includes not just what the columns are called, but how they behave.
Layer 2: Hybrid search combining semantic and keyword matching
Pure semantic search misses exact matches. A user asks about "invoice_number" and semantic search returns schemas for "billing_reference" and "payment_id" because they are semantically related. Keyword matching would have caught "invoice_number" directly.
Combine both: use semantic search to find related schemas, use keyword matching to boost exact or partial name matches, merge and re-rank results.
Tools like RAGatouille and LlamaIndex support hybrid search patterns where you configure both retrieval methods and blend their results based on configurable weights.
Layer 3: Relationship-aware retrieval
When the user query requires joining tables, retrieve not just the primary table schemas but also related table schemas based on foreign key relationships.
If the query mentions customers and the system retrieves the customers schema, automatically pull in the orders, payments, and addresses schemas because they have foreign keys to customers. This prevents the "missing join" failure where the LLM generates incomplete SQL because it cannot see the full relational context.
Layer 4: Query pattern retrieval
Maintain a library of verified SQL queries that represent common patterns (aggregations, time-based filters, multi-table joins).
When processing a new user question, retrieve similar historical queries and include them as few-shot examples. This grounds the LLM in real query patterns that actually work on your schema instead of generic SQL the LLM learned from training data.
Research into RAG-based text-to-SQL emphasizes that retrieving detailed schema information including table-column relationships, foreign keys, and metadata helps generate SQL that is both syntactically correct and semantically aligned with the database.
The "semantically aligned" part is what separates working systems from disasters.
The prompting structure that keeps the agent honest
Prompt engineering for text-to-SQL requires balancing three goals: providing enough context for accurate generation, maintaining clarity to avoid confusion, and building in constraints that prevent dangerous operations.
The minimal working prompt structure:
You are a SQL generation assistant. Generate PostgreSQL queries based on user questions. SCHEMA: {retrieved_schema_context} RULES: - Generate ONLY SELECT queries. Never generate INSERT, UPDATE, DELETE, DROP, ALTER. - Use explicit column names. Never use SELECT *. - Always include LIMIT clauses for queries without aggregations. Default LIMIT is 100. - If the question is ambiguous, ask for clarification instead of guessing. - If required table/column information is missing from the schema, respond with "I need more information about [specific missing detail]." EXAMPLES: {few_shot_examples} USER QUESTION: {user_question} GENERATED SQL:
That structure works for straightforward cases. Complex schemas need more.
Chain-of-thought prompting for complex queries
For questions that require multiple joins or complex aggregations, prompt the LLM to first explain its reasoning before generating SQL. "Break down the question into steps: What tables are needed? What are the join conditions? What filters apply?
What is the final aggregation?" This forces the LLM to think through the query logic explicitly, reducing errors from rushing directly to SQL generation.
Research shows that chain-of-thought methods improve text-to-SQL accuracy by making the reasoning process explicit and allowing for error detection before execution.
The downside is increased token usage and latency. Reserve it for complex queries detected through a complexity classifier.
Error taxonomy for self-correction
When the generated SQL fails, do not just retry blindly. Classify the error (syntax error, execution error, missing table, type mismatch) and provide targeted correction guidance.
A multi-agent system can have one agent generate SQL, another agent validate it, and a third agent provide corrections based on error categories. This structured correction loop reduces the number of retry attempts and improves success rates.
A practical implementation uses a simple validator that checks:
Does the query reference only tables that exist in the schema?
Does the query reference only columns that exist in those tables?
Are the data types in WHERE clauses compatible?
Does the query include dangerous keywords (DELETE, DROP, TRUNCATE)?
Failing any check triggers a regeneration with explicit feedback about what went wrong.
Read: Ensemble Models: Why, When, and How to Combine Different Machine Learning Families
The guardrails that prevent "technically correct but disastrous" queries
Validation and guardrails sit between SQL generation and SQL execution. This layer is not optional. This layer is the difference between a useful tool and a lawsuit.
Static analysis before execution
Parse the generated SQL using a SQL parser library (sqlparse for Python, sql-parser for JavaScript). Extract the query type (SELECT, UPDATE, DELETE), the tables referenced, the columns used, and the presence of WHERE clauses. Reject any query that matches forbidden patterns:
UPDATE or DELETE without WHERE clause
Queries referencing system tables
Queries with nested deletes
Queries attempting to modify schema (CREATE, ALTER, DROP)
Static analysis catches obvious dangers without executing anything. It is fast, deterministic, and catches the "oops I forgot the WHERE clause" disasters that ruin careers.
Dry run execution with EXPLAIN
Before running the actual query, use EXPLAIN or EXPLAIN ANALYZE to see the query execution plan without modifying data.
Check estimated row counts. If a DELETE query estimates 50,000 affected rows and the user asked to "remove the test customer," something went wrong. Flag queries where estimated impact exceeds expected impact based on the question context.
Dry runs also reveal performance issues. A generated query with a full table scan on a 100-million-row table will timeout or crash. EXPLAIN shows this before you waste resources attempting execution.
Row-based limits and timeouts
Even SELECT queries can cause problems. A query without proper filtering on a massive table consumes resources and returns useless results.
Enforce hard limits: maximum 10,000 rows returned, maximum 30-second execution time. Wrap all queries in timeouts that kill execution if they exceed limits.
For UPDATE and DELETE operations (if you allow them at all), require explicit confirmation and implement a two-phase commit where the agent shows the user exactly what will change before executing.
Role-based access control
The database user that the text-to-SQL agent uses should have minimal privileges. Read-only access to production data.
If the agent somehow generates a DELETE query despite all your guardrails, the database itself rejects it because the user lacks DELETE privileges.
This defense-in-depth approach stacks multiple protection layers. Bypassing one layer hits the next. Bypassing all layers requires defeating static analysis, dry run checks, timeouts, AND database permissions, which is substantially harder than just tricking the LLM.
Real implementation headaches (and the fixes that actually work)
People building text-to-SQL agents hit the same problems repeatedly. The problems cluster around schema complexity, ambiguous questions, and LLM limitations.
Problem: Schema with 300+ tables overwhelms retrieval
Retrieval returns 20 table schemas. The LLM receives 15,000 tokens of schema context. The actual tables needed for the query are buried in position 12 and 18. The LLM prioritizes earlier context and generates SQL using the wrong tables.
Fix: Implement hierarchical retrieval. First retrieve at the domain level (is this a customer question, a finance question, a product question?).
Then retrieve specific tables within that domain. This two-stage process reduces context size while improving relevance. A question about customer orders first narrows to the "customer domain" subset (10 tables) before retrieving specific schemas.
Problem: Column names that mislead the LLM
A column called "customer_value" sounds like total purchase value. It actually stores a boolean flag for VIP status. The LLM generates aggregation queries that sum boolean values and return nonsense.
Fix: Enrich column descriptions with type information and example values during the embedding phase. Instead of embedding "customer_value: customer value indicator," embed "customer_value (BOOLEAN): VIP status flag, values are TRUE for VIP customers and FALSE for regular customers."
The LLM sees the type and understands the semantic meaning correctly.
Problem: Ambiguous questions that allow multiple interpretations
User asks "show me top customers." Top by revenue? Top by order count? Top by recent activity? The LLM picks one interpretation, generates SQL, returns results.
The user expected different results based on a different interpretation of "top."
Fix: Implement ambiguity detection in the prompt. Train the agent to identify underspecified questions and ask for clarification. "I can show you top customers by total revenue, by number of orders, or by most recent activity.
Which metric should I use?" This adds latency but prevents wrong answers from ambiguous questions.
Problem: Queries that are syntactically valid but semantically wrong
Generated SQL uses correct syntax and references real tables and columns. The joins are backwards. The filters use wrong comparison operators.
The aggregation groups by the wrong column. The query runs successfully and returns confident nonsense.
Fix: Implement result validation through a second LLM call that reviews the generated SQL against the original question and asks "does this query actually answer the user's question?"
This validator receives the user question, the generated SQL, and the schema context, then performs sanity checks. Does the JOIN make sense? Are the filters aligned with question constraints? Is the aggregation grouping logical? The validator either approves execution or flags issues for regeneration.
Problem: Users asking questions that require data the database does not contain
User asks "what is the weather forecast for next week?" The database contains sales data. The LLM tries to generate SQL anyway, hallucinates a weather table, and errors out.
Fix: Add domain awareness to the agent. Before attempting SQL generation, classify whether the question is answerable using the available database.
Questions about external data, future predictions, or information outside the database's scope should trigger "I cannot answer this question using the available data" responses instead of failed SQL attempts.
Read: How to get 10000+ Clicks on AdSense Ads Per Month
The toolchain decisions that matter (LangChain, LlamaIndex, RAGatouille, or custom)
Every tutorial recommends a different framework. Some advocate LangChain for its ecosystem. Others prefer LlamaIndex for its focus on retrieval patterns. RAGatouille claims better retrieval quality. DIY proponents argue that frameworks add unnecessary complexity.
The real answer depends on your constraints and your team's tolerance for debugging abstraction layers.
LangChain for rapid prototyping with heavy customization tax later
LangChain provides pre-built text-to-SQL chains, vector store integrations, and agent patterns. You can build a working prototype in hours.
The abstraction makes debugging difficult because errors happen deep inside framework code. When the generated SQL is wrong, tracing back through LangChain's chain execution to find where the prompt construction failed becomes painful.
Use LangChain if you are building a proof-of-concept or need to demo quickly. Plan to refactor or rewrite critical paths once you understand the actual requirements.
LlamaIndex for retrieval-heavy workflows with less agent complexity
LlamaIndex focuses on retrieval and indexing patterns. The text-to-SQL components are less mature than general RAG patterns but the retrieval quality is strong.
If your main challenge is accurate schema retrieval from a large database, LlamaIndex provides good tools for hybrid search, metadata filtering, and relationship-aware retrieval.
Use LlamaIndex if your database schema is large and complex, and you need sophisticated retrieval to narrow context effectively.
RAGatouille for late-interaction retrieval models
RAGatouille implements ColBERT and similar late-interaction models that provide better retrieval quality than traditional bi-encoder models.
For text-to-SQL, late-interaction models excel at matching user questions to relevant schemas when semantic similarity is subtle. The downside is increased computational cost and slower retrieval times.
Use RAGatouille if retrieval accuracy is your bottleneck and you have the compute budget for better models.
Custom implementation for production systems at scale
Building text-to-SQL from scratch using LLM APIs, vector databases, and SQL parsers gives you complete control and eliminates framework overhead.
You write explicit code for each step: embedding generation, retrieval, prompt construction, SQL generation, validation, execution. Debugging is straightforward because you own every line.
Use custom implementations for production systems where performance, reliability, and observability matter more than development speed. The upfront cost is higher. The long-term maintenance cost is lower.
A pragmatic approach: prototype with frameworks, identify bottlenecks and requirements, rewrite critical paths with custom code, keep framework components that work well. This hybrid approach balances speed and control.
The cheap stack that actually ships
You do not need enterprise infrastructure to build working text-to-SQL agents. A minimal production-ready stack costs under $100/month for moderate usage.
Components:
PostgreSQL or MySQL database (your existing database)
pgvector or similar vector extension for schema embeddings
OpenAI API or Anthropic API for LLM calls
A simple web server (Flask, FastAPI, Express)
Basic logging and monitoring
Workflow:
Precompute embeddings for all schema elements (tables, columns, relationships) using an embedding model (OpenAI text-embedding-3-small costs $0.02 per 1M tokens)
Store embeddings in pgvector alongside schema metadata
User submits question via web interface
Compute question embedding, query pgvector for top-k relevant schemas
Construct prompt with retrieved schemas and few-shot examples
Call LLM API to generate SQL
Run static analysis and dry run validation
Execute query with timeout and row limit
Return results to user
Cost breakdown for 10,000 queries/month:
Embedding generation: ~$5 (one-time for schema + $1/month for new questions)
LLM API calls: ~$30-50 depending on prompt sizes and model choice
Database compute: $0 (using existing database)
Vector storage: negligible within existing database
Server hosting: $10-20 for basic cloud VM
Total: $45-75/month
This stack handles thousands of queries per month, provides decent accuracy, and requires minimal maintenance. Scale up by adding caching (Redis for repeated queries), better retrieval models (ColBERT via RAGatouille), or multiple LLM calls for validation.
Read: The Fundamentals of Keyword Research for Blogging
The monitoring and logging that keeps you sane
Text-to-SQL agents fail in creative ways. Monitoring catches failures before users notice or before bad queries corrupt data.
Essential metrics to track:
Query success rate (queries that execute vs queries that error)
Query accuracy rate (queries that return expected results vs queries that return wrong results, requires user feedback or validation)
Average latency (retrieval + generation + execution time)
Token usage per query (tracks cost)
Validation rejection rate (queries rejected by guardrails)
Schema retrieval relevance (are the top-k retrieved schemas actually used in generated SQL?)
Critical logs to capture:
User question (anonymized if sensitive)
Retrieved schema context
Generated SQL before and after validation
Execution results or error messages
User feedback if collected
These logs enable debugging. When a user reports wrong results, pull the logs, see exactly what schemas were retrieved, what SQL was generated, and where the failure occurred. Without logs, debugging text-to-SQL agents is guesswork.
Implement a simple feedback mechanism where users can mark results as correct or incorrect. Track queries marked incorrect, analyze patterns, identify common failure modes, adjust prompts or retrieval logic accordingly.
When NOT to build text-to-SQL agents
Text-to-SQL works well for exploratory analytics, internal tools, and dashboards where non-technical users need database access. Text-to-SQL is wrong for several use cases.
Transactional applications
If your application requires reliable, fast, deterministic database operations, hand-written SQL or ORMs are better. Text-to-SQL adds latency, costs, and failure modes. A checkout flow should not involve an LLM deciding how to query inventory.
Compliance-critical queries
Financial reports, audit logs, and regulatory data pulls require guaranteed correctness. LLM-generated SQL cannot provide that guarantee. Use verified, tested SQL queries stored as code.
High-volume, low-latency access
If you are serving thousands of queries per second, the LLM call latency (200-1000ms) and cost make text-to-SQL impractical. Cache common queries or use traditional query builders.
Sensitive operations
Any operation that modifies data (UPDATE, DELETE, INSERT) should use explicit, tested code paths. Text-to-SQL for reads is risky but manageable. Text-to-SQL for writes is reckless unless you implement multiple confirmation layers and thorough validation.
Know the boundaries. Text-to-SQL provides flexibility and accessibility for exploratory queries. It does not replace proper application architecture for critical paths.
Bringing it together: A realistic architecture for internal analytics
The best production text-to-SQL systems layer defensive components:
Request layer: User submits natural language question, system classifies complexity and ambiguity
Retrieval layer: Hybrid search retrieves relevant schemas using semantic + keyword matching, relationship-aware expansion pulls in related tables
Generation layer: LLM generates SQL using retrieved schemas and few-shot examples, chain-of-thought prompting for complex queries
Validation layer: Static analysis checks for dangerous patterns, dry run with EXPLAIN validates row count estimates, schema validator confirms all referenced tables/columns exist
Execution layer: Query runs with strict timeout and row limits, database user has read-only permissions, results returned with metadata
Feedback layer: Users mark results as correct/incorrect, logs capture full context for debugging, analytics identify failure patterns
Each layer is optional but recommended. Remove layers based on your risk tolerance and use case requirements. Internal analytics with non-critical data can skip some validation. Customer-facing tools or sensitive data need all layers.
The system should fail gracefully. Invalid questions get clear error messages. Ambiguous questions trigger clarification requests. Unsafe queries get rejected with explanations. Timeouts return partial results when possible. Every failure generates logs for later analysis.
Final thoughts: Building agents that respect both data and users
Text-to-SQL agents represent a valuable tool for making databases accessible to non-technical users. They also represent a significant risk when implemented naively. The gap between "generates SQL" and "generates safe, accurate SQL" is where most projects fail.
The developers who succeed with text-to-SQL agents treat them like safety-critical systems. They implement defense in depth. They log everything. They validate aggressively. They limit damage through permissions and constraints. They monitor continuously and iterate based on real failures.
The developers who fail either treat text-to-SQL as a solved problem ("LangChain has a chain for that") or underestimate the complexity of translating vague human questions into precise database operations.
Your database contains your business. Giving an LLM the keys to query it requires care, respect, and healthy paranoia. Build the guardrails first. Test exhaustively. Deploy cautiously. Monitor obsessively.
The convenience of natural language database access is worth the effort when done right. The alternative is explaining to your CEO why a chatbot deleted the customer table.
I hope this guide gives you the patterns and tools to build text-to-SQL agents that work reliably without causing disasters. Come back later for more posts on building AI systems that respect data integrity and user intent.
Comments
Post a Comment