# gs76lee/mysql-mcp-server architecture

> Architecture diagram of the GitHub repository gs76lee/mysql-mcp-server, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-29). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/gs76lee/mysql-mcp-server
- Repository: https://github.com/gs76lee/mysql-mcp-server

## Overview

##### Project Type and Purpose
- This is a backend service (“mysql-mcp-server”) that implements the Model Context Protocol (MCP) for AI models to perform MySQL database operations.
- It exposes a FastMCP server interface, registers a set of “tools” (executors) for CRUD, schema inspection, query planning, reporting, and visualization recommendation, then relays results back to the AI model.

##### File Structure Analysis
- Top-level:
- Dockerfile + docker-compose.yml for containerized deployment
- .env.example, requirements.txt, README.md
- mysql_mcp_server/ (main application)
- assets/ (static images)
- test/ (unit tests)
- Inside mysql_mcp_server/:
- main.py: entry point, starts MCP server
- executors/: each file defines one tool (execute_* functions)
- helper/: utility modules (db_conn_helper, logger_helper, tool_decorator)

##### Key README Insights
- Layers: Interface (MCP Server), Business Logic (executors), Data Access (DatabaseManager via PyMySQL)
- Tech stack: Python, FastMCP, PyMySQL, pandas, python-dotenv, fire
- Deployment: Docker (+ Compose), direct Python run

a. Main Components
- AI Model (external client)
- MCP Server (FastMCP)
- Tool Registry / Controller Layer (tool_decorator, TOOLS_DEFINITION)
- Executors (create_table, desc_table, explain, insert_query, insight_starter, invoke_viz_pro, select_query, show_tables)
- DatabaseManager (singleton managing PyMySQL connections)
- MySQL Database (8.0)
- Docker / Compose Orchestrator

b. Interactions
1. AI Model → MCP Server: request list of tools
2. MCP Server → Controller: serves tool list
3. AI Model → MCP Server: invoke specific tool
4. MCP Server → Executor: call execute_* function
5. Executor → DatabaseManager: issue SQL via PyMySQL
6. DatabaseManager ↔ MySQL: run query, return results
7. Executor → MCP Server: wrap and send results
8. MCP Server → AI Model: return execution results

c. Architectural Patterns
- Layered architecture:
- Interface Layer (MCP Server)
- Business Logic Layer (executors + decorator registry)
- Data Access Layer (DatabaseManager)
- Singleton pattern for DatabaseManager
- Plugin/Registration pattern for tools via decorator and __init__.py imports

d. Technologies / Frameworks
- FastMCP (MCP Server)
- PyMySQL (DB connectivity)
- pandas (data processing inside insight_starter)
- python-dotenv (.env support)
- fire (CLI entry points)
- Docker + Docker Compose

##### Essential Architectural Elements to Capture
- Container orchestration: separate containers for MySQL and MCP server
- Tool registration mechanism (decorator + __all__)
- Execution flow (AI Model ↔ MCP Server ↔ Executor ↔ DatabaseManager ↔ MySQL)
- Layer boundaries and the technologies that implement them

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Orchestrator
    DockerComp["Docker Compose & Orchestrator"]:::external

    %% External Client
    AI["AI Model"]:::external

    %% Environment & Dependencies
    Env["Env Config (.env)"]:::infra
    Req["Dependencies (requirements.txt)"]:::infra
    DockerfileNode["Dockerfile"]:::infra

    %% MCP Server Container
    subgraph "Container: MCP Server"
        direction TB
        MCP["MCP Server\n(port 8081)"]:::interface
        ToolReg["Tool Registry\n(tool_decorator)"]:::business
        subgraph "Executors"
            direction TB
            CT["Create Table Executor"]:::business
            DT["Describe Table Executor"]:::business
            EXQ["Explain Query Executor"]:::business
            IQ["Insert Query Executor"]:::business
            IS["Insight Starter Executor"]:::business
            VI["Visualization Invoker Executor"]:::business
            SQ["Select Query Executor"]:::business
            ST["Show Tables Executor"]:::business
            InitExec["Executors __init__ (Plugin Registration)"]:::business
        end
        DBM["DatabaseManager\n(singleton)"]:::dataaccess
        LogUtil["Logging Utility"]:::dataaccess
    end

    %% MySQL Container
    subgraph "Container: MySQL"
        direction TB
        SQLDB["MySQL Database 8.0\n(port 3306)"]:::dataaccess
    end

    %% Unit Tests
    subgraph "Unit Tests"
        direction TB
        T1["test_tool_list.py"]:::infra
        T2["test_execute.py"]:::infra
        T3["test_sales_insert.py"]:::infra
    end

    %% Interactions
    DockerComp -->|orchestrates| MCP
    DockerComp -->|orchestrates| SQLDB

    AI -->|request tool list| MCP
    MCP -->|serve tool list| AI

    AI -->|invoke tool| MCP
    MCP -->|lookup tool| ToolReg
    ToolReg -->|calls| CT
    ToolReg -->|calls| DT
    ToolReg -->|calls| EXQ
    ToolReg -->|calls| IQ
    ToolReg -->|calls| IS
    ToolReg -->|calls| VI
    ToolReg -->|calls| SQ
    ToolReg -->|calls| ST

    CT -->|uses| DBM
    DT -->|uses| DBM
    EXQ -->|uses| DBM
    IQ -->|uses| DBM
    IS -->|uses pandas &| DBM
    VI -->|uses| DBM
    SQ -->|uses| DBM
    ST -->|uses| DBM

    DBM <-->|SQL queries| SQLDB

    CT -->|return result| MCP
    DT -->|return result| MCP
    EXQ -->|return result| MCP
    IQ -->|return result| MCP
    IS -->|return result| MCP
    VI -->|return result| MCP
    SQ -->|return result| MCP
    ST -->|return result| MCP

    MCP -->|return execution results| AI

    %% Config & Dependencies
    Env --> MCP
    Req --> MCP
    DockerfileNode --> MCP
    dockerComposeYml["docker-compose.yml"]:::infra
    dockerComposeYml --> DockerComp

    %% Tests
    T1 --> CT
    T2 --> DT
    T3 --> IQ

    %% Click Events
    click MCP "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/main.py"
    click ToolReg "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/helper/tool_decorator.py"
    click CT "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/create_table.py"
    click DT "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/desc_table.py"
    click EXQ "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/explain.py"
    click IQ "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/insert_query.py"
    click IS "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/insight_starter.py"
    click VI "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/invoke_viz_pro.py"
    click SQ "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/select_query.py"
    click ST "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/show_tables.py"
    click InitExec "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/executors/__init__.py"
    click DBM "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/helper/db_conn_helper.py"
    click LogUtil "https://github.com/gs76lee/mysql-mcp-server/blob/main/mysql_mcp_server/helper/logger_helper.py"
    click Env "https://github.com/gs76lee/mysql-mcp-server/blob/main/.env.example"
    click Req "https://github.com/gs76lee/mysql-mcp-server/blob/main/requirements.txt"
    click DockerfileNode "https://github.com/gs76lee/mysql-mcp-server/tree/main/Dockerfile"
    click dockerComposeYml "https://github.com/gs76lee/mysql-mcp-server/blob/main/docker-compose.yml"
    click T1 "https://github.com/gs76lee/mysql-mcp-server/blob/main/test/test_tool_list.py"
    click T2 "https://github.com/gs76lee/mysql-mcp-server/blob/main/test/test_execute.py"
    click T3 "https://github.com/gs76lee/mysql-mcp-server/blob/main/test/test_sales_insert.py"

    %% Styles
    classDef external fill:#cccccc,stroke:#333,stroke-width:1px
    classDef interface fill:#c6f6d5,stroke:#2f855a,stroke-width:1px
    classDef business fill:#bee3f8,stroke:#2b6cb0,stroke-width:1px
    classDef dataaccess fill:#fed7aa,stroke:#dd6b20,stroke-width:1px
    classDef infra fill:#e2e8f0,stroke:#4a5568,stroke-width:1px
```
