@bluspace/mssql-mcp-server

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:

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

// 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

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

Schema Exploration

// 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

// 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

// 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