README.md•6.8 kB
# MCP Database Server
A Model Context Protocol (MCP) server that provides PostgreSQL database access with query execution and schema inspection capabilities.
## Overview
This MCP server enables AI assistants (like Claude) to interact with a PostgreSQL database containing data from multiple systems:
- **cs** (CustomerScoreView): Customer and credit assessment data
- **dms** (Data Management System): Document management data
- **los** (Loan Management System): Loan and payment data
- **mls** (Mahatheun Leasing System): Contract and asset leasing data
## Features
- ✅ **Query Execution**: Execute SQL SELECT, INSERT, UPDATE, DELETE queries
- ✅ **Schema Inspection**: List schemas, tables, and column details
- ✅ **Multi-Schema Support**: Organize data by system (cs, dms, los, mls)
- ✅ **Docker Setup**: PostgreSQL in Docker with automatic initialization
- ✅ **CSV Import**: Sample data included and ready to import
## Prerequisites
- Python 3.10 or higher
- Docker and Docker Compose
- pip (Python package installer)
## Installation
1. **Clone or navigate to the project directory:**
```bash
cd c:\Users\chaya\project\mcp-database
```
2. **Install Python dependencies:**
```bash
pip install -r requirements.txt
```
3. **Configure environment variables:**
```bash
copy .env.example .env
```
Edit `.env` if you need to change database credentials (optional).
## Setup
### 1. Start PostgreSQL Database
Start the PostgreSQL container with Docker Compose:
```bash
docker-compose up -d
```
Verify the database is running:
```bash
docker-compose ps
```
### 2. Verify Database Initialization
The database will automatically initialize with schemas and sample data. Check the schemas:
```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\dn"
```
List tables in a schema:
```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\dt cs.*"
```
### 3. (Optional) Import CSV Data
Sample CSV files are provided in `raw_data/`. To import them into the database:
```bash
docker-compose exec postgres bash -c "cd /raw_data && find . -name '*.csv' -type f"
```
Import a specific CSV file:
```bash
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY cs.customers FROM '/raw_data/cs/customers.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY dms.documents FROM '/raw_data/dms/documents.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY los.loans FROM '/raw_data/los/loans.csv' WITH CSV HEADER;"
docker-compose exec postgres psql -U postgres -d mcp_database -c "\COPY mls.contracts FROM '/raw_data/mls/contracts.csv' WITH CSV HEADER;"
```
## Running the MCP Server
### Test Locally
Run the server directly to test:
```bash
python src/server.py
```
The server will start and listen for MCP messages via stdio.
### Configure with Claude Desktop
To use this MCP server with Claude Desktop, add it to your Claude configuration file:
**Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
Add this configuration:
```json
{
"mcpServers": {
"mcp-database": {
"command": "python",
"args": ["c:\\Users\\chaya\\project\\mcp-database\\src\\server.py"],
"env": {
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_NAME": "mcp_database",
"DB_USER": "postgres",
"DB_PASSWORD": "postgres"
}
}
}
}
```
Restart Claude Desktop to load the server.
## Available MCP Tools
### 1. `list_schemas`
Lists all available database schemas.
**Example:**
```
Can you list all database schemas?
```
### 2. `list_tables`
Lists all tables, optionally filtered by schema.
**Parameters:**
- `schema` (optional): Schema name to filter (e.g., "cs", "dms", "los", "mls")
**Example:**
```
Show me all tables in the cs schema
```
### 3. `describe_table`
Get detailed column information for a specific table.
**Parameters:**
- `table_name` (required): Name of the table
- `schema` (optional): Schema name (default: "public")
**Example:**
```
Describe the structure of the customers table in the cs schema
```
### 4. `execute_query`
Execute a SQL query on the database.
**Parameters:**
- `query` (required): SQL query string
- `params` (optional): Array of parameters for parameterized queries
**Examples:**
```
Query all customers with credit score above 700
SELECT * FROM cs.customers WHERE credit_score > 700
Get total loan amount by customer
SELECT customer_code, SUM(loan_amount) as total_loans
FROM los.loans
GROUP BY customer_code
```
## Database Schema
### cs (CustomerScoreView)
- `customers`: Customer information and credit scores
- `credit_assessments`: Credit assessment history
### dms (Data Management System)
- `documents`: Document metadata and storage info
- `document_versions`: Document version history
### los (Loan Management System)
- `loans`: Loan accounts and terms
- `payments`: Payment history
### mls (Mahatheun Leasing System)
- `contracts`: Lease contract information
- `assets`: Asset details for leased items
- `lease_payments`: Lease payment records
## Project Structure
```
mcp-database/
├── src/
│ ├── server.py # Main MCP server
│ ├── database.py # Database connection manager
│ └── tools.py # MCP tool definitions
├── db/
│ └── init.sql # Database initialization script
├── raw_data/
│ ├── cs/ # CustomerScoreView CSV files
│ ├── dms/ # Data Management System CSV files
│ ├── los/ # Loan Management System CSV files
│ └── mls/ # Mahatheun Leasing System CSV files
├── docker-compose.yml # Docker configuration
├── requirements.txt # Python dependencies
├── pyproject.toml # Python project metadata
└── .env.example # Environment variable template
```
## Troubleshooting
### Docker container won't start
```bash
# Check logs
docker-compose logs postgres
# Restart container
docker-compose restart postgres
```
### Connection refused error
- Ensure PostgreSQL is running: `docker-compose ps`
- Check port 5432 is not in use by another process
- Verify `.env` file has correct credentials
### Import errors in Python
```bash
# Reinstall dependencies
pip install --upgrade -r requirements.txt
```
## Development
To extend the server:
1. Add new tools in `src/tools.py`
2. Register handlers in `src/server.py`
3. Update database schema in `db/init.sql`
4. Add sample data in `raw_data/`
## License
This project is provided as-is for database integration purposes.