Build a Text-to-SQL Agent with smolagents in One File

This guide is adapted from the Hugging Face smolagents repository — specifically text_to_sql.py — and is used under the Apache-2.0 licence.

---

Translating plain English into SQL and running that SQL against a live database is one of the most practically useful things a small agent can do. Instead of forcing every analyst or product manager to learn query syntax, a code agent accepts a natural-language question, reasons about the schema, writes the correct SQL, executes it, and returns a grounded answer — all with every intermediate step visible in the agent's trace. The approach differs from retrieval-augmented generation because the agent is not guessing from embedded text chunks; it is running deterministic queries against real data, so the answer is either right or provably wrong.

This guide is for backend engineers and ML practitioners who already understand SQL and want to wire a language model into a database without building a full LangChain pipeline or a heavyweight orchestration layer. The smolagents CodeAgent is intentionally minimal: it exposes a @tool decorator, a model wrapper, and a run loop — nothing more. The whole working example fits in one file. Model inference runs remotely against the Hugging Face Inference API, so a laptop CPU is sufficient. You need a free Hugging Face account and an API token set as HF_TOKEN; no GPU is required and no model is downloaded locally.

Prerequisites

• Python 3.10 or later
• smolagents and sqlalchemy installed (pip install smolagents sqlalchemy)
• A Hugging Face account with HF_TOKEN set in your environment
• No local GPU required — InferenceClientModel calls the Hugging Face Inference API

Step 1: Create the In-Memory Database and Seed It with Data

The first task is building something the agent can actually query. SQLAlchemy's create_engine("sqlite:///:memory:") gives you an ephemeral SQLite database that lives entirely in RAM — it resets on every run and requires no file system permissions.

The receipts table has four columns: an integer primary key, a short customer name, a float price, and a float tip. Four rows are inserted one at a time using insert().values(**row), each wrapped in its own engine.begin() context manager so every write is committed immediately. The inspect call at the end reads live column metadata back from the database and formats it into a human-readable string. That string is pasted directly into the tool's docstring, which is what the language model reads when it decides how to write a query.

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


engine = create_engine("sqlite:///:memory:")
metadata_obj = MetaData()

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

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},
]
for row in rows:
    stmt = insert(receipts).values(**row)
    with engine.begin() as connection:
        cursor = connection.execute(stmt)

inspector = inspect(engine)
columns_info = [(col["name"], col["type"]) for col in inspector.get_columns("receipts")]

table_description = "Columns:\n" + "\n".join([f"  - {name}: {col_type}" for name, col_type in columns_info])
print(table_description)

Running this block prints:

Columns:
  - receipt_id: INTEGER
  - customer_name: VARCHAR(16)
  - price: FLOAT
  - tip: FLOAT

That output confirms the schema is exactly what the agent will be told about.

Step 2: Wrap the Database in a @tool

The @tool decorator transforms a plain Python function into something the CodeAgent can discover and invoke. It reads the function's type annotations and docstring, then registers both as the tool's schema. This is why the docstring matters: the language model cannot inspect the database directly — it reads the docstring and decides what SQL to write based on it.

Two design choices are worth understanding. First, the function accepts a raw SQL string rather than a structured query object, giving the model full expressive power but full responsibility for correctness. Second, the return type is str — rows are concatenated into a single string rather than returned as a list or DataFrame, keeping the tool simple and ensuring the agent can process the result as plain text in its next reasoning step.

from smolagents import tool


@tool
def sql_engine(query: str) -> str:
    """
    Allows you to perform SQL queries on the table. Returns a string representation of the result.
    The table is named 'receipts'. Its description is as follows:
        Columns:
        - receipt_id: INTEGER
        - customer_name: VARCHAR(16)
        - price: FLOAT
        - tip: FLOAT

    Args:
        query: The query to perform. This should be correct SQL.
    """
    output = ""
    with engine.connect() as con:
        rows = con.execute(text(query))
        for row in rows:
            output += "\n" + str(row)
    return output

engine.connect() (read context) is used here rather than engine.begin() (write context). The agent should be reading data, not mutating it — though note that SQLite does not enforce this at the connection level; see the warnings in the section below.

Step 3: Instantiate the Agent and Run a Question

With the tool defined, two more imports complete the setup: CodeAgent and InferenceClientModel. InferenceClientModel is a thin wrapper around the Hugging Face Inference API. The model_id string "meta-llama/Meta-Llama-3.1-8B-Instruct" selects the remote model — small enough for the free tier, capable enough for straightforward SQL generation.

CodeAgent takes the list of tools and the model as its only required arguments. When agent.run() is called, the agent enters a loop: it reasons about the question, calls sql_engine with a constructed query, receives the string result, and either loops again or returns a final answer. Every step is printed to stdout, making the reasoning auditable in a way that a black-box NL-to-SQL API is not.

from smolagents import CodeAgent, InferenceClientModel


agent = CodeAgent(
    tools=[sql_engine],
    model=InferenceClientModel(model_id="meta-llama/Meta-Llama-3.1-8B-Instruct"),
)
agent.run("Can you give me the name of the client who got the most expensive receipt?")

The expected final answer is Woodrow Wilson, whose receipt totals $53.43 — the highest price value in the table. The agent will typically arrive there by generating SELECT customer_name FROM receipts ORDER BY price DESC LIMIT 1.

Step 4: Verify the End-to-End Result

To confirm everything is wired correctly, call sql_engine directly as a sanity check before routing through the agent:

result = sql_engine("SELECT customer_name, price FROM receipts ORDER BY price DESC LIMIT 1")
print(result)

Expected output:

('Woodrow Wilson', 53.43)

If this matches, the database layer is sound and any unexpected agent output is a reasoning or prompt issue, not a data issue. Isolating the tool from the agent is the fastest way to narrow multi-step failures.

Key Design Tradeoffs

What to Watch Out For

Schema drift is silent. The tool's docstring is a snapshot of the schema at the time you wrote it. If a column is renamed or a table is restructured, the agent will keep generating queries against the old schema and receive errors it cannot interpret correctly. Automate docstring regeneration from inspector.get_columns() if your schema changes frequently.

VARCHAR(16) will silently truncate. The customer_name column is declared as String(16). Any name longer than 16 characters will be truncated on insert without raising an error in SQLite. In a real application, use Text or a wider String unless the length limit is a genuine business constraint.

The model can hallucinate table names. Smaller instruction-tuned models sometimes generate SQL against tables that do not exist, particularly when the schema is not spelled out clearly in the docstring. If you see OperationalError: no such table, the fix is almost always making the docstring more explicit, not changing the model.

API rate limits affect reliability. The Hugging Face free tier imposes rate limits. A multi-step agent that calls the model three or four times per question will exhaust those limits faster than a single-turn request. For production use, consider a dedicated endpoint or a locally hosted model — the inference engine optimisation strategies covered elsewhere on this site become relevant once you move off the free tier.

No write protection by default. The sql_engine tool uses engine.connect(), which in SQLite does not prevent DML statements. A sufficiently confused model could generate a DELETE or UPDATE that the engine will execute. Add explicit SQL parsing, or use a read-only database user, in any environment where data integrity matters.

Prompt injection via stored data. If the database contains user-supplied strings, those strings appear in the tool's return value and could influence the agent's subsequent reasoning. This is a known risk with any agent that reads external data; see the safety discussion around rogue agent behaviour for broader context.

Where to Go Next

The natural extensions from this base are: connecting to a real Postgres or DuckDB instance by changing the SQLAlchemy connection string; adding a second tool that returns schema information dynamically so the agent can handle databases with dozens of tables; and switching from InferenceClientModel to a locally hosted model using TransformersModel for offline or privacy-sensitive deployments. For a deeper look at how frameworks decide when to invoke a tool versus reason further, the AWS agent tool-call policy analysis is a useful companion read. The full smolagents source, including additional examples beyond text-to-SQL, is at https://github.com/huggingface/smolagents (Apache-2.0).