Query MCP

Query MCP is a lightweight custom MCP Server that exposes a relational SQLite database as a set of structured tools and resources. It ships with:

• FastMCP server wrapper (WebSocket + JSON-RPC).
• Hardened SQL layer with identifier sanitisation and automatic parameterisation.
• Role-based authentication, granular permissions, and per-tool consent for destructive actions.
• Health-check & schema resources for live observability.
• Comprehensive pytest suite for unit, integration, and end-to-end scenarios.

1 Quick Start

# 1. Clone
git clone https://github.com/chandraprvkvsh/Query-MCP.git
cd Query-MCP

# 2. Create virtual env (Python 3.9+ recommended)
python -m venv .venv
source .venv/bin/activate      # Windows: .venv\Scripts\activate

# 3. Install dependencies
pip install -r requirements.txt

# 4. Run server (default port 8000)
python server.py

When the server starts it will:

1. Open/initialise production.db (or DATABASE_PATH if set).
2. Create sample users and posts tables (unless you disable ENABLE_SAMPLE_DATA).
3. Expose a FastMCP endpoint on ws://localhost:8000/.

2 Features

Area	Highlights
Authentication	PBKDF2-SHA256 + 32-byte salt, session timeout, logout
Authorisation	Enum-based Permission model (READ, WRITE, CREATE, DELETE, ADMIN)
Per-tool consent	Second-factor safeguard for insert, update, delete, create_table, drop_table
SQL safety	Identifier whitelist, parameterised queries, DELETE/UPDATE require WHERE
Observability	health_check tool & db://health resource, structured logging (rotating file handler)
Extensibility	Add new tools by decorating async functions with @mcp.tool

3 Project Layout

Query-MCP/
│
├── auth.py              # AuthManager & permission mapping
├── database.py          # Async, thread-safe SQLite wrapper
├── server.py            # FastMCP server (tools, resources, bootstrap)
├── config.py            # Environment-driven settings
│
├── tests/               # Pytest suites (unit + integration + scenarios)
│   ├── test_auth_comprehensive.py
│   ├── test_mcp_server_integration.py
│   └── test_practical_mcp_scenarios.py
│
└── README.md            # You are here

4 Configuration

All knobs are exposed via environment variables (with sane defaults):

Variable	Default	Description
DATABASE_PATH	./production.db	SQLite file path
LOG_LEVEL	INFO	Python logging level
SESSION_TIMEOUT	3600	Seconds before a session expires
REQUIRE_HTTPS	true	Reject non-TLS origins if true
MAX_CONNECTIONS	100	Hard cap on simultaneous MCP clients
SERVER_NAME	Database MCP Server	Appears in MCP handshake
ENABLE_SAMPLE_DATA	true	Skip sample tables if set false

Example usage:

export DATABASE_PATH=/data/querymcp.db
export LOG_LEVEL=DEBUG
export SESSION_TIMEOUT=900
python server.py

5 Using the API

5.1 Authenticating

from fastmcp import Client
from mcp_schema import CallToolResult   # generated by FastMCP

client = Client("ws://localhost:8000")
result: CallToolResult = await client.call_tool("authenticate", {
    "username": "admin",
    "password": "admin123"
})
print(result.text)    # → “Authentication successful”

5.2 Creating a table (requires explicit user consent as a safety measure before modifying the database schema)

# 1-grant consent
await client.call_tool("grant_consent", {"tool_name": "create_table",
                                         "table": "projects"})

# 2-create
await client.call_tool("create_table", {
    "table_name": "projects",
    "schema_def": {
        "columns": {
            "id":   {"type": "INTEGER", "primary_key": True},
            "name": {"type": "TEXT",    "not_null": True},
            "pm":   {"type": "TEXT"}
        }
    }
})

5.3 Health monitoring

health = await client.call_tool("health_check", {})
# → {"server": "healthy", "database": "healthy", "uptime": "..."}

6 Running Tests

# inside venv
pytest -q

• Unit tests cover hashing, auth, permissions
• Integration tests spin up an in-process server and validate every MCP tool
• Scenario tests model real-world flows (inventory, user-management)

7 Security Notes

• All SQL statements are parameterised; table/column identifiers are strictly alphanumeric (plus _ / -).
• Destructive actions always require both appropriate permission and explicit user consent.
• Sessions auto-expire (SESSION_TIMEOUT) and can be terminated via the logout tool.
• Logs are persisted with rotation (mcp_server.log, 10 MiB × 5 files).