Building Robust AI Agents for SQL Database Interaction with smolagents

In the rapidly evolving landscape of Large Language Models (LLMs), the ability to bridge the gap between natural language and structured data remains one of the most sought-after capabilities for enterprises. However, the standard approach to this problem—often referred to as 'Classic' Text-to-SQL—is increasingly proving inadequate for production-grade environments. To build truly reliable systems, developers are turning toward AI Agents that don't just translate text, but reason, execute, and self-correct.

The Fragility of Classic Text-to-SQL Pipelines

The most straightforward way to connect an LLM to a database is a single-pass pipeline. In this architecture, a user submits a question, the system retrieves the database schema, constructs a prompt, and asks the LLM to output a SQL query. This query is then executed, and the results are returned to the user.

While this works for simple queries on clean datasets, it is inherently fragile for several reasons:

1. Syntax Errors: LLMs, even powerful ones like Claude 3.5 Sonnet or DeepSeek-V3, can occasionally hallucinate a comma, a bracket, or a keyword that invalidates the SQL statement.
2. Schema Hallucination: Models might reference columns that don't exist or confuse table names if the schema is large.
3. Semantic Mismatches: A query might be syntactically perfect but return no data because the LLM misunderstood the relationship between tables (e.g., using a LEFT JOIN instead of an INNER JOIN).
4. Lack of Feedback: In a single-pass system, there is no 'loop.' If the database returns an error, the system simply crashes or returns the error message to the end-user.

To overcome these hurdles, we need an agentic approach. By utilizing n1n.ai, developers can access the high-speed inference required to run iterative agentic loops without the latency bottlenecks associated with traditional providers.

Introduction to Hugging Face smolagents

Hugging Face recently released smolagents, a minimalist library designed to build powerful agents that 'think in code.' Unlike traditional agents that output JSON blobs to call tools, smolagents focuses on CodeAgents. These agents write actual Python snippets to perform tasks, which allows for much more flexible and complex logic compared to restricted JSON schemas.

At its core, smolagents follows the ReAct (Reason + Act) framework. The agent observes the environment, reasons about the next step, takes an action (executes code), observes the result, and repeats until the task is complete. This is the perfect framework for SQL, as it allows the agent to 'test' a query, see if it fails, and fix it autonomously.

Setting Up the Environment

First, we need to prepare our database. We will use SQLAlchemy to create an in-memory SQLite database for our demonstration.

from sqlalchemy import (
    create_engine, MetaData, Table, Column,
    String, Integer, Float, insert, inspect, text
)

# Initialize a mock database
engine = create_engine("sqlite:///:memory:")
metadata_obj = MetaData()

# Define a receipts table
table_name = "receipts"
receipts = Table(
    table_name,
    metadata_obj,
    Column("receipt_id", Integer, primary_key=True),
    Column("customer_name", String(16)),
    Column("price", Float),
    Column("tip", Float),
)
metadata_obj.create_all(engine)

# Insert sample data
rows = [
    {"receipt_id": 1, "customer_name": "Alan Payne", "price": 12.06, "tip": 1.20},
    {"receipt_id": 2, "customer_name": "Alex Mason", "price": 23.86, "tip": 0.24},
    {"receipt_id": 3, "customer_name": "Woodrow Wilson", "price": 53.43, "tip": 5.43},
    {"receipt_id": 4, "customer_name": "Margaret James", "price": 21.11, "tip": 1.00},
]

with engine.begin() as connection:
    for row in rows:
        connection.execute(insert(receipts).values(**row))

Creating the SQL Tool

In smolagents, a tool is defined as a Python function with a clear docstring. The LLM uses this docstring to understand the tool's purpose. For our SQL agent, the tool needs to provide the schema context and an execution interface.

from smolagents import tool

@tool
def sql_engine(query: str) -> str:
    """
    Executes SQL queries against the 'receipts' and 'waiters' tables.
    Args:
        query: A valid SQL string to execute.
    Returns:
        The result of the query as a string.
    """
    try:
        output = ""
        with engine.connect() as con:
            rows = con.execute(text(query))
            for row in rows:
                output += "\n" + str(row)
        return output if output else "No results found."
    except Exception as e:
        return f"Error executing query: {str(e)}"

Initializing the CodeAgent with n1n.ai

To power our agent, we need a robust LLM. Using n1n.ai allows us to switch between models like Llama-3.1-70B or DeepSeek-V3 seamlessly. The CodeAgent will use the sql_engine tool to interact with our data.

from smolagents import CodeAgent, InferenceClientModel

# We use a high-performance model via the n1n.ai compatible interface
model = InferenceClientModel(model_id="meta-llama/Llama-3.1-8B-Instruct")

agent = CodeAgent(
    tools=[sql_engine],
    model=model,
    add_base_tools=False
)

# Run a complex query
response = agent.run("Who paid the highest tip, and what was the total price of their receipt?")
print(response)

Handling Complex Schemas and Multi-Table Joins

The real power of an agent appears when the schema evolves. Let's add a waiters table and see how the agent handles relationships without being explicitly told how to join them.

# Add the waiters table
waiters = Table(
    "waiters",
    metadata_obj,
    Column("receipt_id", Integer, primary_key=True),
    Column("waiter_name", String(16)),
)
metadata_obj.create_all(engine)

waiter_data = [
    {"receipt_id": 1, "waiter_name": "Corey Johnson"},
    {"receipt_id": 2, "waiter_name": "Michael Watts"},
    {"receipt_id": 3, "waiter_name": "Michael Watts"},
    {"receipt_id": 4, "waiter_name": "Margaret James"},
]

with engine.begin() as connection:
    for row in waiter_data:
        connection.execute(insert(waiters).values(**row))

# Update the tool description to include the new table
# (In a real scenario, you'd automate schema extraction)

When you ask the agent: "Which waiter handled the most expensive transaction?", the agent will:

1. Reason: I need to find the maximum price in receipts and then find the waiter_name associated with that receipt_id in the waiters table.
2. Act: Generate a SQL JOIN query.
3. Observe: If the query fails (e.g., column name typo), it reads the error, fixes the SQL, and tries again.
4. Finalize: Provide the natural language answer.

Why Use n1n.ai for Agentic Workflows?

Agentic workflows are inherently token-intensive. Since an agent might make 3-5 calls to the LLM to solve a single complex request, the cost and speed of your API provider become critical. n1n.ai provides a unified API that aggregates the best models, ensuring that your agents have the lowest latency (< 100ms per token) and the highest reliability.

Pro Tips for Production Deployment

1. Validation Layers: Even though the agent is smart, always wrap your sql_engine in a validator to prevent DROP TABLE or DELETE commands unless explicitly required.
2. Schema Pruning: If you have 100+ tables, don't feed them all to the agent. Use a RAG (Retrieval-Augmented Generation) step to find the most relevant table schemas first.
3. Token Usage Monitoring: Because agents can loop, set a max_steps limit in CodeAgent to prevent runaway costs.
4. Model Selection: Use DeepSeek-V3 for complex reasoning tasks; it offers an incredible price-to-performance ratio for iterative agent tasks.

Conclusion

Transitioning from simple SQL generation to autonomous SQL agents is a significant leap in building intelligent data applications. Hugging Face's smolagents provides the lightweight framework needed to implement this, while n1n.ai offers the robust infrastructure to power it. By allowing LLMs to execute code and observe results, we move from a system that 'guesses' the query to one that 'verifies' the answer.

Get a free API key at n1n.ai