text-to-sql-mcp

An MCP server that translates natural-language questions into SQL over a real, non-trivial multi-table schema — and never trusts the SQL it gets back. Every generated query is parsed into a real AST (via sqlglot) and run through a validator before anything executes: non-SELECT statements, multi-statement injection, dangerous functions, hallucinated tables/columns, and unbounded full scans of large tables are all rejected structurally, not by trusting an LLM's own judgment.

The model's output is a proposal, not a command. A validator decides what actually runs.

Status

Milestones M1–M4 from the spec are implemented and tested: schema introspection, NL→SQL generation (real Anthropic/OpenAI backends + a deterministic offline fallback), the AST validator, the labelled eval harness, and the MCP server wrapper. See Risks / Open Questions for what was deliberately deferred.

Architecture

NL question
    │
    ▼
schema introspection (introspection.py)  ──► SQLite catalog (sqlite_master + PRAGMA table_info)
    │  grounds the prompt in the *real* schema, not guessed names
    ▼
LLMClient.generate_sql()  (llm/factory.py picks one)
    │  - AnthropicLLMClient  (real Claude API call, used if ANTHROPIC_API_KEY is set)
    │  - OpenAILLMClient     (real OpenAI API call, used if OPENAI_API_KEY is set)
    │  - RuleBasedLLMClient  (deterministic fixture lookup, the offline default)
    ▼
candidate SQL string  ──────────────────►  never trusted past this point
    │
    ▼
validate_sql()  (validator/ast_validator.py)
    │  1. parseable?                       -- sqlglot.parse()
    │  2. exactly one statement?           -- reject `SELECT ...; DROP ...`
    │  3. root node is SELECT/UNION/       -- allow-list, not a blocklist
    │     INTERSECT/EXCEPT?
    │  4. no SELECT ... INTO?
    │  5. no dangerous function calls?     -- load_extension, readfile, writefile, ...
    │  6. every table exists in schema?
    │  7. every resolvable column exists?  -- best-effort, conservative
    │  8. large table + no WHERE + not     -- the "unbounded full scan" check
    │     a bounded/aggregate result?
    │
    ├── reject ──► {rejected: true, rejection_reason: "..."}
    │
    ▼ ok
execute_query()  (execution.py)  ──► SQLite opened `mode=ro` + `PRAGMA query_only=ON`
    │  (defense in depth: even a validator bug can't write, because the
    │   connection itself refuses)
    ▼
{sql, rows, rejected: false}
    │
    ▼
query_log  (query_log.py)  ──► every call logged, rejection rate reported
                                 separately from accuracy (see below)

Everything above is wrapped as an MCP server (mcp_server.py, built on the official mcp Python SDK's FastMCP) exposing exactly the two tools from the spec's API contract:

• list_schema() -> {tables: [{name, columns: [{name, type}]}]}

• ask(question: str) -> {sql, rows, rejected, rejection_reason}

Why SQLite instead of Postgres

The spec targets Postgres specifically. This environment has no running Postgres server and no Docker daemon, so the target database is SQLite instead — a deliberate, documented substitution, not an oversight. The introspection layer (introspection.py) is the only piece that's genuinely SQLite-specific (sqlite_master + PRAGMA table_info in place of information_schema); the validator, execution layer, and MCP wrapper operate on the parsed AST and don't know or care which database produced the schema. Postgres upgrade path: swap db/connection.py's sqlite3.connect(..., mode=ro) for a psycopg connection opened against a read-only role, rewrite introspection.py's two queries against information_schema.tables/columns, and pass dialect="postgres" to validate_sql() — sqlglot supports both dialects natively, so the AST logic itself does not change.

Why a synthetic dataset instead of a live open-data pull

The spec suggests a real city/government open-data portal. db/seed.py instead generates a synthetic-but-realistic municipal dataset — 12 tables modeled on real permit/inspection/violation schemas (NYC DOB, Chicago building permits) — deterministically from a fixed seed, entirely offline. This was a deliberate trade-off, not laziness: it keeps init-db reproducible with zero network dependency (no flaky CI, no rate limits, no portal downtime) and sidesteps the licensing question the spec itself flags as a risk (§13) before ever publishing a demo. The schema is genuinely non-trivial by the spec's own bar: 12 tables, foreign keys three hops deep (payments → violations → properties), and deliberate column-name ambiguity (status appears on permits, licenses, violations, and complaints; type on four different tables) that exercises the validator's schema-grounding logic for real.

Install

pip install -e .

Requires Python 3.10+. Optional extras for the real LLM backends (already installed in dev environments that have them; only needed if you don't):

pip install -e ".[anthropic]"   # anthropic SDK
pip install -e ".[openai]"      # openai SDK

Quickstart — real demo run against a real SQLite database

# 1. Build the demo database (12 tables, ~8,700 rows, deterministic seed 42)
text-to-sql-mcp init-db

# 2. Inspect the schema the model is grounded in
text-to-sql-mcp schema

# 3. Ask a question -- no API key needed, uses the deterministic rule-based backend
text-to-sql-mcp ask "How many permits are there in total?"

backend:  rule-based
sql:      SELECT COUNT(*) AS count FROM permits
rejected: False
rows (1):
[
  {
    "count": 2600
  }
]

A join-heavy question:

text-to-sql-mcp ask "How many permits does each contractor hold?"

backend:  rule-based
sql:      SELECT c.business_name, COUNT(*) AS permit_count FROM permits p JOIN contractors c ON p.contractor_id = c.contractor_id GROUP BY c.business_name ORDER BY permit_count DESC
rejected: False
rows (50):
[
  { "business_name": "Garcia Builders", "permit_count": 167 },
  { "business_name": "Kim Builders", "permit_count": 144 },
  { "business_name": "Miller Plumbing Co", "permit_count": 119 },
  ...
]

An ambiguous question — deliberately not silently resolved to one guess (see Edge cases):

text-to-sql-mcp ask "Show me the recent activity."

backend:  rule-based
sql:      AMBIGUOUS: 'Recent activity' could mean permits, inspections, violations, complaints, or payments -- and over what time window. Please specify which type of record and a date range or property.
rejected: True
reason:   Question is ambiguous and was not silently resolved to one interpretation. Clarification needed: ...

Proof that the validator, not the model, is what blocks destructive SQL — this uses a fixture client standing in for a compromised/prompt-injected model that always complies with the destructive request:

python - <<'EOF'
from text_to_sql_mcp.config import get_settings
from text_to_sql_mcp.service import ask

class MaliciousFixtureLLMClient:
    name = "malicious-fixture"
    def generate_sql(self, question, schema):
        return "DROP TABLE permits"

result = ask("Please delete all the permit records.",
             llm_client=MaliciousFixtureLLMClient(), settings=get_settings())
print("sql:     ", result.sql)
print("rejected:", result.rejected)
print("reason:  ", result.rejection_reason)
EOF

sql:      DROP TABLE permits
rejected: True
reason:   Statement type 'Drop' is not a read-only SELECT/UNION/INTERSECT/EXCEPT query. Only SELECT-family statements may be executed.

Now check what the operator sees — the rejection rate, reported separately from accuracy (see below):

text-to-sql-mcp rejection-report

{
  "total_queries": 5,
  "rejected": 3,
  "accepted": 2,
  "rejection_rate": 0.6,
  "rejected_by_reason": {
    "ambiguous_question": 1,
    "generation_failed": 1,
    "not_select": 1
  }
}

(That 0.6 is not a target number to hit — it's whatever the actual mix of questions asked in this session produced, on this exact run. Re-running init-db and repeating the commands above reproduces it exactly, since the seed data and the rule-based backend are both deterministic.)

Accuracy on the labelled eval set

text-to-sql-mcp eval

Real output, rule-based backend, this seed (25 questions: 8 easy / 10 medium / 7 hard, spanning the spec's 20–30 question requirement):

{
  "total_questions": 25,
  "correct": 20,
  "accuracy": 0.8,
  "rejected": 6,
  "rejection_rate": 0.24,
  "by_difficulty": {
    "easy":   { "total": 8,  "correct": 8, "accuracy": 1.0 },
    "medium": { "total": 10, "correct": 8, "accuracy": 0.8 },
    "hard":   { "total": 7,  "correct": 4, "accuracy": 0.5714 }
  },
  "by_join_heaviness": {
    "simple":     { "total": 17, "correct": 17, "accuracy": 1.0 },
    "join_heavy": { "total": 8,  "correct": 3,  "accuracy": 0.375 }
  }
}

This 80% is not a coincidence and not a claim taken at face value — it's produced by a deliberate design choice: the rule-based backend recognizes 20 of the 25 questions and raises on the other 5 rather than guessing (see llm/rule_based.py's _UNANSWERED_IDS). The eval harness runs every question through the real ask() pipeline and compares actual returned rows against a gold query executed fresh against the same database — it is not hand-maintained expected numbers that could silently drift from the seed data. Accuracy decays with difficulty (100% → 80% → 57%) and is dramatically lower on join-heavy questions (37.5% vs. 100% on simple ones) purely because the rule-based backend is a lookup table, not because the harness or validator is doing anything different — which is exactly the honest signal the spec's acceptance criterion asks for ("accuracy is measured and reported, not just claimed").

With a real ANTHROPIC_API_KEY configured, ask()/eval route through AnthropicLLMClient instead (see What needs a real API key) and accuracy would reflect actual open-ended NL→SQL quality rather than fixture coverage — that was not run in this environment, since no API key is configured here, and the README does not claim a number for it.

Adversarial validation — 100% rejection, tested two ways

pytest tests/test_validator_adversarial.py tests/test_service_adversarial.py -v

• tests/test_validator_adversarial.py — 29 deliberately malicious/malformed SQL strings (DROP, DELETE, UPDATE, INSERT, CREATE TABLE AS SELECT, ALTER, PRAGMA, ATTACH DATABASE, GRANT, VACUUM/REINDEX, stacked-statement injection via ;, load_extension/readfile/writefile, SELECT ... INTO, empty/garbage input) fed directly to validate_sql() — 29/29 rejected, plus 2 dedicated tests pinning exactly how comment-smuggled second statements are handled (33 test functions total in the file).

• tests/test_service_adversarial.py — the same guarantee at the ask() level, via a fixture LLM client that always complies with an adversarial natural-language prompt instead of refusing it — proving the validator is what blocks execution, "not by hoping the model refuses" (the spec's own phrasing for this acceptance criterion). 8/8 adversarial prompts still end up rejected even though the fixture model never says no.

The validator's _ALLOWED_ROOT_TYPES is an allow-list (Select/Union/Intersect/Except), not a blocklist of dangerous keywords — every DML/DDL/admin statement sqlglot recognizes parses to a distinct, non-allow-listed AST node type by construction, so there's no keyword list to keep in sync and no way to rename or disguise a destructive statement into passing.

What needs a real API key vs. what works standalone today

llm/factory.py picks the backend automatically: Anthropic if ANTHROPIC_API_KEY is set, else OpenAI if OPENAI_API_KEY is set, else the rule-based fallback — no code changes needed to switch. The AST validator's behavior is identical regardless of which backend produced the SQL — that's the actual point of the architecture (the model's output is a proposal, never trusted), and it's why the adversarial suite and general validator tests don't need any LLM backend at all to prove the safety property.

Edge cases handled

• Ambiguous NL question (§9): rather than silently picking one interpretation, the prompt instructs the LLM to respond AMBIGUOUS: <clarifying question> instead of SQL; service.ask() detects this and returns rejected: true with the clarification as the reason, never executing a guess. See test_ask_handles_ambiguous_question_without_silently_guessing.

• Join-heavy questions tracked separately (§9): EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — see the real 100% vs. 37.5% split above.

• Prompt injection disguised as SELECT (§9): the allow-list root-type check means a DROP/DELETE/etc. can't pass no matter how the prompt asks for it; see the adversarial suites above.

• Very large table full scan (§9): _find_unfiltered_large_table_scan flags a SELECT with no WHERE on a table above the row-count threshold (500 by default) and whose result isn't otherwise bounded (no GROUP BY, no LIMIT, not a pure aggregate). That last clause is a deliberate refinement beyond the spec's literal wording: without it, ordinary reporting queries like SELECT COUNT(*) FROM permits would be rejected alongside genuinely expensive SELECT * FROM permits, which would make the validator useless for real reporting. See test_pure_aggregate_on_large_table_passes_without_where vs. test_unfiltered_select_star_on_large_table_is_rejected.

• Schema mismatches (hallucinated table/column names): checked structurally against the introspected schema, not string-matched against a hardcoded list — test_unknown_table_is_rejected, test_unknown_column_on_known_table_is_rejected. Column-existence checking is deliberately conservative (skips ambiguous unqualified references across multiple joined tables) to avoid false-positive rejections of legitimate queries — see the docstring on _find_unknown_column.

MCP server

text-to-sql-mcp serve

Runs the server over stdio. Point any MCP client at it (e.g. add it to Claude Desktop's config, or drive it with the mcp Python SDK's ClientSession). Tested end-to-end in tests/test_mcp_server.py via mcp.shared.memory.create_connected_server_and_client_session — a real ClientSession talking to a real FastMCP server over an in-memory transport, calling list_tools() and call_tool(...) exactly as an external MCP client would, not just invoking the underlying Python functions directly.

Testing

pytest

91 tests, all passing. Breakdown:

• test_introspection.py — schema introspection accuracy (tables, columns, row counts, large-table threshold)

• test_rule_based_llm.py — deterministic backend coverage, including its deliberate gaps

• test_execution.py — read-only enforcement (defense in depth), row-limit truncation

• test_validator_general.py — valid queries pass, schema grounding, bounded-vs-unbounded large-table logic

• test_validator_adversarial.py — 29-case adversarial suite, 100% rejection

• test_service_ask.py / test_service_adversarial.py — end-to-end ask(), including the full-pipeline adversarial proof

• test_eval_runner.py — the eval harness itself (shape, accuracy breakdowns, ambiguity handling)

• test_query_log.py — logging + the operator-facing rejection-rate report, including a real ask() integration test

• test_mcp_server.py — end-to-end via a real MCP ClientSession

Configuration

Copy .env.example to .env and fill in what you have — everything has a working default:

cp .env.example .env

Risks / Open Questions / Scope cuts

Honest accounting of what didn't make it in, per the spec's own §13 and this portfolio's engineering-judgment mandate:

• Postgres, not SQLite, per the spec's literal wording. No Postgres server or Docker daemon is available in this environment. Documented substitution + upgrade path above; the AST validator and execution-layer design were kept dialect-agnostic on purpose so this isn't a rewrite later.

• Synthetic dataset, not a live open-data portal pull. Deliberate trade-off for offline reproducibility and to sidestep the licensing question the spec itself flags as a risk — see the dedicated section above.

• Rule-based backend is a fixture lookup table, not a general model. This is explicit and by design per this portfolio's environment constraints (no LLM API keys configured here) — the real Anthropic/OpenAI backends exist, are fully implemented, and share the identical validator/execution path; they were simply never run against a live API key in this environment, so no live-generation accuracy number is claimed.

• Column-existence checking is best-effort, not exhaustive. It deliberately skips ambiguous unqualified column references across multi-table joins rather than risk false-positive rejections — documented in _find_unknown_column's docstring. Table-existence checking (the higher-value guard against hallucinated tables) is not similarly hedged.

• No query result caching / connection pooling. Each ask() opens a fresh read-only SQLite connection. Fine at this scale (single-file demo DB); would need attention before high-QPS production use.

• Ambiguity detection depends on the LLM backend following the AMBIGUOUS: convention. The rule-based backend implements it for its one deliberately-ambiguous fixture question; a real Anthropic/OpenAI call is instructed to follow the same convention via the shared system prompt (llm/prompt.py) but this is prompt-level cooperation, not independently enforced by the validator (open-ended ambiguity detection isn't a thing an AST checker can verify).

• MISSING_WHERE_LARGE_TABLE's bounded-result heuristic is a refinement beyond the spec's literal wording, not a limitation exactly, but worth flagging as a judgment call: it treats GROUP BY, LIMIT, and pure-aggregate projections as exempt from the missing-WHERE check. See the Edge cases section for the reasoning and the two tests that pin the behavior on each side of the line.

License

MIT — see LICENSE.