<div align="center">

# PG Lens MCP Server

**Securely connect AI assistants to your PostgreSQL databases**

![Version](https://img.shields.io/npm/v/pg-lens-mcp?style=for-the-badge)
![License](https://img.shields.io/badge/license-MIT-green?style=for-the-badge)
![Node](https://img.shields.io/badge/node-%3E%3D18-brightgreen?style=for-the-badge)
![TypeScript](https://img.shields.io/badge/TypeScript-5.7-blue?style=for-the-badge)

[Features](#-features) • [Installation](#-installation) • [Configuration](#️-configuration) • [Tools](#️-available-tools) • [Security](#-security)

</div>

---

## ✨ Features

| Feature | Description |
|---------|-------------|
| **Read-Only by Design** | All queries execute in database-enforced READ ONLY transactions |
| **Full Schema Discovery** | Explore schemas, tables, columns, indexes, and relationships |
| **Query Performance Analysis** | Built-in EXPLAIN and EXPLAIN ANALYZE for optimization |
| **SQL-Injection Safe** | Structured filters with parameterized queries |
| **Token-Optimized Output** | Markdown table formatting reduces AI token usage by ~40-60% |
| **8 Powerful Tools** | Complete toolkit for database exploration and analysis |
| **Production-Ready** | Configurable connection pooling with timeouts and health checks |  

---

## 🏗️ Architecture

```mermaid
graph LR
    A[Claude/AI Assistant] -->|MCP Protocol| B[PostgreSQL MCP Server]
    B -->|READ ONLY Transactions| C[(PostgreSQL Database)]
    
    style B fill:#4CAF50,color:#fff
    style C fill:#336791,color:#fff
```

The server acts as a secure bridge between AI assistants and your PostgreSQL database, enforcing read-only access at the database transaction level.

---

## 📦 Installation

```bash
git clone https://github.com/YohannHommet/pg-lens-mcp.git
cd pg-lens-mcp
npm install
npm run build
```

---

## ⚙️ Configuration

### Environment Variables

Configure the connection using environment variables:

| Variable | Description | Default |
|----------|-------------|---------|
| `DB_HOST` | PostgreSQL host | `localhost` |
| `DB_PORT` | PostgreSQL port | `5432` |
| `DB_DATABASE` | Database name | `postgres` |
| `DB_USERNAME` | Database user | `postgres` |
| `DB_PASSWORD` | Database password | `postgres` |
| `DB_SCHEMA` | Default schema | `public` |
| `DB_MAX_CONNECTIONS` | Connection pool size | `10` |
| `DB_IDLE_TIMEOUT_MS` | Idle connection timeout | `30000` |
| `DB_CONNECTION_TIMEOUT_MS` | Connection attempt timeout | `5000` |

### MCP Configuration

Add to your Claude Desktop config (`~/.claude/claude_desktop_config.json`):

##### Option 1: Direct Node.js (Local Installation)

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-server/dist/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_DATABASE": "your_database",
        "DB_USERNAME": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_SCHEMA": "public"
      }
    }
  }
}
```

##### Option 2: Using npx (No Installation Required)

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "pg-lens-mcp"
      ],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_DATABASE": "your_database",
        "DB_USERNAME": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_SCHEMA": "public"
      }
    }
  }
}
```



##### Option 3: Using Docker

```json
{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "--rm",
        "-i",
        "--network=host",
        "-e", "DB_HOST=localhost",
        "-e", "DB_PORT=5432",
        "-e", "DB_DATABASE=your_database",
        "-e", "DB_USERNAME=your_username",
        "-e", "DB_PASSWORD=your_password",
        "-e", "DB_SCHEMA=public",
        "pg-lens-mcp:latest"
      ]
    }
  }
}
```

**Docker-specific notes:**
- Use `--network=host` for connecting to localhost databases
- For remote databases, you can remove `--network=host`
- For databases in other Docker containers, use custom networks:
  ```json
  "args": [
    "run", "--rm", "-i",
    "--network=your_docker_network",
    "-e", "DB_HOST=postgres_container_name",
    ...
  ]
  ```

> **💡 Tip:** Use a read-only database user for extra security, even though all queries run in READ ONLY transactions.

---

## 🛠️ Available Tools

### 🗂️ Schema Discovery

<details>
<summary><b>list_schemas</b> — List all non-system schemas</summary>

Discover all user-defined schemas in your database.

**Example usage:**
```
"List all schemas in the database"
```

**Returns:** Markdown table with schema names and owners

</details>

<details>
<summary><b>list_tables</b> — List all tables in a schema</summary>

**Parameters:**
- `schema` *(optional)* — Schema name (default: `public`)

**Example usage:**
```
"Show me all tables in the public schema"
```

**Returns:** Markdown table with table names and types (TABLE, VIEW, etc.)

</details>

<details>
<summary><b>search_column</b> — Find tables containing a column pattern</summary>

**Parameters:**
- `column_pattern` *(required)* — Partial or full column name (case-insensitive)

**Example usage:**
```
"Find all tables that have an 'email' column"
```

**Returns:** Markdown table showing schema, table, column name, data type, and nullability

</details>

<details>
<summary><b>get_table_info</b> — Get comprehensive table schema</summary>

**Parameters:**
- `table_name` *(required)* — Name of the table to inspect
- `schema` *(optional)* — Schema name (default: `public`)

**Example usage:**
```
"Show me the complete structure of the users table"
```

**Returns:** JSON with:
- Column details (name, type, nullability, defaults)
- Primary keys
- Foreign key relationships
- Indexes with uniqueness information

</details>

---

### 📊 Data Querying

<details>
<summary><b>get_table_data</b> — Query table data with structured filters</summary>

**Parameters:**
- `table_name` *(required)* — Table to query
- `schema` *(optional)* — Schema name (default: `public`)
- `columns` *(optional)* — Specific columns to select (default: all)
- `filters` *(optional)* — **Structured filters** (SQL injection safe!)
  ```typescript
  [{
    column: "status",
    operator: "=",  // Options: =, !=, <, >, <=, >=, LIKE, ILIKE, IN, IS NULL, IS NOT NULL
    value: "active"
  }]
  ```
- `limit` *(optional)* — Max rows to return (default: 100, max: 1000)
- `offset` *(optional)* — Rows to skip for pagination
- `order_by` *(optional)* — Column to sort by
- `order_direction` *(optional)* — `ASC` or `DESC` (default: `ASC`)

**Example usage:**
```
"Get the first 20 active users created after 2024-01-01, ordered by creation date"
```

**Returns:** Markdown table with:
- Query results
- Metadata (total rows, returned rows, pagination info)

</details>

<details>
<summary><b>execute_query</b> — Execute custom read-only SQL</summary>

**Parameters:**
- `query` *(required)* — SQL SELECT query
- `params` *(optional)* — Query parameters for `$1`, `$2`, etc.

**Example usage:**
```
"Execute this query: 
SELECT u.name, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.name 
ORDER BY order_count DESC 
LIMIT 10"
```

**Returns:** Markdown table with query results

**Security:** Runs in `BEGIN TRANSACTION READ ONLY` — PostgreSQL itself enforces no writes can occur

</details>

---

### ⚡ Performance Analysis

<details>
<summary><b>explain_query</b> — Get query execution plan (without running query)</summary>

**Parameters:**
- `query` *(required)* — SQL query to analyze
- `format` *(optional)* — `text`, `json`, or `yaml` (default: `json`)
- `verbose` *(optional)* — Include verbose details (default: `false`)

**Example usage:**
```
"Explain how PostgreSQL would execute: SELECT * FROM users WHERE email LIKE '%@example.com'"
```

**Returns:** Query execution plan showing:
- Scan types (Sequential Scan, Index Scan, etc.)
- Estimated costs and row counts
- Join strategies

**Use case:** Understanding query performance before optimization

</details>

<details>
<summary><b>explain_analyze</b> — Execute and profile query performance</summary>

**Parameters:**
- `query` *(required)* — SQL query to analyze
- `format` *(optional)* — `text` or `json` (default: `json`)
- `buffers` *(optional)* — Include buffer usage stats (default: `false`)
- `timing` *(optional)* — Include timing info (default: `true`)
- `verbose` *(optional)* — Verbose output (default: `false`)

**Example usage:**
```
"Analyze the actual performance of: SELECT * FROM large_table WHERE indexed_column = 'value'"
```

**Returns:** Actual execution statistics including:
- Real execution time
- Actual rows processed vs. estimated
- Buffer hits/misses (if `buffers: true`)
- Node-level timing breakdown

⚠️ **Note:** This actually executes the query (in READ ONLY mode). May be slow on large datasets.

</details>

---

## 🔐 Security

### Database-Enforced Read-Only Access

Unlike simple keyword filtering, this server uses **PostgreSQL's transactional READ ONLY mode**:

```typescript
await client.query('BEGIN TRANSACTION READ ONLY');
const result = await client.query(userQuery);  // ← PostgreSQL blocks ANY writes
await client.query('COMMIT');
```

**Why this matters:**

✅ **No false positives** — Queries containing words like "UPDATE" or "INSERT" in strings/comments work fine  
✅ **No bypasses** — Cannot be circumvented via stored procedures, functions, or extensions  
✅ **Database-level guarantee** — PostgreSQL itself enforces the read-only constraint  

### SQL Injection Protection

**Structured filters** replace dangerous string concatenation:

❌ **Unsafe approach:**
```javascript
query += ` WHERE ${userInput}`  // Direct concatenation = SQL injection risk
```

✅ **Our approach:**
```typescript
filters: [{
  column: "status",
  operator: "=",
  value: "active"
}]
// Becomes: WHERE "status" = $1 
// PostgreSQL handles escaping automatically
```

All user inputs are properly parameterized, eliminating SQL injection vectors.

---

## 🧪 Testing the Server

### Quick Test

```bash
# Set your database credentials
export DB_HOST=localhost
export DB_DATABASE=your_database
export DB_USERNAME=your_username
export DB_PASSWORD=your_password

# Start the server
node dist/index.js
```

**Expected output:**
```
✓ Database connection verified
✓ PostgreSQL MCP Server running on stdio
```

### Testing with Claude Desktop

1. Add the server to your MCP configuration
2. Restart Claude Desktop
3. Try these example prompts:
   - "List all schemas in the database"
   - "Show me the structure of the users table"
   - "Find all tables with a 'created_at' column"
   - "Explain the query plan for SELECT * FROM large_table LIMIT 10"

---

## 📋 Troubleshooting

### Connection Issues

**"password authentication failed"**
- Check `DB_USERNAME` and `DB_PASSWORD` are correct
- Verify the user has access to the specified database

**"Connection timeout"**
- Check `DB_HOST` is reachable
- Verify PostgreSQL is running on `DB_PORT`
- Check firewall rules if connecting remotely

**"database does not exist"**
- Verify `DB_DATABASE` name is correct
- List available databases: `psql -l`

### Performance

If queries are slow:
1. Use `explain_analyze` tool to identify bottlenecks
2. Check if indexes exist on frequently queried columns
3. Consider adjusting `DB_MAX_CONNECTIONS` based on your workload

---

## 📄 License

MIT License

---

## 🤝 Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

---

<div align="center">

**Built for the Model Context Protocol ecosystem**

Made with ❤️ for AI-assisted database exploration

</div>