# dperussina/mssql-mcp-server architecture

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

- Interactive diagram: https://gitdiagram.com/dperussina/mssql-mcp-server
- Repository: https://github.com/dperussina/mssql-mcp-server

## Overview

##### Project Type & Purpose
- This is an open-source Node.js “bridge” service (the MS SQL MCP Server) that lets AI assistants (Claude, Cursor IDE, etc.) explore and query Microsoft SQL Server databases in a controlled, read-only way.
- There is no customer-facing GUI or traditional frontend; instead, the “client” is either:
- A bundled menu-driven CLI (client.mjs / test-client.js) over stdio
- An HTTP/SSE endpoint for networked AI tools

##### High-Level Components
A. AI Client Layer
- Claude Desktop or Cursor IDE (invokes tools via stdio or HTTP/SSE)
- Bundled CLI client (client.mjs)
B. Transport Layer
- stdio transport (JSON-RPC over stdin/stdout)
- HTTP/SSE transport (Express or native HTTP server with Server-Sent Events)
C. MCP Server Core (server.mjs)
- tools.mjs – Registers JSON-RPC methods & validates parameters
- database.mjs – Connects to SQL Server, executes queries, handles pooling/retries
- resources.mjs – Discovers schemas, tables, columns; formats metadata
- pagination.mjs – Builds OFFSET/FETCH or cursor-based pagination queries
- errors.mjs – Defines error types & JSON-RPC error formatting
- logger.mjs – Configures Winston, logs requests, errors, uncaught exceptions
D. Data Storage & DB
- External Microsoft SQL Server instance
- Local JSON file storage (QUERY_RESULTS_PATH) for large result sets
E. Configuration & Deployment
- .env for credentials & transport selection
- Dockerfile / GitHub workflows for build, test, npm-publish

##### Component Interactions & Data Flow
1. AI Client → Transport
- stdio: JSON-RPC messages in/out
- HTTP/SSE: HTTP POST for requests, SSE stream for responses
2. Transport → server.mjs
- Routes each request to tools.mjs
3. tools.mjs → database.mjs & pagination.mjs
- Validates parameters, constructs SQL with pagination support
4. database.mjs → MS SQL Server
- Executes read-only queries, returns raw rows
5. database.mjs → pagination.mjs (if needed)
- Wraps results with cursors or OFFSET/FETCH metadata
6. Core → errors.mjs & logger.mjs
- On success or failure, format JSON-RPC response or error; log details
7. server.mjs → Transport → AI Client
8. For large result sets, write JSON to QUERY_RESULTS_PATH and return a UUID link

##### Architectural Patterns & Principles
- Modular Separation of Concerns:
- Transport vs. Tool Registration vs. DB Connectivity vs. Pagination vs. Logging vs. Error Handling
- JSON-RPC Protocol (over stdio or HTTP/SSE)
- Read-Only Data Access & SQL Injection Protection
- Configurable Transport & Environment via .env
- File-Backed Result Persistence for large datasets

##### Essential Elements to Capture
- Two client paths: stdio vs. HTTP/SSE
- Core pipeline: request → validation → SQL generation → DB execution → pagination → response/error → logging
- External dependencies: MS SQL Server and file system for JSON results
- Configurability via .env (transport, host, port, credentials)
- Modular grouping of Lib/*.mjs files

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% AI Client Layer
    subgraph "AI Client Layer"
        CLIClient["Bundled CLI Clients"] 
        SSEClient["HTTP/SSE Test Client"]
        ClaudeDesktop["Claude / Cursor IDE"]
    end

    %% Transport Layer
    subgraph "Transport Layer"
        StdioTransport["stdio Transport\n(JSON-RPC over stdin/stdout)"]:::transport
        HTTPTransport["HTTP/SSE Transport\n(HTTP POST & SSE)"]:::transport
    end

    %% MCP Server Core
    subgraph "MCP Server Core"
        Server["server.mjs\n(Entrypoint & Router)"] 
        Tools["tools.mjs\n(Register & Validate)"]:::core
        Database["database.mjs\n(DB Connectivity & Query)"]:::core
        Resources["resources.mjs\n(Schema Discovery)"]:::core
        Pagination["pagination.mjs\n(Pagination Logic)"]:::core
        Errors["errors.mjs\n(Error Types & RPC Formatting)"]:::core
        Logger["logger.mjs\n(Logging Configuration)"]:::core
        Prompts["prompts.mjs\n(Prompt Templates)"]:::core
    end

    %% External Systems
    subgraph "External Systems"
        MSSQL["Microsoft SQL Server"]:::external
        FileStorage["JSON File Storage\n(QUERY_RESULTS_PATH)"]:::external
    end

    %% Deployment & Config
    subgraph "Configuration & Deployment"
        Env[".env.example"]:::config
        DockerfileNode["Dockerfile"]:::config
        CI1[".github/workflows/codeql.yml"]:::config
        CI2[".github/workflows/npm-publish.yml"]:::config
    end

    %% Connections
    ClaudeDesktop -->|invoke via stdio or HTTP/SSE| StdioTransport
    CLIClient -->|JSON-RPC| StdioTransport
    SSEClient -->|HTTP POST & SSE| HTTPTransport

    StdioTransport -->|forwards request| Server
    HTTPTransport -->|forwards request| Server

    Server -->|routes to| Tools
    Tools -->|validate & build SQL| Database
    Tools -->|use pagination| Pagination
    Database -->|execute read-only query| MSSQL
    Database -->|wrap results / retrieve cursors| Pagination
    Pagination -->|paginated result| Server

    Database -->|large result sets| FileStorage

    Server -->|format errors/responses| Errors
    Server -->|log details| Logger

    Server -->|send response| StdioTransport
    Server -->|send response| HTTPTransport

    %% Click Events
    click CLIClient "https://github.com/dperussina/mssql-mcp-server/blob/main/client.mjs"
    click CLIClient "https://github.com/dperussina/mssql-mcp-server/blob/main/test-client.js"
    click CLIClient "https://github.com/dperussina/mssql-mcp-server/blob/main/mssql-client.mjs"
    click SSEClient "https://github.com/dperussina/mssql-mcp-server/blob/main/test-sse.mjs"
    click Server "https://github.com/dperussina/mssql-mcp-server/blob/main/server.mjs"
    click Tools "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/tools.mjs"
    click Database "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/database.mjs"
    click Resources "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/resources.mjs"
    click Pagination "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/pagination.mjs"
    click Errors "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/errors.mjs"
    click Logger "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/logger.mjs"
    click Prompts "https://github.com/dperussina/mssql-mcp-server/blob/main/Lib/prompts.mjs"
    click Env "https://github.com/dperussina/mssql-mcp-server/blob/main/.env.example"
    click DockerfileNode "https://github.com/dperussina/mssql-mcp-server/tree/main/Dockerfile"
    click CI1 "https://github.com/dperussina/mssql-mcp-server/blob/main/.github/workflows/codeql.yml"
    click CI2 "https://github.com/dperussina/mssql-mcp-server/blob/main/.github/workflows/npm-publish.yml"

    %% Styles
    classDef transport fill:#cfe2f3,stroke:#2f75b5,color:#0b5394
    classDef core fill:#d9ead3,stroke:#6aa84f,color:#274e13
    classDef external fill:#fce5cd,stroke:#e69138,color:#bf9000
    classDef config fill:#e7e6e6,stroke:#a6a6a6,color:#333333
```
