# PostgreSQL MCP Server

A comprehensive PostgreSQL server for the Model Context Protocol (MCP) that provides database access through MCP tools and resources.

## Features

- **Database Operations**: Execute SQL queries (SELECT, INSERT, UPDATE, DELETE)
- **Schema Management**: Get table schemas, list tables, create tables
- **Database Statistics**: Get database stats and connection information
- **Connection Management**: Test connections and monitor pool status
- **MCP Resources**: Access database schema and connection status as resources
- **Environment Configuration**: Configure via environment variables
- **npx Support**: Can be run via npx for easy integration with MCP clients

## Installation

### Local Installation
```bash
npm install
```

### Global Installation (for npx usage)
```bash
npm install -g mcp-server-postgres
```

### Using npx (recommended)
```bash
npx mcp-server-postgres
```

## Usage

### Running with npx
The server can be run directly with npx, making it easy to integrate with MCP clients:

```bash
npx mcp-server-postgres
```

### MCP Client Configuration

#### For Local Development
Add this to your MCP client configuration (e.g., Claude Desktop) for local development:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["c:\\Users\\CascadeProjects\\mcp-postgres-server\\bin\\mcp-server-postgres.js"],
      "env": {
        "POSTGRES_USER": "pguser",
        "POSTGRES_HOST": "localhost",
        "POSTGRES_DB": "postgres",
        "POSTGRES_PASSWORD": "admin",
        "POSTGRES_PORT": "5432",
        "POSTGRES_SSL": "false"
      }
    }
  }
}
```

#### For Published Package (npx)
If the package is published to npm registry:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["mcp-server-postgres"],
      "env": {
        "POSTGRES_USER": "pguser",
        "POSTGRES_HOST": "localhost",
        "POSTGRES_DB": "postgres",
        "POSTGRES_PASSWORD": "admin",
        "POSTGRES_PORT": "5432",
        "POSTGRES_SSL": "false"
      }
    }
  }
}
```

### Environment Variables

Configure your PostgreSQL connection using these environment variables:

- `POSTGRES_USER` - Database username (default: postgres)
- `POSTGRES_HOST` - Database host (default: localhost)
- `POSTGRES_DB` - Database name (default: postgres)
- `POSTGRES_PASSWORD` - Database password
- `POSTGRES_PORT` - Database port (default: 5432)
- `POSTGRES_SSL` - Enable SSL (default: false)

### Available Tools

- `execute_query` - Execute SQL queries with optional parameters
- `get_table_schema` - Get detailed schema information for tables
- `list_tables` - List all tables in the database
- `get_database_stats` - Get database statistics and metadata
- `create_table` - Create new tables with columns and constraints
- `test_connection` - Test database connection and get status

### Available Resources

- `postgres://schema/all` - Complete database schema information
- `postgres://status` - Current connection status and pool information

## Testing

Run the test scripts to verify functionality:

```bash
npm test
npm run test-postgres
```