# PostgreSQL MCP Server
<div align="center">
**A high-performance Model Context Protocol (MCP) server for PostgreSQL databases**
[Features](#-features) • [Installation](#️-installation) • [Quick Start](#-quick-start) • [API Reference](#-api-reference) • [Configuration](#️-configuration)
</div>
---
## 📖 Table of Contents
- [Overview](#-overview)
- [Features](#-features)
- [Prerequisites](#-prerequisites)
- [Installation](#️-installation)
- [Quick Start](#-quick-start)
- [API Reference](#-api-reference)
- [Configuration](#️-configuration)
- [Architecture](#️-architecture)
- [Security](#-security)
- [Testing](#-testing)
- [Troubleshooting](#-troubleshooting)
- [Contributing](#-contributing)
- [License](#-license)
## 🔍 Overview
PostgreSQL MCP Server is a robust, production-ready Model Context Protocol server that provides secure and efficient interaction with PostgreSQL databases. Built with modern Python async/await patterns and optimized for high-performance database operations.
### What is MCP?
Model Context Protocol (MCP) is an open standard that enables AI models to securely connect to external data sources and tools. This server implements MCP to provide AI models with direct, controlled access to PostgreSQL databases.
## ✨ Features
### 🚀 **Core Capabilities**
- **MCP Protocol Support**: Full compliance with MCP specification using FastMCP framework
- **PostgreSQL Optimized**: Native support for PostgreSQL with `asyncpg` driver
- **Asynchronous Architecture**: High-performance async/await implementation
- **Connection Pooling**: Intelligent connection management with configurable pool settings
### 🔧 **Database Operations**
- **Universal SQL Execution**: Support for SELECT, INSERT, UPDATE, DELETE, DDL operations
- **Table Structure Queries**: Detailed schema information retrieval
- **Test Data Generation**: Built-in tools for generating sample data
- **Parameterized Queries**: Safe parameter binding to prevent SQL injection
### 🛡️ **Security & Safety**
- **Query Type Restrictions**: Configurable query execution controls
- **Parameter Validation**: Comprehensive input validation
- **Password Protection**: Secure credential handling
- **Connection Isolation**: Instance-based access control
### 📊 **Monitoring & Logging**
- **Structured Logging**: Detailed operation logs with configurable levels
- **Connection Pool Monitoring**: Real-time pool status tracking
- **Error Handling**: Comprehensive error reporting and recovery
## 📋 Prerequisites
- **Python**: 3.12 or higher
- **PostgreSQL**: 12.0 or higher (tested with 17.6)
- **Network Access**: Connectivity to PostgreSQL server
- **Memory**: Minimum 512MB RAM recommended
## 🛠️ Installation
### Option 1: Install from PyPI (Recommended)
```bash
pip install postgresql-mcp-server3
```
### Option 2: Install from Source
```bash
# Clone the repository
git clone https://github.com/j00131120/mcp_database_server.git
cd mcp_database_server/postgresql_mcp_server
# Install dependencies
pip install -r requirements.txt
# Install in development mode
pip install -e .
```
### Option 3: Using UV (Fast Python Package Manager)
```bash
uv add postgresql-mcp-server3
```
## 🚀 Quick Start
### 1. Configure Database Connection
Create a `dbconfig.json` file with your PostgreSQL database credentials:
```json
{
"dbPoolSize": 5,
"dbMaxOverflow": 10,
"dbPoolTimeout": 30,
"dbList": [
{
"dbInstanceId": "postgresql_main",
"dbHost": "localhost",
"dbPort": 5432,
"dbDatabase": "pg_db",
"dbUsername": "root",
"dbPassword": "123456",
"dbType": "PostgreSQL",
"dbVersion": "17.6",
"dbActive": true
},
{ "dbInstanceId": "rasesql_2",
"dbHost": "localhost",
"dbPort": 5432,
"dbDatabase": "rasesql_db",
"dbUsername": "root",
"dbPassword": "123456",
"dbType": "RaseSQL",
"dbVersion": "2.0",
"dbActive": false
}
],
"logPath": "/path/to/logs",
"logLevel": "info"
}
# dbActive
Only database instances with dbActive set to true in the dbList configuration list are available.
# logPath
Mcp server log is stored in /path/to/logs/mcp_server.log.
# logLevel
TRACE, DEBUG, INFO, SUCCESS, WARNING, ERROR, CRITICAL
# dbActive
Only database instances with dbActive set to true in the dbList configuration list are available.
# logPath
Mcp server log is stored in /Volumes/store/mysql_mcp_server/logs/mcp_server.log.
# logLevel
TRACE, DEBUG, INFO, SUCCESS, WARNING, ERROR, CRITICAL
```
### 3. Configure mcp json
```bash
{
"mcpServers": {
"postgresql-mcp-client": {
"command": "postgresql-mcp-server3",
"env": {
"config_file": "/Users/frank/store/dbconfig.json"
},
"disabled": false
}
}
}
# config_file
dbconfig.json file path in your device
```
### 4. Clone the repository
```bash
git clone <repository-url>
cd mysql_mcp_server
import current project into your IDE Tool
```
### 5. Configure mcp json By IDE Tool
```bash
{
"mcpServers": {
"postgresql-mcp-client": {
"command": "/bin/uv",
"args": ["run", "src/server.py"],
"cwd": "/path/to/your/project",
"env": {
"config_file": "/path/to/your/dbconfig.json"
},
"disabled": false,
"autoApprove": ["describe_table", "sql_exec", "generate_demo_data"]
}
}
}
# command
uv absolute path
# cwd
project absolute path
# config_file
dbconfig.json file path
```
### 3. Start the Server
#### Using Installed Package
```bash
postgresql-mcp-server3
```
#### Using FastMCP CLI
```bash
fastmcp run src/server.py
```
#### Direct Python Execution
```bash
python src/server.py
```
#### Development Mode with UV
```bash
uv run src/server.py
```
#### Using fastmcp debug
```bash
fastmcp dev src/server.py
```
### 4. Verify Installation
```bash
# Test connection
python -c "
import asyncio
from src.utils.db_config import load_activate_db_config
try:
active_db, config = load_activate_db_config()
print('✅ Configuration loaded successfully')
print(f'Database: {active_db.db_type} {active_db.db_version}')
except Exception as e:
print(f'❌ Configuration error: {e}')
"
```
## 📚 API Reference
### MCP Tools
#### `sql_exec(sql: str)`
Execute any SQL statement with automatic result formatting.
**Parameters:**
- `sql` (str): SQL statement to execute
**Returns:**
```json
{
"success": true,
"result": [...], // Query results or affected row count
"message": "SQL executed successfully"
}
```
**Examples:**
```sql
-- Query data
SELECT * FROM users WHERE age > 18 LIMIT 10;
-- Insert data
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
-- Update data
UPDATE users SET last_login = NOW() WHERE id = 1;
-- DDL operations
CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(255));
```
#### `describe_table(table_name: str)`
Get detailed table structure information.
**Parameters:**
- `table_name` (str): Table name (supports `schema.table` format)
**Returns:**
- Detailed column information including types, constraints, and defaults
**Examples:**
```python
# Describe table in public schema
describe_table("users")
# Describe table in specific schema
describe_table("inventory.products")
```
#### `generate_demo_data(table_name: str, columns_name: List[str], num: int)`
Generate test data for development and testing.
**Parameters:**
- `table_name` (str): Target table name
- `columns_name` (List[str]): Column names to populate
- `num` (int): Number of test records to generate
**Example:**
```python
generate_demo_data("users", ["name", "email", "phone"], 100)
```
### MCP Resources
#### `database://tables`
Provides metadata for all database tables including:
- Table names and schemas
- Column definitions and types
- Primary keys and constraints
- Row counts
#### `database://config`
Returns current database configuration (sensitive data masked):
- Connection parameters
- Pool settings
- Database version information
## ⚙️ Configuration
### Database Configuration (`dbconfig.json`)
```json
{
"dbPoolSize": 5, // Minimum connection pool size
"dbMaxOverflow": 10, // Maximum additional connections
"dbPoolTimeout": 30, // Connection timeout in seconds
"dbList": [
{
"dbInstanceId": "unique_identifier",
"dbHost": "localhost",
"dbPort": 5432,
"dbDatabase": "database_name",
"dbUsername": "username",
"dbPassword": "password",
"dbType": "PostgreSQL",
"dbVersion": "17.6",
"dbActive": true // Only one instance should be active
}
],
"logPath": "/path/to/logs", // Log file directory
"logLevel": "info" // TRACE, DEBUG, INFO, WARNING, ERROR, CRITICAL
}
```
### Environment Variables
- `config_file`: Override default configuration file path
- `LOG_LEVEL`: Override log level from configuration
### MCP Client Configuration Examples
#### Claude Desktop
```json
{
"mcpServers": {
"postgresql": {
"command": "postgresql-mcp-server3",
"env": {
"config_file": "/Users/yourusername/dbconfig.json"
}
}
}
}
```
#### Development with UV
```json
{
"mcpServers": {
"postgresql-dev": {
"command": "uv",
"args": ["run", "src/server.py"],
"cwd": "/path/to/postgresql_mcp_server",
"env": {
"config_file": "/path/to/dbconfig.json"
}
}
}
}
```
## 🏗️ Architecture
### Project Structure
```
postgresql_mcp_server/
├── src/
│ ├── server.py # MCP server entry point
│ ├── utils/ # Core utilities
│ │ ├── db_config.py # Configuration management
│ │ ├── db_pool.py # Connection pool
│ │ ├── db_operate.py # Database operations
│ │ ├── logger_util.py # Logging utilities
│ │ └── __init__.py # Module exports
│ ├── resources/ # MCP resources
│ │ └── db_resources.py # Database metadata resources
│ └── tools/ # MCP tools
│ └── db_tool.py # Database operation tools
├── dbconfig.json # Database configuration
├── pyproject.toml # Package configuration
├── requirements.txt # Dependencies
└── README.md # Documentation
```
### Key Components
#### 🔗 **Connection Pool Management**
- **Singleton Pattern**: Single pool instance per application
- **Async Operations**: Non-blocking connection handling
- **Health Monitoring**: Automatic connection validation
- **Resource Cleanup**: Proper connection lifecycle management
#### ⚙️ **Configuration System**
- **JSON-based**: Human-readable configuration
- **Environment Override**: Flexible deployment options
- **Validation**: Comprehensive configuration validation
- **Hot Reload**: Configuration updates without restart
#### 📝 **Logging Framework**
- **Structured Logging**: JSON-formatted log entries
- **Multiple Outputs**: Console and file logging
- **Log Rotation**: Automatic log file management
- **Debug Support**: Detailed operation tracing
## 🛡️ Security
### Connection Security
- **Parameterized Queries**: Automatic SQL injection prevention
- **Connection Encryption**: SSL/TLS support for database connections
- **Credential Protection**: Secure password handling and masking
- **Access Control**: Instance-based permission management
### Query Safety
- **SQL Validation**: Query type verification
- **Result Limiting**: Automatic row count restrictions
- **Parameter Sanitization**: Input validation and cleaning
- **Error Handling**: Secure error message formatting
### Configuration Security
- **Environment Variables**: Secure credential management
- **File Permissions**: Proper configuration file protection
- **Network Security**: Firewall and access control recommendations
## 🧪 Testing
### Connection Testing
```bash
# Test database connectivity
python -c "
import asyncio
from src.utils.db_pool import get_db_pool
async def test():
pool = await get_db_pool()
conn = await pool.get_connection()
result = await conn.fetchval('SELECT version()')
print(f'Connected to: {result[:50]}...')
await pool.release_connection(conn)
asyncio.run(test())
"
```
### SQL Execution Testing
```python
# Test SQL execution
from src.tools.db_tool import sql_exec
result = await sql_exec("SELECT current_timestamp as now")
print(result)
```
### Load Testing
```python
# Test concurrent connections
import asyncio
from src.utils.db_operate import execute_sql
async def load_test():
tasks = []
for i in range(10):
task = execute_sql(f"SELECT {i} as test_id, pg_sleep(0.1)")
tasks.append(task)
results = await asyncio.gather(*tasks)
print(f"Completed {len(results)} concurrent queries")
asyncio.run(load_test())
```
## 🚨 Troubleshooting
### Common Issues
#### Connection Errors
```bash
# Check PostgreSQL connectivity
psql -h localhost -p 5432 -U username -d database_name
# Test configuration
python -c "
from src.utils.db_config import load_activate_db_config
try:
db, config = load_activate_db_config()
print('✅ Configuration valid')
except Exception as e:
print(f'❌ Configuration error: {e}')
"
```
#### Permission Issues
- Ensure PostgreSQL user has necessary privileges:
```sql
GRANT CONNECT ON DATABASE your_db TO your_user;
GRANT USAGE ON SCHEMA public TO your_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_user;
```
- Check firewall settings and network connectivity
- Verify PostgreSQL server is running and accepting connections
#### Configuration Problems
- Validate JSON syntax in `dbconfig.json`
- Check file permissions and paths
- Verify environment variables
- Review log files for detailed error messages
### Debug Mode
Enable detailed logging:
```json
{
"logLevel": "debug"
}
```
Or set environment variable:
```bash
export LOG_LEVEL=debug
python src/server.py
```
### Log Analysis
```bash
# View recent logs
tail -f /path/to/logs/mcp_server.log
# Search for errors
grep -i error /path/to/logs/mcp_server.log
# Monitor connection pool
grep -i "connection pool" /path/to/logs/mcp_server.log
```
## 🤝 Contributing
We welcome contributions! Please follow these guidelines:
### Getting Started
1. Fork the repository
2. Create a feature branch: `git checkout -b feature/your-feature`
3. Make your changes
4. Add tests for new functionality
5. Run the test suite
6. Submit a pull request
### Development Setup
```bash
# Clone and setup
git clone https://github.com/j00131120/mcp_database_server.git
cd mcp_database_server/postgresql_mcp_server
# Create virtual environment
python -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate
# Install development dependencies
pip install -e ".[dev,test]"
# Install pre-commit hooks
pre-commit install
```
### Code Quality
```bash
# Format code
black src/
isort src/
# Lint code
flake8 src/
mypy src/
# Run tests
pytest tests/ -v
# Run tests with coverage
pytest --cov=src --cov-report=html
```
### Pull Request Guidelines
- Write clear, descriptive commit messages
- Include tests for new features
- Update documentation as needed
- Ensure all tests pass
- Follow existing code style and conventions
## 📄 License
This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
## 👥 Authors & Contributors
- **Frank Jin** - *Initial development* - [j00131120@163.com](mailto:j00131120@163.com)
## 🙏 Acknowledgments
- [FastMCP](https://github.com/fastmcp/fastmcp) - MCP framework foundation
- [asyncpg](https://github.com/MagicStack/asyncpg) - High-performance PostgreSQL driver
- [loguru](https://github.com/Delgan/loguru) - Modern logging library
- [PostgreSQL](https://www.postgresql.org/) - World's most advanced open source database
## 📞 Support
### Getting Help
- 📖 **Documentation**: Check this README and inline code documentation
- 🐛 **Bug Reports**: [Create an issue](https://github.com/j00131120/mcp_database_server/issues)
- 💬 **Questions**: Contact [j00131120@163.com](mailto:j00131120@163.com)
- 💡 **Feature Requests**: [Submit an enhancement request](https://github.com/j00131120/mcp_database_server/issues)
### Community
- Star ⭐ this repository if you find it useful
- Share with colleagues working with PostgreSQL and AI
- Contribute improvements and bug fixes
---
<div align="center">
**Made with ❤️ for the PostgreSQL and AI community**
[⬆ Back to Top](#postgresql-mcp-server)
</div>
Raw data
{
"_id": null,
"home_page": null,
"name": "postgresql-mcp-server3",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.12",
"maintainer_email": "Frank Jin <j00131120@163.com>",
"keywords": "mcp, postgresql, model-context-protocol, async, connection-pool",
"author": null,
"author_email": "Frank Jin <j00131120@163.com>",
"download_url": "https://files.pythonhosted.org/packages/2b/b0/6e03686d18af09c33b17bac9f5509d50c940a50e9d7315f0bcaa1cd10cc4/postgresql_mcp_server3-1.0.1.tar.gz",
"platform": null,
"description": "# PostgreSQL MCP Server\n\n<div align=\"center\">\n\n\n\n\n\n\n**A high-performance Model Context Protocol (MCP) server for PostgreSQL databases**\n\n[Features](#-features) \u2022 [Installation](#\ufe0f-installation) \u2022 [Quick Start](#-quick-start) \u2022 [API Reference](#-api-reference) \u2022 [Configuration](#\ufe0f-configuration)\n\n</div>\n\n---\n\n## \ud83d\udcd6 Table of Contents\n\n- [Overview](#-overview)\n- [Features](#-features)\n- [Prerequisites](#-prerequisites)\n- [Installation](#\ufe0f-installation)\n- [Quick Start](#-quick-start)\n- [API Reference](#-api-reference)\n- [Configuration](#\ufe0f-configuration)\n- [Architecture](#\ufe0f-architecture)\n- [Security](#-security)\n- [Testing](#-testing)\n- [Troubleshooting](#-troubleshooting)\n- [Contributing](#-contributing)\n- [License](#-license)\n\n## \ud83d\udd0d Overview\n\nPostgreSQL MCP Server is a robust, production-ready Model Context Protocol server that provides secure and efficient interaction with PostgreSQL databases. Built with modern Python async/await patterns and optimized for high-performance database operations.\n\n### What is MCP?\n\nModel Context Protocol (MCP) is an open standard that enables AI models to securely connect to external data sources and tools. This server implements MCP to provide AI models with direct, controlled access to PostgreSQL databases.\n\n## \u2728 Features\n\n### \ud83d\ude80 **Core Capabilities**\n- **MCP Protocol Support**: Full compliance with MCP specification using FastMCP framework\n- **PostgreSQL Optimized**: Native support for PostgreSQL with `asyncpg` driver\n- **Asynchronous Architecture**: High-performance async/await implementation\n- **Connection Pooling**: Intelligent connection management with configurable pool settings\n\n### \ud83d\udd27 **Database Operations**\n- **Universal SQL Execution**: Support for SELECT, INSERT, UPDATE, DELETE, DDL operations\n- **Table Structure Queries**: Detailed schema information retrieval\n- **Test Data Generation**: Built-in tools for generating sample data\n- **Parameterized Queries**: Safe parameter binding to prevent SQL injection\n\n### \ud83d\udee1\ufe0f **Security & Safety**\n- **Query Type Restrictions**: Configurable query execution controls\n- **Parameter Validation**: Comprehensive input validation\n- **Password Protection**: Secure credential handling\n- **Connection Isolation**: Instance-based access control\n\n### \ud83d\udcca **Monitoring & Logging**\n- **Structured Logging**: Detailed operation logs with configurable levels\n- **Connection Pool Monitoring**: Real-time pool status tracking\n- **Error Handling**: Comprehensive error reporting and recovery\n\n## \ud83d\udccb Prerequisites\n\n- **Python**: 3.12 or higher\n- **PostgreSQL**: 12.0 or higher (tested with 17.6)\n- **Network Access**: Connectivity to PostgreSQL server\n- **Memory**: Minimum 512MB RAM recommended\n\n## \ud83d\udee0\ufe0f Installation\n\n### Option 1: Install from PyPI (Recommended)\n\n```bash\npip install postgresql-mcp-server3\n```\n\n### Option 2: Install from Source\n\n```bash\n# Clone the repository\ngit clone https://github.com/j00131120/mcp_database_server.git\ncd mcp_database_server/postgresql_mcp_server\n\n# Install dependencies\npip install -r requirements.txt\n\n# Install in development mode\npip install -e .\n```\n\n### Option 3: Using UV (Fast Python Package Manager)\n\n```bash\nuv add postgresql-mcp-server3\n```\n\n## \ud83d\ude80 Quick Start\n\n### 1. Configure Database Connection\n\nCreate a `dbconfig.json` file with your PostgreSQL database credentials:\n\n```json\n{\n \"dbPoolSize\": 5,\n \"dbMaxOverflow\": 10,\n \"dbPoolTimeout\": 30,\n \"dbList\": [\n {\n \"dbInstanceId\": \"postgresql_main\",\n \"dbHost\": \"localhost\",\n \"dbPort\": 5432,\n \"dbDatabase\": \"pg_db\",\n \"dbUsername\": \"root\",\n \"dbPassword\": \"123456\",\n \"dbType\": \"PostgreSQL\",\n \"dbVersion\": \"17.6\",\n \"dbActive\": true\n },\n { \"dbInstanceId\": \"rasesql_2\",\n \"dbHost\": \"localhost\",\n \"dbPort\": 5432,\n \"dbDatabase\": \"rasesql_db\",\n \"dbUsername\": \"root\",\n \"dbPassword\": \"123456\",\n \"dbType\": \"RaseSQL\",\n \"dbVersion\": \"2.0\",\n \"dbActive\": false\n }\n ],\n \"logPath\": \"/path/to/logs\",\n \"logLevel\": \"info\"\n}\n# dbActive\nOnly database instances with dbActive set to true in the dbList configuration list are available. \n# logPath\nMcp server log is stored in /path/to/logs/mcp_server.log.\n# logLevel\nTRACE, DEBUG, INFO, SUCCESS, WARNING, ERROR, CRITICAL\n# dbActive\nOnly database instances with dbActive set to true in the dbList configuration list are available. \n# logPath\nMcp server log is stored in /Volumes/store/mysql_mcp_server/logs/mcp_server.log.\n# logLevel\nTRACE, DEBUG, INFO, SUCCESS, WARNING, ERROR, CRITICAL\n```\n\n### 3. Configure mcp json\n\n```bash\n{\n \"mcpServers\": {\n \"postgresql-mcp-client\": {\n \"command\": \"postgresql-mcp-server3\",\n \"env\": {\n \"config_file\": \"/Users/frank/store/dbconfig.json\"\n },\n \"disabled\": false\n }\n }\n}\n\n# config_file\ndbconfig.json file path in your device\n```\n\n### 4. Clone the repository\n```bash\ngit clone <repository-url>\ncd mysql_mcp_server\nimport current project into your IDE Tool\n\n```\n\n### 5. Configure mcp json By IDE Tool\n```bash\n{\n \"mcpServers\": {\n \"postgresql-mcp-client\": {\n \"command\": \"/bin/uv\",\n \"args\": [\"run\", \"src/server.py\"],\n \"cwd\": \"/path/to/your/project\",\n \"env\": {\n \"config_file\": \"/path/to/your/dbconfig.json\"\n },\n \"disabled\": false,\n \"autoApprove\": [\"describe_table\", \"sql_exec\", \"generate_demo_data\"]\n }\n }\n}\n\n# command\nuv absolute path\n# cwd\nproject absolute path\n# config_file\ndbconfig.json file path\n```\n\n### 3. Start the Server\n\n#### Using Installed Package\n```bash\npostgresql-mcp-server3\n```\n\n#### Using FastMCP CLI\n```bash\nfastmcp run src/server.py\n```\n\n#### Direct Python Execution\n```bash\npython src/server.py\n```\n\n#### Development Mode with UV\n```bash\nuv run src/server.py\n```\n\n#### Using fastmcp debug\n```bash\nfastmcp dev src/server.py\n```\n### 4. Verify Installation\n\n```bash\n# Test connection\npython -c \"\nimport asyncio\nfrom src.utils.db_config import load_activate_db_config\ntry:\n active_db, config = load_activate_db_config()\n print('\u2705 Configuration loaded successfully')\n print(f'Database: {active_db.db_type} {active_db.db_version}')\nexcept Exception as e:\n print(f'\u274c Configuration error: {e}')\n\"\n```\n\n## \ud83d\udcda API Reference\n\n### MCP Tools\n\n#### `sql_exec(sql: str)`\n\nExecute any SQL statement with automatic result formatting.\n\n**Parameters:**\n- `sql` (str): SQL statement to execute\n\n**Returns:**\n```json\n{\n \"success\": true,\n \"result\": [...], // Query results or affected row count\n \"message\": \"SQL executed successfully\"\n}\n```\n\n**Examples:**\n```sql\n-- Query data\nSELECT * FROM users WHERE age > 18 LIMIT 10;\n\n-- Insert data\nINSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');\n\n-- Update data\nUPDATE users SET last_login = NOW() WHERE id = 1;\n\n-- DDL operations\nCREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(255));\n```\n\n#### `describe_table(table_name: str)`\n\nGet detailed table structure information.\n\n**Parameters:**\n- `table_name` (str): Table name (supports `schema.table` format)\n\n**Returns:**\n- Detailed column information including types, constraints, and defaults\n\n**Examples:**\n```python\n# Describe table in public schema\ndescribe_table(\"users\")\n\n# Describe table in specific schema\ndescribe_table(\"inventory.products\")\n```\n\n#### `generate_demo_data(table_name: str, columns_name: List[str], num: int)`\n\nGenerate test data for development and testing.\n\n**Parameters:**\n- `table_name` (str): Target table name\n- `columns_name` (List[str]): Column names to populate\n- `num` (int): Number of test records to generate\n\n**Example:**\n```python\ngenerate_demo_data(\"users\", [\"name\", \"email\", \"phone\"], 100)\n```\n\n### MCP Resources\n\n#### `database://tables`\n\nProvides metadata for all database tables including:\n- Table names and schemas\n- Column definitions and types\n- Primary keys and constraints\n- Row counts\n\n#### `database://config`\n\nReturns current database configuration (sensitive data masked):\n- Connection parameters\n- Pool settings\n- Database version information\n\n## \u2699\ufe0f Configuration\n\n### Database Configuration (`dbconfig.json`)\n\n```json\n{\n \"dbPoolSize\": 5, // Minimum connection pool size\n \"dbMaxOverflow\": 10, // Maximum additional connections\n \"dbPoolTimeout\": 30, // Connection timeout in seconds\n \"dbList\": [\n {\n \"dbInstanceId\": \"unique_identifier\",\n \"dbHost\": \"localhost\",\n \"dbPort\": 5432,\n \"dbDatabase\": \"database_name\",\n \"dbUsername\": \"username\",\n \"dbPassword\": \"password\",\n \"dbType\": \"PostgreSQL\",\n \"dbVersion\": \"17.6\",\n \"dbActive\": true // Only one instance should be active\n }\n ],\n \"logPath\": \"/path/to/logs\", // Log file directory\n \"logLevel\": \"info\" // TRACE, DEBUG, INFO, WARNING, ERROR, CRITICAL\n}\n```\n\n### Environment Variables\n\n- `config_file`: Override default configuration file path\n- `LOG_LEVEL`: Override log level from configuration\n\n### MCP Client Configuration Examples\n\n#### Claude Desktop\n```json\n{\n \"mcpServers\": {\n \"postgresql\": {\n \"command\": \"postgresql-mcp-server3\",\n \"env\": {\n \"config_file\": \"/Users/yourusername/dbconfig.json\"\n }\n }\n }\n}\n```\n\n#### Development with UV\n```json\n{\n \"mcpServers\": {\n \"postgresql-dev\": {\n \"command\": \"uv\",\n \"args\": [\"run\", \"src/server.py\"],\n \"cwd\": \"/path/to/postgresql_mcp_server\",\n \"env\": {\n \"config_file\": \"/path/to/dbconfig.json\"\n }\n }\n }\n}\n```\n\n## \ud83c\udfd7\ufe0f Architecture\n\n### Project Structure\n```\npostgresql_mcp_server/\n\u251c\u2500\u2500 src/\n\u2502 \u251c\u2500\u2500 server.py # MCP server entry point\n\u2502 \u251c\u2500\u2500 utils/ # Core utilities\n\u2502 \u2502 \u251c\u2500\u2500 db_config.py # Configuration management\n\u2502 \u2502 \u251c\u2500\u2500 db_pool.py # Connection pool\n\u2502 \u2502 \u251c\u2500\u2500 db_operate.py # Database operations\n\u2502 \u2502 \u251c\u2500\u2500 logger_util.py # Logging utilities\n\u2502 \u2502 \u2514\u2500\u2500 __init__.py # Module exports\n\u2502 \u251c\u2500\u2500 resources/ # MCP resources\n\u2502 \u2502 \u2514\u2500\u2500 db_resources.py # Database metadata resources\n\u2502 \u2514\u2500\u2500 tools/ # MCP tools\n\u2502 \u2514\u2500\u2500 db_tool.py # Database operation tools\n\u251c\u2500\u2500 dbconfig.json # Database configuration\n\u251c\u2500\u2500 pyproject.toml # Package configuration\n\u251c\u2500\u2500 requirements.txt # Dependencies\n\u2514\u2500\u2500 README.md # Documentation\n```\n\n### Key Components\n\n#### \ud83d\udd17 **Connection Pool Management**\n- **Singleton Pattern**: Single pool instance per application\n- **Async Operations**: Non-blocking connection handling\n- **Health Monitoring**: Automatic connection validation\n- **Resource Cleanup**: Proper connection lifecycle management\n\n#### \u2699\ufe0f **Configuration System**\n- **JSON-based**: Human-readable configuration\n- **Environment Override**: Flexible deployment options\n- **Validation**: Comprehensive configuration validation\n- **Hot Reload**: Configuration updates without restart\n\n#### \ud83d\udcdd **Logging Framework**\n- **Structured Logging**: JSON-formatted log entries\n- **Multiple Outputs**: Console and file logging\n- **Log Rotation**: Automatic log file management\n- **Debug Support**: Detailed operation tracing\n\n## \ud83d\udee1\ufe0f Security\n\n### Connection Security\n- **Parameterized Queries**: Automatic SQL injection prevention\n- **Connection Encryption**: SSL/TLS support for database connections\n- **Credential Protection**: Secure password handling and masking\n- **Access Control**: Instance-based permission management\n\n### Query Safety\n- **SQL Validation**: Query type verification\n- **Result Limiting**: Automatic row count restrictions\n- **Parameter Sanitization**: Input validation and cleaning\n- **Error Handling**: Secure error message formatting\n\n### Configuration Security\n- **Environment Variables**: Secure credential management\n- **File Permissions**: Proper configuration file protection\n- **Network Security**: Firewall and access control recommendations\n\n## \ud83e\uddea Testing\n\n### Connection Testing\n```bash\n# Test database connectivity\npython -c \"\nimport asyncio\nfrom src.utils.db_pool import get_db_pool\n\nasync def test():\n pool = await get_db_pool()\n conn = await pool.get_connection()\n result = await conn.fetchval('SELECT version()')\n print(f'Connected to: {result[:50]}...')\n await pool.release_connection(conn)\n\nasyncio.run(test())\n\"\n```\n\n### SQL Execution Testing\n```python\n# Test SQL execution\nfrom src.tools.db_tool import sql_exec\n\nresult = await sql_exec(\"SELECT current_timestamp as now\")\nprint(result)\n```\n\n### Load Testing\n```python\n# Test concurrent connections\nimport asyncio\nfrom src.utils.db_operate import execute_sql\n\nasync def load_test():\n tasks = []\n for i in range(10):\n task = execute_sql(f\"SELECT {i} as test_id, pg_sleep(0.1)\")\n tasks.append(task)\n \n results = await asyncio.gather(*tasks)\n print(f\"Completed {len(results)} concurrent queries\")\n\nasyncio.run(load_test())\n```\n\n## \ud83d\udea8 Troubleshooting\n\n### Common Issues\n\n#### Connection Errors\n```bash\n# Check PostgreSQL connectivity\npsql -h localhost -p 5432 -U username -d database_name\n\n# Test configuration\npython -c \"\nfrom src.utils.db_config import load_activate_db_config\ntry:\n db, config = load_activate_db_config()\n print('\u2705 Configuration valid')\nexcept Exception as e:\n print(f'\u274c Configuration error: {e}')\n\"\n```\n\n#### Permission Issues\n- Ensure PostgreSQL user has necessary privileges:\n ```sql\n GRANT CONNECT ON DATABASE your_db TO your_user;\n GRANT USAGE ON SCHEMA public TO your_user;\n GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_user;\n ```\n- Check firewall settings and network connectivity\n- Verify PostgreSQL server is running and accepting connections\n\n#### Configuration Problems\n- Validate JSON syntax in `dbconfig.json`\n- Check file permissions and paths\n- Verify environment variables\n- Review log files for detailed error messages\n\n### Debug Mode\n\nEnable detailed logging:\n```json\n{\n \"logLevel\": \"debug\"\n}\n```\n\nOr set environment variable:\n```bash\nexport LOG_LEVEL=debug\npython src/server.py\n```\n\n### Log Analysis\n```bash\n# View recent logs\ntail -f /path/to/logs/mcp_server.log\n\n# Search for errors\ngrep -i error /path/to/logs/mcp_server.log\n\n# Monitor connection pool\ngrep -i \"connection pool\" /path/to/logs/mcp_server.log\n```\n\n## \ud83e\udd1d Contributing\n\nWe welcome contributions! Please follow these guidelines:\n\n### Getting Started\n1. Fork the repository\n2. Create a feature branch: `git checkout -b feature/your-feature`\n3. Make your changes\n4. Add tests for new functionality\n5. Run the test suite\n6. Submit a pull request\n\n### Development Setup\n```bash\n# Clone and setup\ngit clone https://github.com/j00131120/mcp_database_server.git\ncd mcp_database_server/postgresql_mcp_server\n\n# Create virtual environment\npython -m venv venv\nsource venv/bin/activate # On Windows: venv\\Scripts\\activate\n\n# Install development dependencies\npip install -e \".[dev,test]\"\n\n# Install pre-commit hooks\npre-commit install\n```\n\n### Code Quality\n```bash\n# Format code\nblack src/\nisort src/\n\n# Lint code\nflake8 src/\nmypy src/\n\n# Run tests\npytest tests/ -v\n\n# Run tests with coverage\npytest --cov=src --cov-report=html\n```\n\n### Pull Request Guidelines\n- Write clear, descriptive commit messages\n- Include tests for new features\n- Update documentation as needed\n- Ensure all tests pass\n- Follow existing code style and conventions\n\n## \ud83d\udcc4 License\n\nThis project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.\n\n## \ud83d\udc65 Authors & Contributors\n\n- **Frank Jin** - *Initial development* - [j00131120@163.com](mailto:j00131120@163.com)\n\n## \ud83d\ude4f Acknowledgments\n\n- [FastMCP](https://github.com/fastmcp/fastmcp) - MCP framework foundation\n- [asyncpg](https://github.com/MagicStack/asyncpg) - High-performance PostgreSQL driver\n- [loguru](https://github.com/Delgan/loguru) - Modern logging library\n- [PostgreSQL](https://www.postgresql.org/) - World's most advanced open source database\n\n## \ud83d\udcde Support\n\n### Getting Help\n- \ud83d\udcd6 **Documentation**: Check this README and inline code documentation\n- \ud83d\udc1b **Bug Reports**: [Create an issue](https://github.com/j00131120/mcp_database_server/issues)\n- \ud83d\udcac **Questions**: Contact [j00131120@163.com](mailto:j00131120@163.com)\n- \ud83d\udca1 **Feature Requests**: [Submit an enhancement request](https://github.com/j00131120/mcp_database_server/issues)\n\n### Community\n- Star \u2b50 this repository if you find it useful\n- Share with colleagues working with PostgreSQL and AI\n- Contribute improvements and bug fixes\n\n---\n\n<div align=\"center\">\n\n**Made with \u2764\ufe0f for the PostgreSQL and AI community**\n\n[\u2b06 Back to Top](#postgresql-mcp-server)\n\n</div>\n",
"bugtrack_url": null,
"license": null,
"summary": "A Model Context Protocol (MCP) server that enables secure interaction with PostgreSQL\u3001RaseSQL databases.",
"version": "1.0.1",
"project_urls": {
"Bug Tracker": "https://github.com/j00131120/mcp_database_server/issues",
"Changelog": "https://github.com/j00131120/mcp_database_server/blob/main/postgresql_mcp_server/CHANGELOG.md",
"Documentation": "https://github.com/j00131120/mcp_database_server/blob/main/postgresql_mcp_server/README.md",
"Download": "https://github.com/j00131120/mcp_database_server/tree/main/postgresql_mcp_server",
"Homepage": "https://github.com/j00131120/mcp_database_server/tree/main/postgresql_mcp_server",
"Repository": "https://github.com/j00131120/mcp_database_server.git",
"Source Code": "https://github.com/j00131120/mcp_database_server/tree/main/postgresql_mcp_server"
},
"split_keywords": [
"mcp",
" postgresql",
" model-context-protocol",
" async",
" connection-pool"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "17f100bb4a554fb713207c9e100d5f45099ad3c771767cf54a98c831f15a4d6e",
"md5": "ca01c77e236eb3e50c1ceb2ceb89608c",
"sha256": "c936a2286bee42779f3cd51277e68c0e05ba0b1a0f8b5df6ac3a4d2ca1a2e8be"
},
"downloads": -1,
"filename": "postgresql_mcp_server3-1.0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "ca01c77e236eb3e50c1ceb2ceb89608c",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.12",
"size": 21760,
"upload_time": "2025-08-19T09:27:41",
"upload_time_iso_8601": "2025-08-19T09:27:41.940932Z",
"url": "https://files.pythonhosted.org/packages/17/f1/00bb4a554fb713207c9e100d5f45099ad3c771767cf54a98c831f15a4d6e/postgresql_mcp_server3-1.0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "2bb06e03686d18af09c33b17bac9f5509d50c940a50e9d7315f0bcaa1cd10cc4",
"md5": "66103ee032b6b0f300d363cdbcad6a75",
"sha256": "a4d6708cb3bb2352f6edc174f1e74d00d3c8f5b0255ce2ae4d159f586682888b"
},
"downloads": -1,
"filename": "postgresql_mcp_server3-1.0.1.tar.gz",
"has_sig": false,
"md5_digest": "66103ee032b6b0f300d363cdbcad6a75",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 19667,
"upload_time": "2025-08-19T09:27:43",
"upload_time_iso_8601": "2025-08-19T09:27:43.804845Z",
"url": "https://files.pythonhosted.org/packages/2b/b0/6e03686d18af09c33b17bac9f5509d50c940a50e9d7315f0bcaa1cd10cc4/postgresql_mcp_server3-1.0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-19 09:27:43",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "j00131120",
"github_project": "mcp_database_server",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "postgresql-mcp-server3"
}
JSON
