MCP 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:
- Creates PostgreSQL database "mydb"
- Enables hstore extension
- Updates .env with POSTGRES_DB_NAME=mydb
- Runs Django setup commands in sequence
- 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 SDKFlask- Web UI frameworkpsycopg2-binary- PostgreSQL adapterpython-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_EXECpoints to correct virtual environment - Check
.envfile 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?
- Separation of Concerns: Server logic separate from client interfaces
- Multiple Interfaces: Same server, different access methods
- Type Safety: MCP protocol with schema validation
- Error Handling: Comprehensive error reporting
- Environment Isolation: Uses virtual environment Python
PostgreSQL Naming Rules
- Unquoted identifiers converted to lowercase
CREATE DATABASE MyDBcreatesmydb- 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 migrationsmakemigrations- Create new migrationscreate_text_search_config- Custom commandupdate_fixtures- Custom fixture managementrunserver- Start development server
🤝 Contributing
To extend this server with new tools:
- 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"]
}
)
- 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:
- Check the Troubleshooting section
- Verify configuration in
server.py - Test with
run_workflow.pyfor debugging/automation - Check PostgreSQL logs for database issues
- 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