Query-MCP

# 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 ```bash # 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: ```bash export DATABASE_PATH=/data/querymcp.db export LOG_LEVEL=DEBUG export SESSION_TIMEOUT=900 python server.py ``` ## 5 Using the API ### 5.1 Authenticating ```python 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) ```python # 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 ```python health = await client.call_tool("health_check", {}) # → {"server": "healthy", "database": "healthy", "uptime": "..."} ``` ## 6 Running Tests ```bash # 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).