Mantle SQL API MCP Server

Mantle SQL API Model Context Protocol Server helps in connecting any MS SQL Server via env configuration intended to help AI models be aware of the database data context to answer LLM prompts. This MCP server provides three core tools with optimized performance for production use.

Tools

The server provides the following tools:

1. health_check

Checks the health of the database connection and server.

Usage:

• No parameters required
• Returns detailed status information including:
  • Database connection status
  • Connection pool statistics
  • Memory usage metrics
  • Schema cache status
  • Server version information

2. query_database

Executes read-only SQL queries against the database with pagination and performance controls.

Parameters:

• query (string, required): SQL query to execute
• timeout (number, optional): Query timeout in milliseconds (default: 30000)
• maxRows (number, optional): Maximum number of rows to return (default: 10000)
• page (number, optional): Page number for pagination, 1-based (default: 1)
• pageSize (number, optional): Number of rows per page (default: 1000)

Features:

• Automatic query validation to ensure read-only operations
• Query complexity analysis to prevent resource-intensive queries
• Automatic pagination for large result sets
• Result size limiting to prevent memory issues
• Detailed execution statistics

3. get_schema

Retrieves database schema information with efficient caching.

Parameters:

• forceRefresh (boolean, optional): Force refresh of schema cache (default: false)

Features:

• Efficient schema caching with configurable TTL
• Detailed table and column metadata
• Index information
• Foreign key relationships
• Performance metrics

4. manage_cache

Manages the schema cache for optimal performance.

Parameters:

• action (enum): One of:
  • status: Get current cache status
  • clear: Clear the cache
  • refresh: Force refresh the cache

Features:

• Cache statistics monitoring
• Memory usage tracking
• Manual cache control

Performance Optimizations

Connection Pool Management

The server implements advanced connection pool management techniques:

• Dynamic pool sizing: Automatically adjusts pool size based on system resources
• CPU-aware scaling: Scales connection pool size based on available CPU cores
• Configurable timeouts: Fine-tuned connection acquisition and idle timeouts
• Connection monitoring: Tracks connection acquisition times and usage patterns
• Automatic recovery: Self-healing connection pool with retry mechanics
• Detailed statistics: Comprehensive pool utilization metrics

Query Execution Enhancements

Advanced query handling features:

• Query timeout control: Configurable timeouts with automatic cancellation
• Pagination support: Efficient handling of large result sets with OFFSET/FETCH
• Result size limiting: Prevents memory issues with large result sets
• Memory-aware execution: Monitors available memory before executing large queries
• Query complexity analysis: Identifies potentially slow or resource-intensive queries
• Enhanced security: Sophisticated SQL validation with parsing

Schema Caching

Efficient schema management:

• Time-based caching: Stores schema information with configurable TTL
• Cache statistics: Monitoring of cache hit rates and memory usage
• Selective refresh: Ability to force refresh when needed
• Memory efficiency: Optimized memory usage for cached schema data
• Cache invalidation: Proper versioning for cache management

Memory Management

Robust memory monitoring and management:

• System memory tracking: Monitors host system memory usage
• Process memory tracking: Tracks Node.js process memory consumption
• Memory leak detection: Identifies potential memory leaks through usage patterns
• Resource throttling: Adapts operations based on available memory
• Detailed memory statistics: Provides insights into memory usage patterns

Enhanced Security

Improved security measures:

• SQL parsing: Sophisticated SQL validation through proper parsing
• Command analysis: Identifies SQL command types and structures
• Metadata extraction: Extracts table names, joins, and query characteristics
• Pattern detection: Identifies potentially dangerous SQL patterns
• Read-only enforcement: Multiple layers of validation for read-only access

Extended MCP Server Capabilities

Logging

The server implements comprehensive logging with the following features:

• Multiple log levels: ERROR, WARN, INFO, DEBUG, TRACE
• Context-based logging: Component-specific logging contexts
• File-based logging: Optional file output with rotation
• Automatic rotation: Size-based log file rotation
• Performance metrics: Detailed performance information in logs

Configuration

The server is configured through environment variables:

Database Configuration

• DB_SERVER: SQL Server hostname or IP address (with optional port)
• DB_USER: Database username
• DB_PASSWORD: Database password
• DB_NAME: Database name
• DB_MAX_CONNECTIONS: Maximum connection pool size (default: CPU count × 2)
• DB_MIN_CONNECTIONS: Minimum connection pool size (default: 0)
• DB_IDLE_TIMEOUT: Connection idle timeout in ms (default: 20000)
• DB_ACQUIRE_TIMEOUT: Connection acquisition timeout in ms (default: 15000)
• DB_CREATE_TIMEOUT: Connection creation timeout in ms (default: 30000)
• DB_REQUEST_TIMEOUT: Query request timeout in ms (default: 30000)
• DB_ENCRYPT: Enable encryption (default: true)
• DB_TRUST_SERVER_CERT: Trust server certificate (default: false)

Logging Configuration

• LOG_LEVEL: Logging level (error, warn, info, debug, trace)
• LOG_FILE: Log file path (optional)
• LOG_MAX_SIZE: Maximum log file size before rotation (default: 5MB)
• LOG_MAX_FILES: Maximum number of rotated log files to keep (default: 5)

Performance Configuration

• SCHEMA_CACHE_TTL_MS: Schema cache time-to-live in ms (default: 900000 - 15 min)

Installation and Deployment

Prerequisites

• Node.js 22.15.0 or higher
• Access to a Microsoft SQL Server database

Installation

# Clone the repository
git clone https://github.com/yourusername/mantle-sql-api-mcp-server.git

# Install dependencies
npm install

# Build the application
npm run build

Running in Development Mode

# Start in development mode (watches for changes)
npm run dev

Running in Production Mode

# Start in production mode
npm start

Docker Deployment

# Build the Docker image
docker build -t mantleai-mcp-server-sqlserver .

# Run with Docker
docker run --rm -p 8080:8080 --env-file .env.docker --add-host=host.docker.internal:host-gateway --name mantleai-mcp-server-sqlserver mantleai-mcp-server-sqlserver

# Or use docker-compose
docker-compose up -d

Deploy to ClaudeAi Desktop Cliens

as nodejs

run echo "$(which node)" to identify node version to use in the settings

{
  "mcpServers": {
    "@mantleai/mcp-server-sqlserver": {
      "command": "/Users/princepark/.nvm/versions/node/v22.15.0/bin/node",
      "env": {
        "DB_SERVER": "localhost:5433",
        "DB_NAME": "--database_name--",
        "DB_USER": "--username--",
        "DB_PASSWORD": "--user_password--",
        "LOG_LEVEL": "info"
      },
      "args": ["/Volumes/Projects/ai/mcps/mcp-server-sqlserver/build/index.js"]
    }
  }
}

as docker

{
  "mcpServers": {
    "@mantleai/mcp-server-sqlserver": {
      "command": "docker",
      "args": [
        "run",
        "--rm",
        "-i",
        "-p",
        "8080:8080",
        "--add-host=host.docker.internal:host-gateway",
        "--name", "mantleai-mcp-server-sqlserver",
        "-e","DB_SERVER=host.docker.internal:5433",
        "-e","DB_NAME=--database_name--",
        "-e","DB_USER=--username--",
        "-e","DB_PASSWORD=--user_password--",
        "-e","LOG_LEVEL=info",
        "mantleai-mcp-server-sqlserver"
      ]
    }
  }
}

Project Structure

src/
├── db/                # Database-related code
│   ├── connection.ts  # Connection pool management
│   ├── validators.ts  # SQL validation
│   ├── sql-parser.ts  # SQL parsing and validation
│   └── schema-cache.ts # Schema caching
├── tools/             # MCP tool implementations
│   ├── health-check.ts   # Health check implementation
│   ├── query-database.ts # Query database implementation
│   ├── get-schema.ts     # Get schema implementation
│   └── index.ts          # Tool exports
├── utils/             # Utility functions
│   └── memory-monitor.ts # Memory monitoring
├── logger.ts          # Logging implementation
└── index.ts           # Main entry point