# MCP Database Query Server

An MCP (Model Context Protocol) server that lets a VS Code AI agent query databases through a structured, step-by-step workflow.

## Supported Databases

- **SQLite** — via Python's built-in `sqlite3` (always available)
- **PostgreSQL** — via `psycopg2` (optional — install `psycopg2-binary`)
- **MySQL** — via `mysql-connector-python` (optional)
- **Microsoft SQL Server** — via `pyodbc` (optional — requires ODBC drivers)

> **Note:** Only SQLite support is required to get started. The server loads database adapters lazily — missing optional drivers won't prevent the server from starting.

## Setup

### 1. Install dependencies

```bash
pip install -r requirements.txt
```

### 2. Configure connections

Edit `config.json` to add your database connections:

```json
{
  "connections": [
    {
      "name": "My Postgres",
      "type": "postgres",
      "host": "localhost",
      "port": 5432,
      "database": "mydb",
      "username": "user",
      "password": "pass"
    },
    {
      "name": "Local SQLite",
      "type": "sqlite",
      "database": "./data/local.db"
    }
  ]
}
```

### 3. (Optional) Seed the sample SQLite database

```bash
python seed_sample_db.py
```

This creates `data/sample.db` with sample customers, products, orders, and order items.

### 4. Register in VS Code

Add the server to your VS Code MCP settings (`.vscode/mcp.json` in the workspace root):

```json
{
  "servers": {
    "database-query": {
      "command": "python",
      "args": ["server.py"],
      "cwd": "${workspaceFolder}"
    }
  }
}
```

> **Important:** `${workspaceFolder}` must resolve to the directory containing `server.py`. If this project is inside a subfolder of your workspace, adjust to `"${workspaceFolder}/MCP Server"`.

### 5. Start the MCP Server in VS Code

The server needs to be started by VS Code before the tools become available:

1. Open the **Command Palette** (`Ctrl+Shift+P`)
2. Run **"MCP: List Servers"** — you should see `database-query`
3. If it shows as stopped, select it and choose **Start**

Alternatively, run **"MCP: Restart Server"** and pick `database-query`.

Once the server is running, the prompt and tools (`get_connections`, `list_tables`, `get_table_schema`, `query_table`) will be available to the AI agent.

## MCP Tools

The server exposes four tools, designed to be used in order:

| # | Tool | Purpose |
|---|------|---------|
| 1 | `get_connections` | List available database connections (no passwords exposed) |
| 2 | `list_tables` | List all user tables in a database |
| 3 | `get_table_schema` | Get column details for a specific table |
| 4 | `query_table` | Execute a read-only SELECT query |

## Security

- Database passwords are never exposed in tool responses.
- Only `SELECT` / `WITH` / `EXPLAIN` statements are allowed — all write and DDL operations are rejected.
- Query results are capped at 500 rows.

## Project Structure

```
MCP Server/
├── config.json            # Database connection configuration
├── prompt.md              # Design / prompt spec
├── seed_sample_db.py      # Creates sample SQLite database
├── server.py              # Main MCP server entry point
├── requirements.txt
├── README.md
├── data/
│   └── sample.db          # Sample SQLite database (created by seed script)
├── db/
│   ├── __init__.py
│   ├── connection.py      # Connection factory (lazy-loads adapters)
│   ├── postgres.py        # PostgreSQL adapter
│   ├── mysql.py           # MySQL adapter
│   ├── sqlite_adapter.py  # SQLite adapter
│   └── mssql.py           # MSSQL adapter
└── tools/
    ├── __init__.py
    ├── get_connections.py
    ├── list_tables.py
    ├── get_table_schema.py
    └── query_table.py
```