📊

pg-lens

Secure PostgreSQL integration for AI assistants via Model Context Protocol

0 installs

Trust: 39 — Low

Data

Installation

npx pg-lens-mcp

Ask AI about pg-lens

I know everything about pg-lens. Ask me about installation, configuration, usage, or troubleshooting.

0/500

Loading tools...

Reviews

Documentation

PG Lens MCP Server

Securely connect AI assistants to your PostgreSQL databases

Features • Installation • Configuration • Tools • Security

✨ 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

list_schemas — List all non-system schemas

Discover all user-defined schemas in your database.

Example usage:

"List all schemas in the database"

Returns: Markdown table with schema names and owners

list_tables — List all tables in a schema

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.)

search_column — Find tables containing a column pattern

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

get_table_info — Get comprehensive table schema

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

📊 Data Querying

get_table_data — Query table data with structured filters

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)

execute_query — Execute custom read-only SQL

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

⚡ Performance Analysis

explain_query — Get query execution plan (without running query)

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

explain_analyze — Execute and profile query performance

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.

🔐 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

Add the server to your MCP configuration
Restart Claude Desktop
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:

Use explain_analyze tool to identify bottlenecks
Check if indexes exist on frequently queried columns
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.

Built for the Model Context Protocol ecosystem

Made with ❤️ for AI-assisted database exploration

pg-lens

Installation

Reviews

Documentation

PG Lens MCP Server

✨ Features

🏗️ Architecture

📦 Installation

⚙️ Configuration

Environment Variables

MCP Configuration

Option 1: Direct Node.js (Local Installation)

Option 2: Using npx (No Installation Required)

Option 3: Using Docker

🛠️ Available Tools

🗂️ Schema Discovery

📊 Data Querying

⚡ Performance Analysis

🔐 Security

Database-Enforced Read-Only Access

SQL Injection Protection

🧪 Testing the Server

Quick Test

Testing with Claude Desktop

📋 Troubleshooting

Connection Issues

Performance

📄 License

🤝 Contributing

Tags

Security Checklist