Postgres MCP Router

A read-only PostgreSQL MCP server with multi-database semantic routing. It enumerates several PostgreSQL databases, indexes their schemas (tables, columns, foreign keys) into a local SQLite catalog with locally-computed embeddings, and exposes MCP tools so a host LLM can:

1. Route a natural-language intent to the correct logical database (match_databases).
2. Discover the relevant tables within that database (search_schema, describe_table).
3. Execute a single read-only SQL statement (query) against exactly one database.

Tools

| Tool | Purpose | |------|---------| | list_databases | Enumerate configured logical databases and catalog status. | | match_databases | Score logical databases against a natural-language intent; returns ranked candidates with rollup + top-table evidence. | | search_schema | Semantic table search scoped to one database. | | describe_table | Columns, primary key, and foreign keys for a single table from the cached catalog. | | query | Execute one read-only SQL statement against a logical database. Either pass database explicitly or intent for guarded auto-routing. | | refresh_catalog | Re-introspect databases and rebuild catalog + embeddings. |

Resources

• postgres-mcp://catalog/overview — All configured databases and catalog metadata.
• postgres-mcp://{database}/graph — Foreign-key graph for one database.
• postgres-mcp://{database}/{schema}.{table}/overview — Cached table overview.

Configuration

Each logical database is configured with a small group of environment variables sharing the same logical-name segment. The logical name (SALES below) is lowercased into the database argument used at runtime.

| Variable | Required | Purpose | |----------|----------|---------| | POSTGRES_MCP_DB_<NAME>_URL | yes | postgresql://user:pass@host:port/dbname connection string. | | POSTGRES_MCP_DB_<NAME>_DESCRIPTION | no | Free-form text. Strongly recommended — improves routing scores for ambiguous intents. | | POSTGRES_MCP_DB_<NAME>_SCHEMA_ALLOWLIST | no | Comma-separated list of schemas to index. | | POSTGRES_MCP_DB_<NAME>_SCHEMA_BLOCKLIST | no | Comma-separated list of schemas to skip. | | POSTGRES_MCP_DB_<NAME>_POOL_MAX | no | Max pool size (default 4). |

Logical names must match [A-Za-z0-9_-]+. Use underscores in env-var names; the logical name is the lowercased segment between POSTGRES_MCP_DB_ and the trailing field name.

Example (two databases):

export POSTGRES_MCP_DB_SALES_URL=postgresql://reader:pass@db1:5432/sales
export POSTGRES_MCP_DB_SALES_DESCRIPTION='Orders, invoices, and revenue'
export POSTGRES_MCP_DB_SALES_SCHEMA_ALLOWLIST=public,billing
export POSTGRES_MCP_DB_CRM_URL=postgresql://reader:pass@db2:5432/crm
export POSTGRES_MCP_DB_CRM_DESCRIPTION='Customers, accounts, support tickets'

Optional environment variables

| Variable | Default | Purpose | |----------|---------|---------| | POSTGRES_MCP_CATALOG_PATH | platform cache dir (see below) | SQLite catalog location. | | POSTGRES_MCP_EMBEDDING_MODEL | Xenova/all-MiniLM-L6-v2 | Hugging Face model id loaded via @xenova/transformers. Downloaded once and cached locally. | | POSTGRES_MCP_STATEMENT_TIMEOUT_MS | 30000 | Per-statement and idle-in-transaction timeout. | | POSTGRES_MCP_MAX_ROWS | 1000 | Server-side row cap returned to the client. | | POSTGRES_MCP_ROUTING_MIN_MARGIN | 0.05 | Minimum margin between top‑1 and top‑2 scores for query auto-routing. | | POSTGRES_MCP_ROUTING_MIN_ABSOLUTE | 0.25 | Minimum absolute score required for query auto-routing. | | POSTGRES_MCP_ROUTING_TOP_K | 5 | Default number of routing candidates returned. | | POSTGRES_MCP_AUTO_INDEX | true | Run an initial catalog refresh on startup. |

Cursor configuration

Add to .cursor/mcp.json (or your claude_desktop_config.json). Each database is a flat group of env keys — no external file, no escaped JSON:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@wanderfaux/postgres-mcp-router"],
      "env": {
        "POSTGRES_MCP_DB_SALES_URL": "postgresql://reader:pass@host:5432/sales",
        "POSTGRES_MCP_DB_SALES_DESCRIPTION": "Orders, invoices, and revenue",
        "POSTGRES_MCP_DB_CRM_URL": "postgresql://reader:pass@host:5432/crm",
        "POSTGRES_MCP_DB_CRM_DESCRIPTION": "Customers, accounts, support tickets"
      }
    }
  }
}

The first npx invocation will download @wanderfaux/postgres-mcp-router plus its dependencies (~200 MB including the @xenova/transformers runtime and better-sqlite3 prebuilds) — this is a one-time cost; subsequent launches reuse the local cache.

If you'd rather run from a local checkout (e.g. while developing):

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-mcp-router/dist/index.js"],
      "env": { "POSTGRES_MCP_DB_SALES_URL": "...", "POSTGRES_MCP_DB_SALES_DESCRIPTION": "..." }
    }
  }
}

For Docker, forward each variable explicitly:

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm", "--network=host",
        "-e", "POSTGRES_MCP_DB_SALES_URL",
        "-e", "POSTGRES_MCP_DB_SALES_DESCRIPTION",
        "-e", "POSTGRES_MCP_DB_CRM_URL",
        "-e", "POSTGRES_MCP_DB_CRM_DESCRIPTION",
        "postgres-mcp-router"
      ],
      "env": {
        "POSTGRES_MCP_DB_SALES_URL": "postgresql://reader:pass@host:5432/sales",
        "POSTGRES_MCP_DB_SALES_DESCRIPTION": "Orders, invoices, and revenue",
        "POSTGRES_MCP_DB_CRM_URL": "postgresql://reader:pass@host:5432/crm",
        "POSTGRES_MCP_DB_CRM_DESCRIPTION": "Customers, accounts, support tickets"
      }
    }
  }
}

Recommended workflow

flowchart LR
  Intent --> Match[match_databases] --> Pick[Pick database]
  Pick --> Search[search_schema] --> Describe[describe_table] --> Query[query]

1. Call match_databases({ intent: "..." }). Inspect the candidates and evidence.
2. Pick a database from the response.
3. Call search_schema({ database, query }) to find relevant tables.
4. Optionally call describe_table for column-level detail or read the postgres-mcp://{database}/{schema}.{table}/overview resource.
5. Call query({ database, sql }) with a SELECT/WITH/SHOW/EXPLAIN/VALUES/TABLE statement.

The query tool also accepts intent instead of database. The server then runs match_databases internally and only executes if the top-1 score clears POSTGRES_MCP_ROUTING_MIN_ABSOLUTE and the margin over top-2 clears POSTGRES_MCP_ROUTING_MIN_MARGIN. Otherwise it returns the candidate list and refuses to execute. Prefer the explicit path until thresholds are calibrated for your databases.

Build and run from source

git clone https://github.com/alphapseudo/postgres-mcp-router.git
cd postgres-mcp-router
npm install
npm run build
POSTGRES_MCP_DB_SALES_URL=postgresql://reader:pass@localhost:5432/sales \
POSTGRES_MCP_DB_SALES_DESCRIPTION='Orders, invoices, and revenue' \
  node dist/index.js

Development mode with reload:

npm run dev

Run the unit suite:

npm test

Indexing notes

• The first run downloads the embedding model (about 25 MB for the default MiniLM model). Subsequent runs use the local ~/.cache/huggingface cache.
• Indexing introspects every non-system schema by default. Use schemaAllowlist/schemaBlocklist per database to scope the catalog.
• Catalog and embeddings are stored in SQLite at POSTGRES_MCP_CATALOG_PATH. Cosine similarity is computed in-process; this is appropriate for catalogs up to roughly low thousands of tables across several databases.

Default catalog location

If POSTGRES_MCP_CATALOG_PATH is unset, the server writes to a platform-appropriate cache directory.

| Platform | Default path | |----------|--------------| | macOS | ~/Library/Caches/postgres-mcp-router/catalog.sqlite | | Linux | ${XDG_CACHE_HOME:-~/.cache}/postgres-mcp-router/catalog.sqlite | | Windows | %LOCALAPPDATA%\postgres-mcp-router\Cache\catalog.sqlite |

The directory is created automatically on first index. The catalog is regenerable — deleting it just forces the next startup (or the next refresh_catalog call) to rebuild.

Safety model

• Every SQL statement runs inside a BEGIN TRANSACTION READ ONLY / ROLLBACK envelope.
• A static guard rejects multi-statement input and any token from a list of write/DDL keywords (INSERT, UPDATE, DELETE, TRUNCATE, DROP, ALTER, CREATE, GRANT, REVOKE, COPY, VACUUM, ANALYZE, REINDEX, LOCK, NOTIFY, LISTEN, BEGIN, COMMIT, ROLLBACK, ...).
• Configure each database connection with a least-privilege read-only role for defense in depth.
• statement_timeout and idle_in_transaction_session_timeout are set per pool.