npm package: @opichi/postgres-inspector
Description: A generic PostgreSQL database inspector MCP server
Author: Opichi
Latest version: 2.0.0

# @opichi/postgres-inspector

A generic PostgreSQL database inspector MCP (Model Context Protocol) server that allows AI assistants to inspect any PostgreSQL database.

## Features

- **Generic Database Support**: Works with any PostgreSQL database via connection URI
- **Comprehensive Inspection Tools**: 
  - List all tables
  - Show table schemas
  - Preview table data
  - Execute read-only SELECT queries
  - Count table rows
  - Show foreign key relationships
- **Safe Write Operations**: 
  - INSERT operations with user approval
  - UPDATE operations with user approval
  - Non-destructive schema changes with user approval
  - Comprehensive impact analysis before execution
  - Transaction-based execution with automatic rollback
- **Advanced Safety Features**:
  - Strict prohibition of destructive operations (DROP, TRUNCATE, DELETE)
  - Mandatory user approval for all write operations
  - Detailed risk assessment and rollback strategies
  - Audit trail for all approved operations
- **Easy Integration**: Works with Claude Desktop, Windsurf, Cursor, and other MCP-compatible clients

## Installation

```bash
npx @opichi/postgres-inspector
```

## Usage

### With Claude Desktop

Add this to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}
```

### With Windsurf

Add this to your Windsurf MCP configuration:

```json
{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}
```

### With Cursor

Add this to your Cursor MCP configuration:

```json
{
  "mcpServers": {
    "postgres-inspector": {
      "command": "npx",
      "args": ["@opichi/postgres-inspector"]
    }
  }
}
```

## Available Tools

### `list-tables`
Lists all tables in the specified PostgreSQL database.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI

### `show-schema`
Shows the schema (columns, types, etc.) for a specific table.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `tableName` (string): Name of the table to inspect

### `show-data`
Shows a preview of data from a table.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `tableName` (string): Name of the table to query
- `limit` (number, optional): Maximum number of rows to return (default: 10)

### `execute-query`
Executes a read-only SELECT SQL query.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `query` (string): SQL SELECT query to execute

### `count-rows`
Counts the total number of rows in a table.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `tableName` (string): Name of the table to count

### `show-relationships`
Shows the foreign key relationships between tables.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI

## Safe Write Operations

⚠️ **IMPORTANT**: All write operations require explicit user approval and provide detailed impact analysis before execution.

### `propose-insert`
Proposes an INSERT operation for user approval with detailed impact analysis.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `sql` (string): INSERT SQL statement to propose
- `description` (string): Human-readable description of what this INSERT will accomplish

**Example:**
```sql
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')
```

### `propose-update`
Proposes an UPDATE operation for user approval with detailed impact analysis.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `sql` (string): UPDATE SQL statement to propose
- `description` (string): Human-readable description of what this UPDATE will accomplish

**Example:**
```sql
UPDATE users SET last_login = NOW() WHERE id = 123
```

### `propose-schema-change`
Proposes a non-destructive schema change for user approval with detailed impact analysis.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `sql` (string): Schema change SQL statement to propose (ALTER TABLE ADD, CREATE INDEX, etc.)
- `description` (string): Human-readable description of what this schema change will accomplish

**Example:**
```sql
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20)
```

### `execute-approved-write`
Executes a previously approved write operation using its approval ID.

**Parameters:**
- `approvalId` (string): The approval ID from a previous propose-* operation

### `list-pending-approvals`
Lists all pending write operations awaiting approval.

**Parameters:** None

### `cancel-approval`
Cancels a pending approval request.

**Parameters:**
- `approvalId` (string): The approval ID to cancel

## Write Operation Workflow

1. **Propose Operation**: Use `propose-insert`, `propose-update`, or `propose-schema-change`
2. **Review Impact Analysis**: The system provides:
   - 🔍 Detailed explanation of what will happen
   - 🎯 Risk assessment (🟢 Low, 🟡 Medium, 🔴 High)
   - 📊 Estimated number of affected rows
   - 🔄 Rollback strategy and recommendations
   - 📋 List of affected tables
3. **Approve or Cancel**: Use `execute-approved-write` to proceed or `cancel-approval` to abort
4. **Execution**: Operation runs within a transaction with automatic rollback on errors
5. **Audit Trail**: All operations are logged with timestamps and approval IDs

## Safety Guarantees

### ✅ Allowed Operations
- `INSERT` statements (adding new data)
- `UPDATE` statements (modifying existing data)
- `CREATE` statements (tables, indexes, views)
- `ALTER TABLE ADD` statements (adding columns, constraints)

### ❌ Prohibited Operations
- `DROP` statements (tables, columns, databases)
- `TRUNCATE` statements
- `DELETE` statements
- `ALTER TABLE DROP` statements (removing columns)
- Any destructive schema changes

### 🛡️ Safety Features
- **Transaction Isolation**: All writes execute within transactions
- **Automatic Rollback**: Failed operations are automatically rolled back
- **Impact Analysis**: Detailed assessment before any write operation
- **Approval Required**: No write operation can execute without explicit user approval
- **Audit Trail**: Complete logging of all approved operations

### `count-rows`
Counts the total number of rows in a table.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI
- `tableName` (string): Name of the table to count

### `show-relationships`
Shows the foreign key relationships between tables.

**Parameters:**
- `connectionUri` (string): PostgreSQL connection URI

## Connection URI Format

The connection URI should follow the PostgreSQL standard format:

```
postgresql://username:password@hostname:port/database_name
```

Example:
```
postgresql://myuser:mypassword@localhost:5432/mydatabase
```

## Security

- Only SELECT queries are allowed for safety
- Connection pooling is configured with reasonable limits
- All database connections are properly closed after use

## Development

```bash
# Clone the repository
git clone https://github.com/opichillc/postgres-inspector-mcp.git
cd postgres-inspector-mcp

# Install dependencies
npm install

# Build the project
npm run build

# Test locally
node build/index.js
```

## License

MIT

## Contributing

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