# MCPQL - SQL Server MCP

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Node.js Version](https://img.shields.io/badge/node-%3E%3D18.0.0-brightgreen)](https://nodejs.org/)
[![TypeScript](https://img.shields.io/badge/TypeScript-5.8.3-blue)](https://www.typescriptlang.org/)
[![npm version](https://img.shields.io/npm/v/mcpql.svg)](https://www.npmjs.com/package/mcpql)
[![Downloads](https://img.shields.io/npm/dm/mcpql.svg)](https://www.npmjs.com/package/mcpql)
[![GitHub stars](https://img.shields.io/github/stars/hendrickcastro/MCPQL.svg)](https://github.com/hendrickcastro/MCPQL/stargazers)
[![GitHub issues](https://img.shields.io/github/issues/hendrickcastro/MCPQL.svg)](https://github.com/hendrickcastro/MCPQL/issues)
[![GitHub forks](https://img.shields.io/github/forks/hendrickcastro/MCPQL.svg)](https://github.com/hendrickcastro/MCPQL/network)
[![Build Status](https://img.shields.io/github/actions/workflow/status/hendrickcastro/MCPQL/ci.yml?branch=main)](https://github.com/hendrickcastro/MCPQL/actions)
[![Coverage Status](https://img.shields.io/codecov/c/github/hendrickcastro/MCPQL)](https://codecov.io/gh/hendrickcastro/MCPQL)
[![SQL Server](https://img.shields.io/badge/SQL%20Server-2016%2B-red)](https://www.microsoft.com/sql-server)
[![Azure SQL](https://img.shields.io/badge/Azure%20SQL-Supported-blue)](https://azure.microsoft.com/services/sql-database/)
[![MCP Protocol](https://img.shields.io/badge/MCP-Protocol-purple)](https://github.com/modelcontextprotocol/sdk)
[![Claude Desktop](https://img.shields.io/badge/Claude-Desktop-orange)](https://claude.ai/desktop)
[![Cursor IDE](https://img.shields.io/badge/Cursor-IDE-green)](https://cursor.sh/)
[![Trae AI](https://img.shields.io/badge/Trae%20AI-IDE-blue)](https://trae.ai/)
[![Docker](https://img.shields.io/badge/Docker-Ready-blue)](https://hub.docker.com/r/hendrickcastro/mcpql)
[![Security](https://img.shields.io/badge/Security-Audited-green)](https://github.com/hendrickcastro/MCPQL/security)
[![Maintenance](https://img.shields.io/badge/Maintained-Yes-brightgreen)](https://github.com/hendrickcastro/MCPQL/graphs/commit-activity)

A comprehensive **Model Context Protocol (MCP)** server for **SQL Server** database operations. This server provides 10 powerful tools for database analysis, object discovery, and data manipulation through the MCP protocol.

## 🚀 Quick Start

### Prerequisites
- Node.js 18+ and npm
- SQL Server database with appropriate connection credentials
- MCP-compatible client (like Claude Desktop, Cursor IDE, or any MCP client)

### Installation & Configuration

#### Option 1: Using npx from GitHub (Recommended)
No installation needed! Just configure your MCP client:

**For Claude Desktop (`claude_desktop_config.json`):**
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "your_server",
        "DB_NAME": "your_database",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

**For Cursor IDE:**
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "your_server",
        "DB_NAME": "your_database",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

#### Option 2: Local Development Installation

1. **Clone and setup:**
```bash
git clone https://github.com/hendrickcastro/MCPQL.git
cd MCPQL
npm install
npm run build
```

2. **Configure database connection:**
Create a `.env` file with your database credentials:
```bash
# Basic SQL Server connection
DB_AUTHENTICATION_TYPE=sql
DB_SERVER=localhost
DB_NAME=MyDatabase
DB_USER=sa
DB_PASSWORD=YourPassword123!
DB_PORT=1433
DB_ENCRYPT=false
DB_TRUST_SERVER_CERTIFICATE=true
```

3. **Configure MCP client with local path:**
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "node",
      "args": ["path/to/MCPQL/dist/server.js"]
    }
  }
}
```

## 🛠️ Available Tools

MCPQL provides 11 comprehensive tools for SQL Server database operations:

### 1. 🏗️ **Table Analysis** - `mcp_table_analysis`
Complete table structure analysis including columns, keys, indexes, and constraints.

### 2. 📋 **Stored Procedure Analysis** - `mcp_sp_structure`
Analyze stored procedure structure including parameters, dependencies, and source code.

### 3. 👀 **Data Preview** - `mcp_preview_data`
Preview table data with optional filtering and row limits.

### 4. 📊 **Column Statistics** - `mcp_get_column_stats`
Get comprehensive statistics for a specific column.

### 5. ⚙️ **Execute Stored Procedure** - `mcp_execute_procedure`
Execute stored procedures with parameters and return results.

### 6. 🔍 **Execute SQL Query** - `mcp_execute_query`
Execute custom SQL queries with full error handling.

### 7. ⚡ **Quick Data Analysis** - `mcp_quick_data_analysis`
Quick statistical analysis including row count, column distributions, and top values.

### 8. 🔎 **Comprehensive Search** - `mcp_search_comprehensive`
Search across database objects by name and definition with configurable criteria.

### 9. 🔗 **Object Dependencies** - `mcp_get_dependencies`
Get dependencies for database objects (tables, views, stored procedures, etc.).

### 10. 🎯 **Sample Values** - `mcp_get_sample_values`
Get sample values from a specific column in a table.

### 11. 🔒 **Security Status** - `mcp_get_security_status`
Get current security configuration and status for database operations.

## 📋 Usage Examples

### Analyzing a Table
```typescript
// Get complete table structure
const analysis = await mcp_table_analysis({ 
  table_name: "dbo.Users" 
});

// Get quick data overview
const overview = await mcp_quick_data_analysis({ 
  table_name: "dbo.Users",
  sample_size: 500
});

// Preview table data with filters
const data = await mcp_preview_data({
  table_name: "dbo.Users",
  filters: { "Status": "Active", "Department": "IT" },
  limit: 25
});
```

### Finding Database Objects
```typescript
// Find all objects containing "User"
const objects = await mcp_search_comprehensive({ 
  pattern: "User",
  search_in_names: true,
  search_in_definitions: false
});

// Find procedures that query a specific table
const procedures = await mcp_search_comprehensive({ 
  pattern: "FROM Users",
  object_types: ["PROCEDURE"],
  search_in_definitions: true
});
```

### Analyzing Stored Procedures
```typescript
// Get complete stored procedure analysis
const spAnalysis = await mcp_sp_structure({ 
  sp_name: "dbo.usp_GetUserData" 
});

// Execute a stored procedure
const result = await mcp_execute_procedure({
  sp_name: "dbo.usp_GetUserById",
  params: { "UserId": 123, "IncludeDetails": true }
});
```

### Data Analysis
```typescript
// Get column statistics
const stats = await mcp_get_column_stats({
  table_name: "dbo.Users",
  column_name: "Age"
});

// Get sample values from a column
const samples = await mcp_get_sample_values({
  table_name: "dbo.Users",
  column_name: "Department",
  limit: 15
});
```

## 🔧 Environment Variables & Connection Types

MCPQL supports multiple SQL Server connection types with comprehensive configuration options:

### 🔐 Authentication Types

Set `DB_AUTHENTICATION_TYPE` to one of:
- `sql` - SQL Server Authentication (default)
- `windows` - Windows Authentication
- `azure-ad` - Azure Active Directory Authentication

### 📋 Complete Environment Variables

| Variable | Description | Default | Required For |
|----------|-------------|---------|--------------|
| **Basic Connection** |
| `DB_AUTHENTICATION_TYPE` | Authentication type (sql/windows/azure-ad) | sql | All |
| `DB_SERVER` | SQL Server hostname/IP | - | All |
| `DB_NAME` | Database name | - | All |
| `DB_PORT` | SQL Server port | 1433 | All |
| `DB_TIMEOUT` | Connection timeout (ms) | 30000 | All |
| `DB_REQUEST_TIMEOUT` | Request timeout (ms) | 30000 | All |
| **SQL Server Authentication** |
| `DB_USER` | SQL Server username | - | SQL Auth |
| `DB_PASSWORD` | SQL Server password | - | SQL Auth |
| **Windows Authentication** |
| `DB_DOMAIN` | Windows domain | - | Windows Auth |
| `DB_USER` | Windows username | current user | Windows Auth |
| `DB_PASSWORD` | Windows password | - | Windows Auth |
| **Azure AD Authentication** |
| `DB_USER` | Azure AD username | - | Azure AD (Password) |
| `DB_PASSWORD` | Azure AD password | - | Azure AD (Password) |
| `DB_AZURE_CLIENT_ID` | Azure AD App Client ID | - | Azure AD (Service Principal) |
| `DB_AZURE_CLIENT_SECRET` | Azure AD App Client Secret | - | Azure AD (Service Principal) |
| `DB_AZURE_TENANT_ID` | Azure AD Tenant ID | - | Azure AD (Service Principal) |
| **SQL Server Express** |
| `DB_INSTANCE_NAME` | Named instance (e.g., SQLEXPRESS) | - | Express instances |
| **Security Settings** |
| `DB_ENCRYPT` | Enable encryption | false | All |
| `DB_TRUST_SERVER_CERTIFICATE` | Trust server certificate | false | All |
| `DB_ENABLE_ARITH_ABORT` | Enable arithmetic abort | true | All |
| `DB_USE_UTC` | Use UTC for dates | true | All |
| **Connection Pool** |
| `DB_POOL_MAX` | Maximum connections | 10 | All |
| `DB_POOL_MIN` | Minimum connections | 0 | All |
| `DB_POOL_IDLE_TIMEOUT` | Idle timeout (ms) | 30000 | All |
| **Advanced Settings** |
| `DB_CANCEL_TIMEOUT` | Cancel timeout (ms) | 5000 | All |
| `DB_PACKET_SIZE` | Packet size (bytes) | 4096 | All |
| `DB_CONNECTION_STRING` | Complete connection string | - | Alternative to individual settings |
| **Security Controls** |
| `DB_ALLOW_MODIFICATIONS` | Allow DML/DDL operations | false | All |
| `DB_ALLOW_STORED_PROCEDURES` | Allow stored procedure execution | false | All |

## 🔧 Connection Configuration Examples

### 1. 🏠 SQL Server Local (SQL Authentication)
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "localhost",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

### 2. 🏢 SQL Server Express (Named Instance)
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "sql",
        "DB_SERVER": "localhost",
        "DB_INSTANCE_NAME": "SQLEXPRESS",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

### 3. 🪟 Windows Authentication
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "windows",
        "DB_SERVER": "MYSERVER",
        "DB_NAME": "MyDatabase",
        "DB_DOMAIN": "MYDOMAIN",
        "DB_USER": "myuser",
        "DB_PASSWORD": "mypassword",
        "DB_ENCRYPT": "false",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

### 4. ☁️ Azure SQL Database (Azure AD Password)
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "azure-ad",
        "DB_SERVER": "myserver.database.windows.net",
        "DB_NAME": "MyDatabase",
        "DB_USER": "user@domain.com",
        "DB_PASSWORD": "userpassword",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}
```

### 5. 🔐 Azure SQL Database (Service Principal)
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_AUTHENTICATION_TYPE": "azure-ad",
        "DB_SERVER": "myserver.database.windows.net",
        "DB_NAME": "MyDatabase",
        "DB_AZURE_CLIENT_ID": "your-client-id",
        "DB_AZURE_CLIENT_SECRET": "your-client-secret",
        "DB_AZURE_TENANT_ID": "your-tenant-id",
        "DB_PORT": "1433",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}
```

### 6. 🔗 Using Connection String
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_CONNECTION_STRING": "Server=localhost;Database=MyDatabase;User Id=sa;Password=YourPassword123!;Encrypt=false;TrustServerCertificate=true;"
      }
    }
  }
}
```

## 🔒 Security Features

MCPQL includes comprehensive security controls to prevent accidental database modifications, especially important in production environments.

### 🛡️ Security Controls

#### Database Modification Protection
- **`DB_ALLOW_MODIFICATIONS`**: Controls DML/DDL operations (INSERT, UPDATE, DELETE, ALTER, DROP, CREATE)
- **`DB_ALLOW_STORED_PROCEDURES`**: Controls stored procedure execution
- **Default**: Both variables default to `false` for maximum security

#### Security Status Tool
Use `mcp_get_security_status` to check current security configuration:
```typescript
const status = await mcp_get_security_status({});
```

### 🔧 Enabling Operations

#### For Development Environment
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_NAME": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword123!",
        "DB_ALLOW_MODIFICATIONS": "true",
        "DB_ALLOW_STORED_PROCEDURES": "true"
      }
    }
  }
}
```

#### For Production Environment (Recommended)
```json
{
  "mcpServers": {
    "mcpql": {
      "command": "npx",
      "args": ["-y", "hendrickcastro/mcpql"],
      "env": {
        "DB_SERVER": "prod-server",
        "DB_NAME": "ProductionDB",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "secure_password",
        "DB_ALLOW_MODIFICATIONS": "false",
        "DB_ALLOW_STORED_PROCEDURES": "false"
      }
    }
  }
}
```

### 🚨 Security Error Messages

When operations are blocked, MCPQL provides clear guidance:

```
Error: Modification operations are disabled for security.
To enable modifications, configure: DB_ALLOW_MODIFICATIONS=true

Error: Stored procedure execution is disabled for security.
To enable stored procedures, configure: DB_ALLOW_STORED_PROCEDURES=true
```

### 📋 Always Allowed Operations

These operations are always permitted regardless of security settings:
- SELECT queries
- Table analysis and schema inspection
- Column statistics and data preview
- Object search and dependency analysis
- Database metadata operations

For complete security documentation, see [SECURITY.md](SECURITY.md).

## 🚨 Troubleshooting Common Issues

### Connection Issues
- **"Login failed"**: Check username/password. For Windows auth, ensure `DB_AUTHENTICATION_TYPE=windows`
- **"Server was not found"**: Verify server name and port. For SQL Express, add `DB_INSTANCE_NAME`
- **"Certificate" errors**: For local development, set `DB_TRUST_SERVER_CERTIFICATE=true`
- **Timeout errors**: Increase `DB_TIMEOUT` or check network connectivity

### SQL Server Express Setup
1. Enable TCP/IP protocol in SQL Server Configuration Manager
2. Set a static port (usually 1433) or use dynamic port with Browser Service
3. Configure Windows Firewall to allow SQL Server traffic
4. Use `DB_INSTANCE_NAME=SQLEXPRESS` for default Express installations

### Azure SQL Database Setup
1. Create server firewall rules to allow client IP
2. Use format: `server.database.windows.net` for server name
3. Always set `DB_ENCRYPT=true` and `DB_TRUST_SERVER_CERTIFICATE=false`
4. For Service Principal auth, register app in Azure AD and assign permissions

## 🧪 Testing

Run the comprehensive test suite:
```bash
npm test
```

The test suite includes comprehensive testing of all 10 tools with real database testing and complete coverage.

## 🏗️ Architecture

### Project Structure
```
MCPQL/
├── src/
│   ├── __tests__/          # Comprehensive test suite
│   ├── tools/              # Modular tool implementations
│   │   ├── tableAnalysis.ts      # Table analysis tools
│   │   ├── storedProcedureAnalysis.ts  # SP analysis tools
│   │   ├── dataOperations.ts     # Data operation tools
│   │   ├── objectSearch.ts       # Search and discovery tools
│   │   ├── types.ts              # Type definitions
│   │   └── index.ts              # Tool exports
│   ├── db.ts               # Database connection management
│   ├── server.ts           # MCP server setup and handlers
│   ├── tools.ts            # Tool definitions and schemas
│   └── mcp-server.ts       # Tool re-exports
├── dist/                   # Compiled JavaScript output
└── package.json           # Dependencies and scripts
```

### Key Features
- ⚡ **Connection Pooling**: Efficient database connection management
- 🛡️ **Robust Error Handling**: Comprehensive error handling and validation
- 📋 **Rich Metadata**: Detailed results with comprehensive database information
- 🔧 **Flexible Configuration**: Environment-based configuration
- 📊 **Optimized Queries**: Efficient SQL queries for all operations

## 📝 Important Notes

- **Object Names**: Always use schema-qualified names (e.g., `dbo.Users`, `api.Idiomas`)
- **Error Handling**: All tools return structured responses with success/error indicators
- **Type Safety**: Full TypeScript support with proper type definitions
- **Connection Management**: Automatic connection pooling and retry logic
- **Security**: Parameterized queries to prevent SQL injection

## 🤝 Contributing

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Make your changes and add tests
4. Ensure all tests pass (`npm test`)
5. Commit your changes (`git commit -m 'Add amazing feature'`)
6. Push to the branch (`git push origin feature/amazing-feature`)
7. Open a Pull Request

## 📄 License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## 🙏 Acknowledgments

- Built with the [Model Context Protocol SDK](https://github.com/modelcontextprotocol/sdk)
- Uses [mssql](https://github.com/tediousjs/node-mssql) for SQL Server connectivity
- Comprehensive testing with [Jest](https://jestjs.io/)

## 🏷️ Tags & Keywords

**Database:** `sql-server` `azure-sql` `database-analysis` `database-tools` `mssql` `t-sql` `database-management` `database-administration` `database-operations` `data-analysis`

**MCP & AI:** `model-context-protocol` `mcp-server` `mcp-tools` `ai-tools` `claude-desktop` `cursor-ide` `anthropic` `llm-integration` `ai-database` `intelligent-database`

**Technology:** `typescript` `nodejs` `npm-package` `cli-tool` `database-client` `sql-client` `database-sdk` `rest-api` `json-api` `database-connector`

**Features:** `table-analysis` `stored-procedures` `data-preview` `column-statistics` `query-execution` `database-search` `object-dependencies` `schema-analysis` `data-exploration` `database-insights`

**Deployment:** `docker` `azure-deployment` `cloud-ready` `enterprise-ready` `production-ready` `scalable` `secure` `authenticated` `encrypted` `configurable`

**Use Cases:** `database-development` `data-science` `business-intelligence` `database-migration` `schema-documentation` `performance-analysis` `data-governance` `database-monitoring` `troubleshooting` `automation`

---

**🎯 MCPQL provides comprehensive SQL Server database analysis and manipulation capabilities through the Model Context Protocol. Perfect for database administrators, developers, and anyone working with SQL Server databases!** 🚀