MCP Server with SQL Database Integration

This is a Model Context Protocol (MCP) server built with TypeScript and Express.js that provides comprehensive integration with SQL databases, Jira API, and intelligent AI-powered tools for data processing and analysis. The server uses Server-Sent Events (SSE) for real-time communication and features persistent storage for enhanced user experience.

Key Features

🗄️ SQL Database Integration

Complete CRUD operations for Users, Vendors, Commodities, Currencies, and Roles
Advanced search and pagination capabilities
Robust connection management with SQL Server
Database health monitoring and connection testing

🎯 Jira Integration

Comprehensive Jira API integration with JQL (Jira Query Language) support
Intelligent JQL query generation using AI
Persistent JQL example storage with similarity-based deduplication
Issue creation, searching, and management
Sprint and project analytics

🤖 AI-Powered Features

OpenAI integration for intelligent tool routing and data processing
Real-time streaming responses for better user experience
Smart JQL query generation with learning capabilities
Contextual data analysis and markdown formatting

📊 Data Management

Products from DummyJSON API integration
Users from DummyJSON API integration
Real-time application status monitoring
Persistent storage with automatic file synchronization

🔄 Real-time Communication

Server-Sent Events (SSE) for streaming responses
Multiple concurrent session management
Graceful error handling and connection management

Available Tools

🗄️ SQL Database Tools

User Management

get-users - Get a list of users with optional pagination
get-user-by-id - Get detailed information about a specific user by ID
search-users - Search for users by keyword

Vendor Management

get-vendors - Get a list of vendors with optional pagination
get-vendor-by-id - Get detailed information about a specific vendor by ID
search-vendors - Search for vendors by keyword

Commodity Management

get-commodities - Get a list of commodities with optional pagination
get-commodity-by-id - Get detailed information about a specific commodity by ID
search-commodities - Search for commodities by keyword

Currency Management

get-currencies - Get a list of currencies with optional pagination
get-currency-by-id - Get detailed information about a specific currency by ID
search-currencies - Search for currencies by keyword

Role Management

get-roles - Get a list of roles with optional pagination
get-role-by-id - Get detailed information about a specific role by ID
search-roles - Search for roles by keyword

🎯 Jira Integration Tools

get-jira-issue-by-id - Get detailed information about a specific Jira issue
search-jira-issues - Search for Jira issues using intelligent JQL generation
create-jira-issue - Create new Jira issues with specified project, summary, and issue type
create-jira-subtask - Create subtasks under existing Jira issues

📊 Product & External API Tools

get-products - Get a list of products with optional pagination from DummyJSON API
get-product-by-id - Get detailed information about a specific product by ID
search-products - Search for products by keyword
get-products-by-category - Get all products in a specific category
get-categories - Get a list of all product categories

🔍 Application Monitoring

get-application-status - Get real-time status information for specific applications in different environments

🗄️ SQL Database Integration

This MCP server includes comprehensive SQL Server integration for enterprise-grade data management.

Database Setup

Install and Configure SQL Server
- Ensure SQL Server is installed and running
- Create your target database

Configure Database Connection

Create a .env file in the project root with your database credentials:

# SQL Server Configuration
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_HOST=your_server_host
MSSQL_DATABASE=your_database_name

# Alternative: Connection String
MSSQL_CONNECTION_STRING=Server=yourserver;Database=yourdatabase;User Id=username;Password=password;Encrypt=false;TrustServerCertificate=true;

# OpenAI Configuration (Required for AI features)
OPENAI_API_KEY=your_openai_api_key
LLM_MODEL=gpt-4

# Jira Configuration (Optional)
JIRA_URL=https://your-domain.atlassian.net
JIRA_USERNAME=your_email@example.com
JIRA_API_TOKEN=your_jira_api_token

Database Schema

The server expects the following tables in your SQL database:
- Users - User management with roles and authentication details
- Vendors - Vendor/supplier information
- Commodities - Product commodities data
- Currencies - Currency information
- Roles - User role definitions

Testing Database Connection

Verify your database setup:

npm run build
npm run test-connection

🎯 Jira Integration

Setup Jira Integration

Create Jira API Token
- Go to https://id.atlassian.com/manage-profile/security/api-tokens
- Create a new API token
- Add it to your .env file

Configure Jira Settings

JIRA_URL=https://your-domain.atlassian.net
JIRA_USERNAME=your_email@example.com
JIRA_API_TOKEN=your_jira_api_token

Intelligent JQL Features

The server includes advanced JQL (Jira Query Language) capabilities:

AI-Powered JQL Generation: Converts natural language queries to JQL
Persistent Learning: Stores successful JQL examples for improved future queries
Smart Deduplication: Prevents duplicate JQL examples using similarity detection
Real-time Synchronization: JQL examples are immediately saved to data/jql-examples.json

Example JQL Queries

The system learns from usage and can generate JQL for queries like:

"Show me open bugs in the current sprint"
"Get all tasks assigned to John Doe"
"Find issues created this week with high priority"
"Display all closed sprints for velocity analysis"

🤖 AI Integration

OpenAI Configuration

The server uses OpenAI for intelligent data processing and query generation:

Get OpenAI API Key
- Visit https://platform.openai.com/api-keys
- Create a new secret key
- Add it to your .env file
Supported Models
- GPT-4 (recommended)
- GPT-3.5-turbo
- Any OpenAI-compatible model

AI Features

Intelligent Tool Routing: Automatically determines which tool to use based on user queries
Real-time Streaming: Responses stream in real-time for better user experience
Context-Aware Processing: Maintains context across multiple interactions
Data Formatting: Automatically formats responses as markdown tables, charts, or text

🚀 Installation & Setup

Prerequisites

Node.js (v16 or higher)
npm or yarn
SQL Server (for database features)
OpenAI API key (for AI features)
Jira account (optional, for Jira integration)

Installation Steps

Clone the Repository

git clone <repository-url>
cd MCP-SERVER

Install Dependencies
```
npm install
```
Environment Configuration

Copy the example environment file and configure your settings:
```
copy .env.example .env
```
Edit .env with your specific configuration (see setup sections above).
Build the Project
```
npm run build
```
Start the Server
```
npm run start
```

Development Mode

For development with auto-recompilation and hot-reload:

npm run dev

🔧 VS Code Integration

Available Tasks

The project includes VS Code tasks for easy development:

Press Ctrl+Shift+P and select "Tasks: Run Task"
Choose from available tasks:
- Build MCP Server: Compiles TypeScript code
- Start MCP Server: Builds and starts the server
- Dev MCP Server (Watch Mode): Development mode with auto-reload

Debugging

Debug the MCP server in VS Code:

Press F5 or select "Run > Start Debugging"
Choose the "Launch MCP Server" configuration
View debug output in the Debug Console

Using with AI Assistants

Start the MCP server using the tasks above
Connect your AI assistant (like GitHub Copilot) to the server
Use natural language to request data and perform operations

📁 Project Structure

🏗️ Architecture Overview

graph TD
    A[🎯 MCP Server] --> B[🎮 Controllers Layer]
    A --> C[🏢 Services Layer]
    A --> D[📋 Models Layer]
    A --> E[⚙️ Configuration Layer]
    
    B --> B1[👥 User Controller]
    B --> B2[🎫 Jira Controller]
    B --> B3[📦 Product Controller]
    B --> B4[🏪 Vendor Controller]
    B --> B5[📊 Commodity Controller]
    B --> B6[💰 Currency Controller]
    B --> B7[🔐 Role Controller]
    
    C --> C1[🗄️ SQL Services]
    C --> C2[🎫 Jira Service]
    C --> C3[📦 Product Service]
    C --> C4[👥 User Service]
    
    C1 --> C1A[SQL User Service]
    C1 --> C1B[SQL Vendor Service]
    C1 --> C1C[SQL Commodity Service]
    C1 --> C1D[SQL Currency Service]
    C1 --> C1E[SQL Role Service]
    
    D --> D1[🗃️ Database Models]
    D --> D2[🌐 API Models]
    D --> D3[📝 Type Definitions]
    
    E --> E1[💾 Database Config]
    E --> E2[🌐 API Config]
    E --> E3[🗄️ MSSQL Config]
    
    F[🌍 External Systems] --> F1[🗄️ SQL Server Database]
    F --> F2[🎫 Jira API]
    F --> F3[📦 DummyJSON API]
    F --> F4[🤖 OpenAI API]
    
    G[💾 Data Storage] --> G1[📄 JQL Examples JSON]
    G --> G2[📊 Application Data]
    
    style A fill:#e1f5fe
    style B fill:#f3e5f5
    style C fill:#e8f5e8
    style D fill:#fff3e0
    style E fill:#fce4ec
    style F fill:#e0f2f1
    style G fill:#f1f8e9

🔄 Data Flow Architecture

sequenceDiagram
    participant 👤 Client
    participant 🌐 SSE Transport
    participant 🤖 AI Router
    participant 🎮 Controller
    participant 🏢 Service
    participant 🗄️ Database
    participant 🧠 OpenAI
    
    👤->>🌐: 📝 Natural Language Request
    🌐->>🤖: 🔀 Route Message
    🤖->>🧠: 🔍 Analyze Intent
    🧠-->>🤖: 🎯 Tool Selection
    🤖->>🎮: ⚡ Execute Tool
    🎮->>🏢: 🛠️ Business Logic
    🏢->>🗄️: 📊 Data Query
    🗄️-->>🏢: 📈 Raw Data
    🏢-->>🎮: 🔧 Processed Data
    🎮->>🧠: 📋 Format Request
    🧠-->>🎮: 📄 Formatted Response
    🎮-->>🌐: 🌊 Stream Response
    🌐-->>👤: ⚡ Real-time Output

📂 Detailed File Structure

MCP-SERVER/
├── 📁 data/                          # 💾 Persistent Data Storage
│   └── 📄 ...                        # 📄 Configuration files
│
├── 📁 src/                           # 💻 Source Code
│   ├── 📄 ...                        # 📄 TypeScript files
│   │
│   ├── 📁 app-data/                  # 📊 Application Data
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 config/                    # ⚙️ Configuration Files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 controllers/               # 🎮 MCP Tool Controllers
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 db-models/                 # 🗃️ Database Models
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 llm-api/                   # 🤖 AI Integration
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 models/                    # 📋 Data Models
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 modules/                   # 🔧 Core Server Modules
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 services/                  # 🏢 Business Logic Services
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   ├── 📁 types/                     # 📝 TypeScript Definitions
│   │   ├── 📄 ...                    # 📄 TypeScript files
│   │   └── 📄 ...                    # 📄 TypeScript files
│   │
│   └── 📁 utils/                     # 🛠️ Utility Functions
│       └── 📄 ...                    # 📄 TypeScript files
│
├── 📁 scripts/                       # 🔨 Build & Deployment
│   └── 📄 ...                        # 📄 JavaScript files
│
├── 📄 ...                            # 📄 HTML files
├── 📄 ...                            # 📄 Configuration files
├── 📄 ...                            # 📄 Configuration files
├── 📄 ...                            # 📄 Configuration files
└── 📄 ...                            # 📄 Documentation files

🔀 Component Interaction Map

graph LR
    subgraph "🌍 External APIs"
        EXT1[🗄️ SQL Server]
        EXT2[🎫 Jira API]
        EXT3[📦 DummyJSON]
        EXT4[🤖 OpenAI]
    end
    
    subgraph "🎯 MCP Server Core"
        SSE[🌐 SSE Transport]
        ROUTE[🤖 AI Router]
        CTRL[🎮 Controllers]
        SVC[🏢 Services]
    end
    
    subgraph "💾 Data Layer"
        DB[(🗄️ Database)]
        FILES[📄 JSON Files]
    end
    
    CLIENT[👤 Client] --> SSE
    SSE --> ROUTE
    ROUTE --> CTRL
    CTRL --> SVC
    
    SVC --> EXT1
    SVC --> EXT2
    SVC --> EXT3
    SVC --> EXT4
    
    SVC --> DB
    SVC --> FILES
    
    style CLIENT fill:#e3f2fd
    style SSE fill:#f1f8e9
    style ROUTE fill:#fff3e0
    style CTRL fill:#fce4ec
    style SVC fill:#e8f5e8
    style DB fill:#e0f2f1
    style FILES fill:#f3e5f5

🔍 API Reference & Data Sources

External APIs

DummyJSON Products: https://dummyjson.com/products
DummyJSON Users: https://dummyjson.com/users
Jira REST API: Atlassian Cloud/Server REST API v2/v3
OpenAI API: GPT models for intelligent processing

Response Formats

The server supports multiple response formats:

Markdown Tables: Structured data in table format
Markdown Text: Formatted text with analysis
Charts: Data visualization using chart libraries
JSON: Raw structured data

🚦 Server Communication

Server-Sent Events (SSE)

The server uses SSE for real-time communication:

Endpoint: /sse/stream
Message Endpoint: /messages
Session Management: Automatic session handling with IDs
Streaming Responses: Real-time data streaming for better UX

CORS Configuration

The server is configured to handle cross-origin requests with appropriate CORS headers.

💡 Usage Examples

Basic Queries

# Get all users with pagination
curl "http://localhost:3000/messages" -X POST \
  -H "Content-Type: application/json" \
  -d '{"message": "show me all users"}'

# Search for specific vendor
curl "http://localhost:3000/messages" -X POST \
  -H "Content-Type: application/json" \
  -d '{"message": "find vendor with name containing Microsoft"}'

# Get Jira issues
curl "http://localhost:3000/messages" -X POST \
  -H "Content-Type: application/json" \
  -d '{"message": "show me open bugs in current sprint"}'

Natural Language Queries

The AI-powered server understands natural language:

"Show me all high-priority bugs assigned to John"
"Get the last 10 vendors created this month"
"Find all USD currency transactions"
"Display sprint velocity chart for closed sprints"
"List all users with admin role"

JQL Learning Examples

The server learns from JQL usage:

User Query: "show me open bugs"
Generated JQL: project = SCRUM AND issuetype = Bug AND status != "Done" ORDER BY priority DESC
Saved: ✅ (New example learned)

User Query: "get open bugs"  
Generated JQL: project = SCRUM AND issuetype = Bug AND status != "Done" ORDER BY priority DESC
Saved: ❌ (Duplicate detected - 85% similarity)

🛠️ Customization

Adding New Tools

Create Controller

// src/controllers/myController.ts
export function registerMyTools(server: McpServer) {
  server.tool('my-tool', 'Description', schema, handler);
}

Register in Main

// src/index.ts
import { registerMyTools } from './controllers/myController.js';
registerMyTools(server);

Extending Database Models

Add Interface

// src/db-models/myModel.ts
export interface MyModel {
  Id: number;
  Name: string;
  // ... other fields
}

Create Service

// src/services/myService.ts
export class MyService {
  async getAll(): Promise<MyModel[]> {
    // Implementation
  }
}

Customizing AI Prompts

Edit prompts in src/llm-api/prompts.ts:

export const MyCustomPrompt = `
Your custom system prompt here...
Format responses as markdown tables.
`;

🐛 Troubleshooting

Common Issues

Database Connection Failed
- Verify SQL Server is running
- Check credentials in .env file
- Test with npm run test-connection
OpenAI API Errors
- Verify API key is valid
- Check model availability
- Monitor API usage limits
Jira Authentication Failed
- Ensure API token is correct
- Verify Jira URL format
- Check user permissions
Port Already in Use
- Change port in configuration
- Kill existing processes: npx kill-port 3000

Debug Mode

Enable detailed logging:

DEBUG=mcp:* npm run start

Log Files

Check logs in:

Console output for real-time logs
data/jql-examples.json for JQL learning progress
SQL Server logs for database issues

📈 Performance Monitoring

Metrics Available

Database connection health
API response times
JQL query success rates
Memory usage patterns
Active session counts

Health Checks

# Database health
curl "http://localhost:3000/messages" -X POST \
  -H "Content-Type: application/json" \
  -d '{"message": "check database health"}'

# Application status
curl "http://localhost:3000/messages" -X POST \
  -H "Content-Type: application/json" \
  -d '{"message": "get application status for MyApp in production"}'

🤝 Contributing

Fork the repository
Create a feature branch
Make your changes
Add tests if applicable
Submit a pull request

Development Guidelines

Follow TypeScript best practices
Add proper error handling
Include JSDoc comments
Test database operations
Validate all inputs with Zod schemas

📄 License

This project is licensed under the ISC License - see the package.json file for details.

🆘 Support

For support and questions:

Check the troubleshooting section above
Review the project issues on GitHub
Consult the MCP Protocol documentation
Check OpenAI API documentation for AI-related issues

Built with ❤️ using the Model Context Protocol (MCP)

Name		Name	Last commit message	Last commit date
Latest commit History 61 Commits
.github		.github
.vscode		.vscode
data		data
scripts		scripts
src		src
.env.example		.env.example
.gitignore		.gitignore
README.md		README.md
package-lock.json		package-lock.json
package.json		package.json
package.vscode.json		package.vscode.json
tsconfig.json		tsconfig.json

shahidain/mcp-server

Folders and files

Latest commit

History

Repository files navigation

MCP Server with SQL Database Integration

Key Features

🗄️ SQL Database Integration

🎯 Jira Integration

🤖 AI-Powered Features

📊 Data Management

🔄 Real-time Communication

Available Tools

🗄️ SQL Database Tools

User Management

Vendor Management

Commodity Management

Currency Management

Role Management

🎯 Jira Integration Tools

📊 Product & External API Tools

🔍 Application Monitoring

🗄️ SQL Database Integration

Database Setup

Testing Database Connection

🎯 Jira Integration

Setup Jira Integration

Intelligent JQL Features

Example JQL Queries

🤖 AI Integration

OpenAI Configuration

AI Features

🚀 Installation & Setup

Prerequisites

Installation Steps

Development Mode

🔧 VS Code Integration

Available Tasks

Debugging

Using with AI Assistants

📁 Project Structure

🏗️ Architecture Overview

🔄 Data Flow Architecture

📂 Detailed File Structure

🔀 Component Interaction Map

🔍 API Reference & Data Sources

External APIs

Response Formats

🚦 Server Communication

Server-Sent Events (SSE)

CORS Configuration

💡 Usage Examples

Basic Queries

Natural Language Queries

JQL Learning Examples

🛠️ Customization

Adding New Tools

Extending Database Models

Customizing AI Prompts

🐛 Troubleshooting

Common Issues

Debug Mode

Log Files

📈 Performance Monitoring

Metrics Available

Health Checks

🤝 Contributing

Development Guidelines

📄 License

🆘 Support

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Languages

Packages