# PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides a standardized interface for interacting with PostgreSQL databases. This server enables LLMs and other tools to perform database operations through a consistent protocol.

## Features

- **Full CRUD Operations**: Execute queries, insert, update, and delete data
- **Schema Management**: Create/drop tables, manage indexes
- **Database Introspection**: List tables, describe table structures, analyze indexes
- **Performance Analysis**: Query performance analysis and table statistics
- **Configurable Schema Support**: Use custom schemas or default to 'public'
- **Connection Pooling**: Efficient database connection management
- **Type Safety**: Full TypeScript support

## Installation

```bash
npm install
```

## Configuration

Create a `.env` file in the project root with your PostgreSQL connection details:

```env
# PostgreSQL Database Configuration
DATABASE_URL=postgresql://username:password@localhost:5432/dbname

# Or use individual parameters
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database
DB_USER=your_username
DB_PASSWORD=your_password
DB_SCHEMA=          # Optional: defaults to 'public' if not specified

# Connection Pool Settings (optional)
DB_POOL_MIN=2
DB_POOL_MAX=10
DB_POOL_IDLE_TIMEOUT=30000

# Logging Level (optional)
LOG_LEVEL=info
```

### Schema Configuration

- If `DB_SCHEMA` is empty or not provided, the server uses 'public' schema
- If `DB_SCHEMA` is set (e.g., `DB_SCHEMA=myschema`), that schema is used as default
- Individual tool calls can override the default by providing a `schema` parameter

## Building

```bash
npm run build
```

## Running the Server

```bash
npm start
```

The server communicates via stdin/stdout using the MCP protocol.

## Available Tools

### Query Operations
- **execute_query**: Execute SELECT queries with optional parameters
- **analyze_query_performance**: Analyze query execution plans

### Data Manipulation
- **insert_data**: Insert data into tables
- **update_data**: Update existing data
- **delete_data**: Delete data from tables

### Schema Operations
- **create_table**: Create new tables with column definitions
- **drop_table**: Drop existing tables
- **create_index**: Create indexes on table columns

### Introspection
- **list_tables**: List all tables in a schema
- **describe_table**: Get detailed table structure information
- **list_indexes**: List all indexes for a table
- **get_table_stats**: Get table statistics (row count, size, etc.)

## Development

```bash
# Run in development mode with auto-reload
npm run dev

# Clean build artifacts
npm run clean

# Run tests (if available)
npm test
```

## MCP Protocol

This server implements the Model Context Protocol, allowing it to be used with any MCP-compatible client. The protocol uses JSON-RPC 2.0 for communication.

### Example Request

```json
{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "list_tables",
    "arguments": {
      "schema": "public"
    }
  },
  "id": 1
}
```

### Example Response

```json
{
  "jsonrpc": "2.0",
  "result": {
    "content": [
      {
        "type": "text",
        "text": "{\"success\": true, \"tables\": [...]}"
      }
    ]
  },
  "id": 1
}
```

## Security Considerations

- Never commit `.env` files with real credentials
- Use environment-specific configurations for different deployments
- Consider using connection pooling for production environments
- Implement proper access controls at the database level

## License

MIT

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.