DEV Community: Jefferson Vargas

From Natural Language to PostgreSQL: Building a Multi-Provider Text-to-SQL Assistant with Chat UI, Query Logs, and Safe Execution

Jefferson Vargas — Sun, 05 Jul 2026 06:47:38 +0000

Text-to-SQL is one of the most practical ways to apply AI to real software. Instead of forcing users to understand database schemas, joins, filters, and aggregates, a Text-to-SQL system lets them ask questions in natural language and receive answers backed by real data.

In this project, the goal was not only to generate SQL from a user prompt. The objective was to build a complete developer-friendly application with a chat-style interface, a backend that translates questions into SQL, a validation layer that prevents dangerous queries, a PostgreSQL database, a natural-language answer generator, and a logging system that records each query for observability.

This article walks through the architecture, design decisions, safety model, logging strategy, and implementation details behind the project. Rather than presenting a shallow demo, it shows how a Text-to-SQL idea can evolve into a small but coherent application that feels much closer to a real product.

Source code: text-to-sql-smolagents-demo

Why build a Text-to-SQL assistant?

A lot of people know what they want to ask from data, but they do not know how to write the SQL query needed to retrieve it. Even developers regularly spend time translating business questions into SQL syntax, checking table names, fixing joins, and validating aggregates before they can get a useful answer.

A Text-to-SQL assistant reduces that friction by acting as a bridge between natural language and structured data. Instead of writing queries manually, a user can ask questions such as “Which customer has the most completed orders?”, “How many critical tickets are still open?”, or “What is the total revenue by city?” The system takes that question, understands the available schema, generates a query, validates it, executes it, and returns both a human-readable answer and the raw result set.

That last part matters a lot. If the system only returns SQL, it is not very helpful for non-technical users. If it only returns a sentence without showing the SQL and the data, it becomes harder to trust and debug. The best experience exposes both layers: a clean answer for the user and enough technical detail for inspection.

Project goals

The project started with a few concrete goals. It needed to accept natural-language questions through a chat-style frontend, support multiple model providers and a mock mode for development, use PostgreSQL as the execution engine, validate generated SQL before execution, and return a natural-language answer in addition to the SQL and result table. On top of that, it had to store query history and basic metadata for observability, while remaining easy to run locally with Docker Compose.

Those goals pushed the project beyond a notebook or one-file prototype. The app needed a small but clear architecture with separation of concerns, so each stage in the flow could be understood, tested, and improved independently.

Application in action

Before diving into the architecture, here is a real example of the application answering a natural-language question through the chat interface.

In this example, the user asks:

Which customer has the most completed orders?

The assistant generates SQL, validates it, executes it against PostgreSQL, and returns both a human-readable answer and the raw result table. That interaction captures the core behavior of the app: turning a business question into a safe database query and presenting the result in a way that is easy to understand and easy to inspect.

This screenshot shows the end-to-end flow working as intended. The user asks a question in plain language, the backend translates it into SQL, the database returns rows, and the interface presents the answer together with the underlying query and execution details.

High-level architecture

At a high level, the system is organized around five layers: the chat UI, the backend API, the Text-to-SQL engine, the PostgreSQL database, and the observability layer that stores query history.

The flow is simple to describe, but useful because each stage has a clear responsibility. The frontend sends the user question and selected model configuration to the backend. The backend inspects the database schema, builds a prompt for SQL generation, validates the generated query, executes it against PostgreSQL, and then synthesizes a natural-language answer. Finally, it stores execution metadata in query_logs and returns the full response to the frontend.

That structure makes the app easier to reason about. Instead of letting the model control everything in one opaque step, the pipeline is broken into visible stages that can be debugged and improved separately.

Why a chat UI instead of a traditional form?

The choice of interface matters more than it might seem. A plain form with a textarea and a submit button feels like a utility, while a chat UI feels like an assistant. Since the product is trying to bridge a human question and a relational database, the conversational format communicates the intent of the system much better.

A chat-style layout also helps organize the response. The user question appears on one side, the assistant answer on the other, and technical details such as the SQL query, result table, and execution metadata can be revealed as supporting information. This makes the app easier to understand for non-technical users without hiding the implementation details from developers.

It also improves perceived responsiveness. Loading states like “Thinking…”, “Generating SQL…”, “Querying PostgreSQL…” and “Preparing response…” give the impression of a system that is actively working through a pipeline, rather than a page that has simply frozen.

Backend responsibilities

The backend is the center of the application. Its job is not just to call a model and hope for the best, but to coordinate the full lifecycle of each request. It builds prompts for SQL generation, validates and executes SQL, synthesizes a natural-language answer, and records logs for history and observability.

That separation is important in any Text-to-SQL system. If the model directly owns every step, it becomes difficult to understand failures or guarantee safe behavior. By keeping the stages explicit, the backend turns the app into something that is much easier to inspect and maintain.

Main request pipeline

The request pipeline can be summarized in a simple sequence: the user submits a question, the backend loads the schema, the model generates SQL, the validation layer checks it, PostgreSQL executes it, the result is converted into a natural-language answer, and the entire interaction is logged.

This pipeline is one of the strongest parts of the design because it avoids turning the system into a black box. Each stage produces an intermediate artifact that can be inspected: the schema summary, the generated SQL, the validated SQL, the rows returned by the database, the answer shown to the user, and the log entry recorded for later analysis.

Database schema awareness

One of the most common reasons Text-to-SQL systems fail is that the model does not know enough about the available schema. If it invents columns or tables, the query fails immediately. If it misunderstands relationships between tables, the query may run but return the wrong answer.

To reduce that risk, the backend inspects the live PostgreSQL schema and builds a prompt that includes table names, column names, data types, and any other useful context. That extra structure helps the model stay grounded in the actual database rather than guessing what might exist.

A simplified prompt fragment looks like this:

You are a SQL generation assistant.
Use only the tables and columns listed below.
Return only one SQL SELECT statement.
Do not use markdown.
Do not explain the query.

Schema:
- customers(id, name, city)
- orders(id, customer_id, status, total, created_at)
- tickets(id, customer_id, severity, status)

Question:
Which customer has the most completed orders?

This kind of constraint is simple, but very effective. Instead of asking the model to improvise, it narrows the problem to “given this exact schema, produce one valid query.”

SQL generation module

The SQL generation layer is intentionally small. Its purpose is to isolate prompt construction from the rest of the pipeline and make the generation step easy to test or replace later.

from typing import Protocol

class LLMProvider(Protocol):
    def generate(self, prompt: str, model: str) -> str:
        ...


def build_sql_prompt(question: str, schema_text: str) -> str:
    return f"""
You are a SQL assistant.
Generate exactly one safe SQL SELECT query.
Use only the schema below.
Do not include markdown fences.
Do not explain anything.

Schema:
{schema_text}

User question:
{question}
""".strip()


def generate_sql(question: str, schema_text: str, provider: LLMProvider, model: str) -> str:
    prompt = build_sql_prompt(question, schema_text)
    sql = provider.generate(prompt, model=model).strip()
    return sql

This module does not try to be clever. That is a good thing. The simpler the generation layer is, the easier it becomes to reason about prompt behavior and compare providers later.

SQL validation layer

SQL validation is one of the most important parts of the project. Letting AI-generated SQL run without checks is risky even in a demo, because the model could produce mutating or destructive statements such as DROP TABLE customers;, DELETE FROM orders;, or UPDATE users SET role = 'admin';.

The validation layer acts as a small guardrail between the model and the database. It only allows read-only queries, blocks dangerous keywords, prevents multiple statements, and can add a default LIMIT if the query does not include one.

import re

FORBIDDEN = [
    "INSERT", "UPDATE", "DELETE", "DROP", "ALTER",
    "TRUNCATE", "GRANT", "REVOKE", "CREATE"
]


def validate_sql(sql: str) -> str:
    clean = sql.strip().rstrip(";")
    upper = clean.upper()

    if not upper.startswith("SELECT"):
        raise ValueError("Only SELECT queries are allowed.")

    for keyword in FORBIDDEN:
        if re.search(rf"\b{keyword}\b", upper):
            raise ValueError(f"Forbidden SQL keyword detected: {keyword}")

    if ";" in clean:
        raise ValueError("Multiple SQL statements are not allowed.")

    if "LIMIT" not in upper:
        clean += " LIMIT 100"

    return clean

This is not a complete security model, but it is a strong baseline for a demo, internal prototype, or educational project. More importantly, it makes the safety policy explicit instead of assuming the model will always behave correctly.

PostgreSQL execution

Once the query has passed validation, the backend can execute it against PostgreSQL. The execution layer is deliberately minimal because its job is straightforward: send the SQL, collect the rows, and convert them into a structure the API can return.

from sqlalchemy import text


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

Keeping this function short is useful because it reduces the chances of hiding logic in the wrong place. Query generation belongs to the model layer, validation belongs to the guard, and execution belongs to the database layer.

Natural-language answer synthesis

Returning raw rows is useful for debugging, but most users want a direct answer first. That is why the project adds a second AI stage after SQL execution. The model receives the original question, the SQL that actually ran, and a truncated version of the returned rows, then writes a short answer in plain language.

You are a data assistant.
Answer in Spanish.
Use only the SQL results provided.
Do not invent data.
If there are no rows, say that no results were found.
Do not repeat the SQL unless necessary.

Question:
What customer has the most completed orders?

Executed SQL:
SELECT c.name, COUNT(*) AS total_orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.name
ORDER BY total_orders DESC
LIMIT 1;

Rows:
[{"name": "Ana Torres", "total_orders": 4}]

A possible output would be:

El cliente con más órdenes completadas es Ana Torres, con 4 pedidos.

That extra synthesis step changes the feel of the app considerably. Instead of exposing users to raw tables first, it gives them an answer they can read immediately while still preserving the technical evidence behind it.

Fallback answer logic

Relying entirely on a second model call is not ideal. If the provider fails, the API key is missing, or the request times out, the app should still produce a reasonable response. For that reason, the backend includes a lightweight fallback function that can summarize simple result sets without another LLM call.

def fallback_natural_answer(question: str, rows: list[dict]) -> str:
    if not rows:
        return "No se encontraron resultados para tu consulta."

    if len(rows) == 1 and len(rows) == 1:
        value = next(iter(rows.values()))
        return f"El resultado es {value}."

    if len(rows) == 1:
        parts = [f"{k}: {v}" for k, v in rows.items()]
        return "Se encontró un resultado: " + ", ".join(parts) + "."

    return f"Se encontraron {len(rows)} resultados."

This feature is small, but it improves resilience and ensures the app remains usable even when the answer synthesis provider is unavailable.

API design

The query endpoint returns both user-friendly and debug-friendly data. That balance is important because the frontend needs enough information to present a polished assistant response, while developers still need visibility into what happened under the hood.

{
  "provider": "mock",
  "model": "demo-sql-v1",
  "question": "¿Qué cliente tiene más órdenes completadas?",
  "answer": "El cliente con más órdenes completadas es Ana Torres, con 4 pedidos.",
  "sql": "SELECT c.name, COUNT(*) AS total_orders FROM ... LIMIT 1",
  "rows": [
    {"name": "Ana Torres", "total_orders": 4}
  ],
  "row_count": 1,
  "error": null
}

This structure makes the frontend easier to build because it can always render the answer, SQL, and result table from the same response object. It also keeps logs consistent, since the same fields can be stored for later inspection.

Query logging and observability

One of the most valuable improvements in the project was adding observability. Most AI demos stop at “it works,” but once a project is published publicly or used in a longer demo, it becomes much more useful to know what question was asked, which provider and model were used, how long the request took, whether it failed or succeeded, how many rows came back, and what SQL was executed.

That need led to the creation of a query_logs table that stores each interaction as a structured event rather than letting it disappear after the response is rendered.

CREATE TABLE IF NOT EXISTS query_logs (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    question TEXT NOT NULL,
    provider TEXT NOT NULL,
    model TEXT NOT NULL,
    generated_sql TEXT,
    natural_answer TEXT,
    row_count INTEGER NOT NULL DEFAULT 0,
    latency_ms INTEGER NOT NULL DEFAULT 0,
    status TEXT NOT NULL,
    error_message TEXT
);

This schema is intentionally small, but it adds a lot of value. It turns the project into something you can inspect, analyze, and explain more confidently.

Logging flow

The logging layer sits alongside the main pipeline and records the most important execution details: the generated SQL, the answer returned to the user, the request latency, the number of rows produced, and whether the request succeeded or failed.

Once those logs exist, the frontend can expose a history sidebar showing previous questions, timestamps, status badges, provider/model information, latency, and row count. That makes the application feel much closer to a real product and not just a one-time demo.

Query logger module

To keep concerns separated, the logging logic lives in its own module rather than being mixed directly into the SQL generation or API code.

from sqlalchemy import text


def log_query_execution(
    engine,
    question: str,
    provider: str,
    model: str,
    generated_sql: str | None,
    natural_answer: str | None,
    row_count: int,
    latency_ms: int,
    status: str,
    error_message: str | None = None,
):
    sql = text("""
        INSERT INTO query_logs (
            question, provider, model, generated_sql,
            natural_answer, row_count, latency_ms,
            status, error_message
        ) VALUES (
            :question, :provider, :model, :generated_sql,
            :natural_answer, :row_count, :latency_ms,
            :status, :error_message
        )
    """)

    with engine.begin() as conn:
        conn.execute(sql, {
            "question": question,
            "provider": provider,
            "model": model,
            "generated_sql": generated_sql,
            "natural_answer": natural_answer,
            "row_count": row_count,
            "latency_ms": latency_ms,
            "status": status,
            "error_message": error_message,
        })

This design keeps the main request pipeline smaller and easier to understand, while also making it simpler to change logging behavior later without touching unrelated parts of the application.

Main pipeline example

When all the pieces are connected, the request handler ends up looking fairly clean. The pipeline generates SQL, validates it, executes it, synthesizes an answer, computes latency, and records the final result in the log table.

import time


def process_question(engine, provider_client, model: str, question: str, schema_text: str):
    start = time.time()
    generated_sql = None
    answer = None
    rows = []

    try:
        generated_sql = generate_sql(question, schema_text, provider_client, model)
        safe_sql = validate_sql(generated_sql)
        rows = run_query(engine, safe_sql)
        answer = fallback_natural_answer(question, rows)

        latency_ms = int((time.time() - start) * 1000)
        log_query_execution(
            engine=engine,
            question=question,
            provider="mock",
            model=model,
            generated_sql=safe_sql,
            natural_answer=answer,
            row_count=len(rows),
            latency_ms=latency_ms,
            status="success",
            error_message=None,
        )

        return {
            "question": question,
            "answer": answer,
            "sql": safe_sql,
            "rows": rows,
            "row_count": len(rows),
            "error": None,
        }

    except Exception as exc:
        latency_ms = int((time.time() - start) * 1000)
        log_query_execution(
            engine=engine,
            question=question,
            provider="mock",
            model=model,
            generated_sql=generated_sql,
            natural_answer=None,
            row_count=0,
            latency_ms=latency_ms,
            status="error",
            error_message=str(exc),
        )

        return {
            "question": question,
            "answer": None,
            "sql": generated_sql,
            "rows": [],
            "row_count": 0,
            "error": str(exc),
        }

That lifecycle is one of the reasons the project feels well structured. Every request follows the same pattern: generate, validate, execute, answer, and log.

Frontend rendering strategy

The frontend is designed so the natural-language answer is the most visible part of the assistant response. The SQL query and the raw table remain available, but they appear as supporting details instead of overwhelming the user.

<div class="message assistant">
  <div class="bubble">
    <p class="answer">El cliente con más órdenes completadas es Ana Torres, con 4 pedidos.</p>

    <details>
      <summary>View SQL</summary>
      <pre><code>SELECT c.name, COUNT(*) AS total_orders ...</code></pre>
    </details>

    <div class="result-table"></div>

    <div class="meta">
      <span>mock</span>
      <span>demo-sql-v1</span>
      <span>1 row</span>
    </div>
  </div>
</div>

That rendering strategy keeps the app accessible to non-technical users while still giving developers enough insight into what the backend actually did.

Why observability changed the quality of the project

Before query logs existed, the system could answer questions, but it was harder to explain and harder to debug. Once logging was added, the app became much more credible. It became possible to review past questions, inspect generated SQL, compare latency across providers, test error handling more deliberately, and present a complete system lifecycle in an article or video.

That is one of the biggest differences between a small AI demo and a more mature engineering project. Observability does not just help operations; it also helps storytelling, debugging, and iteration.

Docker and local development

Docker Compose makes the stack much easier to run and share. Instead of asking readers to manually install PostgreSQL, configure dependencies, run migrations, and match environment settings by hand, the project can start in a predictable way with the app service, database, configuration, and static frontend assets working together.

That is especially important for public repositories. If someone clones the project, they should be able to get it running without reverse-engineering the local environment from scattered instructions.

Example use cases

This architecture can be useful in several real workflows. In business analytics, a team could ask which city generated the most revenue this month, how many customers placed repeat orders, or what the average order value is by region. In customer support, the same approach could answer questions such as how many critical tickets remain open, which users have the most unresolved incidents, or what the average resolution time is by category.

It is also useful as an internal developer tool. A developer or analyst can use the app as a safe read-only SQL assistant to inspect a staging dataset without writing queries manually. That makes the system valuable even before it is polished enough for broader audiences.

Limitations

The project works well as a demo and engineering walkthrough, but it still has clear limits. It is not a full production system, it does not implement role-based access control, and its SQL validation is rule-based rather than a formal security model. Query correctness still depends heavily on prompt quality and schema clarity, and although pgvector is enabled as an architectural foundation, semantic retrieval is still a future extension rather than a fully implemented feature.

Those limitations are normal for a repository built to teach architecture and experimentation. In fact, stating them clearly makes the project more credible because it sets expectations honestly.

Possible future improvements

There are several directions that would make the system even stronger. Hybrid retrieval with pgvector would allow semantic search to complement structured SQL. Automatic retries for malformed SQL could improve resilience. Provider comparison based on latency and success logs would make the system more measurable. Export to CSV, evaluation datasets, benchmark prompts, and authentication for private deployments would all push the app further toward production-style use cases.

For now, though, the current version already demonstrates the most important idea: a clean and inspectable bridge between natural-language questions and relational data.

Final thoughts

The main lesson from this project is that Text-to-SQL is not just about generating SQL. A useful system needs surrounding engineering: prompt construction, schema awareness, safety validation, SQL execution, answer synthesis, frontend UX, and observability.

Once those pieces are combined, the result becomes much more than a toy demo. It becomes a small but meaningful AI application that is easy to explain, easy to demonstrate, and easy to evolve. If the goal is to build something publishable, this combination works especially well because it shows both AI capability and software engineering discipline. The assistant does not just generate SQL; it behaves like a real product layer between humans and data.

Repository

The full source code for this project is available here:

GitHub: text-to-sql-smolagents-demo

The repository includes the Docker Compose setup, PostgreSQL + pgvector configuration, backend API, chat-style frontend, SQL validation layer, natural-language answer generation, and query history with observability.

References

This project was inspired by and built on top of the following resources:

Hugging Face smolagents: GitHub repository
Hugging Face Text-to-SQL example: official example
Union.ai tutorial: Natural Language SQL Query Agent using Smolagent

Cómo construir un agente de IA que "habla" con tu base de datos SQL

Jefferson Vargas — Sun, 05 Jul 2026 01:01:28 +0000

Introducción

Escribir SQL a mano es una de esas habilidades que, sin querer, deja fuera a mucha gente de los datos: product managers, agentes de soporte e incluso desarrolladores junior muchas veces saben exactamente qué quieren preguntarle a una base de datos, pero no cómo expresarlo en SQL. Este es el problema que los sistemas Text-to-SQL intentan resolver: escribir una pregunta en lenguaje natural (inglés o español) y obtener datos reales de una base de datos real.

En este artículo construyo un pequeño pero completo agente de IA Text-to-SQL usando la librería smolagents de Hugging Face, explico por qué un agente es más confiable que un pipeline ingenuo de "el LLM escribe SQL y lo ejecutamos", recorro código real, y cubro las consideraciones de seguridad que necesitas antes de conectar algo así a una base de datos en producción.

El código fuente completo de este proyecto está disponible en el repositorio público enlazado al final de este artículo.

¿Por qué no simplemente pedirle el SQL a un LLM?

El pipeline Text-to-SQL más simple posible se ve así:

Enviar el esquema de la base de datos + la pregunta del usuario a un LLM.
Pedirle al LLM que devuelva una consulta SQL.
Ejecutar esa consulta y mostrar el resultado.

Esto funciona... hasta que deja de funcionar. El problema es que una sola llamada al LLM es frágil: el modelo puede generar una consulta sintácticamente válida pero semánticamente incorrecta (join equivocado, filtro equivocado, agregación equivocada), y aun así devolverá un resultado, solo que no el correcto. No hay ningún mecanismo integrado para verificar la respuesta o intentarlo de nuevo.

Esta es exactamente la "pregunta de oro" con la que abre la documentación de smolagents: ¿por qué no mantenerlo simple con un solo prompt? La respuesta es que un agente —algo capaz de razonar, invocar herramientas, observar el resultado y decidir si debe intentarlo de nuevo— es fundamentalmente más robusto para este tipo de tarea.

¿Qué es smolagents y qué significa "pensar en código"?

smolagents es un framework de agentes ligero de Hugging Face, descrito por sus propios mantenedores como "una librería minimalista para agentes que piensan en código". En lugar de forzar al modelo a producir llamadas a herramientas en JSON rígido, un CodeAgent escribe y ejecuta código Python real como parte de su ciclo de razonamiento:

Pensar — el modelo razona sobre qué hacer a continuación.
Actuar — escribe un fragmento de código Python que invoca una de las herramientas que le diste (en nuestro caso, una herramienta de ejecución SQL).
Observar — el resultado de ese código (la salida de la consulta) se reintegra al contexto del modelo.
Repetir o responder — el agente decide si tiene suficiente información para responder, o si necesita ajustar la consulta e intentar de nuevo.

Este ciclo es lo que hace que el agente sea autocorrectivo: si una consulta devuelve algo sospechoso (un resultado vacío, un número obviamente incorrecto), el modelo puede reescribir la consulta en lugar de devolver con confianza una respuesta equivocada.

Construyendo el proyecto: una pequeña base de datos SQLite de "recibos"

Para mantener el ejemplo accesible, uso una base de datos SQLite en memoria con dos tablas pequeñas, la misma estructura usada en el ejemplo oficial de Text-to-SQL de smolagents.

1. Configurando la base de datos

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

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

# Tabla 1: receipts (recibos)
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:
        connection.execute(stmt)

# Tabla 2: waiters (meseros), para hacer joins
table_name = "waiters"
waiters = Table(
    table_name,
    metadata_obj,
    Column("receipt_id", Integer, primary_key=True),
    Column("waiter_name", String(16), primary_key=True),
)
metadata_obj.create_all(engine)

waiter_rows = [
    {"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"},
]

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

2. Describiendo el esquema para el agente

El agente necesita saber qué columnas existen antes de poder escribir SQL útil. Construimos esa descripción de forma dinámica en lugar de escribirla a mano:

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

table_description = "Columns:\n"
for name, col_type in columns_info:
    table_description += f"  - {name}: {col_type}\n"

print(table_description)

3. Definiendo la herramienta SQL

Esta es la pieza central: una sola función de Python, decorada con @tool, que el agente tiene permitido invocar. Su docstring es la documentación que el modelo lee para entender cómo y cuándo usarla, así que debe ser precisa.

from smolagents import tool

@tool
def sql_engine(query: str) -> str:
    """
    Permite realizar consultas SQL sobre la tabla 'receipts'.
    Devuelve una representación en texto del resultado.
    La tabla se llama 'receipts'. Su descripción es la siguiente:
        Columns:
        - receipt_id: INTEGER
        - customer_name: VARCHAR(16)
        - price: FLOAT
        - tip: FLOAT

    Args:
        query: La consulta SQL a ejecutar. Debe ser SQL válido.
    """
    output = ""
    with engine.connect() as con:
        rows = con.execute(text(query))
        for row in rows:
            output += "\n" + str(row)
    return output

4. Creando el agente

from smolagents import CodeAgent, InferenceClientModel

agent = CodeAgent(
    tools=[sql_engine],
    model=InferenceClientModel(model_id="meta-llama/Llama-3.1-8B-Instruct"),
)

agent.run("¿Puedes darme el nombre del cliente con el recibo más caro?")

Por detrás, el agente escribe algo equivalente a:

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

...observa el resultado, y solo entonces produce una respuesta en lenguaje natural: "Woodrow Wilson tuvo el recibo más caro, de $53.43."

5. Escalando: joins entre dos tablas

Una vez que introducimos una segunda tabla (waiters), simplemente extendemos el docstring de la herramienta para describir ambas tablas y cambiamos a un modelo de razonamiento más potente:

updated_description = """Permite realizar consultas SQL sobre las tablas. Ten en cuenta que la salida de esta herramienta es una representación en texto de la ejecución.
Puede usar las siguientes tablas:"""

updated_description += table_description  # receipts

updated_description += """

Tabla 'waiters':
Columns:
  - receipt_id: INTEGER
  - waiter_name: VARCHAR(16)
"""

sql_engine.description = updated_description

agent = CodeAgent(
    tools=[sql_engine],
    model=InferenceClientModel(model_id="Qwen/Qwen3-Next-80B-A3B-Thinking"),
)

agent.run("¿Qué mesero recibió más dinero en propinas en total?")

Ahora el agente tiene que razonar sobre un join entre receipts y waiters, ejecutarlo e interpretar el resultado, todo sin que nosotros escribamos ese SQL manualmente.

Prompt directo al LLM vs. agente que valida su trabajo

	LLM directo → SQL	Agente con smolagents
Generación de consultas	Un solo intento, sin retroalimentación	Iterativa, puede reintentar
Manejo de errores	Puede devolver respuestas erróneas en silencio	Puede inspeccionar la salida y autocorregirse
Conocimiento del esquema	Inyectado de forma estática en el prompt	Docstring de la herramienta, actualizable dinámicamente
Razonamiento multi-paso (joins, agregaciones)	Débil, propenso a errores	Más sólido, especialmente con modelos grandes
Auditabilidad	La consulta suele quedar oculta dentro de un prompt/respuesta	La consulta es código explícito, fácil de registrar (log)

La conclusión clave: un agente no elimina los errores, pero le da al sistema la oportunidad de notarlos y reaccionar ante ellos en lugar de devolver con confianza un número incorrecto.

Seguridad: no te saltes esta sección

Permitir que un modelo de IA genere y ejecute SQL contra una base de datos real es poderoso, y peligroso si se deja sin control. Antes de lanzar algo así, hay que atender:

Acceso de solo lectura. La herramienta solo debería poder ejecutar sentencias SELECT. Hay que bloquear o filtrar DROP, DELETE, UPDATE, INSERT y ALTER a nivel de la herramienta, no solo "pidiéndoselo amablemente" en el prompt.
Usuarios de base de datos con mínimo privilegio. Conectar mediante un rol que solo pueda ver las vistas/tablas que realmente necesita, nunca el superusuario de producción.
Vistas en lugar de tablas crudas. Exponer vistas curadas con solo las columnas relevantes para el caso de uso, ocultando por completo campos sensibles (datos personales, costos internos, etc.).
Límites en las consultas. Forzar cláusulas LIMIT y timeouts para que una consulta mal formada o demasiado amplia no escanee millones de filas ni cuelgue la conexión.
Registro y auditoría. Como la "acción" del agente es código literal y legible, conviene registrar cada consulta generada y su resultado; esto es más sencillo aquí que con llamadas a herramientas en JSON opaco.

Casos de uso reales

Dashboards de analítica — en lugar de construir un gráfico para cada pregunta posible, dejar que los usuarios pregunten "¿cuántos registros nuevos tuvimos la semana pasada por región?" y generar la consulta de agregación al vuelo.
Herramientas de soporte al cliente — los agentes de soporte pueden preguntar "muéstrame los últimos 5 tickets de este usuario" sin necesidad de conocer el esquema del sistema de tickets.
Inteligencia de negocio interna — personas no técnicas pueden explorar datos de ventas, inventario o uso de forma conversacional, dejando que el agente maneje los joins y las agregaciones.
Herramientas para desarrolladores — exploración rápida de datos durante debugging, sin escribir scripts SQL desechables a mano.

Conclusión

Text-to-SQL es un gran ejemplo de un caso donde "simplemente llamar a un LLM" no es suficiente: la diferencia entre un prompt de un solo intento y un verdadero ciclo de agente es la diferencia entre una demo y algo en lo que realmente confiarías con datos reales. El enfoque "código primero" de smolagents hace que ese ciclo sea transparente, inspeccionable y, con las protecciones adecuadas, genuinamente viable para producción.

Repositorio: el código completo y funcional de este proyecto (configuración de la base de datos, definición de la herramienta y configuración del agente) está disponible aquí: github.com/iovargasjeff/text-to-sql-smolagents-demo

Video demostrativo: un video corto mostrando al agente respondiendo preguntas en lenguaje natural sobre la base de datos de ejemplo está enlazado arriba.

Referencias: Documentación de Hugging Face smolagents – Text-to-SQL, Repositorio de GitHub de smolagents