# SQL MCP Server Demo

This repository implements a local Model Context Protocol (MCP) server for SQL Server using Microsoft Data API Builder (DAB) 2.0. It exposes read-only data access tools (`describe_entities`, `read_records`, and `aggregate_records`) to a local Ollama model (`qwen3:8b`) via a Python MCP client and Streamlit frontend.

## Repository Structure

```text
A:\sql-mcp-demo
│   .env.example         - Template environment variables
│   .gitignore           - Configured git ignore rules
│   README.md            - Project documentation
│   requirements.txt     - Python dependencies
│   mcp_client.py        - Python MCP Client and LLM function calling loop
│   app.py               - Streamlit frontend UI
│   test_ollama.py       - Verifies basic connection to Ollama
│   test_tools.py        - Verifies tool-calling capability of qwen3:8b
│
├── .venv/               - Python virtual environment
│
├── dab/
│   └── dab-config.json  - Schema-valid Data API Builder configuration
│
├── database/
│   └── verify.sql       - Reference SQL database queries
│
└── scratch/             - Developer tests and verification scripts
```

## Architecture

```text
               ┌──────────────────────┐
               │    Streamlit UI      │
               │       app.py         │
               └──────────┬───────────┘
                          │ User Query
                          ▼
               ┌──────────────────────┐
               │      Qwen3:8B        │
               │       Ollama         │
               └──────────▲───────────┘
                          │ Function Call
                          │ Negotiation
                          ▼
               ┌──────────────────────┐
               │    MCP Client        │
               │   mcp_client.py      │
               └──────────┬───────────┘
                          │
                          │ MCP HTTP (Streamable HTTP)
                          ▼
               ┌──────────────────────┐
               │ Data API Builder     │
               │    SQL MCP Server    │
               │ localhost:5000/mcp   │
               └──────────┬───────────┘
                          │
                          │ SQL Connection
                          ▼
               ┌──────────────────────┐
               │ SQL Server LocalDB   │
               │      EmployeeDB      │
               │ └── dbo.Employee     │
               └──────────────────────┘
```

---

## Running the Project

Follow these steps to start and run the application:

### Step 1: Start Services
Ensure your local SQL Server instance and Ollama are running:
```powershell
sqllocaldb start MSSQLLocalDB
ollama run qwen3:8b
```

### Step 2: Start the DAB MCP Server
Open a terminal, activate your virtual environment, and run the DAB server:
```powershell
# Navigate to workspace
cd A:\sql-mcp-demo

# Activate environment
.venv\Scripts\activate

# Start DAB MCP Server
C:\Users\kamal\.dotnet\tools\dab.exe start --config .\dab\dab-config.json
```
The server will start listening at `http://localhost:5000/mcp`.

### Step 3: Run the Streamlit Application
Open a second terminal window and launch the frontend dashboard:
```powershell
# Navigate to workspace
cd A:\sql-mcp-demo

# Activate environment
.venv\Scripts\activate

# Launch Streamlit
streamlit run app.py
```
This will open the dashboard in your web browser at `http://localhost:8501`.

---

## Verification & Tests

### Dynamic Schema & Client test
To test the tool negotiation loop directly via the command-line client:
```powershell
python mcp_client.py
```

### Verification Scripts
*   [test_ollama.py](file:///a:/sql-mcp-demo/test_ollama.py): Verifies connection to Ollama.
*   [test_tools.py](file:///a:/sql-mcp-demo/test_tools.py): Verifies basic tool selection capability.
*   [scratch/test_all_queries.py](file:///a:/sql-mcp-demo/scratch/test_all_queries.py): Runs a batch of 7 validation queries testing counts, filters, averages, group-bys, and sort conditions.

---

## Schema Validation Bug Fixed

A critical schema validation issue in Microsoft's Data API Builder v2.0.9 was addressed:
- The standard DAB schema validator contains a bug where defining `"mcp"` at the entity level for `table` types incorrectly triggers a stored-procedure constraint (requiring `source.type` to be `stored-procedure`).
- This was resolved by removing the redundant entity-level `"mcp"` block since MCP capability is already configured globally at the runtime level.