MCP SQL Server — Python (FastAPI + SQLite + SSE)

This project implements a Model Context Protocol (MCP)-style server in Python using FastAPI, SQLite, and Server-Sent Events (SSE) for incremental (streamed) database responses.

It connects to a local SQLite database that stores patient and scan data, and exposes HTTP endpoints compatible with LLM tools such as GitHub Copilot or Cursor via JSON-RPC and HTTP APIs.

---

Features

=>Local SQLite database (data/database.db) with:

• patients table (demographics + calcium risk) -scans table (per-patient calcium scan results)

=>FastAPI backend with modular structure

=>Streaming responses using Server-Sent Events (SSE) at /mcp/stream

=>MCP-compatible tools:

• health
• list_tables
• describe_table
• run_sql

=>Structured validation & error handling

=>One-command start using uvicorn

---

Project Structure

mcp-sql-server-python/
├── app/
│ ├── main.py # FastAPI entry point
│ ├── mcp.py # MCP core logic
│ ├── tools.py # Tool definitions
│ ├── db.py # SQLite connection helpers
│ └── validators.py # Input validation
│
├── scripts/
│ └── seed.py # Creates & populates database
│
├── data/
│ └── database.db # Auto-created SQLite DB
│
├── .vscode/ # Editor configs for Copilot/Cursor
│
├── requirements.txt # Python dependencies
└── README.md # You are here

---

Setup (Windows PowerShell)

1.Create and activate a virtual environment py -3 -m venv .venv ..venv\Scripts\Activate.ps1

2.Install dependencies pip install -r requirements.txt

3.Seed the database python .\scripts\seed.py

4.Run the MCP server uvicorn app.main:app --reload --port 7071

5.Open another PowerShell for testing

---

Testing the Server (PowerShell Commands)

1.Check if the server is running Invoke-RestMethod http://127.0.0.1:7071/

2.Get available tools Invoke-RestMethod http://127.0.0.1:7071/mcp | ConvertTo-Json -Depth 9

3.Health check Invoke-RestMethod -Uri http://127.0.0.1:7071/mcp/call -Method POST -Headers @{ "Content-Type"="application/json" } -Body '{ "tool":"health" }' | ConvertTo-Json -Depth 2

4.List tables Invoke-RestMethod -Uri http://127.0.0.1:7071/mcp/call -Method POST -Headers @{ "Content-Type"="application/json" } -Body '{ "tool":"list_tables" }' | ConvertTo-Json -Depth 2

5.Describe the patients table Invoke-RestMethod -Uri http://127.0.0.1:7071/mcp/call -Method POST -Headers @{ "Content-Type"="application/json" } -Body '{ "tool":"describe_table", "params": { "table": "patients" } }' | ConvertTo-Json -Depth 2

6.Run a SQL query Invoke-RestMethod -Uri http://127.0.0.1:7071/mcp/call -Method POST -Headers @{ "Content-Type"="application/json" } -Body '{ "tool":"run_sql", "params": { "sql":"select id,name,sex,calcium_score from patients order by calcium_score desc limit 5" } }' | ConvertTo-Json -Depth 2

7.Test streaming results (SSE) @' {"tool":"run_sql","params":{"sql":"select p.name, s.scan_date, s.agatston_total from patients p join scans s on s.patient_id = p.id order by s.agatston_total desc limit 10"}} '@ | curl.exe --no-buffer --silent --request POST "http://127.0.0.1:7071/mcp/stream" ` -H "Content-Type: application/json" --data-binary "@-"

---

Testing with Copilot Chat

1.Ensure your config points to 7071 in ".vscode/mcp.json" file

2.Run these in Copilot Chat one by one

(2.1) List tables Use the local-sql tool to call "list_tables" and return the raw JSON only.

(2.2) Describe table Use the local-sql tool to call "describe_table" with {"table":"patients"} and return the raw JSON only.

(2.3) Run SQL Use the local-sql tool to call "run_sql" with {"sql":"select id,name,sex,calcium_score,risk_band from patients order by calcium_score desc limit 5"} and return the raw JSON only.

---

What I have Learned

This project taught me several key backend engineering concepts:

• How to design a modular FastAPI application with clean separation between routes, tools, and validation.

• SQLite integration using Python’s built-in sqlite3, and the importance of PRAGMA foreign_keys and WAL mode.

• Server-Sent Events (SSE) for real-time data streaming over HTTP.

• Structured error handling and input validation to prevent bad requests or unsafe SQL.

• Debugging PowerShell using Invoke-RestMethod effectively.

• How to handle environment setup, execution policies, and port conflicts on Windows.

Overall, the project strengthened my understanding of API architecture, streaming protocols, and AI-tool integration while giving me hands-on practice with real-world debugging and problem-solving.

---

Final Verification Checklist

• Database seeded successfully (patients, scans)

• /mcp returns tool registry

• /mcp/call works for all tools

• /mcp/stream streams incremental results

• JSON-RPC (POST /mcp) responds to initialize, tools/list, and tools/call

• Verified with PowerShell and GitHub Copilot integration

---