postgres-mcp-js

A Model Context Protocol (MCP) server for PostgreSQL — written in TypeScript.

Connect it to Claude, Cursor, Windsurf, or any MCP-compatible AI and give it safe, structured access to your Postgres database: health checks, index tuning, lock analysis, bloat detection, and more.

---

Install

npm install -g postgres-mcp-js

Or use without installing:

npx postgres-mcp-js

---

Local setup

1. Clone and install

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

2. Configure credentials

cp .env.example .env

Open .env and fill in your values:

# Required
DB_PRIMARY_URL=postgresql://user:password@localhost:5432/mydb
DB_PRIMARY_NAME=primary

# Optional — second database (replica, staging, etc.)
# DB_REPLICA_URL=postgresql://user:password@replica:5432/mydb
# DB_REPLICA_NAME=replica

# restricted = read-only + 30s timeout  ← use this for production
# unrestricted = full read/write         ← dev/local only
ACCESS_MODE=restricted

3. Build and run

npm run build
node dist/index.js

Or skip the build step in dev mode:

npm run dev

---

Connect to your AI client

All clients need three things: command, args, and env (your DB credentials).

No .env file needed. The env block in your MCP client config is passed directly to the server process — credentials never need to touch a file on disk. The .env file is only useful if you run the server manually in a terminal.

Claude Desktop

Edit ~/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"
      }
    }
  }
}

If you cloned locally and built it, point directly at the file instead:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/Users/yourname/postgres-mcp-js/dist/index.js"],
      "env": {
        "DB_PRIMARY_URL": "postgresql://user:pass@localhost:5432/mydb",
        "ACCESS_MODE": "restricted"
      }
    }
  }
}

Claude Code (CLI)

Edit ~/.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 and paste:

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

Windsurf

Edit ~/.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"
      }
    }
  }
}

Two databases at once

Add both URLs — the AI can then query either one by name:

{
  "env": {
    "DB_PRIMARY_URL":  "postgresql://user:pass@primary:5432/mydb",
    "DB_PRIMARY_NAME": "primary",
    "DB_REPLICA_URL":  "postgresql://user:pass@replica:5432/mydb",
    "DB_REPLICA_NAME": "replica",
    "ACCESS_MODE": "restricted"
  }
}

Then ask the AI: "Compare buffer cache hit ratio between primary and replica" — it will call the same tool twice with database: "primary" and database: "replica".

---

Tools

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

Schema

Tool	Description
list_databases	Show configured DB aliases — credentials never exposed
list_schemas	All schemas in the database
list_objects	Tables / views / sequences / extensions in a schema
get_object_details	Columns, constraints, indexes for a table or view

SQL & query plans

Tool	Description
execute_sql	Run SQL. Read-only in restricted mode; full access in unrestricted
explain_query	EXPLAIN plan. Pass hypothetical_indexes to simulate indexes via hypopg
get_top_queries	Slowest/heaviest queries from pg_stat_statements

Index analysis

Tool	Description
analyze_workload_indexes	Index recommendations from the full query workload
analyze_query_indexes	Index recommendations for up to 10 specific queries
detect_index_misses	Tables with seq-scan dominance, FK columns without indexes

Health checks

Run any subset or pass all to run everything:

health_type: "index" | "connection" | "vacuum" | "sequence" | "replication" | "buffer" | "constraint" | "all"

Check	What it looks for
index	Unused, invalid, duplicate indexes; FK columns with no index
connection	State breakdown, max_connections, 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

---

Optional Postgres extensions

Not required, but unlock more features:

-- 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, also add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

---

Security

• Credentials never reach the AI. DB URLs are read from env at startup; tool arguments and results never contain them.
• Alias only. The AI sees "primary" / "replica", never a connection string.
• Read-only enforcement. In restricted mode every query runs inside BEGIN; SET TRANSACTION READ ONLY; ... ROLLBACK — PostgreSQL itself blocks any writes.
• Statement validation. First keyword is checked (SELECT / EXPLAIN / SHOW / WITH only) before the query hits the DB.
• 30-second timeout. SET LOCAL statement_timeout = 30000 kills runaway queries.

---

Publishing to npm

Before publishing, update package.json:

{
  "name": "your-package-name",
  "author": "Your Name <you@example.com>",
  "homepage": "https://github.com/you/your-repo#readme",
  "repository": { "url": "git+https://github.com/you/your-repo.git" }
}

Then:

npm login          # one-time login to npmjs.com
npm publish --access public

Users can now install it:

npm install -g your-package-name
# or
npx your-package-name

To release a new version:

npm version patch   # 1.0.0 → 1.0.1  (bug fix)
npm version minor   # 1.0.0 → 1.1.0  (new feature)
npm version major   # 1.0.0 → 2.0.0  (breaking change)
npm publish

---

Docker (local only)

Build and run locally without npm:

docker build -t postgres-mcp-js .

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

Or with docker-compose (includes a bundled Postgres for testing):

cp .env.example .env   # fill in your DB details
docker compose up

---

License

MIT