Tokenlite Mysql Mcp

TokenLite MySQL MCP

A robust and secure MySQL database server implemented under Anthropic's Model Context Protocol (MCP). Designed specifically to solve the shortcomings of current generic MCP servers through Graceful Degradation, Active Performance Protection, and Aggressive Token Optimization.

---

🌟 Core Pillars

1. 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.
2. 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).
3. 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.
4. 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.
5. 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".
6. 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.
7. MCP Completions: Table names and template keywords support the official completions/complete capability for resource templates (mysql://tables/{name}) and the query_templates prompt.

---

📋 Requirements

• Node.js v20 or higher
• MySQL 5.7 or higher (MySQL 8.0+ recommended)
• A MySQL user with SELECT and SHOW VIEW privileges.

---

🚀 Installation & Usage

You can use this MCP server with any compatible client. Below are the configurations for the most popular ones.

1. Claude Desktop

Edit 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:

Using NPX (Recommended)

{
  "mcpServers": {
    "tokenlite-mysql": {
      "command": "npx",
      "args": [
        "-y",
        "@andezdev/tokenlite-mysql-mcp"
      ],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_db_user",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database",
        "MCP_EXPLAIN_MAX_SCAN_ROWS": "1000",
        "MCP_QUERY_ROW_LIMIT": "500",
        "MCP_SAFE_QUERY_ENABLE_BLOCKING": "true"
      }
    }
  }
}

2. Claude Code (CLI)

You can easily integrate this server globally into Claude Code:

claude mcp add tokenlite_mysql \
  -e DB_HOST="127.0.0.1" \
  -e DB_PORT="3306" \
  -e DB_USER="root" \
  -e DB_PASSWORD="your_password" \
  -e DB_NAME="your_database" \
  -- npx -y @andezdev/tokenlite-mysql-mcp

3. Cursor IDE

To use within Cursor IDE:

1. Open Cursor Settings > Features > MCP.
2. Click + Add New MCP Server.
3. Set the Type to command.
4. Name it tokenlite-mysql.
5. Set the command to:

   npx -y @andezdev/tokenlite-mysql-mcp
   

(Note: Cursor handles environment variables directly in the IDE UI, make sure to add your DB credentials there).

---

⚙️ Environment Variables Reference

Understanding Query Limits (two independent knobs)

execute_safe_query applies two separate limits. Do not confuse them:

Variable	What it controls	Default	Example
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
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

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.

Worked example with MCP_EXPLAIN_MAX_SCAN_ROWS=5000 and MCP_QUERY_ROW_LIMIT=500 on a customers table (~1502 rows):

1. SELECT * FROM customers → passes EXPLAIN (1502 < 5000), executes with LIMIT 500, returns 500 rows + truncation footer.
2. Same config but table has 8000 rows → blocked by EXPLAIN before execution.

Variable	Description	Default	Required
DB_HOST	MySQL Host address	localhost	No
DB_PORT	MySQL Port	3306	No
DB_USER	MySQL Username	root	No
DB_PASSWORD	MySQL Password	''	No
DB_NAME	MySQL Database name	test	Yes
MCP_EXPLAIN_MAX_SCAN_ROWS	EXPLAIN guardrail: max estimated rows for unindexed full table scans before blocking.	1000	No
MCP_QUERY_ROW_LIMIT	Max rows returned per SELECT (AST-injected LIMIT + truncation footer).	500	No
MCP_SAFE_QUERY_ENABLE_BLOCKING	Enable or disable the EXPLAIN guardrail.	true	No
MCP_METADATA_PATH	Absolute path to your custom metadata.json dictionary.	(Disabled)	No
MCP_TEMPLATES_PATH	Absolute path to your custom templates.json queries.	(Disabled)	No
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
MCP_RATE_LIMIT_RPM	Max tool invocations per minute (sliding window). Set to 0 to disable.	60	No
MYSQL_QUERY_TIMEOUT	Max execution time for a query (in ms). Aborts heavy queries to protect against DoS.	15000	No
MYSQL_CONNECTION_LIMIT	Max concurrent pool connections.	10	No
MYSQL_CONNECT_TIMEOUT	Max time to wait for a socket to establish (in ms).	10000	No
MYSQL_RETRY_ATTEMPTS	Max retries on transient connection errors (ECONNREFUSED, PROTOCOL_CONNECTION_LOST, etc.).	3	No
MYSQL_RETRY_DELAY_MS	Base delay (ms) for exponential backoff between retries (1s, 2s, 4s...).	1000	No
MYSQL_QUEUE_LIMIT	Max queued requests when all pool connections are busy. Prevents unbounded growth if MySQL is down.	50	No
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
MCP_LOG_LEVEL	Minimum severity for MCP log notifications: debug, info, notice, warning, error, critical, alert, emergency.	info	No
ALLOW_INSERT_OPERATION	Enable INSERT and REPLACE queries.	false	No
ALLOW_UPDATE_OPERATION	Enable UPDATE queries.	false	No
ALLOW_DELETE_OPERATION	Enable DELETE and TRUNCATE queries.	false	No
ALLOW_DDL_OPERATION	Enable Data Definition Language (CREATE, ALTER, DROP, RENAME).	false	No
DB_SSL	Enable TLS for the MySQL connection (recommended for managed/cloud databases).	false	No
DB_SSL_REJECT_UNAUTHORIZED	Reject self-signed or untrusted TLS certificates when DB_SSL=true.	true	No

---

🛡️ Business Intelligence Features (Opt-in)

TokenLite 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:

metadata.json (Semantic Dictionary)

Translate integer statuses or internal jargon so the LLM understands the data.

{
  "orders.status": {
    "pending": "The order is waiting for payment validation",
    "shipped": "The order has left the warehouse"
  }
}

Custom Relationships

Define FK mappings the heuristic engine can't auto-detect (e.g., created_by → users). Add a _relationships key to your metadata.json:

{
  "orders.status": { "pending": "...", "shipped": "..." },
  "_relationships": {
    "orders.created_by": "users.id",
    "categories.parent_id": "categories.id"
  }
}

These are treated as authoritative (not heuristic) and take priority over automatic detection.

The 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.

templates.json (Pre-approved SQL)

Stop the LLM from hallucinating complex metrics by providing vetted templates.

[
  {
    "name": "Customer Lifetime Value (LTV)",
    "description": "Calculates total revenue generated by delivered orders per customer.",
    "sql": "SELECT c.id, SUM(oi.price) FROM customers c JOIN orders o... WHERE o.status='delivered'"
  }
]

---

📈 Benchmarks & Token Savings

TokenLite 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.

To run the benchmark in your own environment:

npm run benchmark

Baseline: Standard MCP Pattern

The 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.

1. Schema Discovery (Input Tokens)

Standard 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).

Scenario	Standard MCP Pattern	TokenLite	📉 Savings
Mock (50 tables, Enterprise CRM)	15,566 tokens	883 tokens	94.3%
Live (9 tables, Test DB)	2,208 tokens	531 tokens	75.9%

Savings 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.

2. Query Result Payloads (Output Tokens)

TokenLite 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.

Mock data (varied: NULLs, long descriptions, mixed lengths):

Rows Returned	Standard MCP Pattern (Tokens)	TokenLite CSV (Tokens)	📉 Output Savings (%)
10 rows	1,167	601	48.5%
50 rows	5,803	2,927	49.6%
100 rows	11,607	5,842	49.7%
500 rows	57,990	29,119	49.8%

Live data (real MySQL test database with NULLs, ENUMs, variable-length text):

Rows Returned	Standard MCP Pattern (Tokens)	TokenLite CSV (Tokens)	📉 Output Savings (%)
10 rows	1,007	628	37.6%
50 rows	5,029	3,114	38.1%
100 rows	10,071	6,232	38.1%
500 rows	50,327	31,116	38.2%

---

📊 Logging & Observability

TokenLite 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.

Severity levels (from least to most severe): debug, info, notice, warning, error, critical, alert, emergency.

The 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.

Before the MCP session is established (e.g., during pool initialization), logs fall back to stderr.

---

🌐 Advanced Networking & Remote Connections

By 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.

If you need to connect to remote databases or expose this server over the network, here are the recommended, enterprise-grade alternatives:

1. Connecting to Remote Databases (SSH Tunnels)

Instead 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).

Simply open a terminal and run:

ssh -N -L 3306:127.0.0.1:3306 user@your-remote-server.com

Then, point tokenlite-mysql-mcp to localhost and port 3306.

2. Exposing the MCP Server over HTTP/Network

If 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. Instead, wrap the process using standard open-source MCP proxies like mcp-proxy. This cleanly separates the transport layer security from the AI logic.

🐛 Troubleshooting

Error: OptimizerError: Full table scan detected... The LLM attempted to execute a query that requires scanning thousands of rows without using an index. 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.

Error: calling "initialize": invalid character... This 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.

---

Built for the AI Engineering era.

---