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

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

2. 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

   

3. 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

1. 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

2. 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:

1. Get OpenAI API Key
- Visit https://platform.openai.com/api-keys
- Create a new secret key
- Add it to your .env file

2. 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

1. Clone the Repository

   git clone <repository-url>

   cd MCP-SERVER

   

2. Install Dependencies

   npm install

   

3. 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).

4. Build the Project

   npm run build

   

5. 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:

…