Content
# PostgreSQL MCP Server
A Model Context Protocol (MCP) server that provides dynamic PostgreSQL database connectivity for Claude Desktop. This server allows you to connect to any PostgreSQL database by simply changing the connection URL, with automatic schema discovery and comprehensive database operations.
## Features
- 🔄 **Dynamic Database Connection**: Connect to any PostgreSQL database by changing the URL
- 🔍 **Automatic Schema Discovery**: Dynamically discovers tables, columns, relationships, and constraints
- 📊 **Comprehensive Database Operations**: Support for SELECT, INSERT, UPDATE, DELETE operations
- 🛡️ **Safe Query Execution**: Parameterized queries to prevent SQL injection
- 🏗️ **Schema Introspection**: Get detailed information about tables, columns, indexes, and foreign keys
- ⚡ **Connection Pooling**: Efficient database connection management
- 🎯 **Claude Desktop Integration**: Seamless integration with Claude Desktop application
## Installation
1. **Clone or download this repository**
```bash
git clone <repository-url>
cd Postgres_mcp
```
2. **Install dependencies**
```bash
npm install
```
3. **Build the project**
```bash
npm run build
```
## Configuration
### Environment Variables
Create a `.env` file in the root directory:
```bash
cp env.example .env
```
Edit the `.env` file with your database configuration:
```env
# PostgreSQL Database Configuration
DATABASE_URL=postgresql://username:password@localhost:5432/database_name
# Optional: Connection pool settings
MAX_CONNECTIONS=20
IDLE_TIMEOUT=30000
CONNECTION_TIMEOUT=60000
```
### Claude Desktop Configuration
Add the following to your Claude Desktop configuration file:
**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
```json
{
"mcpServers": {
"postgres-mcp": {
"command": "node",
"args": ["/path/to/your/Postgres_mcp/dist/index.js"],
"env": {
"DATABASE_URL": "postgresql://username:password@localhost:5432/database_name"
}
}
}
}
```
**Important**: Replace `/path/to/your/Postgres_mcp/` with the actual absolute path to your project directory.
## Usage
### Starting the Server
You can start the server in several ways:
1. **Using the start script** (recommended):
```bash
./start-server.sh "postgresql://user:pass@localhost:5432/mydb"
```
2. **Using npm**:
```bash
npm start
```
3. **Direct node execution**:
```bash
node dist/index.js
```
### Switching Databases
To connect to a different database, you have several options:
1. **Update the DATABASE_URL in your `.env` file**
2. **Pass the URL as an argument to the start script**:
```bash
./start-server.sh "postgresql://user:pass@remote-host:5432/production_db"
```
3. **Update the Claude Desktop configuration** and restart Claude
### Available Tools
Once connected to Claude Desktop, you'll have access to these tools:
#### 1. `execute_query`
Execute any SQL query on the database.
```
Parameters:
- query (required): SQL query to execute
- params (optional): Array of parameters for prepared statements
```
#### 2. `list_tables`
List all tables in the specified schema.
```
Parameters:
- schema (optional): Schema name (defaults to 'public')
```
#### 3. `describe_table`
Get detailed information about a specific table.
```
Parameters:
- table_name (required): Name of the table to describe
- schema (optional): Schema name (defaults to 'public')
```
#### 4. `get_database_schema`
Get complete database schema information.
```
Parameters:
- schema (optional): Schema name (defaults to 'public')
```
#### 5. `insert_data`
Insert data into a table.
```
Parameters:
- table_name (required): Name of the table
- data (required): Object with column-value pairs
- schema (optional): Schema name (defaults to 'public')
```
#### 6. `update_data`
Update data in a table.
```
Parameters:
- table_name (required): Name of the table
- data (required): Object with column-value pairs to update
- where_clause (required): WHERE condition (without WHERE keyword)
- where_params (optional): Parameters for the WHERE clause
- schema (optional): Schema name (defaults to 'public')
```
#### 7. `delete_data`
Delete data from a table.
```
Parameters:
- table_name (required): Name of the table
- where_clause (required): WHERE condition (without WHERE keyword)
- where_params (optional): Parameters for the WHERE clause
- schema (optional): Schema name (defaults to 'public')
```
## Example Interactions with Claude
Once set up, you can ask Claude questions like:
- "Show me all tables in the database"
- "Describe the users table structure"
- "Find all orders from the last 30 days"
- "Insert a new user with email 'john@example.com'"
- "Update the status of order ID 123 to 'shipped'"
- "Get the database schema for the public schema"
## Database URL Formats
The server supports standard PostgreSQL connection URLs:
```
postgresql://username:password@hostname:port/database_name
postgres://username:password@hostname:port/database_name
```
Examples:
```
postgresql://postgres:password@localhost:5432/myapp
postgresql://user:pass@db.example.com:5432/production
postgresql://readonly_user:secret@replica.db.com:5432/analytics
```
## Security Considerations
- Use read-only database users when possible
- Limit database permissions based on your needs
- Use environment variables for sensitive connection details
- Consider using connection pooling limits for production environments
- Validate and sanitize data before database operations
## Troubleshooting
### Connection Issues
- Verify your DATABASE_URL is correct
- Ensure the PostgreSQL server is running and accessible
- Check firewall settings and network connectivity
- Verify user permissions and database existence
### Claude Desktop Integration
- Ensure the path in `claude_desktop_config.json` is absolute and correct
- Restart Claude Desktop after configuration changes
- Check that the project is built (`npm run build`)
- Verify the server starts without errors
### Common Error Messages
- "Database connection failed": Check your DATABASE_URL and network connectivity
- "Tool not found": Ensure the server is properly registered with Claude Desktop
- "Permission denied": Verify database user permissions
## Development
### Running in Development Mode
```bash
npm run dev
```
### Building the Project
```bash
npm run build
```
### Project Structure
```
src/
├── index.ts # Main MCP server implementation
└── postgres-service.ts # PostgreSQL service layer
dist/ # Compiled JavaScript files
claude-desktop-config.json # Example Claude Desktop configuration
start-server.sh # Startup script
env.example # Environment variables template
```
## Contributing
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request
## License
MIT License - see LICENSE file for details.
## Support
For issues and questions:
1. Check the troubleshooting section
2. Review the PostgreSQL connection documentation
3. Open an issue on the repository
---
**Happy querying with Claude and PostgreSQL! 🚀**
# Postgres_MCP
