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:

   cd c:\Users\chaya\project\mcp-database

2. Install Python dependencies:

   pip install -r requirements.txt

3. Configure environment variables:

   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:

docker-compose up -d

Verify the database is running:

docker-compose ps

2. Verify Database Initialization

The database will automatically initialize with schemas and sample data. Check the schemas:

docker-compose exec postgres psql -U postgres -d mcp_database -c "\dn"

List tables in a schema:

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:

docker-compose exec postgres bash -c "cd /raw_data && find . -name '*.csv' -type f"

Import a specific CSV file:

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:

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:

{
  "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

# 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

# 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.