Sql MCP Server

🚀 SQL-MCP-Server

A SQL server based on the MCP framework for interacting with SQL Server databases.

🚀 Quick Start

# Install dependencies
npm install

# Build the project
npm run build

# Start the service
npm start

📦 Project Structure

sql-mcp-server/
├── src/
│   ├── services/       # Service modules of the application
│   │   └── sql-service.ts
│   ├── tools/          # MCP toolset
│   │   ├── execute-select-query-tool.ts
│   │   ├── get-database-schema-tool.ts
│   │   └── get-databases-tool.ts
│   └── index.ts        # Entry file of the service program
├── .env                # Environment variable configuration file
├── package.json
└── tsconfig.json

💻 Available Tools

The project provides the following SQL-related tools:

1. get-databases-tool - List available databases on the SQL Server.
2. get-database-schema-tool - Get the schema information of a specified database.
3. execute-select-query-tool - Execute parameterized SELECT query statements.

🔧 SQL Service

The SqlService class provides core functions, including:

• Generate a connection string using environment variables.
• Execute query operations on the SQL Server database.

⚙️ Environment Configuration

Configure the database connection information in the .env file:

DB_SERVER=localhost\SQLEXPRESS
DB_DATABASE=AdventureWorks2019
DB_USERNAME=readonly_user
DB_PASSWORD=StrongPassword!123
DB_PORT=1433

AdventureWorks Sample Database

This project uses the AdventureWorks sample database. You can download and install it from the following link:

• Microsoft Official Documentation: Install and Configure AdventureWorks

Download the database backup file according to the instructions in the Microsoft documentation and restore it to your SQL Server instance.

Create a Read-Only User

To create a user for reading data only:

-- Create a new login account and user in the master database
CREATE LOGIN readonly_user WITH PASSWORD = 'StrongPassword!123';
GO

-- Switch to the target database AdventureWorks2019
USE AdventureWorks2019;
GO

-- Create a database user for the MasterDB login
CREATE USER readonly_user FOR LOGIN readonly_user;

-- Add the user to the db_datareader role for read-only access
ALTER ROLE db_datareader ADD MEMBER readonly_user;
GO

⚠️ Important Note

You need to create a login account in the master database using SQL Server Management Studio or the CREATE LOGIN command first, and then run the above commands.

🔌 Integration with VS Code

To configure the MCP server in VS Code, follow these steps:

1. Create a .vscode/mcp.json file in the project root directory:
   • If there is no .vscode directory, create one first.
   • Add the mcp.json file to this directory with the following content:

{
  "servers": {
    "sql-mcp-server": {
      "url": "http://localhost:1337/mcp"
    }
  }
}

🛠️ Build and Test

1. Modify your tool code.
2. Run npm run build for compilation.
3. After starting, the server will automatically load your tools.

🐞 Debugging Guide

You can use the mcp-debug tool for debugging in the following way:

# Start the service in one terminal
npm start

# Run mcp-debug in another terminal
npx mcp-debug

The mcp-debug tool provides a command-line interface for:

• Listing available tools on the MCP server.
• Executing tools with specific parameters.
• Viewing the complete results returned by the server.

📚 Learning Resources

• MCP Framework Official Documentation
• TypeScript Getting Started Guide
• Node.js Official Tutorial