# MCP Database Tools Server
## 📋 Table of Contents
- [Overview](#overview)
- [Architecture](#architecture)
- [Project Structure](#project-structure)
- [Component Flow](#component-flow)
- [Installation & Setup](#installation--setup)
- [Usage](#usage)
- [Configuration](#configuration)
- [Troubleshooting](#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:
```python
┌─────────────────────────────────────────────────────────┐
│ 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:**
```python
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:**
```python
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:**
```bash
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
```bash
cd /home/chaitanyaphani/MCP_project
```
### Step 2: Create Virtual Environment
```bash
python3 -m venv venv
source venv/bin/activate # Linux/Mac
# or
venv\Scripts\activate # Windows
```
### Step 3: Install Dependencies
```bash
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`:
```python
PG_USER = "postgres"
PG_PASSWORD = "your_password" # Update this!
PG_HOST = "localhost"
```
### Step 5: Configure Django Paths
Edit `server.py`:
```python
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`):
```json
{
"mcpServers": {
"dbtools": {
"command": "python",
"args": ["server.py"],
"cwd": "/home/chaitanyaphani/MCP_project"
}
}
}
```
---
## 🚀 Usage
### Method 1: Automated Workflow
```bash
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:
```env
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
```json
{
"name": "create_database",
"arguments": {
"db_name": "string (optional, default: sample_project_db)"
},
"returns": "Database {db_name} created."
}
```
### enable_hstore
```json
{
"name": "enable_hstore",
"arguments": {
"db_name": "string (optional, default: sample_project_db)"
},
"returns": "hstore extension enabled in {db_name}."
}
```
### update_env
```json
{
"name": "update_env",
"arguments": {
"db_name": "string (optional, default: sample_project_db)"
},
"returns": ".env updated: POSTGRES_DB_NAME={db_name}"
}
```
### django
```json
{
"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()`:
```python
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()`:
```python
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
