pg-lens-mcp

<div align="center">

PG Lens MCP Server

Securely connect AI assistants to your PostgreSQL databases

!Version !License !Node !TypeScript

Features • Installation • Configuration • Tools • 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

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

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)

{
  "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)

{
  "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

{
  "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:

  "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!)

  [{
    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:

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

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