# souhar-dya/mcp-db-server architecture

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

- Interactive diagram: https://gitdiagram.com/souhar-dya/mcp-db-server
- Repository: https://github.com/souhar-dya/mcp-db-server

## Overview

##### Project type and purpose
- This is a backend microservice (“mcp-db-server”) exposing relational databases (PostgreSQL/MySQL) to AI agents over REST. It accepts natural-language queries, converts them to SQL via a transformer model, executes read-only SQL, and returns JSON.

##### File structure
- Top-level artifacts:
- app/
– server.py: FastAPI app and endpoint definitions
– nl_to_sql.py: NL→SQL conversion module (HuggingFace model)
– db.py: Async SQLAlchemy database connection, query validation, and result limiting
- mcp_server.py: CLI entrypoint (alias to app.server)
- Dockerfile & docker-compose.yml: Container & multi-service orchestration (including sample DB)
- test_suite.py: end-to-end tests
- CI workflows under .github/workflows
- Controller layer (FastAPI endpoints)
- Domain logic (NL→SQL, validation)
- Data layer (async ORM)

##### Extract architecture hints from the README
- RESTful API with endpoints (/health, /mcp/list_tables, /mcp/describe, /mcp/query, /mcp/tables/{name}/sample)
- “AI agent friendly” design, containerized for Docker Compose or Kubernetes
- Security: read-only SQL enforcement, injection protection, row limits
- External integrations: HuggingFace transformers, SQLAlchemy, FastAPI

##### Main components
a. Clients / AI Agents
- External AI clients or CURL scripts calling the REST endpoints
b. API Layer (FastAPI container)
- Endpoint handlers (server.py)
- OpenAPI/Swagger UI
c. NL→SQL Engine
- Transformer model from HuggingFace (nl_to_sql.py)
d. Validation & Throttling
- Query validator (db.py)
- Row-limit enforcement
e. Database Layer
- Async SQLAlchemy + asyncpg / PyMySQL connecting to PostgreSQL/MySQL
f. Dev/Test/CI Infrastructure
- Docker Compose service definitions
- GitHub Actions pipelines
- Kubernetes manifests (optional)

##### Relationships and data flow
- API Layer → NL→SQL Engine (in-process function call)
- NL→SQL Engine → Validation Module → Database Layer (async SQL execution)
- Database Layer → API Layer (JSON results) → Clients

##### Architectural patterns
- Layered architecture (Controller → Business Logic → Persistence)
- Single container microservice pattern
- Read-only façade over relational data for AI agents
- Use of asynchronous I/O throughout (FastAPI + async DB drivers)

##### Technologies & frameworks
- FastAPI (HTTP server, dependency injection)
- HuggingFace Transformers (NL→SQL)
- SQLAlchemy (ORM + query building)
- asyncpg / PyMySQL (async drivers)
- Docker & Docker Compose (containerization)
- GitHub Actions (CI/CD)
- Kubernetes (optional deployment)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Client
    Clients["Clients / AI Agents"]:::external

    %% Main Container
    subgraph "mcp-db-server Container"
        direction TB
        API["FastAPI Endpoints<br/>(/health, /mcp/list_tables, /mcp/describe, /mcp/query, /mcp/tables/{name}/sample)"]:::api
        NL["NL→SQL Engine<br/>(HuggingFace Transformer)"]:::nlp
        VAL["Validation & Throttling<br/>(Read-only enforcement, row limits)"]:::data
        DBConn["Async DB Connection<br/>(SQLAlchemy + asyncpg/PyMySQL)"]:::data
    end

    %% External Databases
    PostgreSQL["PostgreSQL Database"]:::db
    MySQL["MySQL Database"]:::db

    %% Infrastructure
    subgraph "Infrastructure & CI/CD"
        direction TB
        DockerfileNode["Dockerfile"]:::infra
        Compose["docker-compose.yml"]:::infra
        Env[".env.example"]:::infra
        subgraph "CI/CD Workflows"
            direction TB
            CI_Test[".github/workflows/test.yml"]:::infra
            CI_Publish[".github/workflows/docker-publish.yml"]:::infra
        end
        Tests["test_suite.py (E2E Tests)"]:::infra
        README1["README.md"]:::infra
        README2["README_DOCKER_REGISTRY.md"]:::infra
        GitIgnore[".gitignore"]:::infra
        License["LICENSE"]:::infra
        Config1["claude_desktop_config_example.json"]:::infra
        Config2["claude_desktop_config_local_example.json"]:::infra
        Registry["mcp-registry-entry.json"]:::infra
        Requirements["requirements.txt"]:::infra
    end

    %% CLI Entrypoint
    Cli["mcp_server.py (CLI Entrypoint)"]:::api

    %% Data Flows
    Clients -->|"HTTP REST"| API
    API -->|"call nl_to_sql()"| NL
    NL -->|"SQL query"| VAL
    VAL -->|"validated SQL"| DBConn
    DBConn -->|"async query"| PostgreSQL
    DBConn -->|"async query"| MySQL
    DBConn -->|"JSON results"| API
    API -->|"JSON response"| Clients
    Cli -->|"starts"| API

    %% Click Events
    click API "https://github.com/souhar-dya/mcp-db-server/blob/main/app/server.py"
    click Cli "https://github.com/souhar-dya/mcp-db-server/blob/main/mcp_server.py"
    click NL "https://github.com/souhar-dya/mcp-db-server/blob/main/app/nl_to_sql.py"
    click VAL "https://github.com/souhar-dya/mcp-db-server/blob/main/app/db.py"
    click DBConn "https://github.com/souhar-dya/mcp-db-server/blob/main/app/db.py"
    click DockerfileNode "https://github.com/souhar-dya/mcp-db-server/tree/main/Dockerfile"
    click Compose "https://github.com/souhar-dya/mcp-db-server/blob/main/docker-compose.yml"
    click Env "https://github.com/souhar-dya/mcp-db-server/blob/main/.env.example"
    click CI_Test "https://github.com/souhar-dya/mcp-db-server/blob/main/.github/workflows/test.yml"
    click CI_Publish "https://github.com/souhar-dya/mcp-db-server/blob/main/.github/workflows/docker-publish.yml"
    click Tests "https://github.com/souhar-dya/mcp-db-server/blob/main/test_suite.py"
    click README1 "https://github.com/souhar-dya/mcp-db-server/blob/main/README.md"
    click README2 "https://github.com/souhar-dya/mcp-db-server/blob/main/README_DOCKER_REGISTRY.md"
    click GitIgnore "https://github.com/souhar-dya/mcp-db-server/blob/main/.gitignore"
    click License "https://github.com/souhar-dya/mcp-db-server/tree/main/LICENSE"
    click Config1 "https://github.com/souhar-dya/mcp-db-server/blob/main/claude_desktop_config_example.json"
    click Config2 "https://github.com/souhar-dya/mcp-db-server/blob/main/claude_desktop_config_local_example.json"
    click Registry "https://github.com/souhar-dya/mcp-db-server/blob/main/mcp-registry-entry.json"
    click Requirements "https://github.com/souhar-dya/mcp-db-server/blob/main/requirements.txt"

    %% Styles
    classDef api fill:#D0E8FF,stroke:#024F9D,color:#024F9D;
    classDef nlp fill:#FFE0B2,stroke:#FF9900,color:#993D00;
    classDef data fill:#E0F7E9,stroke:#2E7D32,color:#1B5E20;
    classDef db fill:#F0F0F0,stroke:#666666,color:#333333,stroke-dasharray: 5 5;
    classDef infra fill:#F5E1FD,stroke:#8E24AA,color:#4A0072;
    classDef external fill:#F5F5F5,stroke:#757575,color:#212121,stroke-dasharray: 2 2;
```
