postgres-mcp-js

The open-source PostgreSQL MCP server for AI assistants. Give Claude, Cursor, Windsurf, or any MCP-compatible AI safe, structured access to your Postgres database — without ever exposing credentials.

What is this?

postgres-mcp-js is a Model Context Protocol (MCP) server that connects AI assistants to PostgreSQL databases. Instead of copy-pasting schema dumps or query results into a chat window, your AI can directly inspect your database, diagnose performance problems, detect missing indexes, analyze locks, and run read-only queries — all through a single, secure interface.

Works with any AI client that supports MCP: Claude Desktop, Claude Code, Cursor, Windsurf, Zed, and more.

Features

• Connect up to 3 databases at once — primary, secondary (replica), tertiary (analytics, staging)
• 13 built-in tools — schema inspection, health checks, index analysis, lock detection, bloat analysis, query plans
• Credential isolation — DB passwords never appear in tool calls or AI responses; the AI only sees aliases like "primary"
• Restricted mode — read-only transactions enforced at the PostgreSQL level, 30-second timeout
• Index miss detection — finds tables suffering from sequential scans and FK columns without indexes
• Lock analysis — surfaces blocking chains and idle-in-transaction sessions in real time
• Table bloat analysis — dead-tuple bloat, index bloat, VACUUM recommendations
• hypopg support — simulate hypothetical indexes before creating them
• pg_stat_statements support — surface the slowest, most expensive queries in your workload
• Zero config for users — credentials passed via the MCP client env block, no .env file needed

Quick start

Option 1 — npx (no install)

npx postgres-mcp-js

Option 2 — global install

npm install -g postgres-mcp-js
postgres-mcp

Option 3 — local clone

git clone https://github.com/shubham4038/postgres-mcp-js.git
cd postgres-mcp-js
npm install && npm run build
node dist/index.js

Connect to your AI

Credentials go in the env block of your MCP client config — no .env file needed. The server reads them directly from the process environment at startup.

Claude Desktop

File: ~/Library/Application Support/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["postgres-mcp-js"],
      "env": {
        "DB_PRIMARY_URL": "postgresql://user:pass@localhost:5432/mydb",
        "DB_PRIMARY_NAME": "primary",
        "ACCESS_MODE": "restricted"
      }
    }
  }
}

Claude Code

File: ~/.claude/settings.json

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["postgres-mcp-js"],
      "env": {
        "DB_PRIMARY_URL": "postgresql://user:pass@localhost:5432/mydb",
        "ACCESS_MODE": "restricted"
      }
    }
  }
}

Cursor

Go to Settings → MCP → Add new MCP server:

{
  "command": "npx",
  "args": ["postgres-mcp-js"],
  "env": {
    "DB_PRIMARY_URL": "postgresql://user:pass@localhost:5432/mydb",
    "ACCESS_MODE": "restricted"
  }
}

Windsurf

File: ~/.codeium/windsurf/mcp_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["postgres-mcp-js"],
      "env": {
        "DB_PRIMARY_URL": "postgresql://user:pass@localhost:5432/mydb",
        "ACCESS_MODE": "restricted"
      }
    }
  }
}

Any MCP-compatible client

{
  "command": "node",
  "args": ["/path/to/postgres-mcp-js/dist/index.js"],
  "env": {
    "DB_PRIMARY_URL": "postgresql://...",
    "ACCESS_MODE": "restricted"
  }
}

Multiple databases

Connect up to 3 Postgres databases simultaneously. Each gets an alias — the AI uses the alias to target the right database, and never sees the actual connection string.

{
  "env": {
    "DB_PRIMARY_URL":    "postgresql://user:pass@primary:5432/mydb",
    "DB_PRIMARY_NAME":   "primary",

    "DB_SECONDARY_URL":  "postgresql://user:pass@replica:5432/mydb",
    "DB_SECONDARY_NAME": "replica",

    "DB_TERTIARY_URL":   "postgresql://user:pass@analytics:5432/analytics",
    "DB_TERTIARY_NAME":  "analytics",

    "ACCESS_MODE": "restricted"
  }
}

Example AI prompts that use multi-DB:

• "Compare buffer cache hit ratio between primary and replica"
• "Is the analytics DB missing the same indexes as production?"
• "Show me the top slow queries on staging but not on production"

Tools

Every tool accepts an optional database parameter (defaults to primary).

Schema inspection

| Tool | Description | |------|-------------| | list_databases | Show configured aliases — credentials never revealed | | list_schemas | All schemas with system vs user classification | | list_objects | Tables, views, sequences, extensions in a schema | | get_object_details | Columns, constraints, and indexes for any object |

SQL & query plans

| Tool | Description | |------|-------------| | execute_sql | Run SQL. Read-only in restricted mode; full access in unrestricted | | explain_query | EXPLAIN plan. Simulate hypothetical indexes with hypopg | | get_top_queries | Heaviest queries from pg_stat_statements on one database, sorted by time or resource usage | | list_slow_queries | Slow queries across all configured databases at once — historical from pg_stat_statements + currently running. No database param needed |

Index analysis

| Tool | Description | |------|-------------| | analyze_workload_indexes | Recommendations from the full query workload | | analyze_query_indexes | Recommendations for up to 10 specific queries | | detect_index_misses | Sequential scan hotspots, FK columns without indexes |

Health checks

Pass health_type: "all" or any subset:

| Check | What it catches | |-------|----------------| | index | Unused, invalid, duplicate indexes; FK columns with no index | | connection | State breakdown, max_connections usage, long-running queries | | vacuum | Dead tuple bloat, transaction ID wraparound risk | | sequence | Sequences > 75% exhausted | | replication | Replica lag, dangling replication slots | | buffer | Shared buffer cache hit ratio globally and per table | | constraint | Invalid / not-yet-validated constraints |

Advanced diagnostics

| Tool | Description | |------|-------------| | analyze_locks | Blocking chains, idle-in-transaction sessions, lock waits by table | | analyze_table_bloat | Dead-tuple bloat, index bloat, TOAST sizes, VACUUM commands |

Security model

| Protection | How it works | |------------|-------------| | Credentials never reach the AI | DB URLs are read from env at server startup only; never passed through tool args or returned in results | | Alias-only identity | The AI sees "primary" / "replica", never a connection string or hostname | | DB-level read-only enforcement | In restricted mode, every query runs inside BEGIN; SET TRANSACTION READ ONLY; ... ROLLBACK — PostgreSQL itself blocks any writes, not just application logic | | Statement type validation | First SQL keyword is checked (SELECT / EXPLAIN / SHOW / WITH only) before the query reaches the database | | Query timeout | SET LOCAL statement_timeout = 30000 kills runaway queries after 30 seconds | | Non-root Docker image | Container runs as an unprivileged mcpuser, not root |

Optional Postgres extensions

These unlock additional features but are not required to run the server:

-- Enables get_top_queries and analyze_workload_indexes
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Enables hypothetical index simulation in explain_query
CREATE EXTENSION IF NOT EXISTS hypopg;

For pg_stat_statements you also need this line in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Docker

# Build
docker build -t postgres-mcp-js .

# Run
docker run --rm -i \
  -e DB_PRIMARY_URL="postgresql://user:pass@host:5432/mydb" \
  -e ACCESS_MODE=restricted \
  postgres-mcp-js

With docker-compose (includes a bundled Postgres instance for testing):

cp .env.example .env   # add your DB credentials
docker compose up

Local development

git clone https://github.com/shubham4038/postgres-mcp-js.git
cd postgres-mcp-js
npm install

# Run without a build step (auto-recompiles on save)
npm run dev

# Type check
npm run typecheck

# Production build
npm run build

Extending with new tools

All tools live in src/tools/. To add a new one:

1. Create src/tools/my-tool.ts — export an async function that takes pool: Pool and returns a JSON string
2. Import and register it in src/server.ts with server.tool(name, description, schema, handler)

Comparison

| | postgres-mcp-js | crystaldba/postgres-mcp | |-|:-:|:-:| | Language | TypeScript / Node.js | Python | | Max simultaneous databases | 3 | 1 | | Credentials exposed to AI | Never | Never | | Index miss detection | ✅ | ❌ | | Lock / blocking analysis | ✅ | ❌ | | Table & index bloat | ✅ | ❌ | | npx / npm install | ✅ | ❌ | | Docker | ✅ | ✅ | | hypopg simulation | ✅ | ✅ | | pg_stat_statements | ✅ | ✅ |

License

MIT — free to use, modify, and distribute.

GitHub topics to add

After pushing to GitHub, add these topics in Settings → Topics to improve discoverability:

mcp model-context-protocol postgresql postgres database ai claude cursor llm developer-tools typescript nodejs