# PostgreSQL MCP Server

A Model Context Protocol (MCP) server that enables AI assistants to interact with PostgreSQL databases using natural language queries. This server provides secure, read-only access to database schemas and allows for natural language to SQL translation.

## Features

- 🔒 **Secure**: Read-only operations only (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH)
- 🗄️ **Schema Inspection**: List tables, describe structures, view relationships
- 🔍 **Query Execution**: Execute SQL queries with safety checks and result limits
- 🌐 **Natural Language**: Enables AI assistants to convert natural language to SQL
- ⚡ **Fast**: Built with TypeScript and the official MCP SDK

## Available Tools

### Database Connection
- `connect_database` - Connect to a PostgreSQL database
- `disconnect_database` - Disconnect from the current database

### Schema Inspection
- `list_tables` - List all tables in the current database
- `describe_table` - Get detailed information about a table's structure
- `get_schema` - Get an overview of the database schema and relationships

### Query Execution
- `execute_query` - Execute read-only SQL queries with safety checks

## Quick Test with Docker

### 1. Start Test Database
```bash
npm run docker:up
```

### 2. Run Tests
```bash
npm run test
```

### 3. Try Manual Testing
```bash
npm run test:manual
```

### 4. Clean Up
```bash
npm run docker:down
```

## Installation

### Prerequisites
- Node.js 16 or higher
- PostgreSQL database access
- Claude for Desktop (for testing with AI assistant)

### Setup

1. **Clone and build**:
   ```bash
   git clone https://github.com/melihbirim/pg-mcp.git
   cd pg-mcp
   npm install
   npm run build
   ```

2. **Configure Claude for Desktop**:
   Edit your Claude configuration file:
   ```bash
   # macOS
   code ~/Library/Application\ Support/Claude/claude_desktop_config.json
   
   # Windows
   code %APPDATA%\\Claude\\claude_desktop_config.json
   ```

3. **Add server configuration**:
   ```json
   {
     "mcpServers": {
       "postgresql": {
         "command": "node",
         "args": ["/ABSOLUTE/PATH/TO/pg-mcp/build/index.js"]
       }
     }
   }
   ```

4. **Restart Claude for Desktop**

## Usage Examples

Once connected to Claude for Desktop, you can use natural language to query your database:

### Connect to Database
"Connect to my PostgreSQL database named 'inventory' on localhost with username 'admin'"

### Schema Exploration
- "What tables are in this database?"
- "Describe the users table structure"
- "Show me the database schema"

### Natural Language Queries
- "How many users are in the system?"
- "Show me all products with price greater than $100"
- "What are the top 5 customers by order count?"
- "Find all orders placed in the last 30 days"

## Security Features

- **Read-only operations**: Only SELECT, SHOW, DESCRIBE, EXPLAIN, and WITH statements allowed
- **Query validation**: Automatic checking for dangerous operations
- **Result limits**: Automatic LIMIT clauses added to prevent large result sets
- **Error handling**: Secure error messages without exposing sensitive information

## Development

### Project Structure
```
pg-mcp/
├── src/
│   └── index.ts          # Main MCP server implementation
├── build/                # Compiled JavaScript output
├── .github/
│   └── copilot-instructions.md
├── .vscode/
│   └── mcp.json          # VS Code MCP configuration
├── package.json
├── tsconfig.json
└── README.md
```

### Building
```bash
npm run build
```

### Running Locally
```bash
npm run dev
```

### Testing
The server communicates via stdio, so you can test it by running:
```bash
echo '{"jsonrpc": "2.0", "id": 1, "method": "initialize", "params": {"protocolVersion": "2024-11-05", "capabilities": {}, "clientInfo": {"name": "test", "version": "1.0.0"}}}' | node build/index.js
```

## Environment Variables

You can optionally set these environment variables:
- `PG_HOST` - PostgreSQL host (default: localhost)
- `PG_PORT` - PostgreSQL port (default: 5432)
- `PG_DATABASE` - Default database name
- `PG_USER` - Default username
- `PG_PASSWORD` - Default password

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

ISC License

## Troubleshooting

### Claude for Desktop Issues
- Check Claude's logs: `~/Library/Logs/Claude/mcp*.log`
- Ensure absolute paths in configuration
- Restart Claude for Desktop after config changes

### Database Connection Issues
- Verify PostgreSQL is running
- Check connection credentials
- Ensure network access to database host

### Permission Issues
```bash
chmod +x build/index.js
```