# Microsoft SQL Server MCP Server

A comprehensive Node.js-based Model Context Protocol (MCP) server for Microsoft SQL Server databases. This tool enables AI agents and MCP-compatible clients to interact with SQL Server databases through a rich set of tools for schema exploration, data manipulation, and advanced database operations.

## Features

### 🔍 Schema Exploration
- **Comprehensive Discovery**: List schemas, tables, views, functions, stored procedures, triggers, indexes, and constraints
- **Detailed Metadata**: Get row counts, column information, relationships, and more
- **Pattern Matching**: Filter database objects using SQL wildcards

### 📊 Data Operations  
- **CRUD Operations**: Create, read, update, and delete rows with full transaction support
- **Query Builder**: Build complex SQL queries programmatically without writing raw SQL
- **Bulk Operations**: Efficient bulk insert, update, delete, and upsert operations
- **Transaction Management**: Execute multiple operations atomically with isolation level control

### 🚀 Advanced Features
- **Stored Procedures**: Execute procedures with input/output parameters
- **Performance**: Connection pooling and optimized batch operations
- **Security**: Permission-based access control and parameterized queries to prevent SQL injection
- **Easy Deployment**: Simple npm/npx installation

## Installation

You can run this tool directly via `npx`:

```bash
npx -y @bluspace/mssql-mcp-server \
  --user dbuser --password secret \
  --server localhost --port 1433 --database mydb \
  --permissions all
```

## Usage

The CLI accepts the following parameters:

- `--user <string>` (required): Database user
- `--password <string>` (required): Database password
- `--server <string>` (required): SQL Server hostname or IP address
- `--port <number>` (optional, default: "1433"): Database port
- `--database <string>` (required): Database name
- `--permissions <read|write|all>` (optional, default: "all"): Allowed operations

## Available Tools

### Schema Exploration Tools (available with `read` permissions)

- `listSchemas`: List all schema names in the database
- `readSchema`: Get detailed table and column information for schemas
- `listTables`: List all tables with metadata (row counts, column counts)
- `listViews`: List all views in the database
- `listFunctions`: List user-defined functions (scalar and table-valued)
- `listStoredProcedures`: List stored procedures with filtering options
- `listIndexes`: Get detailed index information for a table
- `listConstraints`: List constraints (PK, FK, CHECK, UNIQUE, DEFAULT)
- `listTriggers`: List triggers with timing and event information
- `getTableSchema`: Get complete table structure with all column details, keys, and metadata

### Query Tools (available with `read` permissions)

- `queryBuilder`: Build and execute complex SQL queries programmatically
- `executeStoredProcedure`: Execute stored procedures with parameters
- `getStoredProcedureInfo`: Get detailed information about a stored procedure

### Data Manipulation Tools (available with `write` or `all` permissions)

- `createRow`: Insert a new row into a table
- `readRows`: Read rows with filtering (supports up to 100 rows by default)
- `updateRow`: Update existing rows by primary key
- `deleteRow`: Delete rows by primary key

### Bulk Operations (available with `write` or `all` permissions)

- `bulkInsert`: Efficiently insert multiple rows with batching
- `bulkUpdate`: Update multiple rows in batches
- `bulkDelete`: Delete multiple rows in batches
- `bulkUpsert`: Insert or update using SQL Server MERGE

### Transaction Tools (available with `write` or `all` permissions)

- `executeTransaction`: Execute multiple operations in a single transaction
- `beginTransaction`: Start a long-running transaction
- `commitTransaction`: Commit an active transaction
- `rollbackTransaction`: Rollback an active transaction

### DDL (Data Definition Language) Tools (available with `write` or `all` permissions)

- `createTable`: Create new tables with columns, constraints, and indexes
- `alterTable`: Modify table structure (add/drop columns, constraints)
- `dropTable`: Drop tables with optional IF EXISTS clause
- `createIndex`: Create indexes with various options (unique, clustered, filtered, included columns)
- `dropIndex`: Drop indexes from tables
- `executeQuery`: Execute custom SQL queries with parameterized inputs (supports DDL, DML, and other operations)
- `executeSqlBatch`: Execute multiple SQL statements in sequence with automatic transaction support

## Examples

### Query Builder
```javascript
// Complex query with joins and aggregations
await mcp.call("queryBuilder", {
  select: ["c.CustomerName", "COUNT(o.OrderID) as OrderCount", "SUM(o.Total) as TotalSpent"],
  from: { table: "Customers", alias: "c" },
  joins: [{
    type: "LEFT",
    table: "Orders o",
    on: "c.CustomerID = o.CustomerID"
  }],
  where: [
    { column: "o.OrderDate", operator: ">=", value: "2024-01-01" },
    { column: "c.Country", operator: "IN", values: ["USA", "Canada"] }
  ],
  groupBy: ["c.CustomerID", "c.CustomerName"],
  having: "COUNT(o.OrderID) > 5",
  orderBy: [{ column: "TotalSpent", direction: "DESC" }],
  limit: 10
});
```

### Bulk Operations
```javascript
// Bulk insert with validation
await mcp.call("bulkInsert", {
  table: "Products",
  rows: productData, // Array of product objects
  batchSize: 1000,
  validateSchema: true
});
```

### Schema Exploration
```javascript
// Discover foreign key relationships
await mcp.call("listConstraints", {
  table: "Orders",
  type: "FOREIGN KEY"
});

// Find all tables with 'Customer' in the name
await mcp.call("listTables", {
  pattern: "%Customer%"
});
```

### DDL Operations
```javascript
// Create a new table with various column types and constraints
await mcp.call("createTable", {
  table: "Products",
  columns: [
    {
      name: "ProductID",
      type: "int",
      nullable: false,
      identity: { seed: 1, increment: 1 },
      primaryKey: true
    },
    {
      name: "ProductName",
      type: "nvarchar(100)",
      nullable: false
    },
    {
      name: "Price",
      type: "decimal(10,2)",
      nullable: false,
      check: "Price > 0"
    },
    {
      name: "CategoryID",
      type: "int",
      nullable: true,
      references: {
        table: "Categories",
        column: "CategoryID",
        onDelete: "SET NULL"
      }
    },
    {
      name: "CreatedDate",
      type: "datetime2",
      nullable: false,
      default: "GETDATE()"
    }
  ],
  constraints: {
    uniqueKeys: [{
      columns: ["ProductName"]
    }],
    checks: [{
      expression: "LEN(ProductName) > 0",
      name: "CHK_ProductName_NotEmpty"
    }]
  }
});

// Create an index with included columns
await mcp.call("createIndex", {
  indexName: "IX_Products_CategoryPrice",
  table: "Products",
  columns: [
    { name: "CategoryID", direction: "ASC" },
    { name: "Price", direction: "DESC" }
  ],
  include: ["ProductName"],
  where: "Price > 10"
});

// Execute custom DDL using executeQuery
await mcp.call("executeQuery", {
  query: "CREATE VIEW vw_ExpensiveProducts AS SELECT * FROM Products WHERE Price > 100"
});
```

### SQL Batch Execution
```javascript
// Execute multiple statements in a transaction (default behavior)
await mcp.call("executeSqlBatch", {
  statements: [
    "DELETE FROM OrderItems WHERE OrderID = 123",
    "DELETE FROM Orders WHERE OrderID = 123"
  ]
});

// Execute with parameterized queries
await mcp.call("executeSqlBatch", {
  statements: [
    {
      sql: "INSERT INTO Categories (Name) VALUES (@name)",
      parameters: { name: "Electronics" }
    },
    {
      sql: "INSERT INTO Products (Name, CategoryID, Price) VALUES (@name, @catId, @price)",
      parameters: { name: "Laptop", catId: 1, price: 999.99 }
    }
  ]
});

// Execute without transaction for independent operations
await mcp.call("executeSqlBatch", {
  statements: [
    "UPDATE Statistics SET LastUpdated = GETDATE()",
    "EXEC sp_UpdateCache",
    "DELETE FROM TempData WHERE CreatedDate < DATEADD(day, -7, GETDATE())"
  ],
  useTransaction: false,
  stopOnError: false  // Continue even if one statement fails
});

// Complex migration with DDL and DML
await mcp.call("executeSqlBatch", {
  statements: [
    "ALTER TABLE Users ADD Email nvarchar(255)",
    "UPDATE Users SET Email = Username + '@example.com' WHERE Email IS NULL",
    "ALTER TABLE Users ALTER COLUMN Email nvarchar(255) NOT NULL",
    "CREATE UNIQUE INDEX IX_Users_Email ON Users(Email)"
  ],
  isolationLevel: "SERIALIZABLE"  // Use highest isolation for schema changes
});
```

## Development

1. Clone the repository
2. Install dependencies: `npm install`
3. Build the project: `npm run build`
4. Run the CLI: `npm start -- [options]`

## Version History

### 1.2.0
- Added comprehensive DDL (Data Definition Language) tools:
  - `createTable`: Create tables with full constraint and column options
  - `alterTable`: Modify table structure (add/drop columns, constraints)
  - `dropTable`: Drop tables with IF EXISTS support
  - `createIndex`: Create indexes with advanced options (filtered, included columns)
  - `dropIndex`: Drop indexes safely
  - `executeQuery`: Execute custom SQL queries with parameterized inputs
  - `executeSqlBatch`: Execute multiple SQL statements with automatic transaction support
- Fixed queryBuilder limitation - DDL operations now supported via dedicated tools
- Fixed transaction tool complexity - new executeSqlBatch provides simple multi-statement execution

### 1.1.1
- Added `getTableSchema` tool for comprehensive table structure information
- Shows column details, data types, nullability, defaults, identity columns, computed columns
- Includes primary keys, foreign keys, and table metadata (size, row count)

### 1.1.0
- Added comprehensive schema exploration tools (tables, views, functions, indexes, constraints, triggers)
- Implemented Phase 1 developer features:
  - Query Builder for complex SQL queries
  - Stored Procedure support with input/output parameters
  - Transaction Management with isolation levels
  - Bulk Operations (insert, update, delete, upsert)
- Enhanced default behaviors (dbo schema, increased row limits)
- Improved error handling and logging

### 1.0.10
- Initial release with basic CRUD operations and schema inspection

## License

MIT