How dperussina/mssql-mcp-server works
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