AI Agent with Database Access: Text-to-SQL Development

Imagine: a commercial director wants to know the conversion rate from pending to delivered over the last 30 days by customer segment. Instead of writing an SQL query or waiting for a BI specialist's report, they ask a question in natural language. Three minutes later, the answer is ready. This is not science fiction but a real result of deploying an AI agent with database access (Text-to-SQL).

We develop such agents — Text-to-SQL solutions that turn employee questions into correct SQL queries, execute them securely, and return understandable results. Below is how it works in practice, with a focus on security, accuracy, and performance.

How the AI Agent Converts Natural Language to SQL

Text-to-SQL (see Wikipedia) is the task of automatically generating SQL queries from natural language questions. At its core is a combination of an LLM and database interaction tools. We use LangChain and the SQLDatabaseToolkit library. The agent connects to PostgreSQL via a read-only user, retrieves the schema description (tables, columns, relationships), and generates SQL based on the question.

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

# Connect to PostgreSQL
db = SQLDatabase.from_uri(
    "postgresql://user:password@localhost:5432/company_db",
    include_tables=["orders", "customers", "products", "inventory"],
    sample_rows_in_table_info=3,
)

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

# SQL agent with automatic error correction
agent = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    verbose=True,
    handle_parsing_errors=True,
    max_iterations=10,
)

# Example queries
result = agent.invoke({"input": "What are the top 5 customers by revenue over the last 3 months?"})
result = agent.invoke({"input": "Show products with stock less than 10 units"})

Why Security Is Critical for an AI Agent with Database Access

An error in SQL generation can lead to data leaks or corruption. Therefore, we build multi-layered protection. As stated in the LangChain documentation, a read-only user is the minimum standard.

from langchain_community.utilities import SQLDatabase
from sqlalchemy import create_engine, text

# PostgreSQL read-only user
READ_ONLY_USER_URI = "postgresql://readonly_user:pass@localhost:5432/db"

# Additional validation: forbid DML operations
def validate_sql_query(query: str) -> bool:
    """Check that the query is only SELECT"""
    forbidden_keywords = ["INSERT", "UPDATE", "DELETE", "DROP", "CREATE", "ALTER", "TRUNCATE"]
    query_upper = query.upper()
    for keyword in forbidden_keywords:
        if keyword in query_upper:
            return False
    return True

class SafeSQLTool:
    def __init__(self, db_uri: str):
        self.engine = create_engine(db_uri)

    def execute_query(self, query: str) -> str:
        if not validate_sql_query(query):
            return "ERROR: Only SELECT queries are allowed"

        # Limit number of rows
        if "LIMIT" not in query.upper():
            query = f"{query.rstrip(';')} LIMIT 100"

        with self.engine.connect() as conn:
            result = conn.execute(text(query))
            rows = result.fetchall()
            columns = result.keys()
            return str([dict(zip(columns, row)) for row in rows])

Additionally, we enforce a read-only user at the DBMS level, limit rows (default LIMIT 100), and log every query for auditing. This approach guarantees that accidental or malicious data modification is impossible.

Few-Shot Learning for Higher Accuracy

Few-shot examples are samples of correct SQL queries provided in the prompt along with the question. They critically boost accuracy on complex queries: from 60-70% to 85-95%. Without them, the LLM might misinterpret business logic (e.g., counting revenue across all statuses instead of only 'delivered').

FEW_SHOT_EXAMPLES = """
Examples of correct queries:

Question: Top 10 products by margin for the last quarter
SQL:
SELECT p.name, p.sku,
       SUM(oi.quantity * (p.price_rub - p.cost_rub)) AS margin_rub
FROM order_items oi
JOIN products p ON oi.sku = p.sku
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'delivered'
  AND o.created_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
GROUP BY p.name, p.sku
ORDER BY margin_rub DESC
LIMIT 10;

Question: Average order value by month for the current year
SQL:
SELECT DATE_TRUNC('month', created_at) AS month,
       ROUND(AVG(total_amount), 0) AS average_order_value,
       COUNT(*) AS order_count
FROM orders
WHERE status = 'delivered'
  AND o.created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY 1
ORDER BY 1;
"""

We adapt these examples to your schema and typical business questions.

Practical Case Study: BI Agent for E-commerce (From Our Practice)

Goal: an analytics assistant for the commercial director — sales analysis, ABC assortment analysis, order funnel, cohort retention.

Database: PostgreSQL, 15 tables, 3M orders.

Example dialogues:

The commercial director asks: "What is the conversion rate from pending to delivered over the last 30 days by customer segment?"

The agent generates:

SELECT
    c.segment AS segment,
    COUNT(*) FILTER (WHERE o.status = 'pending') AS pending,
    COUNT(*) FILTER (WHERE o.status = 'delivered') AS delivered,
    ROUND(
        COUNT(*) FILTER (WHERE o.status = 'delivered')::decimal /
        NULLIF(COUNT(*), 0) * 100, 1
    ) AS conversion_pct
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.segment
ORDER BY conversion_pct DESC
LIMIT 100;

Results:

• Time to get analytics: 2 days → 3 minutes
• SQL accuracy (questions → correct SQL): 87%
• Typical errors: incorrect JOINs for complex queries (solved via few-shot examples in the prompt)

Database Schema Context for LLM

The quality of Text-to-SQL critically depends on the quality of the schema description. We provide not only table names but also sample data, relationship descriptions, and business rules in the prompt.

SCHEMA_CONTEXT = """
Database tables:

1. orders
   - id: PK, INTEGER
   - customer_id: FK -> customers.id
   - status: VARCHAR (pending, confirmed, shipped, delivered, cancelled)
   - total_amount: DECIMAL(12,2) — order amount in rubles
   - created_at: TIMESTAMP
   - shipped_at: TIMESTAMP (NULL if not shipped)

2. customers
   - id: PK
   - name: VARCHAR — company name or full name
   - inn: VARCHAR(12) — TIN of legal entity/sole proprietor
   - segment: VARCHAR (enterprise, mid, small) — customer segment
   - manager_id: FK -> employees.id — responsible manager

3. products
   - sku: VARCHAR — SKU
   - name: VARCHAR
   - category: VARCHAR
   - price_rub: DECIMAL
   - cost_rub: DECIMAL — cost price

IMPORTANT: Order statuses: 'delivered' = successfully completed. 'cancelled' = canceled.
       Revenue = sum of total_amount for orders with status 'delivered'.
"""

system_prompt = f"""You are a data analyst. Translate questions into SQL queries.
Use the following database schema:

{SCHEMA_CONTEXT}

Rules:
- Only SELECT queries
- Always add LIMIT (max 1000)
- Use English aliases for readability
- When aggregating, add ORDER BY"""

What's Included in the Work

Comparison: Off-the-Shelf Solutions vs Custom Development

Custom development delivers 85-95% accuracy — twice as high as off-the-shelf solutions (60-70%). Other advantages:

Custom development pays off within 3–4 months due to analyst time savings. In one project, the agent helped identify a 10% conversion increase. Analyst time savings reach up to 80%: depends on scope. Contact us for a free project evaluation.

How to Order AI Agent Development

1. Submit a consultation request — we'll discuss your database and business tasks.
2. We'll perform a schema and access rights audit (2–3 days).
3. Develop the Text-to-SQL core with basic queries.
4. Tune prompts and few-shot examples for your domain.
5. Test with real data and hand over the finished agent.

Get a consultation — we'll explain how an AI agent with database access can accelerate your team's work.