rudhrasingh/sqlite_mcp_server

SQLite MCP Server

A Model Context Protocol (MCP) server implementation that manages an employee database using SQLite. This project provides a robust database system for managing employee and department information with features for data generation and querying.

Features

• Employee Management: Store and manage employee information including:

  • Personal details (first name, last name, date of birth)
  • Professional information (salary, hire date, email)
  • Department association

• Department Management: Handle department data including:

  • Department names with dynamic generation
  • Location tracking
  • Budget allocation
  • Department manager assignment

• Flexible Database Initialization:

  • Configurable number of employees (default: 100)
  • Customizable database path
  • Force override option for existing databases
  • Batch processing for large datasets

Getting Started

Prerequisites

• Python 3.x
• Virtual Environment (venv)

Installation

1. Clone the repository:

git clone <repository-url>
cd sqlite_mcp_server

2. Set up and activate the virtual environment:

python -m venv py_mcp
source py_mcp/bin/activate  # On Unix/macOS

3. Install required packages:

pip install -r requirements.txt

Usage

Initialize the Database

Basic initialization with default settings:

python database.py

Custom initialization with options:

python database.py --employee-count 200 --force --db-path custom.db

Command Line Arguments

• --db-path: Specify custom database file path (default: company.db)
• --employee-count: Set number of employees to generate (default: 100)
• --force: Override existing database if it exists

Data Structure

Employees Table

• id: Primary Key
• first_name: Employee's first name
• last_name: Employee's last name
• email: Unique email address
• department_id: Foreign key to departments
• salary: Employee's salary
• hire_date: Date of hiring
• date_of_birth: Employee's birth date

Departments Table

• id: Primary Key
• name: Department name
• location: Department location
• budget: Department budget
• manager_id: Foreign key to employees

Features in Detail

1. Dynamic Department Generation: Automatically generates department names by combining:

   • Base department names
   • Geographic divisions
   • Roman numerals

2. Unique Email Generation: Ensures unique email addresses for all employees

3. Realistic Data Generation:

   • Random but realistic salary ranges
   • Sensible hire dates and birth dates
   • Varied department locations and budgets

MCP Configuration

Setting up MCP in VS Code

1. Create a .vscode folder in your project root if it doesn't exist
2. Create an mcp.json file inside the .vscode folder with the following structure:

{
    "servers": {
        "sqlite-mcp-server": {
            "type": "stdio",
            "command": "<path-to-your-venv>/bin/python",
            "args": [
                "<path-to-your-project>/server.py"
            ]
        }
    },
    "inputs": []
}

Configuration Details

• servers: Defines the MCP server configurations
  • sqlite-mcp-server: The name of your MCP server (can be customized)
  • type: Set to "stdio" for standard input/output communication
  • command: Path to your Python interpreter in your virtual environment
  • args: Array containing the path to your server.py file

Example Configuration

For a typical setup, your paths might look like:

{
    "servers": {
        "sqlite-mcp-server": {
            "type": "stdio",
            "command": "/path/to/your/project/py_mcp/bin/python",
            "args": [
                "/path/to/your/project/server.py"
            ]
        }
    },
    "inputs": []
}

Make sure to:

1. Use absolute paths for both the Python interpreter and server.py
2. Update the paths according to your project location
3. Ensure the Python interpreter path points to your virtual environment

Contributing

Feel free to submit issues and enhancement requests!