MCP Server for MySQL

by benborla
Databases
TypeScript
MIT License
1,219
104
Reddit Discord
Overview InspectNew Schema Related Servers Reviews Score
Need Help?View Source Code Report Issue
tests/e2e/server.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import * as mysql2 from 'mysql2/promise';
import * as dotenv from 'dotenv';
import { spawn, ChildProcess } from 'child_process';
import * as path from 'path';

// Load test environment variables
dotenv.config({ path: '.env.test' });

// Helper function to create a test client
function createTestClient() {
  // This would be a simplified version of an MCP client for testing
  return {
    async listTools() {
      // Determine which operations are enabled
      const allowInsert = process.env.ALLOW_INSERT_OPERATION === 'true';
      const allowUpdate = process.env.ALLOW_UPDATE_OPERATION === 'true';
      const allowDelete = process.env.ALLOW_DELETE_OPERATION === 'true';
      
      let description = 'Run SQL queries against MySQL database';
      if (allowInsert || allowUpdate || allowDelete) {
        description += ' with support for:';
        if (allowInsert) description += ' INSERT,';
        if (allowUpdate) description += ' UPDATE,';
        if (allowDelete) description += ' DELETE,';
        description = description.replace(/,$/, '') + ' and READ operations';
      } else {
        description += ' (READ-ONLY)';
      }
      
      return {
        tools: [
          {
            name: 'mysql_query',
            description,
            inputSchema: {
              type: 'object',
              properties: {
                sql: { type: 'string' },
              },
            },
          },
        ],
      };
    },
    
    async callTool(name: string, args: any) {
      // Implementation would send the request to the server
      if (name !== 'mysql_query') {
        throw new Error(`Unknown tool: ${name}`);
      }
      
      // Check if the query is a write operation
      const sql = args.sql.trim().toUpperCase();
      const isInsert = sql.startsWith('INSERT');
      const isUpdate = sql.startsWith('UPDATE');
      const isDelete = sql.startsWith('DELETE');
      
      // Check if the operations are allowed
      const allowInsert = process.env.ALLOW_INSERT_OPERATION === 'true';
      const allowUpdate = process.env.ALLOW_UPDATE_OPERATION === 'true';
      const allowDelete = process.env.ALLOW_DELETE_OPERATION === 'true';
      
      // If it's a write operation and not allowed, return an error
      if (isInsert && !allowInsert) {
        return {
          content: [{ type: 'text', text: 'Error: INSERT operations are not allowed.' }],
          isError: true,
        };
      }
      
      if (isUpdate && !allowUpdate) {
        return {
          content: [{ type: 'text', text: 'Error: UPDATE operations are not allowed.' }],
          isError: true,
        };
      }
      
      if (isDelete && !allowDelete) {
        return {
          content: [{ type: 'text', text: 'Error: DELETE operations are not allowed.' }],
          isError: true,
        };
      }
      
      // Mock responses based on the operation type
      if (isInsert && allowInsert) {
        return {
          content: [
            {
              type: 'text',
              text: 'Insert successful. Affected rows: 1, Last insert ID: 42',
            },
          ],
          isError: false,
        };
      }
      
      if (isUpdate && allowUpdate) {
        return {
          content: [
            {
              type: 'text',
              text: 'Update successful. Affected rows: 2, Changed rows: 1',
            },
          ],
          isError: false,
        };
      }
      
      if (isDelete && allowDelete) {
        return {
          content: [
            {
              type: 'text',
              text: 'Delete successful. Affected rows: 1',
            },
          ],
          isError: false,
        };
      }
      
      // For read operations, return a mock result
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify([{ result: 'test' }], null, 2),
          },
        ],
        isError: false,
      };
    },
    
    async listResources() {
      // Implementation would communicate with the server
      return {
        resources: [
          {
            uri: `mysql://127.0.0.1:3306/test_table/schema`,
            mimeType: 'application/json',
            name: '"test_table" database schema',
          },
        ],
      };
    },
    
    async readResource(uri: string) {
      // Implementation would communicate with the server
      return {
        contents: [
          {
            uri,
            mimeType: 'application/json',
            text: JSON.stringify([
              { column_name: 'id', data_type: 'int' },
              { column_name: 'name', data_type: 'varchar' },
              { column_name: 'created_at', data_type: 'timestamp' },
            ], null, 2),
          },
        ],
      };
    },
    
    close() {
      // Clean up resources
    }
  };
}

describe('Server', () => {
  let serverProcess: any;
  let pool: any;
  let client: ReturnType<typeof createTestClient>;
  
  beforeAll(async () => {
    // Set the write operation flags to false by default
    process.env.ALLOW_INSERT_OPERATION = 'false';
    process.env.ALLOW_UPDATE_OPERATION = 'false';
    process.env.ALLOW_DELETE_OPERATION = 'false';
    
    // Set up test database
    pool = mysql2.createPool({
      host: process.env.MYSQL_HOST || '127.0.0.1',
      port: Number(process.env.MYSQL_PORT || '3306'),
      user: process.env.MYSQL_USER || 'root',
      password: process.env.MYSQL_PASS || '',
      database: process.env.MYSQL_DB || 'mcp_test',
      connectionLimit: 5,
    });
    
    // Create test client
    client = createTestClient();
  });
  
  afterAll(async () => {
    // Clean up
    if (serverProcess) {
      serverProcess.kill();
    }
    if (pool) {
      await pool.end();
    }
    if (client) {
      client.close();
    }
  });
  
  it('should list available tools', async () => {
    const result = await client.listTools();
    expect(result.tools).toHaveLength(1);
    expect(result.tools[0].name).toBe('mysql_query');
    // By default, should be read-only
    expect(result.tools[0].description).toContain('READ-ONLY');
  });
  
  it('should execute a query tool', async () => {
    const result = await client.callTool('mysql_query', { sql: 'SELECT * FROM test_table' });
    expect(result.isError).toBe(false);
    expect(result.content).toHaveLength(1);
    expect(result.content[0].type).toBe('text');
  });
  
  it('should list available resources', async () => {
    const result = await client.listResources();
    expect(result.resources).toHaveLength(1);
    expect(result.resources[0].name).toContain('test_table');
  });
  
  it('should read a resource', async () => {
    const uri = 'mysql://127.0.0.1:3306/test_table/schema';
    const result = await client.readResource(uri);
    expect(result.contents).toHaveLength(1);
    
    const content = JSON.parse(result.contents[0].text);
    expect(Array.isArray(content)).toBe(true);
    expect(content.length).toBeGreaterThan(0);
    expect(content[0]).toHaveProperty('column_name');
    expect(content[0]).toHaveProperty('data_type');
  });
  
  // Tests for write operations
  describe('Write Operations', () => {
    it('should block INSERT operations by default', async () => {
      const result = await client.callTool('mysql_query', { 
        sql: 'INSERT INTO test_table (name) VALUES ("Test Insert")'
      });
      
      expect(result.isError).toBe(true);
      expect(result.content[0].text).toContain('INSERT operations are not allowed');
    });
    
    it('should block UPDATE operations by default', async () => {
      const result = await client.callTool('mysql_query', { 
        sql: 'UPDATE test_table SET name = "Updated" WHERE id = 1'
      });
      
      expect(result.isError).toBe(true);
      expect(result.content[0].text).toContain('UPDATE operations are not allowed');
    });
    
    it('should block DELETE operations by default', async () => {
      const result = await client.callTool('mysql_query', { 
        sql: 'DELETE FROM test_table WHERE id = 1'
      });
      
      expect(result.isError).toBe(true);
      expect(result.content[0].text).toContain('DELETE operations are not allowed');
    });
    
    it('should allow INSERT operations when enabled', async () => {
      // Enable INSERT operations for this test
      process.env.ALLOW_INSERT_OPERATION = 'true';
      
      const result = await client.callTool('mysql_query', { 
        sql: 'INSERT INTO test_table (name) VALUES ("Test Insert")'
      });
      
      expect(result.isError).toBe(false);
      expect(result.content[0].text).toContain('Insert successful');
      
      // Reset the flag
      process.env.ALLOW_INSERT_OPERATION = 'false';
    });
    
    it('should allow UPDATE operations when enabled', async () => {
      // Enable UPDATE operations for this test
      process.env.ALLOW_UPDATE_OPERATION = 'true';
      
      const result = await client.callTool('mysql_query', { 
        sql: 'UPDATE test_table SET name = "Updated" WHERE id = 1'
      });
      
      expect(result.isError).toBe(false);
      expect(result.content[0].text).toContain('Update successful');
      
      // Reset the flag
      process.env.ALLOW_UPDATE_OPERATION = 'false';
    });
    
    it('should allow DELETE operations when enabled', async () => {
      // Enable DELETE operations for this test
      process.env.ALLOW_DELETE_OPERATION = 'true';
      
      const result = await client.callTool('mysql_query', { 
        sql: 'DELETE FROM test_table WHERE id = 1'
      });
      
      expect(result.isError).toBe(false);
      expect(result.content[0].text).toContain('Delete successful');
      
      // Reset the flag
      process.env.ALLOW_DELETE_OPERATION = 'false';
    });
    
    it('should update the tool description when write operations are enabled', async () => {
      // Enable all write operations for this test
      process.env.ALLOW_INSERT_OPERATION = 'true';
      process.env.ALLOW_UPDATE_OPERATION = 'true';
      process.env.ALLOW_DELETE_OPERATION = 'true';
      
      const result = await client.listTools();
      
      expect(result.tools[0].description).toContain('INSERT');
      expect(result.tools[0].description).toContain('UPDATE');
      expect(result.tools[0].description).toContain('DELETE');
      expect(result.tools[0].description).not.toContain('READ-ONLY');
      
      // Reset the flags
      process.env.ALLOW_INSERT_OPERATION = 'false';
      process.env.ALLOW_UPDATE_OPERATION = 'false';
      process.env.ALLOW_DELETE_OPERATION = 'false';
    });
  });
}); 