#!/usr/bin/env node
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListToolsRequestSchema,
Tool,
} from "@modelcontextprotocol/sdk/types.js";
import mysql from "mysql2/promise";
// MySQL connection configuration
const dbConfig = {
host: process.env.MYSQL_HOST || "localhost",
port: parseInt(process.env.MYSQL_PORT || "3306"),
user: process.env.MYSQL_USER || "root",
password: process.env.MYSQL_PASS || "",
database: process.env.MYSQL_DB || "test",
};
// Operation permissions
const ALLOW_INSERT = process.env.ALLOW_INSERT_OPERATION === "true";
const ALLOW_UPDATE = process.env.ALLOW_UPDATE_OPERATION === "true";
const ALLOW_DELETE = process.env.ALLOW_DELETE_OPERATION === "true";
// SQL operation detection patterns
const SQL_PATTERNS = {
INSERT: /^\s*INSERT\s+INTO/i,
UPDATE: /^\s*UPDATE\s+/i,
DELETE: /^\s*DELETE\s+FROM/i,
DROP: /^\s*DROP\s+/i,
TRUNCATE: /^\s*TRUNCATE\s+/i,
ALTER: /^\s*ALTER\s+/i,
};
/**
* Validates SQL query against operation restrictions
*/
function validateQuery(query: string): { allowed: boolean; reason?: string } {
// Always block destructive schema operations
if (SQL_PATTERNS.DROP.test(query)) {
return { allowed: false, reason: "DROP operations are not allowed" };
}
if (SQL_PATTERNS.TRUNCATE.test(query)) {
return { allowed: false, reason: "TRUNCATE operations are not allowed" };
}
if (SQL_PATTERNS.ALTER.test(query)) {
return { allowed: false, reason: "ALTER operations are not allowed" };
}
// Check INSERT permission
if (SQL_PATTERNS.INSERT.test(query) && !ALLOW_INSERT) {
return {
allowed: false,
reason: "INSERT operations are disabled. Set ALLOW_INSERT_OPERATION=true to enable.",
};
}
// Check UPDATE permission
if (SQL_PATTERNS.UPDATE.test(query) && !ALLOW_UPDATE) {
return {
allowed: false,
reason: "UPDATE operations are disabled. Set ALLOW_UPDATE_OPERATION=true to enable.",
};
}
// Check DELETE permission
if (SQL_PATTERNS.DELETE.test(query) && !ALLOW_DELETE) {
return {
allowed: false,
reason: "DELETE operations are disabled. Set ALLOW_DELETE_OPERATION=true to enable.",
};
}
return { allowed: true };
}
/**
* Creates a MySQL connection pool
*/
async function createPool() {
return mysql.createPool({
...dbConfig,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
});
}
// Create MCP server
const server = new Server(
{
name: "mcp-mysql-server",
version: "1.0.0",
},
{
capabilities: {
tools: {},
},
}
);
// Define available tools
const tools: Tool[] = [
{
name: "mysql_query",
description: `Execute a MySQL query with safety restrictions.
Safety Features:
- INSERT operations: ${ALLOW_INSERT ? "ENABLED" : "DISABLED"}
- UPDATE operations: ${ALLOW_UPDATE ? "ENABLED" : "DISABLED"}
- DELETE operations: ${ALLOW_DELETE ? "ENABLED" : "DISABLED"}
- DROP/TRUNCATE/ALTER: Always DISABLED
Use this tool to query your MySQL database. SELECT queries are always allowed.
Returns query results as JSON.`,
inputSchema: {
type: "object",
properties: {
query: {
type: "string",
description: "The SQL query to execute",
},
params: {
type: "array",
description: "Optional array of parameters for prepared statement (prevents SQL injection)",
items: {
type: ["string", "number", "boolean", "null"],
},
},
},
required: ["query"],
},
},
{
name: "mysql_list_tables",
description: "List all tables in the current database",
inputSchema: {
type: "object",
properties: {},
},
},
{
name: "mysql_describe_table",
description: "Get the structure/schema of a specific table",
inputSchema: {
type: "object",
properties: {
table: {
type: "string",
description: "The name of the table to describe",
},
},
required: ["table"],
},
},
{
name: "mysql_get_database_info",
description: "Get information about the current database connection",
inputSchema: {
type: "object",
properties: {},
},
},
];
// Handle list tools request
server.setRequestHandler(ListToolsRequestSchema, async () => {
return { tools };
});
// Handle tool execution
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
if (!args) {
return {
content: [{ type: "text", text: JSON.stringify({ error: "No arguments provided" }, null, 2) }],
isError: true,
};
}
try {
const pool = await createPool();
switch (name) {
case "mysql_query": {
const query = args.query as string;
const params = (args.params as any[]) || [];
// Validate query
const validation = validateQuery(query);
if (!validation.allowed) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
error: validation.reason,
blocked: true,
},
null,
2
),
},
],
};
}
// Execute query
const [rows, fields] = await pool.execute(query, params);
await pool.end();
return {
content: [
{
type: "text",
text: JSON.stringify(
{
success: true,
rows: rows,
rowCount: Array.isArray(rows) ? rows.length : 0,
fields: fields?.map((f: any) => ({
name: f.name,
type: f.type,
})),
},
null,
2
),
},
],
};
}
case "mysql_list_tables": {
const [rows] = await pool.execute("SHOW TABLES");
await pool.end();
return {
content: [
{
type: "text",
text: JSON.stringify(
{
success: true,
tables: rows,
},
null,
2
),
},
],
};
}
case "mysql_describe_table": {
const table = args.table as string;
// Validate table name to prevent SQL injection
if (!/^[a-zA-Z0-9_]+$/.test(table)) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
error: "Invalid table name. Only alphanumeric characters and underscores are allowed.",
},
null,
2
),
},
],
};
}
const [rows] = await pool.execute(`DESCRIBE ${table}`);
await pool.end();
return {
content: [
{
type: "text",
text: JSON.stringify(
{
success: true,
table: table,
columns: rows,
},
null,
2
),
},
],
};
}
case "mysql_get_database_info": {
const [versionRows] = await pool.execute("SELECT VERSION() as version");
const [dbRows] = await pool.execute("SELECT DATABASE() as current_db");
await pool.end();
return {
content: [
{
type: "text",
text: JSON.stringify(
{
success: true,
connection: {
host: dbConfig.host,
port: dbConfig.port,
user: dbConfig.user,
database: dbConfig.database,
},
server: versionRows,
currentDatabase: dbRows,
permissions: {
INSERT: ALLOW_INSERT,
UPDATE: ALLOW_UPDATE,
DELETE: ALLOW_DELETE,
SELECT: true,
DROP: false,
TRUNCATE: false,
ALTER: false,
},
},
null,
2
),
},
],
};
}
default:
throw new Error(`Unknown tool: ${name}`);
}
} catch (error: any) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
error: error.message,
code: error.code,
sqlState: error.sqlState,
},
null,
2
),
},
],
isError: true,
};
}
});
// Logging utility
function log(level: "info" | "warn" | "error", message: string, ...args: any[]) {
const timestamp = new Date().toISOString();
const prefix = `[${timestamp}] [${level.toUpperCase()}]`;
console.error(prefix, message, ...args);
}
// Start the server
async function main() {
const transport = new StdioServerTransport();
log("info", "Starting MCP MySQL Server...");
try {
// Test database connection
const testPool = await createPool();
await testPool.execute("SELECT 1");
await testPool.end();
log("info", "Database connection test successful");
} catch (error: any) {
log("error", "Failed to connect to database:", error.message);
throw error;
}
await server.connect(transport);
// Log to stderr so it doesn't interfere with MCP protocol
log("info", "MCP MySQL Server running");
log("info", `Connected to: ${dbConfig.host}:${dbConfig.port}/${dbConfig.database}`);
log("info", `User: ${dbConfig.user}`);
log("info", `Permissions - INSERT: ${ALLOW_INSERT}, UPDATE: ${ALLOW_UPDATE}, DELETE: ${ALLOW_DELETE}`);
log("info", "Server ready to accept requests");
}
main().catch((error) => {
log("error", "Fatal error:", error);
process.exit(1);
});