TokenLite MySQL MCP

npm version

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

   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:

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.

🛡️ 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).

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

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

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