#!/usr/bin/env node
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import {
CallToolRequestSchema,
ErrorCode,
ListToolsRequestSchema,
McpError,
} from '@modelcontextprotocol/sdk/types.js';
import { randomUUID } from 'crypto';
import mysql from 'mysql2/promise';
const MYSQL_HOST = process.env.MYSQL_HOST || 'localhost';
const MYSQL_PORT = parseInt(process.env.MYSQL_PORT || '3306', 10);
const MYSQL_USER = process.env.MYSQL_USER || 'mcp101';
const MYSQL_PASSWORD = process.env.MYSQL_PASSWORD || '123qwe';
const MYSQL_DATABASE = process.env.MYSQL_DATABASE || 'mcpdb';
interface SqlQueryArgs {
query: string;
}
// Validate SQL query arguments
const isValidSqlQueryArgs = (args: any): args is SqlQueryArgs =>
typeof args === 'object' &&
args !== null &&
typeof args.query === 'string';
// Check if query is read-only (SELECT)
const isReadOnlyQuery = (query: string): boolean =>
query.trim().toLowerCase().startsWith('select');
// Check if query is for creating a table
const isCreateTableQuery = (query: string): boolean =>
query.trim().toLowerCase().startsWith('create table');
// Check if query is for inserting data
const isInsertQuery = (query: string): boolean =>
query.trim().toLowerCase().startsWith('insert into');
// Check if query is for updating data
const isUpdateQuery = (query: string): boolean =>
query.trim().toLowerCase().startsWith('update');
// Check if query is for deleting data
const isDeleteQuery = (query: string): boolean =>
query.trim().toLowerCase().startsWith('delete from');
// Generate a unique transaction ID for logging
const generateTransactionId = (): string => randomUUID();
class MySqlServer {
private server: Server;
private pool: mysql.Pool;
constructor() {
this.server = new Server(
{
name: 'mysql-mcp-server',
version: '1.0.0',
},
{
capabilities: {
tools: {},
},
}
);
this.pool = mysql.createPool({
host: MYSQL_HOST,
port: MYSQL_PORT,
user: MYSQL_USER,
password: MYSQL_PASSWORD,
database: MYSQL_DATABASE,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
});
this.setupToolHandlers();
// Error handling
this.server.onerror = (error) => console.error('[MCP Error]', error);
process.on('SIGINT', async () => {
await this.pool.end();
await this.server.close();
process.exit(0);
});
}
private setupToolHandlers() {
this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
tools: [
{
name: 'run_sql_query',
description: 'Executes a read-only SQL query (SELECT statements only) against the MySQL database.',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL SELECT query to execute.',
},
},
required: ['query'],
},
},
{
name: 'create_table',
description: 'Creates a new table in the MySQL database.',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL CREATE TABLE query to execute.',
},
},
required: ['query'],
},
},
{
name: 'insert_data',
description: 'Inserts data into a table in the MySQL database.',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL INSERT INTO query to execute.',
},
},
required: ['query'],
},
},
{
name: 'update_data',
description: 'Updates data in a table in the MySQL database.',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL UPDATE query to execute.',
},
},
required: ['query'],
},
},
{
name: 'delete_data',
description: 'Deletes data from a table in the MySQL database.',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL DELETE FROM query to execute.',
},
},
required: ['query'],
},
},
{
name: 'execute_sql',
description: 'Executes any non-SELECT SQL statement (e.g., ALTER TABLE, DROP, etc.)',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL statement to execute.',
},
},
required: ['query'],
},
},
],
}));
this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
const transactionId = generateTransactionId();
console.error(`[${transactionId}] Processing request: ${request.params.name}`);
// Handle different tool types
switch (request.params.name) {
case 'run_sql_query':
return this.handleReadQuery(request, transactionId);
case 'create_table':
return this.handleCreateTable(request, transactionId);
case 'insert_data':
return this.handleInsertData(request, transactionId);
case 'update_data':
return this.handleUpdateData(request, transactionId);
case 'delete_data':
return this.handleDeleteData(request, transactionId);
case 'execute_sql':
return this.handleExecuteSql(request, transactionId);
default:
throw new McpError(
ErrorCode.MethodNotFound,
`Unknown tool: ${request.params.name}`
);
}
});
}
// Handle read-only queries (SELECT)
private async handleReadQuery(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (!isReadOnlyQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'Only SELECT queries are allowed with run_sql_query tool.'
);
}
console.error(`[${transactionId}] Executing SELECT query: ${query}`);
try {
const [rows] = await this.pool.query(query);
console.error(`[${transactionId}] Query executed successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify(rows, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] Query error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
// Handle CREATE TABLE queries
private async handleCreateTable(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (!isCreateTableQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'Only CREATE TABLE queries are allowed with create_table tool.'
);
}
console.error(`[${transactionId}] Executing CREATE TABLE query: ${query}`);
try {
const [result] = await this.pool.query(query);
console.error(`[${transactionId}] Table created successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify({
success: true,
message: 'Table created successfully',
result
}, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] Query error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
// Handle INSERT INTO queries
private async handleInsertData(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (!isInsertQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'Only INSERT INTO queries are allowed with insert_data tool.'
);
}
console.error(`[${transactionId}] Executing INSERT query: ${query}`);
try {
const [result] = await this.pool.query(query);
console.error(`[${transactionId}] Data inserted successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify({
success: true,
message: 'Data inserted successfully',
result
}, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] Query error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
// Handle UPDATE queries
private async handleUpdateData(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (!isUpdateQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'Only UPDATE queries are allowed with update_data tool.'
);
}
console.error(`[${transactionId}] Executing UPDATE query: ${query}`);
try {
const [result] = await this.pool.query(query);
console.error(`[${transactionId}] Data updated successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify({
success: true,
message: 'Data updated successfully',
result
}, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] Query error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
// Handle DELETE FROM queries
private async handleDeleteData(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (!isDeleteQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'Only DELETE FROM queries are allowed with delete_data tool.'
);
}
console.error(`[${transactionId}] Executing DELETE query: ${query}`);
try {
const [result] = await this.pool.query(query);
console.error(`[${transactionId}] Data deleted successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify({
success: true,
message: 'Data deleted successfully',
result
}, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] Query error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
async run() {
const transport = new StdioServerTransport();
await this.server.connect(transport);
console.error('MySQL MCP server running on stdio');
}
// Handle general SQL execution (non-SELECT)
private async handleExecuteSql(request: any, transactionId: string) {
if (!isValidSqlQueryArgs(request.params.arguments)) {
throw new McpError(
ErrorCode.InvalidParams,
'Invalid SQL query arguments.'
);
}
const query = request.params.arguments.query;
if (isReadOnlyQuery(query)) {
throw new McpError(
ErrorCode.InvalidParams,
'SELECT queries are not allowed with execute_sql tool.'
);
}
console.error(`[${transactionId}] Executing general SQL: ${query}`);
try {
const [result] = await this.pool.query(query);
console.error(`[${transactionId}] SQL executed successfully`);
return {
content: [
{
type: 'text',
text: JSON.stringify({
success: true,
message: 'SQL executed successfully',
result
}, null, 2),
},
],
};
} catch (error) {
console.error(`[${transactionId}] SQL error:`, error);
if (error instanceof Error) {
return {
content: [
{
type: 'text',
text: `MySQL error: ${error.message}`,
},
],
isError: true,
};
}
throw error;
}
}
}
const server = new MySqlServer();
server.run().catch(console.error);