PostgreSQL MCP Server

A comprehensive PostgreSQL Model Context Protocol (MCP) server that provides full database operation capabilities for AI agents. This server enables AI assistants to interact with PostgreSQL databases through a standardized interface, supporting all major database operations from simple queries to complex schema management.

🌟 Features

🔧 Complete Database Operations

• Connection Management: Secure database connections with pooling
• Query Execution: Execute any SQL query (SELECT, INSERT, UPDATE, DELETE, DDL)
• Schema Operations: List tables, describe structures, create tables and indexes
• Data Management: Insert, update, delete with parameterized queries
• Utilities: Database info, table backups, and more

🤖 Universal AI Agent Support

• Claude Desktop/API: Native MCP integration
• Custom AI Agents: Works with any MCP-compatible client
• Multiple Platforms: Windows, macOS, Linux support
• Easy Integration: Simple copy-and-use approach

🛡️ Production Ready

• Security: SQL injection protection via parameterized queries
• Performance: Connection pooling and query optimization
• Error Handling: Comprehensive error reporting and recovery
• Monitoring: Detailed logging and debugging support

🚀 Quick Start

Installation

# Clone the repository
git clone https://github.com/halim-23/postgresql-mcp-server.git
cd postgresql-mcp-server

# Install dependencies
npm install

# Verify installation
npm run verify

Basic Usage

1. Start the MCP server:

   npm start
   

2. Configure your AI agent to use the server (see Configuration section)

3. Connect to your database:

   // Use the connect_postgres tool with your connection string
   connection_string: "postgresql://username:password@host:port/database"
   

4. Start using database operations!

🛠️ Available Tools

Tool	Description	Example Use Case
connect_postgres	Connect to PostgreSQL database	Initial setup and authentication
execute_query	Execute any SQL query	Complex queries, joins, aggregations
list_tables	List all tables in schema	Schema exploration and discovery
describe_table	Get table structure details	Understanding data models
create_table	Create new tables	Setting up new data structures
insert_data	Insert records into tables	Adding new data entries
update_data	Update existing records	Modifying data with conditions
delete_data	Delete records from tables	Data cleanup and removal
create_index	Create database indexes	Performance optimization
backup_table	Create table backups	Data safety and versioning
get_database_info	Get database information	System diagnostics and info

⚙️ Configuration

For Claude Desktop

Add to your Claude Desktop configuration (~/.claude/config.json):

{
  "mcpServers": {
    "postgresql": {
      "command": "node",
      "args": ["/path/to/postgresql-mcp-server/src/server.js"],
      "env": {
        "NODE_ENV": "production"
      }
    }
  }
}

For Custom AI Agents

// Node.js example
const { spawn } = require('child_process');
const mcpServer = spawn('node', ['/path/to/postgresql-mcp-server/src/server.js']);

# Python example
import subprocess
mcp_server = subprocess.Popen([
    'node', '/path/to/postgresql-mcp-server/src/server.js'
], stdin=subprocess.PIPE, stdout=subprocess.PIPE)

Environment Variables

# Optional: Set default connection
export POSTGRES_CONNECTION_STRING="postgresql://user:pass@host:port/db"

# Optional: Enable debug logging
export DEBUG=postgresql-mcp:*

📚 Usage Examples

Basic Database Operations

// 1. Connect to database
await callTool('connect_postgres', {
  connection_string: "postgresql://user:password@localhost:5432/mydb"
});

// 2. List all tables
const tables = await callTool('list_tables', { schema: 'public' });

// 3. Execute a custom query
const users = await callTool('execute_query', {
  query: "SELECT * FROM users WHERE age > $1",
  params: [18]
});

// 4. Insert new data
await callTool('insert_data', {
  table_name: 'users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    age: 25
  }
});

Advanced Operations

// Create a new table
await callTool('create_table', {
  table_name: 'products',
  columns: [
    { name: 'id', type: 'SERIAL', constraints: 'PRIMARY KEY' },
    { name: 'name', type: 'VARCHAR(255)', constraints: 'NOT NULL' },
    { name: 'price', type: 'DECIMAL(10,2)' },
    { name: 'created_at', type: 'TIMESTAMP', constraints: 'DEFAULT NOW()' }
  ]
});

// Create an index for performance
await callTool('create_index', {
  index_name: 'idx_products_name',
  table_name: 'products',
  columns: ['name'],
  unique: false
});

🔧 Development

Project Structure

postgresql-mcp-server/
├── src/
│   └── server.js              # Main MCP server implementation
├── test/
│   └── test-client.js         # Test client for validation
├── scripts/
│   ├── setup.js              # Setup and initialization
│   └── verify-installation.js # Installation verification
├── docs/
│   ├── API.md                # Detailed API documentation
│   ├── EXAMPLES.md           # Usage examples
│   └── TROUBLESHOOTING.md    # Common issues and solutions
├── config/
│   └── examples/             # Configuration examples
└── README.md

Running Tests

# Run verification tests
npm run verify

# Run integration tests (requires PostgreSQL)
npm test

# Development mode with inspection
npm run dev

Contributing

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Commit changes: git commit -m 'Add amazing feature'
4. Push to branch: git push origin feature/amazing-feature
5. Open a Pull Request

🔒 Security

Connection Security

• Always use strong passwords and secure connection strings
• Enable SSL/TLS for production databases: ?sslmode=require
• Use environment variables for sensitive configuration
• Implement proper database user permissions

Query Security

• All queries use parameterized statements to prevent SQL injection
• Input validation and sanitization
• Connection pooling with limits to prevent resource exhaustion

📖 Documentation

• API Reference - Detailed tool documentation
• Examples - Comprehensive usage examples
• Integration Guide - AI agent integration
• Troubleshooting - Common issues and solutions

🤝 Support

Getting Help

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Documentation: Check the docs directory

Common Issues

• Connection Problems: Verify PostgreSQL is running and credentials are correct
• Permission Errors: Check database user permissions
• Module Not Found: Run npm install to install dependencies

🎯 Roadmap

• [ ] Performance Monitoring: Built-in query performance tracking
• [ ] Schema Migration Support: Database migration tools
• [ ] Multiple Database Support: Connection to multiple databases
• [ ] GUI Configuration: Web-based configuration interface
• [ ] Docker Support: Containerized deployment options
• [ ] TypeScript Support: Full TypeScript implementation

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• Model Context Protocol for the excellent standard
• PostgreSQL for the robust database system
• node-postgres for the PostgreSQL client library
• The open-source community for continuous inspiration

📊 Stats

---

⭐ If this project helps you, please consider giving it a star on GitHub! ⭐