# Universal MCP

A database gateway that exposes a relational database to LLM clients through two paths: a set of MCP tools for direct, deterministic access, and a REST endpoint that translates natural language into validated SQL with conversation memory. Client-supplied SQL on either path goes through the same validator, so no query reaches the database without passing the same check.

Built on FastMCP and FastAPI, with GPT-4o for text-to-SQL and MySQL as the backing store.

---

## Two access paths, and why

An MCP client and a person asking a question need different things from the same database, and collapsing them into one interface makes both worse.

**MCP tools — deterministic access.** An MCP client already knows what it wants: list the tables, describe one, page through rows, run a query. These are exposed as typed tools with no model in the loop, so the result is reproducible and cheap.

**REST endpoint — natural language.** A person asking *"who are the five highest-paid engineers?"* has no schema in hand. That path sends the schema to the model as context, gets SQL back, validates it, and executes it — with session history so follow-up questions resolve against what was just asked.

---

## Natural language pipeline

1. **Schema as context** — the database schema is serialized to YAML and cached, then supplied to the model so it generates SQL against real table and column names.
2. **Generation** — GPT-4o receives the schema, the session's prior turns, and the question at `temperature=0`, constrained by prompt to emit a single raw `SELECT`.
3. **Validation** — the generated SQL is parsed with `sqlparse` and rejected unless it is exactly one statement of type `SELECT`. Prompt rules are not treated as a security boundary; this check is.
4. **Execution** — the validated query runs through the async SQLAlchemy engine with bound parameters.
5. **Memory** — the question, the SQL, and a summary of the result set are appended to a per-session JSON file, so context carries forward without replaying full result sets into the prompt.

---

## MCP tools

| Tool | Description |
|------|-------------|
| `run_execute_query` | Execute a client-supplied query; rejected unless it parses to a single `SELECT` |
| `run_get_schema` | List every table in the database |
| `run_list_tables` | Table listing for client discovery |
| `run_describe_table` | Column names, types, keys and nullability for one table |
| `run_get_table_data` | Paginated row access via `page` / `page_size` |
| `run_validate_query` | Check a query against the read-only policy before running it |

## REST API

| Endpoint | Method | Description |
|----------|--------|-------------|
| `/api/v1/query/natural-language` | `POST` | Takes `question` and `session_id`; returns the generated SQL and result rows |
| `/health` | `GET` | Liveness check that issues a real query against the database |
| `/` | `GET` | Service banner |

An interactive terminal client (`scripts/chat.py`) drives the NLQ endpoint, rendering generated SQL with syntax highlighting and results as a formatted table.

---

## Security

- **Read-only by construction.** Every query originating outside the server — whether typed by an MCP client or generated by the model — is parsed and must resolve to a single `SELECT` statement. `INSERT`, `UPDATE`, `DELETE` and `DROP` never pass validation. Internal introspection queries (`SHOW TABLES`, `DESCRIBE`) are server-constructed and bypass the check by design.
- **No statement chaining.** Multi-statement input is rejected at parse time, closing off `SELECT ...; DROP TABLE ...` style payloads.
- **Bound parameters.** Pagination and other server-constructed queries pass values as bound parameters rather than string interpolation.
- **Fail closed.** A parse error, an empty query, or an LLM error resolves to rejection, not to execution.

---

## Project layout

```
api/          FastAPI app — NLQ route, health check
core/
  mcp/        FastMCP server and tool definitions
  database/   Async engine, connection manager, query executor
  schema/     Schema-to-YAML generation
  security/   SQL validation
services/     LLM, schema, and query orchestration
storage/      File-backed session store for conversation history
scripts/      Database setup, interactive chat client
tests/        Unit and integration tests
```

---

## Quick start

**Prerequisites:** Python 3.11+, a running MySQL server, an OpenAI API key.

```bash
git clone https://github.com/nandeshkanagaraju/mcp-server-bridge.git
cd mcp-server-bridge

python -m venv venv && source venv/bin/activate
pip install -r requirements.txt

cp .env.example .env    # add OPENAI_API_KEY and your MySQL credentials
```

Create the database and user, then seed it with sample tables and generated data:

```bash
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS company_db;"
python -m scripts.setup_database
```

Run the REST API, and the chat client in a second terminal:

```bash
uvicorn api.main:app --reload --port 8000
python scripts/chat.py
```

The MCP server runs separately, over stdio, for MCP-capable clients:

```bash
python -m core.mcp.server
```

---

## Configuration

All settings load from `.env` via `pydantic-settings`.

| Variable | Purpose | Default |
|----------|---------|---------|
| `OPENAI_API_KEY` | Text-to-SQL generation | *required* |
| `DB_HOST` / `DB_PORT` / `DB_NAME` | MySQL target | `localhost` / `3306` / `company_db` |
| `DB_USER` / `DB_PASSWORD` | MySQL credentials | — |
| `QUERY_TIMEOUT` | Query timeout in seconds | `30` |
| `MAX_QUERY_LENGTH` | Maximum accepted query length | `10000` |
| `LOG_LEVEL` | Log verbosity | `INFO` |

Logs stream to the console and to `mcp_server.log`.

---

## Technology stack

| Layer | Technology |
|-------|-----------|
| MCP server | FastMCP, MCP Python SDK |
| REST API | FastAPI, Uvicorn |
| Language model | OpenAI GPT-4o |
| Database access | SQLAlchemy (async), aiomysql, PyMySQL |
| Validation | `sqlparse` |
| Configuration | `pydantic-settings` |
| Terminal client | `rich`, `httpx` |
| Sample data | Faker |
| Testing | pytest, `pytest-asyncio` |

---

## Testing

```bash
pytest tests/unit/         # SQL validation rules
pytest tests/integration/  # NLQ endpoint, mocked LLM
```

---

## Design decisions

- **Validation sits outside the prompt.** The model is asked for read-only SQL, but a parser decides what actually executes. A prompt is guidance; a parse tree is enforcement.
- **One validator for both paths.** The MCP tool and the NLQ endpoint route client-supplied SQL through the same parser check, so the read-only rule cannot drift between interfaces. They still execute over separate connection stacks — consolidating those is open work.
- **Summarized history, not stored result sets.** Conversation memory keeps the question, the SQL, and a row-count summary — enough for follow-ups without growing the prompt with data.
- **Schema cached, not re-read per request.** Schema changes rarely; regenerating YAML on every question would add latency for nothing.
- **Sessions on disk.** A file-backed store keeps history across restarts with no extra infrastructure at development scale.

---

## Limitations and next steps

- **MySQL only.** The adapter package is scaffolded for PostgreSQL and SQLite, but only MySQL is implemented.
- **Schema source is a fixture.** `services/schema_service.py` returns a hardcoded schema; live introspection against the connected database is the next step.
- **MCP tools use their own connection path.** They connect directly rather than through the shared async engine, and should be consolidated onto it.
- **Table names are interpolated, not bound.** `describe_table` and the pagination query build SQL by f-string on `table_name`, which is client-supplied. An identifier allowlist check is needed.
- **Test coverage is narrow.** Validation and the NLQ endpoint are covered; the tool layer and adapters are not.
- **No rate limiting or auth on the REST API.** Both are scaffolded and unimplemented.

---

Nandesh Kanagaraju — github.com/nandeshkanagaraju