# PostgreSQL MCP Server

[![npm version](https://badge.fury.io/js/postgresql-mcp-server.svg)](https://badge.fury.io/js/postgresql-mcp-server)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Node.js Version](https://img.shields.io/badge/node-%3E%3D16.0.0-brightgreen.svg)](https://nodejs.org/)
[![MCP Compatible](https://img.shields.io/badge/MCP-Compatible-blue.svg)](https://modelcontextprotocol.io/)

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

```bash
# 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:**
   ```bash
   npm start
   ```

2. **Configure your AI agent** to use the server (see [Configuration](#configuration) section)

3. **Connect to your database:**
   ```javascript
   // 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`):

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

### For Custom AI Agents

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

```python
# 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

```bash
# 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

```javascript
// 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

```javascript
// 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

```bash
# 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](docs/API.md)** - Detailed tool documentation
- **[Examples](docs/EXAMPLES.md)** - Comprehensive usage examples  
- **[Integration Guide](docs/INTEGRATION.md)** - AI agent integration
- **[Troubleshooting](docs/TROUBLESHOOTING.md)** - Common issues and solutions

## 🤝 Support

### Getting Help
- **Issues**: [GitHub Issues](https://github.com/halim-23/postgresql-mcp-server/issues)
- **Discussions**: [GitHub Discussions](https://github.com/halim-23/postgresql-mcp-server/discussions)
- **Documentation**: Check the [docs](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](LICENSE) file for details.

## 🙏 Acknowledgments

- [Model Context Protocol](https://modelcontextprotocol.io/) for the excellent standard
- [PostgreSQL](https://postgresql.org/) for the robust database system
- [node-postgres](https://node-postgres.com/) for the PostgreSQL client library
- The open-source community for continuous inspiration

## 📊 Stats

![GitHub stars](https://img.shields.io/github/stars/halim-23/postgresql-mcp-server?style=social)
![GitHub forks](https://img.shields.io/github/forks/halim-23/postgresql-mcp-server?style=social)
![GitHub issues](https://img.shields.io/github/issues/halim-23/postgresql-mcp-server)
![GitHub pull requests](https://img.shields.io/github/issues-pr/halim-23/postgresql-mcp-server)

---

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