postgres-mcp-server CLAUDE.md

postgres-mcp-server

A general-purpose Model Context Protocol (MCP) server that lets an LLM query and (opt-in) mutate PostgreSQL databases safely. Node.js/TypeScript, distributed via npm.

Status: v0.2 (Phase 2). Meta, SQL, and schema introspection tools shipped; observability tools are deferred to Phase 3.

Stack

• Node.js ≥ 20 (ESM only, "type": "module")
• TypeScript 5.x strict (strict: true, no any in safety/ or connections/ layers)
• @modelcontextprotocol/sdk — MCP protocol
• pg (node-postgres) — Postgres driver + pooling
• zod — input schemas and config validation
• zod-to-json-schema — publishes real JSON Schema for tool inputs
• pino — structured logging (stderr only; stdout reserved for MCP JSON-RPC)
• commander — CLI parsing
• vitest + @testcontainers/postgresql — unit / integration / smoke tests
• changesets — versioning + npm publish

Architecture — three horizontal layers

Middleware runs at the dispatcher, so any new tool inherits safety guarantees.

src/
  server.ts                  # MCP stdio bootstrap, CLI, banner, shutdown
  types.ts                   # ConnectionMode, ToolContext, ToolResult, Row
  errors.ts                  # ToolError class + pgErrorToToolError
  logging.ts                 # pino + redactUrl
  config/
    schema.ts                # zod schema for the config file
    loader.ts                # discovery order + validation → { config, path }
  connections/
    manager.ts               # ConnectionManager — pool per alias, owned getClient
    session.ts               # initSessionSQL (per-mode SET statements)
    transactions.ts          # TransactionManager — uuid handles, mutex, wall-clock cap
  safety/
    middleware.ts            # assertModeAllowed (read < write < admin)
    limit-cursor.ts          # runWithCursorLimit (server-side cursor)
    result-cap.ts            # applyResultCap (byte cap, rowed + scalar paths)
  tools/
    registry.ts              # ToolRegistry: schema validate → mode gate → dispatch → cap
    index.ts                 # registerAllTools
    meta/                    # ping, list_connections
    sql/                     # query, begin_transaction, commit, rollback, execute