text-to-sql-mcp

실제이면서 단순하지 않은 다중 테이블 스키마를 대상으로 자연어 질문을 SQL로 변환하는 MCP 서버입니다. 그리고 반환된 SQL을 절대 신뢰하지 않습니다. 생성된 모든 쿼리는 실제 AST로 파싱되고(sqlglot 사용) 실행 전에 검증기를 통과합니다. SELECT가 아닌 문장, 다중 문장 주입, 위험한 함수, 환각된 테이블/컬럼, 그리고 대형 테이블에 대한 무제한 풀 스캔은 모두 LLM의 자체 판단에 의존하지 않고 구조적으로 거부됩니다.

모델의 출력은 제안이지 명령이 아닙니다. 실제로 실행될 내용은 검증기가 결정합니다.

상태

스펙의 마일스톤 M1~M4가 구현되고 테스트되었습니다: 스키마 인트로스펙션, NL→SQL 생성(실제 Anthropic/OpenAI 백엔드 + 결정적 오프라인 폴백), AST 검증기, 라벨링된 평가 하네스, MCP 서버 래퍼. 의도적으로 연기된 사항은 위험 요소 / 미해결 질문을 참고하세요.

아키텍처

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)

위의 모든 것은 MCP 서버(mcp_server.py, 공식 mcp Python SDK의 FastMCP 기반)로 래핑되며, 스펙의 API 계약에 정확히 두 가지 도구를 노출합니다:

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

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

SQLite를 선택한 이유 (Postgres 대신)

스펙은 구체적으로 Postgres를 대상으로 합니다. 이 환경에는 실행 중인 Postgres 서버도 Docker 데몬도 없으므로 대상 데이터베이스로 SQLite를 사용합니다. 이는 실수가 아니라 의도적이고 문서화된 대체입니다. 인트로스펙션 계층(introspection.py)이 진정으로 SQLite에 특화된 유일한 부분입니다(information_schema 대신 sqlite_master + PRAGMA table_info 사용). 검증기, 실행 계층, MCP 래퍼는 파싱된 AST에서 동작하며 스키마를 생성한 데이터베이스가 무엇인지 알지 못하고 신경 쓰지 않습니다. Postgres 업그레이드 경로: db/connection.py의 sqlite3.connect(..., mode=ro)를 읽기 전용 역할로 연 psycopg 연결로 교체하고, introspection.py의 두 쿼리를 information_schema.tables/columns 기준으로 다시 작성하고, validate_sql()에 dialect="postgres"를 전달하면 됩니다. sqlglot은 두 방언을 모두 기본 지원하므로 AST 로직 자체는 변경되지 않습니다.

실제 오픈데이터 대신 합성 데이터셋을 사용한 이유

스펙은 실제 도시/정부 오픈데이터 포털을 제안합니다. 그 대신 db/seed.py는 고정된 시드로부터 전적으로 오프라인에서 결정적으로 생성되는, 현실적인 지방 자치 데이터셋을 생성합니다 — 실제 허가/검사/위반 스키마(NYC DOB, 시카고 건축 허가)를 모델로 한 12개 테이블. 이는 게으름이 아니라 의도적인 트레이드오프입니다. init-db를 네트워크 의존성 없이 재현 가능하게 유지하고(불안정한 CI, 속도 제한, 포털 다운타임 없음), 데모를 공개하기 전에 스펙 자체가 위험으로 지적한 라이선스 문제(§13)를 피할 수 있습니다. 스키마는 스펙이 정한 기준에서 진정으로 비자명합니다(non-trivial): 12개 테이블, 3단계 깊이의 외래 키(payments → violations → properties), 그리고 검증기의 스키마 접지(grounding) 로직을 실제로 시험하는 의도적인 컬럼명 모호성(status는 permits, licenses, violations, complaints에 나타나고, type은 서로 다른 네 개 테이블에 나타남)이 포함됩니다.

설치

pip install -e .

Python 3.10+가 필요합니다. 실제 LLM 백엔드용 선택적 패키지(이미 해당 패키지가 있는 개발 환경에는 설치되어 있으며, 없는 경우에만 필요):

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

빠른 시작 — 실제 SQLite 데이터베이스에 대한 실제 데모 실행

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

조인(join)이 많은 질문:

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

모호한 질문 — 하나의 추측으로 조용히 해결되지 않도록 의도적으로 설계되었습니다 (엣지 케이스 참고):

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

파괴적인 SQL을 차단하는 것은 모델이 아니라 검증기라는 증거 — 이 데모는 손상되었거나 프롬프트 주입된 모델을 대신하는 픽스처 클라이언트를 사용하며, 그 모델은 파괴적인 요청에 항상 응합니다:

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.

이제 운영자가 보는 것을 확인하세요 — 정확도와 별도로 보고되는 거부율입니다 (아래 참고):

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

(그 0.6은 달성해야 할 목표 수치가 아니라, 이 정확한 실행에서 이 세션에 제기된 질문의 실제 구성이 만들어낸 값입니다. init-db를 다시 실행하고 위 명령을 반복하면 정확히 재현됩니다. 시드 데이터와 규칙 기반 백엔드가 모두 결정적이기 때문입니다.)

라벨링된 평가 세트에 대한 정확도

text-to-sql-mcp eval

실제 출력, 규칙 기반 백엔드, 이 시드 기준 (25개 질문: 쉬움 8개 / 중간 10개 / 어려움 7개, 스펙의 20~30개 질문 요구사항을 충족):

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

이 80%는 우연도 아니고, 액면 그대로 받아들일 주장도 아닙니다 — 이는 의도적인 설계 선택으로 만들어진 결과입니다: 규칙 기반 백엔드는 25개 질문 중 20개를 인식하고, 추측 대신 나머지 5개에 대해서는 예외를 발생시킵니다 (llm/rule_based.py의 _UNANSWERED_IDS 참고). 평가 하네스는 모든 질문을 실제 ask() 파이프라인으로 실행하고, 실제 반환된 행을 동일한 데이터베이스에 대해 새로 실행된 골드 쿼리와 비교합니다. 이는 시드 데이터에서 조용히 달라질 수 있는 수동으로 유지되는 기대값이 아닙니다. 정확도는 난이도에 따라 감소하며(100% → 80% → 57%), 조인이 많은 질문에서는 극적으로 낮아집니다(단순 질문의 100% 대비 37.5%). 이는 순전히 규칙 기반 백엔드가 조회 테이블이기 때문이지, 하네스나 검증기가 다른 일을 하기 때문이 아닙니다. 이는 스펙의 승인 기준이 요구하는 정직한 신호입니다('정확도는 단순히 주장되는 것이 아니라 측정되고 보고된다').

실제 ANTHROPIC_API_KEY가 구성된 경우, ask()/eval은 대신 AnthropicLLMClient를 통해 라우팅되며(실제 API 키가 필요한 항목 vs. 오늘 독립적으로 작동하는 항목 참고), 정확도는 픽스처 커버리지가 아니라 실제 개방형 NL→SQL 품질을 반영합니다. 이 환경에는 API 키가 구성되어 있지 않아 실행되지 않았으며, README는 이에 대한 수치를 주장하지 않습니다.

적대적 검증 — 100% 거부, 두 가지 방식으로 테스트됨

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

• tests/test_validator_adversarial.py — 의도적으로 악성/비정상적인 SQL 문자열 29개(DROP, DELETE, UPDATE, INSERT, CREATE TABLE AS SELECT, ALTER, PRAGMA, ATTACH DATABASE, GRANT, VACUUM/REINDEX, ;를 통한 스택드 문장 주입, load_extension/readfile/writefile, SELECT ... INTO, 빈/쓰레기 입력)를 validate_sql()에 직접 넣었습니다 — 29/29 모두 거부되었고, 주석으로 숨겨진 두 번째 문장이 정확히 어떻게 처리되는지를 고정하는 전용 테스트 2개가 추가되었습니다(파일 내 총 33개 테스트 함수).

• tests/test_service_adversarial.py — ask() 수준에서 동일한 보장을 제공합니다. 적대적 자연어 프롬프트를 거부하는 대신 항상 응하는 픽스처 LLM 클라이언트를 사용하여, 실행을 차단하는 것이 검증기임을 증명합니다('모델이 거부하기를 바라는 것이 아니라' — 이는 스펙 자체가 이 승인 기준에 대해 사용한 표현입니다). 픽스처 모델이 절대 거부하지 않더라도 8/8 적대적 프롬프트가 여전히 거부됩니다.

검증기의 _ALLOWED_ROOT_TYPES는 위험한 키워드의 블록리스트가 아니라 허용 목록(allow-list) 입니다 (Select/Union/Intersect/Except). sqlglot이 인식하는 모든 DML/DDL/관리 문장은 구조상 허용 목록에 없는 별개의 AST 노드 유형으로 파싱되므로, 동기화해야 할 키워드 목록이 없으며 파괴적인 문장을 통과하도록 이름을 바꾸거나 위장할 방법이 없습니다.

실제 API 키가 필요한 항목 vs. 오늘 독립적으로 작동하는 항목

llm/factory.py는 백엔드를 자동으로 선택합니다: ANTHROPIC_API_KEY가 설정되어 있으면 Anthropic, 그렇지 않으면 OPENAI_API_KEY가 설정되어 있으면 OpenAI, 그 외에는 규칙 기반 폴백을 사용합니다. 전환을 위한 코드 변경은 필요 없습니다. AST 검증기의 동작은 어떤 백엔드가 SQL을 생성했는지와 관계없이 동일합니다 — 이것이 아키텍처의 실제 요점입니다(모델의 출력은 제안일 뿐 결코 신뢰되지 않습니다). 그리고 이것이 적대적 테스트 스위트와 일반 검증기 테스트가 안전성 속성을 증명하기 위해 LLM 백엔드를 전혀 필요로 하지 않는 이유입니다.

처리된 엣지 케이스

• 모호한 자연어 질문 (§9): 하나의 해석을 조용히 선택하는 대신, 프롬프트는 LLM이 SQL 대신 AMBIGUOUS: <clarifying question>로 응답하도록 지시합니다. service.ask()는 이를 감지하고 추측을 실행하지 않고, 명확화 질문을 사유로 포함한 rejected: true를 반환합니다. test_ask_handles_ambiguous_question_without_silently_guessing 참고.

• 조인이 많은 질문은 별도로 추적 (§9): EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — 위의 실제 100% vs 37.5% 분포 참고.

• SELECT로 위장한 프롬프트 주입 (§9): 허용 목록 기반 루트 유형 검사는 프롬프트가 어떻게 요청하든 DROP/DELETE 등이 통과할 수 없음을 의미합니다. 위의 적대적 테스트 스위트 참고.

• 매우 큰 테이블 풀 스캔 (§9): _find_unfiltered_large_table_scan은 WHERE가 없고 행 수 임계값(기본 500) 이상인 테이블에 대한 SELECT 중, 결과가 다른 방식으로 제한되지 않는(GROUP BY 없음, LIMIT 없음, 순수 집계가 아님) 경우를 플래그합니다. 마지막 조건은 스펙의 문자 그대로의 표현을 넘어선 의도적인 개선입니다. 이것이 없으면 SELECT COUNT(*) FROM permits 같은 일반적인 보고 쿼리가 정말 비싼 SELECT * FROM permits와 함께 거부되어, 검증기가 실제 보고에 사용할 수 없게 됩니다. test_pure_aggregate_on_large_table_passes_without_where vs. test_unfiltered_select_star_on_large_table_is_rejected 참고.

• 스키마 불일치 (환각된 테이블/컬럼 이름): 하드코딩된 목록에 대한 문자열 매칭이 아니라, 인트로스펙션된 스키마에 대해 구조적으로 검사합니다 — test_unknown_table_is_rejected, test_unknown_column_on_known_table_is_rejected. 컬럼 존재 확인은 의도적으로 보수적입니다(여러 조인된 테이블에 걸친 모호한 비한정 참조는 건너뜀). 합법적인 쿼리에 대한 오탐(false-positive) 거부를 피하기 위해서입니다 — _find_unknown_column의 docstring 참고.

MCP 서버

text-to-sql-mcp serve

stdio를 통해 서버를 실행합니다. 어떤 MCP 클라이언트든 이를 가리키게 하십시오(예: Claude Desktop의 구성에 추가하거나 mcp Python SDK의 ClientSession으로 구동). tests/test_mcp_server.py에서 mcp.shared.memory.create_connected_server_and_client_session을 통해 엔드투엔드로 테스트되었습니다. 이는 실제 ClientSession이 메모리 내 전송을 통해 실제 FastMCP 서버와 통신하면서 list_tools() 및 call_tool(...)을 외부 MCP 클라이언트와 동일하게 호출하는 것입니다. 기본 Python 함수를 직접 호출하는 것이 아닙니다.

테스트

pytest

91개 테스트, 모두 통과. 세부 내역:

• test_introspection.py — 스키마 인트로스펙션 정확성(테이블, 열, 행 수, 대형 테이블 임계값)

• test_rule_based_llm.py — 결정론적 백엔드 커버리지(의도적 공백 포함)

• test_execution.py — 읽기 전용 강제(심층 방어), 행 수 제한 잘라내기

• test_validator_general.py — 유효한 쿼리 통과, 스키마 근거, 제한/비제한 대형 테이블 로직

• test_validator_adversarial.py — 29개 사례의 적대적 테스트 스위트, 100% 거부

• test_service_ask.py / test_service_adversarial.py — 엔드투엔드 ask(), 전체 파이프라인 적대적 증명 포함

• test_eval_runner.py — eval 하네스 자체(구조, 정확도 분석, 모호성 처리)

• test_query_log.py — 로깅 및 운영자 대상 거부율 보고서, 실제 ask() 통합 테스트 포함

• test_mcp_server.py — 실제 MCP ClientSession을 통한 엔드투엔드

구성

.env.example을 .env로 복사하고 보유한 값을 채우세요 — 모든 항목에는 작동하는 기본값이 있습니다:

cp .env.example .env

위험 / 미해결 질문 / 범위 축소

명세의 §13 및 이 포트폴리오의 엔지니어링 판단 지침에 따라 포함되지 못한 항목을 솔직하게 정리합니다:

• 명세의 문자 그대로의 표현에 따르면 Postgres이지 SQLite가 아닙니다. 이 환경에서는 Postgres 서버나 Docker 데몬을 사용할 수 없습니다. 위에 문서화된 대체 및 업그레이드 경로가 있습니다. AST 검증기와 실행 계층 설계는 의도적으로 방언에 구애받지 않게 유지되어 나중에 다시 작성할 필요가 없습니다.

• 라이브 오픈데이터 포털 수집이 아닌 합성 데이터셋입니다. 오프라인 재현성과 명세에서 위험으로 지적하는 라이선스 문제를 피하기 위한 의도적 절충입니다 — 위의 전용 섹션을 참조하세요.

• 규칙 기반 백엔드는 일반 모델이 아닌 픽스처 조회 테이블입니다. 이는 이 포트폴리오의 환경 제약(여기에는 LLM API 키가 구성되어 있지 않음)에 따른 명시적 설계입니다. 실제 Anthropic/OpenAI 백엔드는 존재하며 완전히 구현되어 있고 동일한 검증/실행 경로를 공유합니다. 다만 이 환경에서는 실제 API 키로 실행된 적이 없으므로 라이브 생성 정확도 수치는 주장되지 않습니다.

• 열 존재 확인은 최선의 노력(best-effort) 방식이며 완전하지 않습니다. 다중 테이블 조인에서 모호한 비한정 열 참조를 의도적으로 건너뛰어 잘못된 긍정 거부(false-positive)의 위험을 피합니다 — 이는 _find_unknown_column의 docstring에 문서화되어 있습니다. 테이블 존재 확인(환각된 테이블에 대한 더 가치 있는 방어)은 유사한 제한을 두지 않습니다.

• 쿼리 결과 캐싱 / 연결 풀링이 없습니다. 각 ask()는 새 읽기 전용 SQLite 연결을 엽니다. 이 규모(단일 파일 데모 DB)에서는 문제없지만, 높은 QPS의 프로덕션 사용 전에는 주의가 필요합니다.

• 모호성 감지는 LLM 백엔드가 AMBIGUOUS: 규칙을 따르는 데 의존합니다. 규칙 기반 백엔드는 의도적으로 모호하게 만든 하나의 픽스처 질문에 대해 이를 구현합니다. 실제 Anthropic/OpenAI 호출은 공유 시스템 프롬프트(llm/prompt.py)를 통해 동일한 규칙을 따르도록 지시됩니다. 그러나 이는 프롬프트 수준의 협력일 뿐 검증기가 독립적으로 강제하지 않습니다(개방형 모호성 감지는 AST 검사기가 검증할 수 있는 것이 아닙니다).

• MISSING_WHERE_LARGE_TABLE의 제한된 결과 휴리스틱은 명세의 문자 그대로를 뛰어넘는 개선입니다. 정확히 말하면 제한 사항은 아니지만, 판단이 필요한 사항으로 표시할 가치가 있습니다. 이는 GROUP BY, LIMIT, 순수 집계 프로젝션을 missing-WHERE 검사에서 제외합니다. 근거와 경계 양쪽의 동작을 고정하는 두 테스트는 경계 사례 섹션을 참조하세요.

라이선스

MIT — LICENSE를 참조하세요.