<div align="center">

PG Lens MCP Server

Securely connect AI assistants to your PostgreSQL databases

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:

query += ` WHERE ${userInput}`  // Direct concatenation = SQL injection risk

✅ Our approach:

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>