MySQL NodeJS MCP Server with Schema Inspection

Overview

This Node.js MCP (Model Context Protocol) server provides a secure, transparent way for LLM-based agents to interact with a MySQL database. It implements schema inspection and configurable access controls so you can expose only the tables and columns you want, enforce read-only or limited write access, and log or audit queries that come from downstream AI models.

By combining schema discovery and fine-grained configuration, the server makes database integration predictable and safe for production or experimental use. Developers building chatbots, query assistants, or automated analytic agents can let LLMs formulate SQL while keeping your data governance rules in place.

Features

• Schema inspection endpoint (table/column metadata) for context-aware agents
• Configurable allowlist/denylist for tables and columns
• Read-only mode and operation-level restrictions
• Parameterized query execution to reduce injection risk
• Connection pooling and query timeouts for reliability
• Query/result size limits (max rows) and response redaction controls
• Audit logging of incoming requests and executed SQL
• Simple HTTP API compatible with MCP-style tool calls
• Docker-friendly and environment-driven configuration

Installation / Configuration

Prerequisites: Node.js (14+), MySQL server accessible to the running process.

Clone and install:

git clone https://github.com/benborla/mcp-server-mysql.git
cd mcp-server-mysql
npm install

Create a .env file (example):

PORT=3000
DATABASE_URL=mysql://user:password@localhost:3306/mydb
MCP_KEY=replace-with-secret
READ_ONLY=true
ALLOWED_TABLES=users,orders
MAX_ROWS=500
QUERY_TIMEOUT=5000

Environment variables (summary):

Advanced configuration can be provided with a JSON config file (example: config.json) to set per-table allowedColumns, disabledStatements, and audit settings:

{
  "allowedTables": ["users", "orders"],
  "allowedColumns": {
    "users": ["id", "email", "name"],
    "orders": ["id", "user_id", "total", "created_at"]
  },
  "disabledStatements": ["DROP", "ALTER"],
  "audit": {
    "enabled": true,
    "logFile": "./logs/queries.log"
  }
}

Run the server:

node server.js
# or
npm start

Docker (build & run):

docker build -t mcp-server-mysql .
docker run -e DATABASE_URL="mysql://user:pass@host/db" -e MCP_KEY="secret" -p 3000:3000 mcp-server-mysql

API endpoints (typical):

• GET /schema — returns allowed schema metadata (requires Authorization header)
• POST /query — execute a parameterized SQL request (body includes SQL and params)
• POST /mcp — an MCP-style tool entrypoint that routes “run_sql” and “inspect_schema”

Example curl requests:

Fetch schema:

curl -H "Authorization: Bearer $MCP_KEY" http://localhost:3000/schema

Run a parameterized query:

curl -X POST -H "Authorization: Bearer $MCP_KEY" -H "Content-Type: application/json" \
  -d '{"query":"SELECT id, email FROM users WHERE created_at >= ?","params":["2026-01-01"],"maxRows":100}' \
  http://localhost:3000/query

Available Resources

• Source & releases: https://github.com/benborla/mcp-server-mysql
• MySQL Node.js driver docs: https://www.npmjs.com/package/mysql2 (or your chosen driver)
• MCP / Tooling: refer to your agent framework or MCP spec for tool-call formats

Include the repository link in your project README or orchestration docs so integrators can find the server code and configuration examples.

Use Cases

• Schema-aware AI assistants: Allow an LLM to inspect the database schema first, then generate SQL that respects your schema and column restrictions.
• Secure read-only analytics: Expose read-only access for business intelligence prompts without risking data modification.
• Controlled automation: Let automation agents run safe, parameterized queries for routine reports, ticketing, or provisioning while auditing every request.
• Prototyping SQL agents: Quickly prototype LLM-driven query builders that rely on live schema metadata to avoid guessing column names.

Concrete example workflow:

1. Agent requests schema via GET /schema to learn table and column names.
2. Agent generates a parameterized SQL statement (e.g., “SELECT user_id, SUM(total) FROM orders WHERE created_at BETWEEN ? AND ? GROUP BY user_id”).
3. Agent calls POST /query with SQL and parameters.
4. Server enforces allowedTables/columns, read-only mode, and maxRows, executes the query, returns results, and writes an audit entry.

This pattern keeps LLMs informed about the