text-to-sql-chat

Ask a sales database questions in plain English or Bahasa Indonesia and get back the SQL, the result table, a one-line answer and a chart when the result has a shape worth plotting. The same guarded query tools are also an MCP server, so Claude Desktop or Cursor can use them too.

The data is Kopi Lereng, a made-up coffee chain: 14 stores in 10 Indonesian cities, 24 products, 700 loyalty members, about 3,700 orders and 6,800 order lines from January 2025 to September 2026. It's generated from a fixed seed, so every install has the same rows.

From the terminal, against local Ollama (table trimmed):

$ uv run sqlchat ask "Produk apa yang paling laris di Bandung tahun 2026?"
SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS total_sold
FROM order_items AS oi
JOIN orders AS o
  ON oi.order_id = o.id
JOIN stores AS s
  ON o.store_id = s.id
JOIN products AS p
  ON oi.product_id = p.id
WHERE
  s.city = 'Bandung'
  AND o.status = 'completed'
  AND o.ordered_at >= '2026-01-01'
  AND o.ordered_at < '2027-01-01'
GROUP BY
  p.name
ORDER BY
  total_sold DESC
LIMIT 200

product_name            total_sold
----------------------  ----------
Es Kopi Susu Gula Aren  84
Cappuccino              64
Caffe Latte             48
Es Kopi Pandan          36
...

Produk yang paling laris di Bandung tahun 2026 adalah Es Kopi Susu Gula Aren dengan total penjualan 84 unit.

LIMIT 200 is the row cap. The validator adds it when the model leaves it out and lowers anything bigger.

How it works

• The model gets the schema built from Postgres table and column comments, plus three sample rows per table. The comments carry the business rules ("only completed orders are revenue"), so the prompt stays short.

• Its SQL is parsed with sqlglot. Only one SELECT over the five sales tables gets through: no DML or DDL, no writes hidden in a CTE, no SELECT INTO. Postgres-only functions have to be on a short allowlist, so pg_sleep, pg_read_file, dblink and friends are out. What runs is the re-rendered tree, with a LIMIT added or clamped.

• It runs as a separate Postgres role that can only SELECT those tables, in a read-only transaction with a statement timeout. If the validator ever misses something, the database still refuses writes and anything over the timeout. Catalog views like pg_roles stay readable to every role, though, so keeping queries on the sales tables is the validator's job.

• When Postgres returns an error (wrong column, bad GROUP BY) or the validator rejects the query (a made-up table, a function that isn't allowed), the reason goes back to the model once for a corrected query.

• A second short call turns the rows into one sentence in the question's language. The chart is picked by plain rules: label + number is a bar chart, date + number is a line chart, anything else is just a table.

• sqlchat-mcp exposes describe_schema, check_sql and run_sql over stdio. There the client's model writes the SQL, and it goes through the same checks.

Related MCP server: Sales Analytics MCP Server

Stack

Python 3.12, uv, LangChain (init_chat_model, Ollama by default), psycopg 3, sqlglot, Streamlit, MCP Python SDK 2 (MCPServer, formerly FastMCP), PostgreSQL 17, pytest, ruff.

Run it

You need Postgres and Ollama. Compose can start the database for you.

git clone https://github.com/ramaraihan12/text-to-sql-chat.git
cd text-to-sql-chat
uv sync
cp .env.example .env
docker compose up -d db
uv run sqlchat setup
ollama pull qwen3.5:9b
uv run streamlit run streamlit_app.py

sqlchat setup creates the tables, loads the seed data and creates the read-only sales_reader role. It's safe to run again. Settings live in .env (see .env.example). On my laptop I use a smaller quant, LLM_MODEL=qwen3.5-9b:iq4xs, because it fits a 6 GB GPU.

Other providers work through the same config. For OpenAI: uv sync --extra openai, then set LLM_PROVIDER=openai, an OpenAI model name in LLM_MODEL and OPENAI_API_KEY. The gemini (provider google_genai) and anthropic extras work the same way.

One question without the UI:

uv run sqlchat ask "Which city had the highest revenue in September 2026?"

Everything in Docker (Postgres, the setup job and the app on http://localhost:8501, with Ollama still on the host):

docker compose up --build

Use it from Claude Desktop or Cursor

Add this to claude_desktop_config.json (Settings, Developer, Edit Config) or to Cursor's mcp.json, with your own path:

{
  "mcpServers": {
    "kopi-lereng-sales": {
      "command": "uv",
      "args": ["--directory", "/path/to/text-to-sql-chat", "run", "sqlchat-mcp"]
    }
  }
}

The server reads the same .env, so it connects as the read-only role. Then ask something like "which store had the most delivery orders last quarter?" and watch it call describe_schema and run_sql.

Tests

uv run pytest

The validator cases, the pipeline (with a fake chat model, including the retry path) and the MCP tools run offline. The integration tests are skipped unless you point them at a scratch database. They rebuild its tables, check that the role really can't write, read other tables or run past the timeout, and run the re-rendered SQL on real Postgres:

TEST_ADMIN_DATABASE_URL=postgresql://postgres:postgres@localhost:5432/text_to_sql \
TEST_DATABASE_URL=postgresql://sales_reader:sales_reader@localhost:5432/text_to_sql \
uv run pytest -m integration

Notes

• A parser allowlist is easy to get subtly wrong. My first version collected CTE names from the whole query, so WITH pg_roles AS (...) inside a subquery let the real pg_roles through outside it. That probe and a few cousins are test cases now. For writes the role and the read-only transaction are the real wall; for catalog reads the validator is all there is.

• With a 9B model on a 6 GB RTX 3060 a question takes about 5 to 10 seconds once the model is loaded. The first one after a model swap is slower.

• A 9B model handles the everyday questions here, but it's still a 9B model. Read the SQL before you trust a number; it's always one click away.

• Next I'd add a small eval set of questions with known answers, so a prompt or model change shows up as a number instead of a feeling.

License

MIT