SQL RAG: Query Your Databases Using Natural Language

80% of enterprise data lives in relational databases. Yet most RAG systems completely ignore structured data, limiting themselves to text documents. SQL RAG lets your users ask questions like "Who's our best customer this quarter?" and get a precise answer directly from your database, without writing a single line of SQL.

TL;DR

• Text-to-SQL converts natural language questions into executable SQL queries
• Three approaches: direct SQL generation, RAG-augmented SQL, hybrid (SQL + documents)
• Spider benchmarks: top models reach 85%+ execution accuracy
• Critical security: SQL injection, sensitive data access, mandatory validation
• Tools: LangChain SQL agents, LlamaIndex NLSQLTableQueryEngine, Vanna.AI
• Use cases: conversational analytics, automated reporting, e-commerce support

Why SQL RAG?

Databases contain the most valuable and up-to-date enterprise data. But they're inaccessible to non-technical users.

Questions only SQL can answer

"What was last month's revenue?"
→ SELECT SUM(amount) FROM orders WHERE date >= '2026-02-01'

"Which products are out of stock?"
→ SELECT name FROM products WHERE stock = 0

"Who are our top 10 customers by revenue?"
→ SELECT customer_name, SUM(amount) as total
  FROM orders GROUP BY customer_name
  ORDER BY total DESC LIMIT 10

"How many support tickets have been open for over 48 hours?"
→ SELECT COUNT(*) FROM tickets
  WHERE status = 'open'
  AND created_at < NOW() - INTERVAL '48 hours'

The Three Approaches

Approach 1: Direct SQL Generation by LLM

The LLM receives the database schema and directly generates the SQL query.

DEVELOPERpython
from langchain_openai import ChatOpenAI
from langchain_community.utilities import SQLDatabase

# Database connection
db = SQLDatabase.from_uri(
    "postgresql://user:password@localhost:5432/mydb",
    include_tables=["orders", "customers", "products"],
    sample_rows_in_table_info=3  # Sample data
)

llm = ChatOpenAI(model="gpt-4o", temperature=0)

def generate_sql(question: str) -> str:
    """Generate a SQL query from a question."""
    schema = db.get_table_info()

    prompt = f"""You are a PostgreSQL SQL expert.
Generate a SQL query to answer the question.

Database schema:
{schema}

Rules:
- Use ONLY tables and columns from the schema
- Return valid PostgreSQL SQL
- Limit results to 50 rows maximum
- NEVER use DROP, DELETE, UPDATE, INSERT, ALTER

Question: {question}

SQL Query:"""

    response = llm.invoke(prompt)
    return response.content.strip().strip("```sql").strip("```")

# Example
sql = generate_sql("What was last month's revenue?")

Approach 2: RAG-Augmented SQL

Enriches the LLM context with similar query examples (few-shot) and schema documentation.

DEVELOPERpython
from langchain_openai import OpenAIEmbeddings
from langchain_community.vectorstores import FAISS
from langchain_core.documents import Document

# SQL examples knowledge base
sql_examples = [
    Document(
        page_content="Question: Last month's revenue\n"
        "SQL: SELECT SUM(amount) FROM orders "
        "WHERE date >= date_trunc('month', CURRENT_DATE - INTERVAL '1 month') "
        "AND date < date_trunc('month', CURRENT_DATE)",
        metadata={"category": "revenue", "tables": "orders"}
    ),
    Document(
        page_content="Question: Top 10 customers by revenue\n"
        "SQL: SELECT c.name, SUM(o.amount) as total "
        "FROM orders o JOIN customers c ON o.customer_id = c.id "
        "GROUP BY c.name ORDER BY total DESC LIMIT 10",
        metadata={"category": "customers", "tables": "orders,customers"}
    ),
    Document(
        page_content="Question: Out of stock products\n"
        "SQL: SELECT name, sku FROM products WHERE stock = 0 "
        "ORDER BY name",
        metadata={"category": "inventory", "tables": "products"}
    ),
    # ... dozens of examples
]

# Vector index of examples
embeddings = OpenAIEmbeddings(model="text-embedding-3-small")
example_store = FAISS.from_documents(sql_examples, embeddings)

class RAGAugmentedSQL:
    def __init__(self, db, llm, example_store):
        self.db = db
        self.llm = llm
        self.retriever = example_store.as_retriever(
            search_kwargs={"k": 3}
        )

    def generate(self, question: str) -> str:
        # 1. Retrieve similar examples
        examples = self.retriever.invoke(question)
        examples_text = "\n\n".join([
            doc.page_content for doc in examples
        ])

        # 2. Generate with few-shot
        schema = self.db.get_table_info()

        prompt = f"""You are a PostgreSQL SQL expert.

Schema:
{schema}

Similar query examples:
{examples_text}

Generate the SQL query for this question.
Question: {question}

SQL Query:"""

        response = self.llm.invoke(prompt)
        return response.content.strip().strip("```sql").strip("```")

rag_sql = RAGAugmentedSQL(db, llm, example_store)
sql = rag_sql.generate("What are the 5 best-selling products?")

Approach 3: Hybrid (SQL + Documents)

Combines SQL data and text documents for enriched answers.

DEVELOPERpython
class HybridSQLDocumentRAG:
    """Hybrid RAG combining SQL and documents."""

    def __init__(self, sql_chain, doc_retriever, llm):
        self.sql_chain = sql_chain
        self.doc_retriever = doc_retriever
        self.llm = llm

    async def query(self, question: str) -> str:
        # 1. Classify the question
        needs_sql = await self._needs_sql(question)
        needs_docs = await self._needs_docs(question)

        context_parts = []

        # 2. Retrieve SQL data if needed
        if needs_sql:
            sql_result = await self.sql_chain.arun(question)
            context_parts.append(
                f"Database data:\n{sql_result}"
            )

        # 3. Retrieve documents if needed
        if needs_docs:
            docs = await self.doc_retriever.aget_relevant_documents(
                question
            )
            doc_context = "\n".join([d.page_content for d in docs])
            context_parts.append(
                f"Documentation:\n{doc_context}"
            )

        # 4. Generate combined response
        context = "\n\n---\n\n".join(context_parts)

        prompt = f"""Answer the question by combining database data
and documentation.

{context}

Question: {question}

Answer (in natural language, not SQL):"""

        return (await self.llm.ainvoke(prompt)).content

    async def _needs_sql(self, question: str) -> bool:
        """Determine if the question needs SQL data."""
        keywords = [
            "how many", "total", "revenue", "sales", "stock",
            "customers", "orders", "best", "worst", "average",
            "last month", "this quarter", "this year"
        ]
        return any(kw in question.lower() for kw in keywords)

    async def _needs_docs(self, question: str) -> bool:
        """Determine if the question needs documentation."""
        keywords = [
            "how to", "why", "policy", "procedure",
            "guide", "tutorial", "explain", "how does"
        ]
        return any(kw in question.lower() for kw in keywords)

Approach Comparison

Spider Dataset Benchmarks

The Spider dataset is the standard benchmark for Text-to-SQL.

Complete Implementation with LangChain

DEVELOPERpython
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import ChatOpenAI
from langchain.agents import create_sql_agent
from langchain.agents.agent_types import AgentType

# 1. Database setup
db = SQLDatabase.from_uri(
    "postgresql://user:password@localhost:5432/ecommerce",
    include_tables=[
        "orders", "order_items", "customers",
        "products", "categories"
    ],
    sample_rows_in_table_info=3
)

# 2. LLM
llm = ChatOpenAI(model="gpt-4o", temperature=0)

# 3. SQL Toolkit
toolkit = SQLDatabaseToolkit(db=db, llm=llm)

# 4. SQL Agent
agent = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    agent_type=AgentType.OPENAI_FUNCTIONS,
    verbose=True,
    max_iterations=10,
    handle_parsing_errors=True,
    prefix="""You are an agent that interacts with a SQL database.
You must answer questions in natural language.

SECURITY RULES:
- NEVER execute queries that modify the database (INSERT, UPDATE, DELETE, DROP)
- Always limit results (LIMIT 50 max)
- NEVER reveal passwords or sensitive data
- If a question is ambiguous, ask for clarification

FORMAT RULES:
- Answer in English
- Format amounts with currency symbols
- Format dates as MM/DD/YYYY
"""
)

# 5. Usage
result = agent.invoke({
    "input": "What's the total revenue for last month "
             "and how many orders did we receive?"
})

Implementation with LlamaIndex

DEVELOPERpython
from llama_index.core import SQLDatabase, VectorStoreIndex
from llama_index.core.query_engine import NLSQLTableQueryEngine
from llama_index.core.indices.struct_store import SQLTableRetrieverQueryEngine
from sqlalchemy import create_engine

# 1. Connection
engine = create_engine(
    "postgresql://user:password@localhost:5432/ecommerce"
)

sql_database = SQLDatabase(
    engine,
    include_tables=["orders", "customers", "products"]
)

# 2. Simple query engine
query_engine = NLSQLTableQueryEngine(
    sql_database=sql_database,
    tables=["orders", "customers", "products"],
    llm=llm
)

response = query_engine.query(
    "Who are the 5 customers with the most orders?"
)
print(response.response)
print(f"Generated SQL: {response.metadata['sql_query']}")

# 3. Advanced query engine with table retrieval
table_node_mapping = sql_database.get_table_node_mapping()
table_schema_index = VectorStoreIndex(
    list(table_node_mapping.values())
)

query_engine = SQLTableRetrieverQueryEngine(
    sql_database=sql_database,
    table_retriever=table_schema_index.as_retriever(
        similarity_top_k=3
    ),
    llm=llm
)

Security: The Critical Part

SQL RAG directly exposes your database. Security is not optional.

1. Query Validation

DEVELOPERpython
import re
from typing import Tuple

class SQLValidator:
    """Validates generated SQL queries before execution."""

    FORBIDDEN_KEYWORDS = [
        "DROP", "DELETE", "UPDATE", "INSERT", "ALTER", "TRUNCATE",
        "CREATE", "GRANT", "REVOKE", "EXEC", "EXECUTE",
        "xp_", "sp_", "--", "/*", "*/", "UNION ALL"
    ]

    MAX_RESULT_LIMIT = 100

    def validate(self, sql: str) -> Tuple[bool, str]:
        """Validate a SQL query. Returns (is_valid, reason)."""
        sql_upper = sql.upper().strip()

        # 1. Check it's a SELECT
        if not sql_upper.startswith("SELECT"):
            return False, "Only SELECT queries are allowed"

        # 2. Check for forbidden keywords
        for keyword in self.FORBIDDEN_KEYWORDS:
            if keyword.upper() in sql_upper:
                return False, f"Forbidden keyword detected: {keyword}"

        # 3. Check for LIMIT presence
        if "LIMIT" not in sql_upper:
            return False, "Query must contain a LIMIT"

        # 4. Check LIMIT is reasonable
        limit_match = re.search(r"LIMIT\s+(\d+)", sql_upper)
        if limit_match:
            limit_val = int(limit_match.group(1))
            if limit_val > self.MAX_RESULT_LIMIT:
                return False, f"LIMIT too high ({limit_val} > {self.MAX_RESULT_LIMIT})"

        # 5. Check for nested subqueries
        if sql_upper.count("SELECT") > 3:
            return False, "Too many nested subqueries"

        return True, "Query validated"

2. Read-Only Database User

DEVELOPERsql
-- Create a read-only user
CREATE USER rag_readonly WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE ecommerce TO rag_readonly;
GRANT USAGE ON SCHEMA public TO rag_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rag_readonly;

-- Exclude sensitive tables
REVOKE SELECT ON users FROM rag_readonly;
REVOKE SELECT ON payment_methods FROM rag_readonly;
REVOKE SELECT ON api_keys FROM rag_readonly;

3. Sensitive Data Masking

DEVELOPERpython
class DataMasker:
    """Masks sensitive data in results."""

    PATTERNS = {
        "email": (r"[\w.-]+@[\w.-]+\.\w+", "***@***.***"),
        "phone": (r"\+?\d{10,15}", "**********"),
        "iban": (r"[A-Z]{2}\d{2}[A-Z0-9]{11,30}", "****"),
        "card": (r"\d{4}[\s-]?\d{4}[\s-]?\d{4}[\s-]?\d{4}", "****-****-****-****"),
    }

    def mask(self, text: str) -> str:
        """Mask sensitive data in text."""
        for pattern_name, (pattern, replacement) in self.PATTERNS.items():
            text = re.sub(pattern, replacement, text)
        return text

Performance Optimization

FAQ

Is Text-to-SQL reliable for production?

With proper safeguards, yes. Current models achieve 80-85% accuracy with few-shot. The 15-20% errors are mainly on complex queries with multiple joins. In production, always add query validation, read-only execution, and a feedback mechanism to correct recurring errors.

How do you handle schemas with hundreds of tables?

Never expose all tables to the LLM. Use a schema retriever that selects the 3-5 relevant tables for each question. LlamaIndex SQLTableRetrieverQueryEngine does exactly this. Annotate your tables with clear descriptions to help the retriever.

Can SQL RAG handle complex analytical queries (GROUP BY, HAVING, subqueries)?

GPT-4o and Claude handle simple GROUP BY and ORDER BY well. Correlated subqueries and window functions remain challenging. For complex cases, create materialized views that simplify queries, or use domain-specific few-shot examples.

How do you protect sensitive data?

Triple protection: (1) read-only database user without access to sensitive tables, (2) SQL query validation before execution (no UNION, no forbidden tables), (3) sensitive data masking in results (emails, phones, IBANs). Never trust the LLM to respect confidentiality instructions alone.

Can you combine SQL RAG and document RAG in the same chatbot?

Yes, and it's recommended. The hybrid approach lets you answer questions like "What's our monthly revenue and how can we improve it?" by combining SQL data (actual revenue) and documentation (improvement strategies). The query classifier routes to the right pipeline, or combines both for mixed questions.

Conclusion

SQL RAG opens up a world of possibilities by making databases accessible in natural language. It's the missing piece for enterprises whose most valuable data lives in PostgreSQL, MySQL, or SQL Server.

Key takeaways:

1. The RAG-augmented approach (few-shot + similar examples) offers the best accuracy/effort ratio
2. Security is non-negotiable: read-only, validation, masking
3. The hybrid approach (SQL + documents) covers all question types
4. The annotated schema is the most important factor for accuracy
5. Materialized views simplify complex queries

Ailog lets you connect your databases to your chatbots for precise, real-time answers. Try it free and give your users the power to query your data without knowing SQL.

Resources

• LangChain SQL Agent - Official documentation
• LlamaIndex NLSQLTableQueryEngine - LlamaIndex guide
• Spider Dataset - Text-to-SQL benchmark
• Vanna.AI - Open source Text-to-SQL framework
• Enterprise Knowledge Base - Building a knowledge base
• E-commerce RAG - RAG for e-commerce