{
  "markdown": "# TokenLite MySQL MCP\n\n[![npm version](https://badge.fury.io/js/@andezdev%2Ftokenlite-mysql-mcp.svg?icon=si%3Anpm)](https://badge.fury.io/js/@andezdev%2Ftokenlite-mysql-mcp)\n\nA robust and secure MySQL database server implemented under Anthropic's **Model Context Protocol (MCP)**. \nDesigned specifically to solve the shortcomings of current generic MCP servers through **Graceful Degradation, Active Performance Protection, and Aggressive Token Optimization**.\n\n---\n\n## 🌟 Core Pillars\n\n1. **Safe-Query Optimizer (AST & EXPLAIN)**: Protects production databases by pre-analyzing queries. Blocks unindexed Full Table Scans that exceed configurable thresholds and injects strict `LIMIT` clauses automatically at the AST level.\n2. **Granular AST-Based Write Permissions**: By default, TokenLite is 100% Read-Only. You can surgically enable specific write operations (INSERT, UPDATE, DELETE, DDL) via environment variables. The firewall uses strict AST parsing to prevent SQL injection and comment-bypass attacks, and strictly prohibits privilege escalation commands (like `GRANT` or `CALL`).\n3. **Session-Level Defense in Depth**: If the server is configured in strict Read-Only mode (all write variables disabled), TokenLite injects `SET SESSION TRANSACTION READ ONLY` directly into the connection pool sockets. This guarantees that even if a theoretical bypass exists in the AST parser, the MySQL engine itself will physically reject any data modification.\n4. **Business Intelligence Injection**: Bridges the gap between raw data and company logic. Automatically attaches semantic dictionaries (`metadata.json`) to database schema exploration, and exposes Semantic Templates via the official **MCP Prompts API** (`templates.json`) so the LLM uses pre-approved analytical queries instead of hallucinating them.\n5. **Graph-Based Semantic Schema**: Avoids sending giant schemas to the LLM that saturate the context window. When a table is searched, the engine uses heuristics to deduce implicit relationships and packages the exact \"Auto-Join Context\".\n6. **CSV Token Compression**: Database results are efficiently transformed into tabular CSV markdown with unambiguous NULL representation (`∅`), saving up to 50% of Output Tokens compared to verbose JSON. When results hit the applied `LIMIT`, a `-- rows: N (truncated at LIMIT X)` footer is appended so the LLM does not assume a complete dataset.\n7. **MCP Completions**: Table names and template keywords support the official `completions/complete` capability for resource templates (`mysql://tables/{name}`) and the `query_templates` prompt.\n\n---\n\n## 📋 Requirements\n\n- Node.js v20 or higher\n- MySQL 5.7 or higher (MySQL 8.0+ recommended)\n- A MySQL user with `SELECT` and `SHOW VIEW` privileges.\n\n---\n\n## 🚀 Installation & Usage\n\nYou can use this MCP server with any compatible client. Below are the configurations for the most popular ones.\n\n### 1. Claude Desktop\n\nEdit your `claude_desktop_config.json` (usually located at `%APPDATA%\\Claude\\claude_desktop_config.json` on Windows or `~/Library/Application Support/Claude/claude_desktop_config.json` on macOS) and add the following:\n\n**Using NPX (Recommended)**\n```json\n{\n  \"mcpServers\": {\n    \"tokenlite-mysql\": {\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"@andezdev/tokenlite-mysql-mcp\"\n      ],\n      \"env\": {\n        \"DB_HOST\": \"localhost\",\n        \"DB_PORT\": \"3306\",\n        \"DB_USER\": \"your_db_user\",\n        \"DB_PASSWORD\": \"your_password\",\n        \"DB_NAME\": \"your_database\",\n        \"MCP_EXPLAIN_MAX_SCAN_ROWS\": \"1000\",\n        \"MCP_QUERY_ROW_LIMIT\": \"500\",\n        \"MCP_SAFE_QUERY_ENABLE_BLOCKING\": \"true\"\n      }\n    }\n  }\n}\n```\n\n### 2. Claude Code (CLI)\n\nYou can easily integrate this server globally into Claude Code:\n\n```bash\nclaude mcp add tokenlite_mysql \\\n  -e DB_HOST=\"127.0.0.1\" \\\n  -e DB_PORT=\"3306\" \\\n  -e DB_USER=\"root\" \\\n  -e DB_PASSWORD=\"your_password\" \\\n  -e DB_NAME=\"your_database\" \\\n  -- npx -y @andezdev/tokenlite-mysql-mcp\n```\n\n### 3. Cursor IDE\n\nTo use within Cursor IDE:\n1. Open Cursor Settings > Features > MCP.\n2. Click **+ Add New MCP Server**.\n3. Set the Type to `command`.\n4. Name it `tokenlite-mysql`.\n5. Set the command to:\n   ```bash\n   npx -y @andezdev/tokenlite-mysql-mcp\n   ```\n*(Note: Cursor handles environment variables directly in the IDE UI, make sure to add your DB credentials there).*\n\n---\n\n## ⚙️ Environment Variables Reference\n\n### Understanding Query Limits (two independent knobs)\n\n`execute_safe_query` applies **two separate limits**. Do not confuse them:\n\n| Variable | What it controls | Default | Example |\n|----------|------------------|---------|---------|\n| `MCP_EXPLAIN_MAX_SCAN_ROWS` | **Security gate**: blocks queries whose EXPLAIN plan shows a full table scan (`type: ALL`) with more estimated rows than this threshold. The query is rejected *before* execution. | `1000` | `5000` allows scanning tables up to ~5000 rows without an index |\n| `MCP_QUERY_ROW_LIMIT` | **Result cap**: max rows returned to the LLM. Injected as `LIMIT` at the AST level. When reached, CSV includes `-- rows: N (truncated at LIMIT X)`. | `500` | `1000` returns up to 1000 rows per SELECT |\n\n**Deprecated alias:** `MCP_SAFE_QUERY_MAX_ROWS` still works as a fallback for `MCP_EXPLAIN_MAX_SCAN_ROWS` only. It does **not** control the result `LIMIT`.\n\n**Worked example** with `MCP_EXPLAIN_MAX_SCAN_ROWS=5000` and `MCP_QUERY_ROW_LIMIT=500` on a `customers` table (~1502 rows):\n\n1. `SELECT * FROM customers` → passes EXPLAIN (1502 < 5000), executes with `LIMIT 500`, returns 500 rows + truncation footer.\n2. Same config but table has 8000 rows → blocked by EXPLAIN before execution.\n\n| Variable | Description | Default | Required |\n|----------|-------------|---------|----------|\n| `DB_HOST` | MySQL Host address | `localhost` | No |\n| `DB_PORT` | MySQL Port | `3306` | No |\n| `DB_USER` | MySQL Username | `root` | No |\n| `DB_PASSWORD` | MySQL Password | `''` | No |\n| `DB_NAME` | MySQL Database name | `test` | Yes |\n| `MCP_EXPLAIN_MAX_SCAN_ROWS` | EXPLAIN guardrail: max estimated rows for unindexed full table scans before blocking. | `1000` | No |\n| `MCP_QUERY_ROW_LIMIT` | Max rows returned per SELECT (AST-injected `LIMIT` + truncation footer). | `500` | No |\n| `MCP_SAFE_QUERY_ENABLE_BLOCKING`| Enable or disable the EXPLAIN guardrail. | `true` | No |\n| `MCP_METADATA_PATH` | Absolute path to your custom `metadata.json` dictionary. | (Disabled) | No |\n| `MCP_TEMPLATES_PATH` | Absolute path to your custom `templates.json` queries. | (Disabled) | No |\n| `TOOL_PREFIX` | Prefix for tool names (useful when running multiple instances). | Derived from `DB_NAME` (e.g., `mydb_`). Random fallback only if `DB_NAME` is unset. | No |\n| `MCP_RATE_LIMIT_RPM` | Max tool invocations per minute (sliding window). Set to `0` to disable. | `60` | No |\n| `MYSQL_QUERY_TIMEOUT` | Max execution time for a query (in ms). Aborts heavy queries to protect against DoS. | `15000` | No |\n| `MYSQL_CONNECTION_LIMIT` | Max concurrent pool connections. | `10` | No |\n| `MYSQL_CONNECT_TIMEOUT` | Max time to wait for a socket to establish (in ms). | `10000` | No |\n| `MYSQL_RETRY_ATTEMPTS` | Max retries on transient connection errors (`ECONNREFUSED`, `PROTOCOL_CONNECTION_LOST`, etc.). | `3` | No |\n| `MYSQL_RETRY_DELAY_MS` | Base delay (ms) for exponential backoff between retries (1s, 2s, 4s...). | `1000` | No |\n| `MYSQL_QUEUE_LIMIT` | Max queued requests when all pool connections are busy. Prevents unbounded growth if MySQL is down. | `50` | No |\n| `MCP_DDL_CACHE_TTL` | Time-to-live (in seconds) for cached DDL statements. Reduces latency on repeated `search_schema` calls. Invalidated by `refresh_schema`. | `60` | No |\n| `MCP_LOG_LEVEL` | Minimum severity for MCP log notifications: `debug`, `info`, `notice`, `warning`, `error`, `critical`, `alert`, `emergency`. | `info` | No |\n| `ALLOW_INSERT_OPERATION` | Enable `INSERT` and `REPLACE` queries. | `false` | No |\n| `ALLOW_UPDATE_OPERATION` | Enable `UPDATE` queries. | `false` | No |\n| `ALLOW_DELETE_OPERATION` | Enable `DELETE` and `TRUNCATE` queries. | `false` | No |\n| `ALLOW_DDL_OPERATION` | Enable Data Definition Language (`CREATE`, `ALTER`, `DROP`, `RENAME`). | `false` | No |\n| `DB_SSL` | Enable TLS for the MySQL connection (recommended for managed/cloud databases). | `false` | No |\n| `DB_SSL_REJECT_UNAUTHORIZED` | Reject self-signed or untrusted TLS certificates when `DB_SSL=true`. | `true` | No |\n\n---\n\n## 🛡️ Business Intelligence Features (Opt-in)\n\nTokenLite can teach the LLM about your company's business rules. To enable this, map the absolute paths of two JSON files via `.env` or your MCP client config:\n\n### `metadata.json` (Semantic Dictionary)\nTranslate integer statuses or internal jargon so the LLM understands the data.\n```json\n{\n  \"orders.status\": {\n    \"pending\": \"The order is waiting for payment validation\",\n    \"shipped\": \"The order has left the warehouse\"\n  }\n}\n```\n\n### Custom Relationships\nDefine FK mappings the heuristic engine can't auto-detect (e.g., `created_by → users`). Add a `_relationships` key to your `metadata.json`:\n```json\n{\n  \"orders.status\": { \"pending\": \"...\", \"shipped\": \"...\" },\n  \"_relationships\": {\n    \"orders.created_by\": \"users.id\",\n    \"categories.parent_id\": \"categories.id\"\n  }\n}\n```\nThese are treated as authoritative (not heuristic) and take priority over automatic detection.\n\nThe heuristic engine also assigns a **confidence score** (0–100) to each inferred FK based on name matching (+40), data type validation (+30), primary key verification (+20), and index presence (+10). Only FKs scoring ≥70 are accepted.\n\n### `templates.json` (Pre-approved SQL)\nStop the LLM from hallucinating complex metrics by providing vetted templates.\n```json\n[\n  {\n    \"name\": \"Customer Lifetime Value (LTV)\",\n    \"description\": \"Calculates total revenue generated by delivered orders per customer.\",\n    \"sql\": \"SELECT c.id, SUM(oi.price) FROM customers c JOIN orders o... WHERE o.status='delivered'\"\n  }\n]\n```\n\n---\n\n## 📈 Benchmarks & Token Savings\n\nTokenLite includes an automated benchmark suite using `o200k_base` tokenization (GPT-4o/GPT-5 standard) to measure efficiency improvements. Token counts are approximate — Claude 4.x uses a proprietary tokenizer; actual counts may vary slightly.\n\nTo run the benchmark in your own environment:\n```bash\nnpm run benchmark\n```\n\n### Baseline: Standard MCP Pattern\nThe benchmark compares against the standard pattern used by generic MySQL MCP servers: full schema exposed as `information_schema.columns` in pretty-printed JSON, and query results returned as `JSON.stringify(rows, null, 2)` with execution time metadata.\n\n### 1. Schema Discovery (Input Tokens)\nStandard MCP servers dump the entire schema to the LLM. For large databases, this consumes thousands of input tokens on every turn. TokenLite's relational graph serves a localized **Auto-Join Context** (target table + direct parent tables + direct child tables).\n\n| Scenario | Standard MCP Pattern | TokenLite | 📉 Savings |\n| :--- | :--- | :--- | :--- |\n| **Mock (50 tables, Enterprise CRM)** | 15,566 tokens | 883 tokens | **94.3%** |\n| **Live (9 tables, Test DB)** | 2,208 tokens | 531 tokens | **75.9%** |\n\nSavings scale with the number of tables: the more tables in the database, the higher the savings because the standard pattern dumps all of them while TokenLite only fetches the target + 1-hop relationships.\n\n### 2. Query Result Payloads (Output Tokens)\nTokenLite converts raw database rows to a dense, structured CSV layout. This avoids JSON syntax overhead (brackets, braces, repeated keys) and compresses the output payload returned to the LLM. When the row count reaches the server-injected `LIMIT`, results include a truncation footer (e.g. `-- rows: 500 (truncated at LIMIT 500)`) so agents know more data may exist.\n\n**Mock data** (varied: NULLs, long descriptions, mixed lengths):\n\n| Rows Returned | Standard MCP Pattern (Tokens) | TokenLite CSV (Tokens) | 📉 Output Savings (%) |\n| :--- | :--- | :--- | :--- |\n| **10 rows** | 1,167 | 601 | **48.5%** |\n| **50 rows** | 5,803 | 2,927 | **49.6%** |\n| **100 rows** | 11,607 | 5,842 | **49.7%** |\n| **500 rows** | 57,990 | 29,119 | **49.8%** |\n\n**Live data** (real MySQL test database with NULLs, ENUMs, variable-length text):\n\n| Rows Returned | Standard MCP Pattern (Tokens) | TokenLite CSV (Tokens) | 📉 Output Savings (%) |\n| :--- | :--- | :--- | :--- |\n| **10 rows** | 1,007 | 628 | **37.6%** |\n| **50 rows** | 5,029 | 3,114 | **38.1%** |\n| **100 rows** | 10,071 | 6,232 | **38.1%** |\n| **500 rows** | 50,327 | 31,116 | **38.2%** |\n\n---\n\n## 📊 Logging & Observability\n\nTokenLite uses MCP-native logging via `notifications/message` instead of raw stderr output. Clients that support MCP logging (e.g., MCP Inspector) will receive structured log messages with severity levels, logger names, and JSON data.\n\n**Severity levels** (from least to most severe): `debug`, `info`, `notice`, `warning`, `error`, `critical`, `alert`, `emergency`.\n\nThe server emits logs at `info` level and above by default. Control the minimum level via `MCP_LOG_LEVEL` or dynamically at runtime through the MCP `logging/setLevel` request.\n\nBefore the MCP session is established (e.g., during pool initialization), logs fall back to stderr.\n\n---\n\n## 🌐 Advanced Networking & Remote Connections\n\nBy design, `tokenlite-mysql-mcp` adheres to the Unix philosophy: it does one thing (AI-driven MySQL interactions) and does it securely via the standard `stdio` transport. It deliberately avoids bloating the codebase with HTTP servers or built-in SSH clients. \n\nIf you need to connect to remote databases or expose this server over the network, here are the recommended, enterprise-grade alternatives:\n\n### 1. Connecting to Remote Databases (SSH Tunnels)\nInstead of embedding SSH libraries, we recommend using native OS tunnels. This is much more secure, respects your `~/.ssh/config`, and supports advanced authentication (2FA, hardware keys).\n\nSimply open a terminal and run:\n\n```bash\nssh -N -L 3306:127.0.0.1:3306 user@your-remote-server.com\n```\nThen, point `tokenlite-mysql-mcp` to `localhost` and port `3306`.\n\n### 2. Exposing the MCP Server over HTTP/Network\nIf you need to host this MCP Server in the cloud (AWS, GCP) and have multiple Claude desktop clients connect to it remotely via HTTP/SSE, do not modify this codebase to add Express/HTTP logic. \nInstead, wrap the process using standard open-source MCP proxies like [mcp-proxy](https://github.com/sparfenyuk/mcp-proxy). This cleanly separates the transport layer security from the AI logic.\n\n## 🐛 Troubleshooting\n\n**Error: `OptimizerError: Full table scan detected...`**\nThe LLM attempted to execute a query that requires scanning thousands of rows without using an index. \n*Solution*: Use `explain_query` to see the full EXPLAIN output and understand why the query was blocked. Rewrite the query with an indexed `WHERE` clause. If you truly need to scan the whole table, increase `MCP_EXPLAIN_MAX_SCAN_ROWS` in your config. To return more rows per query, adjust `MCP_QUERY_ROW_LIMIT` separately.\n\n**Error: `calling \"initialize\": invalid character...`**\nThis means the MCP JSON-RPC protocol crashed. Ensure you are passing the correct DB credentials and that the database is running and accessible from the machine where the MCP server runs.\n\n---\n*Built for the AI Engineering era.*\n\n---\n",
  "bytes": 15341,
  "sha": "b55a750cfe55231c6641f9602822446936a6aa7316039eea0a5801c0fbd52be1",
  "repo_slug": "andezdev/tokenlite-mysql-mcp",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_andezdev_tokenlite_mysql_mcp_07836def/readme"
}