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
Top comments (0)