SQL Chat

Natural language to SQL chat interface. Ask questions in plain English, get answers from a PostgreSQL database — powered by OpenRouter LLMs with MCP-based database access.

Architecture

Browser (React) ──POST /chat──▶ FastAPI ──▶ OpenRouter LLM (LLM_MODEL)
                                    │
                                    ▼
                              FastMCP Server (stdio)
                                    │
                                    ▼
                              PostgreSQL (products table)

The LLM receives MCP tool definitions as function-calling tools. When it needs data, it generates SQL, invokes the MCP server's read-only run_select_query tool, and weaves the result into a natural-language answer.

Prerequisites

• Nix with flakes enabled (tested on NixOS 26.05)
• An OpenRouter API key

Quick Start

# 1. Clone and enter the dev shell (installs everything automatically)
git clone <this-repo>
cd chat-ai-demo
nix develop

# 2. Set your OpenRouter API key
cp .env.example .env
# Edit .env → replace OPENAI_API_KEY with your real key
# Optional: override LLM_MODEL (default: google/gemini-3-flash-preview; mistral-large-0512 in testing)

# 3. Generate and import fake data
python scripts/seed.py
python scripts/import_csv.py

# 4. Start the backend (terminal 1)
cd backend
uvicorn app.main:app --reload

# 5. Start the frontend (terminal 2)
cd frontend
bun run dev

Open http://localhost:5173 and start asking questions like:

• "How many products are there?"
• "What are the top 5 most expensive products in Electronics?"
• "Which categories have the lowest total stock?"

What nix develop Does

• Provides Python 3.12, PostgreSQL 16, and Bun directly from the Nix store — no venv or pip install needed
• Spins up a local PostgreSQL cluster on port 5433 with your username
• Sets DATABASE_URL and OPENAI_BASE_URL environment variables

Without Nix

Bun is not included — install it from bun.sh or your package manager.

python -m venv backend/.venv
source backend/.venv/bin/activate
pip install -e "backend[dev]" -c backend/constraints.txt
# … then cd frontend && bun install && bun run dev

The backend/constraints.txt pins the same package versions that the Nix flake provides.

Project Structure

chat-ai-demo/
├── flake.nix                     # Nix dev shell
├── .env.example                  # Environment template
├── backend/
│   ├── pyproject.toml            # Python deps
│   ├── constraints.txt           # Pinned versions (synced with Nix)
│   └── app/
│       ├── main.py               # FastAPI app
│       ├── config.py             # ENV-based settings
│       ├── database.py           # SQLAlchemy engine
│       ├── models.py             # Products ORM model
│       ├── mcp_server.py         # FastMCP: 3 read-only tools
│       ├── services/
│       │   └── llm.py            # OpenRouter client + tool calling
│       └── router/
│           └── chat.py           # POST /chat endpoint
├── frontend/
│   ├── package.json
│   ├── vite.config.ts            # Dev proxy to backend
│   └── src/
│       ├── App.tsx
│       ├── styles.css            # Fsas design system
│       ├── components/
│       │   ├── ChatWindow.tsx    # Chat UI
│       │   └── SqlResultCard.tsx # SQL + result display
│       └── hooks/
│           └── useChat.ts        # Fetch logic
├── scripts/
│   ├── seed.py                   # Generate 100 fake products
│   └── import_csv.py             # Import into PostgreSQL
└── data/
    └── sample.csv                # Generated seed data

API

POST /chat

Field	Type	Description
message	string	Natural language question

Response:

Field	Type	Description
answer	string	LLM's natural-language response
sql	string?	Generated SQL (if a query was executed)
result	string?	Formatted query result

MCP Tools (Read-Only)

Tool	Description
list_tables()	List all database tables
describe_table(name)	Get DDL + row count for a table
run_select_query(sql)	Execute a SELECT query

Only SELECT queries are permitted — enforced by regex before execution.

Tech Stack

Layer	Technology
LLM	OpenRouter (LLM_MODEL env var)
MCP Server	FastMCP over stdio
Backend	Python 3.12, FastAPI, SQLAlchemy 2.0
Frontend	React 19, TypeScript (strict), Vite
Database	PostgreSQL 16
Dev Environment	Nix flake