Database Tools Server

📋 Table of Contents

• Overview
• Architecture
• Project Structure
• Component Flow
• Installation & Setup
• Usage
• Configuration
• Troubleshooting

---

🎯 Overview

This project is a Model Context Protocol (MCP) Server that automates Django database setup and management tasks. It provides tools to:

• Create PostgreSQL databases
• Enable PostgreSQL extensions (hstore)
• Update Django .env configuration files
• Execute Django management commands

The server integrates with VS Code Copilot and can be accessed via:

• VS Code MCP integration
• Automated workflow scripts

---

🏗️ Architecture

┌─────────────────────────────────────────────────────────────────┐
│                        MCP Client Layer                          │
│  ┌──────────────┐  ┌──────────────┐                           │
│  │  VS Code     │  │   Workflow   │                           │
│  │  Copilot     │  │   Scripts    │                           │
│  └──────┬───────┘  └──────┬───────┘                           │
└─────────┼──────────────────┼──────────────────┼──────────────────┘
          │                  │                  │
          └──────────────────┼──────────────────┘
                             │
                    ┌────────▼────────┐
                    │   MCP Server    │
                    │   (server.py)   │
                    │                 │
                    │  - list_tools() │
                    │  - call_tool()  │
                    └────────┬────────┘
                             │
          ┌──────────────────┼──────────────────┐
          │                  │                  │
    ┌─────▼─────┐     ┌─────▼─────┐     ┌─────▼─────┐
    │PostgreSQL │     │  Django   │     │   .env    │
    │ Database  │     │  Backend  │     │   File    │
    └───────────┘     └───────────┘     └───────────┘

---

📁 Project Structure

MCP_project/
│
├── server.py                  # Main MCP server implementation
├── mcp.json                   # MCP server metadata
├── requirements.txt           # Python dependencies
│
* Web and CLI clients removed: The project no longer includes web_client.py or test_client.py files.
├── run_workflow.py           # Automated workflow executor
│
├── tools/                    # Utility modules (legacy/reference)
│   ├── __init__.py
│   ├── db_tools.py          # PostgreSQL database operations
│   ├── env_tools.py         # Environment file management
│   └── django_tools.py      # Django command execution
│
├── templates/               # Web UI templates
│   └── index.html          # Main web interface
│
└── venv/                   # Python virtual environment

---

🔄 Component Flow

1. Core Server (server.py)

The heart of the system, implementing the MCP protocol:

┌─────────────────────────────────────────────────────────┐
│                    server.py                             │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  Configuration:                                          │
│  ├─ PG_USER, PG_PASSWORD, PG_HOST                       │
│  ├─ DB_NAME (default: sample_project_db)                │
│  ├─ ENV_PATH (Django .env location)                     │
│  ├─ MANAGE_PY (Django manage.py location)               │
│  └─ PYTHON_EXEC (Virtual environment Python)            │
│                                                          │
│  MCP Server Decorators:                                 │
│  ├─ @server.list_tools() → Returns available tools      │
│  └─ @server.call_tool() → Executes tool operations      │
│                                                          │
│  Tools Implemented:                                      │
│  ├─ create_database(db_name)                            │
│  ├─ enable_hstore(db_name)                              │
│  ├─ update_env(db_name)                                 │
│  └─ django(cmd)                                          │
└─────────────────────────────────────────────────────────┘

Key Features:

• Async/await architecture for MCP protocol compliance
• stdio communication (not HTTP) for MCP client integration
• Automatic lowercase conversion for PostgreSQL database names
• Environment variable loading from .env files for Django commands
• Virtual environment Python execution to ensure dependencies

---

2. Tool: create_database

Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase ("mydb")
│
├─ Connects to PostgreSQL server (postgres database)
│  └─ Uses: PG_USER, PG_PASSWORD, PG_HOST
│
├─ Executes: CREATE DATABASE mydb;
│
└─ Returns: "Database mydb created."

PostgreSQL Connection:

psycopg2.connect(
    dbname="postgres",
    user=PG_USER, 
    password=PG_PASSWORD,
    host=PG_HOST
)

---

3. Tool: enable_hstore

Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase
│
├─ Connects to the specified database
│
├─ Executes: CREATE EXTENSION IF NOT EXISTS hstore;
│
└─ Returns: "hstore extension enabled in mydb."

Purpose: Enables PostgreSQL's hstore extension for key-value pair storage.

---

4. Tool: update_env

Input: { db_name: "mydb" }
│
├─ Converts db_name to lowercase
│
├─ Reads ENV_PATH file
│
├─ Finds line: POSTGRES_DB_NAME=old_value
│  └─ Skips commented lines (#)
│
├─ Replaces with: POSTGRES_DB_NAME=mydb
│
└─ Returns: ".env updated: POSTGRES_DB_NAME=mydb"

File Operations:

• Preserves all other .env content
• Only updates non-commented POSTGRES_DB_NAME lines
• Maintains file structure and formatting

---

5. Tool: django

Input: { cmd: "migrate" }
│
├─ Loads environment from ENV_PATH using dotenv
│  └─ Merges with os.environ
│
├─ Executes: PYTHON_EXEC MANAGE_PY migrate
│  └─ In working directory: dirname(MANAGE_PY)
│  └─ With loaded environment variables
│
├─ Captures stdout and stderr
│
└─ Returns: Command output with exit code

Execution Flow:

subprocess.run(
    [PYTHON_EXEC, MANAGE_PY] + cmd.split(),
    cwd=workdir,
    env=env,  # Loaded from .env
    capture_output=True,
    text=True
)

Why Virtual Environment Python?

• Django and dependencies installed in virtual environment
• System Python lacks required packages
• Ensures consistent execution environment

---

Client Interfaces

Access to the server is primarily via VS Code MCP integration and the automated workflow script.

C. Workflow Automation (run_workflow.py)

Complete Database Setup Workflow
┌─────────────────────────────────────────┐
│  Step 1: Create database                 │
│  Step 2: Enable hstore extension         │
│  Step 3: Update .env file                │
│  Step 4: Run create_text_search_config   │
│  Step 5: Run migrations                  │
│  Step 6: Run update_fixtures             │
└─────────────────────────────────────────┘

Usage:

python run_workflow.py mydb

What It Does:

1. Creates PostgreSQL database "mydb"
2. Enables hstore extension
3. Updates .env with POSTGRES_DB_NAME=mydb
4. Runs Django setup commands in sequence
5. Reports success/failure for each step

---

🛠️ Installation & Setup

Prerequisites

• Python 3.12+
• PostgreSQL server running
• Django project (optional, for Django commands)

Step 1: Clone/Setup Project

cd /home/chaitanyaphani/MCP_project

Step 2: Create Virtual Environment

python3 -m venv venv
source venv/bin/activate  # Linux/Mac
# or
venv\Scripts\activate  # Windows

Step 3: Install Dependencies

pip install -r requirements.txt

Dependencies:

• mcp - Model Context Protocol SDK
• Flask - Web UI framework
• psycopg2-binary - PostgreSQL adapter
• python-dotenv - Environment file support

Step 4: Configure PostgreSQL

Edit server.py:

PG_USER = "postgres"
PG_PASSWORD = "your_password"  # Update this!
PG_HOST = "localhost"

Step 5: Configure Django Paths

Edit server.py:

ENV_PATH = "/path/to/your/django/.env"
MANAGE_PY = "/path/to/your/django/manage.py"
PYTHON_EXEC = "/path/to/your/django/venv/bin/python"

Step 6: Configure VS Code (Optional)

Edit VS Code settings (settings.json):

{
  "mcpServers": {
    "dbtools": {
      "command": "python",
      "args": ["server.py"],
      "cwd": "/home/chaitanyaphani/MCP_project"
    }
  }
}

---

🚀 Usage

Method 1: Automated Workflow

python run_workflow.py database_name

Method 2: VS Code Copilot

Once configured, simply ask: Once configured, simply ask:

"Create a database named myproject, enable hstore, 
update the .env file, and run migrations"

---

⚙️ Configuration

Environment Variables

The server uses these configuration constants:

Variable	Purpose	Default
PG_USER	PostgreSQL username	postgres
PG_PASSWORD	PostgreSQL password	root
PG_HOST	PostgreSQL host	localhost
PG_PORT	PostgreSQL port	5432
DB_NAME	Default database name	sample_project_db
ENV_PATH	Django .env file path	/path/to/.env
MANAGE_PY	Django manage.py path	/path/to/manage.py
PYTHON_EXEC	Virtual env Python	/path/to/venv/bin/python

Django .env File Format

Expected format:

POSTGRES_DB_HOST=localhost
POSTGRES_DB_PORT=5432
POSTGRES_DB_NAME=mydb
POSTGRES_DB_USER=postgres
POSTGRES_DB_PASSWORD=password

---

🔍 Troubleshooting

Issue 1: "AttributeError: 'Server' object has no attribute 'define_tool'"

Cause: Using incorrect MCP decorator syntax.
Solution: Use @server.list_tools() and @server.call_tool() instead of @server.define_tool.

Issue 2: "password authentication failed for user 'postgres'"

Cause: Incorrect PostgreSQL password.
Solution: Update PG_PASSWORD in server.py with your actual PostgreSQL password.

Issue 3: "ModuleNotFoundError: No module named 'django'"

Cause: Using system Python instead of virtual environment Python.
Solution: Ensure PYTHON_EXEC points to your Django project's virtual environment Python.

Issue 4: "database 'XX' does not exist" (uppercase names)

Cause: PostgreSQL converts unquoted identifiers to lowercase.
Solution: Server now automatically converts database names to lowercase.

Issue 5: ".env updated but database name not changed"

Cause: Looking for wrong variable name in .env file.
Solution: Ensure your .env uses POSTGRES_DB_NAME= (not POSTGRES_DB=).

Issue 6: "Tables not created after migrate"

Cause: Environment variables not loaded, or wrong Python executable.
Solutions:

• Verify PYTHON_EXEC points to correct virtual environment
• Check .env file is loaded and contains correct database name
• Run migrate manually to see detailed errors

---

📊 Data Flow Diagram

Complete Workflow Example

User Request: "Create database 'myapp'"
│
├─ VS Code Copilot/Web UI/CLI
│  └─ Sends MCP request to server.py
│
├─ server.py receives call_tool("create_database", {"db_name": "myapp"})
│  │
│  ├─ Step 1: create_database
│  │  ├─ Convert "myapp" → "myapp" (lowercase)
│  │  ├─ Connect to PostgreSQL
│  │  ├─ Execute: CREATE DATABASE myapp;
│  │  └─ Return: "Database myapp created."
│  │
│  ├─ Step 2: enable_hstore
│  │  ├─ Connect to "myapp" database
│  │  ├─ Execute: CREATE EXTENSION IF NOT EXISTS hstore;
│  │  └─ Return: "hstore extension enabled in myapp."
│  │
│  ├─ Step 3: update_env
│  │  ├─ Read /path/to/.env
│  │  ├─ Find: POSTGRES_DB_NAME=olddb
│  │  ├─ Replace with: POSTGRES_DB_NAME=myapp
│  │  ├─ Write back to file
│  │  └─ Return: ".env updated: POSTGRES_DB_NAME=myapp"
│  │
│  └─ Step 4: django("migrate")
│     ├─ Load .env into environment
│     ├─ Execute: /venv/bin/python manage.py migrate
│     │  └─ Django reads POSTGRES_DB_NAME=myapp from env
│     │  └─ Connects to "myapp" database
│     │  └─ Applies migrations
│     └─ Return: Migration output
│
└─ Result returned to user

---

🎓 Key Concepts

Model Context Protocol (MCP)

• Protocol for AI assistants to interact with tools
• stdio-based communication (not HTTP)
• Async/await pattern required
• Tool registration via list_tools()
• Tool execution via call_tool()

Why This Architecture?

1. Separation of Concerns: Server logic separate from client interfaces
2. Multiple Interfaces: Same server, different access methods
3. Type Safety: MCP protocol with schema validation
4. Error Handling: Comprehensive error reporting
5. Environment Isolation: Uses virtual environment Python

PostgreSQL Naming Rules

• Unquoted identifiers converted to lowercase
• CREATE DATABASE MyDB creates mydb
• Server automatically handles this conversion

---

📝 Tool Reference

create_database

{
  "name": "create_database",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": "Database {db_name} created."
}

enable_hstore

{
  "name": "enable_hstore",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": "hstore extension enabled in {db_name}."
}

update_env

{
  "name": "update_env",
  "arguments": {
    "db_name": "string (optional, default: sample_project_db)"
  },
  "returns": ".env updated: POSTGRES_DB_NAME={db_name}"
}

django

{
  "name": "django",
  "arguments": {
    "cmd": "string (required) - Django management command"
  },
  "returns": "Command output (stdout/stderr)"
}

Common Django Commands:

• migrate - Apply database migrations
• makemigrations - Create new migrations
• create_text_search_config - Custom command
• update_fixtures - Custom fixture management
• runserver - Start development server

---

🤝 Contributing

To extend this server with new tools:

1. Add tool definition in list_tools():

Tool(
    name="my_new_tool",
    description="What it does",
    inputSchema={
        "type": "object",
        "properties": {
            "param1": {"type": "string", "description": "..."}
        },
        "required": ["param1"]
    }
)

2. Add tool implementation in call_tool():

elif name == "my_new_tool":
    param1 = arguments.get("param1")
    # Your logic here
    return [TextContent(type="text", text="Result")]

---

📞 Support

For issues or questions:

1. Check the Troubleshooting section
2. Verify configuration in server.py
3. Test with run_workflow.py for debugging/automation
4. Check PostgreSQL logs for database issues
5. Check Django logs for Django command issues

---

📄 License

This project is part of the Altiushub backend infrastructure.

---

Last Updated: December 12, 2025
Version: 1.0.0
MCP Protocol Version: Compatible with MCP SDK latest