Skip to content

PovilasKvedaras/TextToSQL

BranchesTags

Repository files navigation

TextToSQL MCP Server

A read-only, schema-aware TextToSQL MCP server. It fronts a SQL warehouse (PostgreSQL or SQLite) and exposes six tools any MCP-capable host or agent can drive: refine a question, browse curated schema knowledge, validate, explain, and run SELECT statements — with independent read-only safety layers in between. Schema knowledge lives as skills-as-markdown: generated drafts you curate once, then freeze. The server ships not yet configured; a bundled SQLite demo database lets you run the full lifecycle end-to-end in about five minutes.

5-minute demo quickstart

The repo bundles the SQL Murder Mystery dataset (demo/murder_mystery.db, 9 tables, ~57k rows — MIT licensed, see THIRD_PARTY_LICENSES.md).

uv sync
cp .env.demo .env                       # SQLite DSN, INTROSPECTION=true
ollama pull gpt-oss:120b                # or set LLM_FALLBACK_API_KEY (see LLM configuration)
uv run python scripts/verify_role.py    # confirms the read-only guard is active
uv run python -m texttosql_mcp.run_http --port 8765

In a second terminal:

curl -s http://localhost:8765/health | python -m json.tool
curl -s -X POST http://localhost:8765/admin/regenerate_skill | python -m json.tool
#   -> 9 tables discovered, 6 FKs; skill drafts under skills/; baseline fixtures written
uv run --extra dev pytest tests/test_fixtures.py -v
#   -> EXPECTED: several fixtures FAIL against the raw drafts (integer dates,
#      lowercase enums, the ssn join). That failure is the point — the drafts
#      contain no semantic knowledge yet.

Now author the curated sections (see The three phases below — fill the <TODO> blocks in skills/ using POST /admin/probe_table samples), then:

uv run --extra dev pytest tests/test_fixtures.py -v   # all demo fixtures pass
# flip INTROSPECTION=false in .env, restart the server
uv run --extra dev pytest tests/test_fixtures.py -v   # still green; admin endpoints now 403
# connect any MCP-capable client/agent to http://localhost:8765/mcp

Architecture

An MCP host (any agent framework, an MCP inspector, or a plain script using the MCP SDK) connects over Streamable HTTP and drives the tool set; the server owns schema knowledge (skills), safety, and DB access. The only server-side LLM call is refine_question — everything else is deterministic.

┌─────────────────────────────────────────────────────────┐
│ MCP host / agent (any MCP-capable client)               │
└──────────────────────────┬──────────────────────────────┘
                           │ Streamable HTTP  POST /mcp/
┌──────────────────────────▼──────────────────────────────┐
│ TextToSQL MCP server (FastAPI + FastMCP)                 │
│                                                          │
│  refine_question ──► LLM (translate + vocabulary map)    │
│  list_tables / get_table_schema ──► skills/*.md (cached) │
│  validate_query ──► sqlglot AST guard                    │
│  explain_query ──► EXPLAIN guard (cost / plan hints)     │
│  run_query ──► validate + explain + execute (row-capped) │
│  introspect_schema ──► Phase 1 only (INTROSPECTION=true) │
│                                                          │
│  read-only connection (RO role / PRAGMA query_only)      │
└──────────────────────────┬──────────────────────────────┘
                           │ async SQLAlchemy
                ┌──────────▼──────────┐
                │ PostgreSQL / SQLite │
                └─────────────────────┘

The three phases

Phase 1 — bootstrap skills (INTROSPECTION=true). POST /admin/regenerate_skill sweeps the schema (tables, columns, PKs, FKs, indexes, low-cardinality enums, sample rows) and renders draft skill files: skills/sql_schema.skill.md (master) plus skills/tables/<table>.md. Drafts auto-fill everything mechanical and leave <TODO> markers in the semantic sections. A baseline fixture file (row counts per table) is also written.

Phase 2 — curate and iterate. Fill the curated sections — the master's OVERVIEW, VOCABULARY, CROSS_TABLE_EXAMPLES and PITFALLS, and each table's PURPOSE, EXAMPLES, PITFALLS — using POST /admin/probe_table?name=<table> to inspect real rows. Iterate with the NL→SQL fixture harness (pytest tests/test_fixtures.py): each fixture asks a question, lets the LLM draft SQL against your skills, runs it, and asserts tables used, clauses, values, and row counts. Re-runs of Phase 1 preserve curated sections (merge_preserving_curated).

Phase 3 — freeze. Set INTROSPECTION=false and restart. The introspection tool and admin endpoints disappear (tools/list shows exactly six tools; admin returns 403). Skill files are the frozen contract; edits to them hot-reload on the next call (mtime cache), no restart needed.

Pointing at your own database

Configuration is env-only — no DSN, schema name, or threshold lives in source. Copy .env.example to .env and set:

Variable Meaning
DATABASE_URL Async SQLAlchemy DSN (required)
SCHEMA_NAME Schema to introspect/query (required; SQLite: main)
INTROSPECTION true during Phases 1–2, false in production
INTROSPECTION_TABLE_PREFIXES CSV of table-name prefixes to limit scope (empty = all)
ALLOWED_QUERY_SCHEMAS Extra schemas SELECTs may reference (empty = SCHEMA_NAME only)
SAMPLE_ROW_LIMIT Sample rows per table in drafts
SKILL_DIR / FIXTURE_DIR Where skills / fixtures live
EXPLAIN_WARN_COST / EXPLAIN_HARD_COST Cost guard thresholds (PostgreSQL only)
DEV_DB_VERSION Bump to invalidate the harness result cache
MCP_HTTP_PORT HTTP port (default 8765)

PostgreSQL — use a read-only role:

CREATE ROLE warehouse_read LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA analytics TO warehouse_read;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO warehouse_read;
ALTER ROLE warehouse_read SET default_transaction_read_only = on;
ALTER ROLE warehouse_read SET statement_timeout = '30s';
DATABASE_URL=postgresql+psycopg://warehouse_read:...@host:5432/db
SCHEMA_NAME=analytics

SQLite — point at any .db file; every connection gets PRAGMA query_only=ON, so writes are refused at the connection level:

DATABASE_URL=sqlite+aiosqlite:///./path/to/your.db
SCHEMA_NAME=main

Bring your own CSVs — build a demo DB from flat files (stdlib only):

python scripts/csv_to_sqlite.py my.db --csv orders=./orders.csv --csv customers=./customers.csv

PKs/FKs are not inferred; add them by hand if you want the JOINS section of the master skill populated (convention-only joins work too — the renderer handles the no-FK case).

Run uv run python scripts/verify_role.py after any connection change — it fails loudly if the connection can write.

LLM configuration

The LLM drives the refine_question tool and the pytest harness; the other five tools never call one. Any OpenAI-compatible endpoint works.

# Primary (default: local Ollama)
LLM_BASE_URL=http://localhost:11434/v1
LLM_MODEL=gpt-oss:120b            # or gpt-oss:20b on smaller machines
LLM_API_KEY=ignored

# Automatic fallback when the primary is unreachable (probe once per process).
# Enabled by setting the key; default target is OpenRouter's automatic router.
LLM_FALLBACK_BASE_URL=https://openrouter.ai/api/v1
LLM_FALLBACK_MODEL=openrouter/auto
LLM_FALLBACK_API_KEY=
LLM_PROBE_TIMEOUT=2

On first LLM use, the server probes GET {LLM_BASE_URL}/models; if unreachable and a fallback key is set, all calls transparently use the fallback (a warning is logged once). GET /health surfaces the resolved endpoint and fallback_active so a dead primary is a one-line diagnosis, not a mid-run timeout.

Safety model

Three independent layers on PostgreSQL; two layers plus plan hints on SQLite:

  1. Read-only connection. PostgreSQL: RO role with default_transaction_read_only=on + statement_timeout. SQLite: PRAGMA query_only=ON on every pooled connection.
  2. AST validation (validate_query). sqlglot rejects anything that is not exactly one SELECT — including CTE-hidden writes — plus references to schemas outside the allowlist.
  3. EXPLAIN guard (explain_query). PostgreSQL: two-tier cost thresholds (warn / reject) with rewrite hints derived from the plan. SQLite: EXPLAIN QUERY PLAN has no cost estimates — the cost guard is a PostgreSQL feature; SQLite mode still catches invalid statements pre-run and degrades to plan-shape hints (full scans, temp B-trees, automatic indexes).

run_query re-runs layers 2–3 internally and enforces a server-side row cap (default 1,000, max 10,000) regardless of any LIMIT in the SQL.

Tracing (Langfuse)

Optional, off by default. Set LANGFUSE_ENABLED=true plus LANGFUSE_PUBLIC_KEY / LANGFUSE_SECRET_KEY / LANGFUSE_BASE_URL to emit one span per MCP tool call, a generation span for refine_question, and harness spans per fixture. Clients that pass session_id into tool calls get their trace stitched with the server's spans under one session. When disabled, the SDK is never imported.

Tests

# Unit layer — no DB, no LLM required
uv run --extra dev pytest tests/test_config_env_only.py tests/test_safety.py \
    tests/test_tools.py tests/test_refine_question.py -v

# Fixture harness — needs the configured DB and a live LLM endpoint
uv run --extra dev pytest tests/test_fixtures.py -v

Fixture runs write reports to tests/reports/<ISO>/ (gitignored) and cache verified SQL results in tests/.db_cache/ keyed on (normalized_sql, db_fingerprint) — the LLM call is never cached.

Operational notes

  • Skill hot-reload: skill files are cached by mtime; edits are picked up on the next MCP call. POST /admin/skill_reload force-invalidates (Phase 1–2).
  • Phase 1 backups: re-running introspection backs up prior skills to skills/.bak-<ISO>/ (retention INTROSPECTION_BACKUP_RETENTION).
  • Table menu is authoritative: a table missing from the master file's TABLES section is invisible to clients even if it exists in the DB.
  • Windows: the launcher (texttosql_mcp.run_http) pins a SelectorEventLoop, required by psycopg async.

Troubleshooting

Symptom Likely cause Fix
Tool calls return HTTP 500 "Task group is not initialized" Mounted app without the FastAPI lifespan Start via texttosql_mcp.run_http, not a bare uvicorn app:...
validate_query rejects with forbidden_schema SQL references a schema outside the allowlist Use unqualified/SCHEMA_NAME tables, or opt in via ALLOWED_QUERY_SCHEMAS
Every query rejected with high cost Thresholds too low for your warehouse Raise EXPLAIN_WARN_COST / EXPLAIN_HARD_COST (PostgreSQL only)
Accented/case-variant text doesn't match Case-sensitive = comparison Use case-insensitive matching (ILIKE on PostgreSQL, LIKE on SQLite)
refine_question returns the question unchanged LLM endpoint down and no fallback key Check GET /health → llm.fallback_active; set LLM_FALLBACK_API_KEY
Fixtures re-execute all SQL after a DB reload Fingerprint changed (expected) Bump DEV_DB_VERSION only when you want invalidation

Repository layout

src/texttosql_mcp/
  server.py           # FastMCP tool registration (six tools)
  server_http.py      # FastAPI app: /mcp mount, /health, /admin/* (Phase 1)
  server_stdio.py     # stdio transport alternative
  run_http.py         # launcher (Windows-safe event loop)
  config.py           # env-only Settings (pydantic-settings)
  db.py               # async engine + read-only guard + db_fingerprint
  llm.py              # ChatOpenAI factory with automatic fallback
  tools/              # refine_question, list_tables, get_table_schema,
                      # validate_query, explain_query, run_query, introspect_schema
  safety/             # sqlglot AST guard, EXPLAIN guard, plan hints
  introspection/      # schema sweep, skill renderer, fixture stub
  skill/              # mtime-cached loader + section parser
skills/               # curated knowledge (generated locally; gitignored)
tests/                # unit tests + NL→SQL fixture harness
demo/                 # bundled SQLite demo DB + dataset license
scripts/              # verify_role.py, csv_to_sqlite.py

License

MIT — see LICENSE. Bundled demo dataset attribution: THIRD_PARTY_LICENSES.md.

About

This is a personal TextToSQL MCP implementation with python and FastAPI

Resources

Readme

License

MIT license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

1 tags

Packages

 
 
 

Contributors

Languages