text-to-sql-mcp

Ein MCP-Server, der natürlichsprachliche Fragen in SQL über ein reales, nicht-triviales Multi-Table-Schema übersetzt — und dem SQL, das er zurückbekommt, niemals vertraut. Jede generierte Abfrage wird in einen echten AST geparst (über sqlglot) und durch einen Validator geschickt, bevor irgendetwas ausgeführt wird: Nicht-SELECT-Anweisungen, Multi-Statement-Injection, gefährliche Funktionen, halluzinierte Tabellen/Spalten und unbegrenzte Vollscans großer Tabellen werden strukturell abgelehnt — nicht indem dem Urteilsvermögen eines LLM vertraut wird.

Das Ergebnis des Modells ist ein Vorschlag, kein Befehl. Ein Validator entscheidet, was tatsächlich ausgeführt wird.

Status

Die Meilensteine M1–M4 aus der Spezifikation sind implementiert und getestet: Schema-Introspection, NL→SQL-Generierung (echte Anthropic/OpenAI-Backends plus ein deterministisches Offline-Fallback), der AST-Validator, die beschriftete Eval-Harness und der MCP-Server-Wrapper. Siehe Risiken / Offene Fragen für das, was bewusst aufgeschoben wurde.

Architektur

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)

Alles oben Genannte ist als MCP-Server gekapselt (mcp_server.py, aufgebaut auf dem offiziellen FastMCP des mcp-Python-SDK) und stellt genau die zwei Werkzeuge aus dem API-Vertrag der Spezifikation bereit:

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

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

Warum SQLite statt Postgres

Die Spezifikation zielt ausdrücklich auf Postgres ab. In dieser Umgebung läuft kein Postgres-Server und kein Docker-Daemon, daher ist die Zieldatenbank stattdessen SQLite — eine bewusste, dokumentierte Substitution, kein Versehen. Die Introspection-Schicht (introspection.py) ist das einzige Stück, das wirklich SQLite-spezifisch ist (sqlite_master + PRAGMA table_info anstelle von information_schema); der Validator, die Ausführungsschicht und der MCP-Wrapper arbeiten auf dem geparsten AST und wissen nicht und kümmern sich nicht darum, welche Datenbank das Schema erzeugt hat. Postgres-Upgrade-Pfad: Ersetze in db/connection.py den sqlite3.connect(..., mode=ro) durch eine psycopg-Verbindung, die gegen eine Read-only-Rolle geöffnet wird, schreibe die beiden Abfragen in introspection.py gegen information_schema.tables/columns um und übergib dialect="postgres" an validate_sql() — sqlglot unterstützt beide Dialekte nativ, sodass sich die AST-Logik selbst nicht ändert.

Warum ein synthetischer Datensatz statt eines Live-Open-Data-Abzugs

Die Spezifikation schlägt ein echtes Stadt-/Verwaltungs-Open-Data-Portal vor. db/seed.py erzeugt stattdessen einen synthetischen, aber realistischen Kommunaldatensatz — 12 Tabellen, modelliert nach realen Permit-/Inspektions-/Verstoß-Schemas (NYC DOB, Chicago Building Permits) — deterministisch aus einem festen Seed, vollständig offline. Dies war ein bewusster Kompromiss, keine Faulheit: Es macht init-db ohne Netzwerkabhängigkeit reproduzierbar (kein flakiges CI, keine Ratenlimits, keine Portal-Ausfallzeiten) und umgeht die Lizenzfrage, die die Spezifikation selbst als Risiko (§13) markiert, bevor überhaupt eine Demo veröffentlicht wird. Das Schema ist nach dem eigenen Maßstab der Spezifikation wirklich nicht-trivial: 12 Tabellen, Fremdschlüssel drei Ebenen tief (payments → violations → properties), und bewusste Spaltennamens-Mehrdeutigkeit (status erscheint auf permits, licenses, violations und complaints; type auf vier verschiedenen Tabellen), die die Schema-Grounding-Logik des Validators wirklich fordert.

Installation

pip install -e .

Erfordert Python 3.10+. Optionale Extras für die echten LLM-Backends (bereits installiert in Entwicklungsumgebungen, die sie haben; nur benötigt, falls du sie nicht hast):

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

Schnellstart — echter Demo-Lauf gegen eine echte SQLite-Datenbank

# 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
  }
]

Eine join-lastige Frage:

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 },
  ...
]

Eine mehrdeutige Frage — bewusst nicht stillschweigend zu einer Vermutung aufgelöst (siehe Randfälle):

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: ...

Beweis, dass der Validator und nicht das Modell destruktives SQL blockiert — dies verwendet einen Fixture-Client als Stellvertreter für ein kompromittiertes/prompt-injiziertes Modell, das destruktiven Anfragen immer nachkommt:

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.

Jetzt sieh, was der Operator sieht — die Ablehnungsrate, getrennt von der Genauigkeit berichtet (siehe unten):

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
  }
}

(Dieses 0.6 ist keine Zielvorgabe, die es zu erreichen gilt — es ist das, was die tatsächliche Mischung der in dieser Sitzung gestellten Fragen bei diesem genauen Lauf ergeben hat. Ein erneutes Ausführen von init-db und Wiederholen der obigen Befehle reproduziert es exakt, da die Seed-Daten und das regelbasierte Backend deterministisch sind.)

Genauigkeit auf dem beschrifteten Eval-Datensatz

text-to-sql-mcp eval

Echte Ausgabe, regelbasiertes Backend, dieser Seed (25 Fragen: 8 einfach / 10 mittel / 7 schwer, die die 20–30-Fragen-Anforderung der Spezifikation abdecken):

{
  "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 }
  }
}

Diese 80 % sind kein Zufall und keine Behauptung, die für bare Münze genommen wird — sie entstehen durch eine bewusste Designentscheidung: Das regelbasierte Backend erkennt 20 der 25 Fragen und wirft bei den anderen 5 einen Fehler, anstatt zu raten (siehe _UNANSWERED_IDS in llm/rule_based.py). Die Eval-Harness führt jede Frage durch die echte ask()-Pipeline und vergleicht die tatsächlich zurückgegebenen Zeilen mit einer Gold-Abfrage, die frisch gegen dieselbe Datenbank ausgeführt wird — es sind keine handgepflegten erwarteten Zahlen, die still vom Seed abweichen könnten. Die Genauigkeit nimmt mit dem Schwierigkeitsgrad ab (100 % → 80 % → 57 %) und ist bei join-lastigen Fragen deutlich niedriger (37,5 % gegenüber 100 % bei einfachen), rein weil das regelbasierte Backend eine Nachschlagetabelle ist, nicht weil die Harness oder der Validator etwas anderes tun — und genau das ist das ehrliche Signal, das das Akzeptanzkriterium der Spezifikation verlangt („Genauigkeit wird gemessen und berichtet, nicht nur behauptet“).

Mit einem echten ANTHROPIC_API_KEY in der Konfiguration laufen ask()/eval stattdessen über AnthropicLLMClient (siehe Was einen echten API-Schlüssel benötigt), und die Genauigkeit würde die tatsächliche offene NL→SQL-Qualität widerspiegeln statt der Fixture-Abdeckung — das wurde in dieser Umgebung nicht ausgeführt, da hier kein API-Schlüssel konfiguriert ist, und die README erhebt dafür keine Zahl.

Adversarial-Validierung — 100 % Ablehnung, auf zwei Arten getestet

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

• tests/test_validator_adversarial.py — 29 absichtlich bösartige/fehlerhafte SQL-Strings (DROP, DELETE, UPDATE, INSERT, CREATE TABLE AS SELECT, ALTER, PRAGMA, ATTACH DATABASE, GRANT, VACUUM/REINDEX, gestapelte Statement-Injection über ;, load_extension/readfile/writefile, SELECT ... INTO, leere/Müll-Eingabe) werden direkt an validate_sql() gefüttert — 29/29 abgelehnt, plus 2 spezielle Tests, die genau festnageln, wie per Kommentar geschmuggelte zweite Anweisungen behandelt werden (insgesamt 33 Testfunktionen in der Datei).

• tests/test_service_adversarial.py — dieselbe Garantie auf der ask()-Ebene, über einen Fixture-LLM-Client, der einer adversarischen natürlichsprachlichen Aufforderung immer nachkommt, anstatt sie abzulehnen — was beweist, dass der Validator die Ausführung blockiert, „nicht in der Hoffnung, dass das Modell sich weigert“ (die eigene Formulierung der Spezifikation für dieses Akzeptanzkriterium). 8/8 adversarische Prompts werden dennoch abgelehnt, obwohl das Fixture-Modell niemals Nein sagt.

_ALLOWED_ROOT_TYPES des Validators ist eine Allowlist (Select/Union/Intersect/Except), keine Blocklist gefährlicher Schlüsselwörter — jede DML/DDL/Admin-Anweisung, die sqlglot erkennt, wird konstruktionsbedingt in einen eigenen AST-Knotentyp geparst, der nicht in der Allowlist enthalten ist, sodass es keine Schlüsselwortliste gibt, die synchron gehalten werden muss, und keine Möglichkeit, eine destruktive Anweisung so umzubenennen oder zu tarnen, dass sie durchkommt.

Was einen echten API-Schlüssel benötigt vs. was heute eigenständig funktioniert

llm/factory.py wählt das Backend automatisch: Anthropic, wenn ANTHROPIC_API_KEY gesetzt ist, sonst OpenAI, wenn OPENAI_API_KEY gesetzt ist, sonst das regelbasierte Fallback — keine Codeänderungen zum Wechseln nötig. Das Verhalten des AST-Validators ist identisch, egal welches Backend das SQL erzeugt hat — genau das ist der eigentliche Punkt der Architektur (die Ausgabe des Modells ist ein Vorschlag, niemals vertraut), und deshalb brauchen die Adversarial-Suite und die allgemeinen Validator-Tests keinerlei LLM-Backend, um die Sicherheitseigenschaft zu beweisen.

Behandelte Randfälle

• Mehrdeutige NL-Frage (§9): Anstatt stillschweigend eine Interpretation zu wählen, weist der Prompt das LLM an, mit AMBIGUOUS: <clarifying question> statt mit SQL zu antworten; service.ask() erkennt dies und gibt rejected: true mit der Klärungsfrage als Grund zurück, ohne jemals eine Vermutung auszuführen. Siehe test_ask_handles_ambiguous_question_without_silently_guessing.

• Join-lastige Fragen getrennt erfasst (§9): EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — siehe die echte 100 % vs. 37,5 %-Aufteilung oben.

• Als SELECT getarnte Prompt-Injection (§9): Die Allowlist-Prüfung des Root-Typs bedeutet, dass ein DROP/DELETE/usw. nicht durchkommt, egal wie der Prompt danach fragt; siehe die Adversarial-Suites oben.

• Vollscan einer sehr großen Tabelle (§9): _find_unfiltered_large_table_scan markiert ein SELECT ohne WHERE auf einer Tabelle oberhalb der Zeilenanzahl-Schwelle (standardmäßig 500) und dessen Ergebnis nicht anderweitig begrenzt ist (kein GROUP BY, kein LIMIT, keine reine Aggregation). Diese letzte Klausel ist eine bewusste Verfeinerung über den wörtlichen Wortlaut der Spezifikation hinaus: Ohne sie würden gewöhnliche Berichtsabfragen wie SELECT COUNT(*) FROM permits zusammen mit wirklich teuren SELECT * FROM permits abgelehnt, was den Validator für echte Berichte unbrauchbar machen würde. Siehe test_pure_aggregate_on_large_table_passes_without_where vs. test_unfiltered_select_star_on_large_table_is_rejected.

• Schema-Mismatches (halluzinierte Tabellen-/Spaltennamen): werden strukturell gegen das introspizierte Schema geprüft, nicht per String-Abgleich gegen eine hartkodierte Liste — test_unknown_table_is_rejected, test_unknown_column_on_known_table_is_rejected. Die Spaltenexistenzprüfung ist bewusst konservativ (überspringt mehrdeutige, nicht qualifizierte Referenzen über mehrere verbundene Tabellen hinweg), um False-Positive-Ablehnungen legitimer Abfragen zu vermeiden — siehe den Docstring von _find_unknown_column.

MCP-Server

text-to-sql-mcp serve

Startet den Server über stdio. Richten Sie einen beliebigen MCP-Client darauf aus (z. B. fügen Sie ihn zur Konfiguration von Claude Desktop hinzu, oder steuern Sie ihn mit der ClientSession des mcp-Python-SDK). End-to-end getestet in tests/test_mcp_server.py über mcp.shared.memory.create_connected_server_and_client_session — eine echte ClientSession, die mit einem echten FastMCP-Server über einen In-Memory-Transport kommuniziert und list_tools() sowie call_tool(...) genau so aufruft, wie es ein externer MCP-Client tun würde, nicht nur durch direkten Aufruf der zugrunde liegenden Python-Funktionen.

Testen

pytest

91 Tests, alle bestanden. Aufschlüsselung:

• test_introspection.py — Genauigkeit der Schema-Introspection (Tabellen, Spalten, Zeilenanzahlen, Schwellenwert für große Tabellen)

• test_rule_based_llm.py — Abdeckung des deterministischen Backends, einschließlich seiner bewussten Lücken

• test_execution.py — Durchsetzung von „nur lesen“ (mehrschichtige Verteidigung), Beschränkung der Zeilenanzahl

• test_validator_general.py — gültige Abfragen bestehen, Schema-Verankerung, Logik für begrenzte vs. unbegrenzte große Tabellen

• test_validator_adversarial.py — Adversarial-Suite mit 29 Fällen, 100 % Ablehnung

• test_service_ask.py / test_service_adversarial.py — End-to-End-ask(), einschließlich des adversarialen Beweises über die gesamte Pipeline

• test_eval_runner.py — die Eval-Harness selbst (Struktur, Genauigkeitsaufschlüsselungen, Umgang mit Mehrdeutigkeiten)

• test_query_log.py — Protokollierung + der an Betreiber gerichtete Bericht zur Ablehnungsrate, einschließlich eines echten ask()-Integrationstests

• test_mcp_server.py — End-to-End über eine echte MCP-ClientSession

Konfiguration

Kopieren Sie .env.example zu .env und füllen Sie aus, was Sie haben – alles hat eine funktionierende Standardeinstellung:

cp .env.example .env

Risiken / Offene Fragen / Umfangsreduzierungen

Ehrliche Auflistung dessen, was nicht aufgenommen wurde, gemäß §13 der Spezifikation und dem Auftrag zur technischen Urteilsfähigkeit dieses Portfolios:

• Postgres statt SQLite, gemäß dem Wortlaut der Spezifikation. In dieser Umgebung ist kein Postgres-Server oder Docker-Daemon verfügbar. Dokumentierter Ersatz + Upgrade-Pfad oben; der AST-Validator und das Design der Ausführungsschicht wurden bewusst dialektunabhängig gehalten, sodass dies später keine Neufassung erfordert.

• Synthetischer Datensatz, kein Abruf eines Live-Open-Data-Portals. Bewusster Kompromiss für Offline-Reproduzierbarkeit und um die Lizenzfrage zu umgehen, die die Spezifikation selbst als Risiko nennt – siehe den entsprechenden Abschnitt oben.

• Das regelbasierte Backend ist eine Fixture-Nachschlagetabelle, kein allgemeines Modell. Das ist explizit und beabsichtigt gemäß den Umgebungsbeschränkungen dieses Portfolios (hier sind keine LLM-API-Schlüssel konfiguriert) – die echten Anthropic/OpenAI-Backends existieren, sind vollständig implementiert und teilen denselben Validierungs-/Ausführungspfad; sie wurden in dieser Umgebung nur nie gegen einen echten API-Schlüssel ausgeführt, daher wird keine Genauigkeitszahl für Live-Erzeugung beansprucht.

• Die Prüfung der Spaltenexistenz ist Best-Effort, nicht vollständig. Sie überspringt bewusst mehrdeutige, nicht qualifizierte Spaltenreferenzen bei Multi-Table-Joins, anstatt das Risiko falsch-positiver Ablehnungen einzugehen – dokumentiert im Docstring von _find_unknown_column. Die Prüfung der Tabellenexistenz (die wertvollere Absicherung gegen erfundene Tabellen) ist nicht ähnlich abgesichert.

• Kein Caching von Abfrageergebnissen / kein Verbindungspooling. Jeder ask()-Aufruf öffnet eine neue schreibgeschützte SQLite-Verbindung. In dieser Größenordnung in Ordnung (einzelne Demo-DB-Datei); vor einem Produktionseinsatz mit hoher QPS-Rate wäre Aufmerksamkeit erforderlich.

• Die Erkennung von Mehrdeutigkeiten hängt davon ab, dass das LLM-Backend der AMBIGUOUS:-Konvention folgt. Das regelbasierte Backend implementiert sie für seine eine bewusst mehrdeutige Fixture-Frage; ein echter Anthropic/OpenAI-Aufruf wird über den gemeinsamen System-Prompt (llm/prompt.py) angewiesen, derselben Konvention zu folgen, aber dies ist Kooperation auf Prompt-Ebene und wird nicht unabhängig vom Validator durchgesetzt (offene Mehrdeutigkeitserkennung ist nichts, das ein AST-Prüfer verifizieren kann).

• Die Heuristik für begrenzte Ergebnisse von MISSING_WHERE_LARGE_TABLE ist eine Verfeinerung über den Wortlaut der Spezifikation hinaus, nicht gerade eine Einschränkung, aber erwähnenswert als Ermessensentscheidung: Sie behandelt GROUP BY, LIMIT und reine Aggregat-Projektionen als von der fehlenden-WHERE-Prüfung ausgenommen. Siehe den Abschnitt Edge cases für die Begründung und die beiden Tests, die das Verhalten auf jeder Seite der Grenze festlegen.

Lizenz

MIT – siehe LICENSE.