PgAgentNexus ⚙️🌐 (Advanced PostgreSQL Data Access Hub)

This implementation provides a highly optimized, strictly typed Model Context Protocol (MCP) Server, engineered to enable AI Assistants (e.g., Cursor, Claude Desktop) seamless, controlled interaction with diverse PostgreSQL environments, including real-time schema discovery.

It utilizes Bun for superior execution speed, TypeScript for reliability, the native postgres driver, and builds upon the robust fastmcp infrastructure for server orchestration.

Core Functionality: Standalone Server Service

This package is delivered as a self-contained server application, not an importable library for direct codebase integration. It operates as a background process, adhering to the JSON-based Model Context Protocol (v2.0), typically communicating via standard I/O streams managed by the consuming AI client (e.g., Cursor).

Debugging and Verification Utilities

Testing via Integrated CLI

The distribution includes a command-line interface for direct server validation:

bash

Execute from the project root:

bun run cli

This launches an interactive session allowing you to:

- Invoke all exposed PostgreSQL operations (query_tool, execute_tool, etc.)

- Review advertised server capabilities

- Run test queries against your configured data sources

Visual Inspection Utility

You can use the bundled MCP Inspector for graphical debugging:

bash

Execute from the project root:

bun run inspect

Connection Troubleshooting Notes

Encountering this warning upon startup:

FastPostgresMCP started [warning] FastMCP could not infer client capabilities

followed by continuous ping exchanges indicates:

1. The server initialized correctly.
2. The communication pipe established.
3. The client failed to complete the protocol handshake (capability negotiation).

This typically demands verification of the client setup. Use bun run cli for local testing, or ensure the server entry point is correctly specified in client configurations (Cursor/Claude Desktop).

🌟 Key Featureset

• ⚡ Performance Focus: Engineered with Bun and the fastmcp backbone.
• 🛡️ Reliability: Rigorous end-to-end type checking via TypeScript and Zod schemas.
• 🗄️ Multi-Source Connectivity: Facilitates controlled interaction across heterogeneous PostgreSQL databases defined via environment configuration.
• 🛑 Injection Defense: All SQL execution employs safe, parameterized statements via the postgres driver.
• 🔑 Access Control Layer: Supports API Key validation via the fastmcp authenticate hook for secured network endpoints (SSE/HTTP).
• 📚 MCP Schema Discovery (Resources):
  • Table Listing: Fetch all tables in a source via db://{dbAlias}/schema/tables.
  • Detailed Schema Retrieval: Fetch column-level metadata for any table via db://{dbAlias}/schema/{tableName}.
• 💬 Enriched Operational Feedback:
  • Contextual Logging: Operations broadcast granular logs back to the client (log context).
  • Status Updates: Long-running tasks provide periodic status reports (reportProgress context).
• 👤 Session Context Awareness: Tool executors can access session-specific metadata (session context).
• 📡 Event Handling: Hooks available via server.on and session.on for lifecycle management.
• 🧑‍💻 Modern DX: Streamlined configuration, straightforward tool APIs, and simple testing integration with fastmcp utilities.

📦 Leveraged fastmcp Components

• Core FastMCP Server Implementation
• Tool Registration: server.addTool (for query_tool, execute_tool, schema_tool, transaction_tool)
• Resource Templates: server.addResourceTemplate (for schema browsing)
• Startup Mechanism: server.start (optimized for stdio transport, extensible to sse/http)
• Security Hook: Optional authenticate hook
• Execution Contexts: Access to log, reportProgress, session within tool bodies
• Schema Validation: Zod for input parameter validation
• Lifecycle Events: server.on for connection tracking

📋 Requirements

• Bun Runtime (v1.0+ highly recommended): Must be installed and accessible in the system PATH.
• PostgreSQL Accessibility: Valid network credentials and connectivity. The connecting user requires SELECT privileges on database system catalogs (e.g., information_schema).

🛠️ Deployment Procedure

Method 1: NPM Distribution

bash

Global installation for system-wide use

npm install -g postgres-mcp

Local installation within a project directory

npm install postgres-mcp

NPM Registry link: https://www.npmjs.com/package/postgres-mcp

Method 2: Source Code Checkout

1. Clone the repository: bash git clone https://github.com/llm-graph/postgres-mcp.git cd postgres-mcp

2. Install Dependencies: bash bun install

🔑 Configuration Protocol (Environment Variables)

Configuration relies on environment variables, typically loaded from .env files.

1. Setup Config Files:

   • Production: cp .env.example .env
   • Development: cp .env.development.example .env.development

2. Loading Hierarchy: Environment variables are merged in this order (later files override earlier ones):

   • .<NODE_ENV> (e.g., .env.development)
   • .env.local (For machine-specific overrides; exclude from Git)
   • .env (Base defaults)

3. Key Variable Definitions:

   • DB_ALIASES - A comma-separated list of distinct database identifiers.
   • DEFAULT_DB_ALIAS - The source to use when a tool call omits an alias.
   • Database Credentials: Specific variables for each alias (e.g., DB_MAIN_HOST, DB_REPORTING_PORT).
   • Optional Auth Key: ENABLE_AUTH, MCP_API_KEY.

dotenv

Sample .env Configuration

REQUIRED: Identifier registry

DB_ALIASES=core,analytics

REQUIRED: Fallback identifier

DEFAULT_DB_ALIAS=core

OPTIONAL: Enable API Key protection for network listeners

ENABLE_AUTH=false MCP_API_KEY=change_this_production_key_immediately

Credentials for the 'core' database

DB_CORE_HOST=db.prod.internal DB_CORE_PORT=5432 DB_CORE_NAME=production_db DB_CORE_USER=app_readwrite_user DB_CORE_PASSWORD=secure_db_password_123 DB_CORE_SSL=require

--- Runtime Logging Level ---

LOG_LEVEL=info # Options: debug, info, warn, error

▶️ Running the Server Process

Invoke the server directly via Bun. The client application manages the invocation.

Executing the Globally Installed CLI

• Manual Launch: postgres-mcp

Executing from Project Directory

• Via npx: npx postgres-mcp

• Programmatic Start (Library Mode): javascript // server_bootstrap.js import { startServer } from 'postgres-mcp';

  // Initiates the MCP listener startServer();

Running from Cloned Source

• Direct Execution (Testing): bun run src/index.ts
• Hot Reload Mode: bun run --watch src/index.ts

Interfacing with fastmcp Dev Tools

• Interactive Terminal Test Harness: bunx fastmcp dev src/index.ts
• Web UI Tool Debugger: bunx fastmcp inspect src/index.ts

💻 Programmatic Integration (Library Usage)

Beyond its role as a standalone MCP server, postgres-mcp exposes core database interaction logic for direct use within Node/TypeScript applications.

Initialization Example

typescript import { createPostgresMcp } from 'postgres-mcp';

// Instantiate the core service provider const pgAgent = createPostgresMcp();

// Start listening for MCP connections pgAgent.start();

// Direct internal database query execution const adminRecords = await pgAgent.executeQuery( 'SELECT id, email FROM admins WHERE status = $1', ['active'], 'core' // Alias specified );

// Graceful shutdown await pgAgent.stop();

Isolated Function Imports

For lightweight tasks, individual connectivity and query functions are available:

typescript import { initConnections, closeConnections, executeQuery, getTableSchema } from 'postgres-mcp';

// Define connection parameters statically const dbMap = { reporting: { / details / }, core: { / details / } };

// Establish all defined connections initConnections(dbMap);

// Execute a query against the 'reporting' source const reportData = await executeQuery( 'SELECT * FROM monthly_summary WHERE year = $1', [2024], 'reporting' );

// Clean up resources await closeConnections();

Configuration Overrides

When initializing programmatically, environment variables can be supplemented or overridden:

typescript const pgAgent = createPostgresMcp({ // Override/define DB connections directly databaseConfigs: { core: { host: 'overridden.host.com', database: 'new_app_db' } }, // Server behavior settings serverConfig: { name: 'ProductionPgAgent', defaultDbAlias: 'core' }, // Specify transport layer: 'stdio' (default), 'sse', or 'http' transport: 'http', port: 3456 });

Full API documentation resides in docs/programmatic-api.md.

🔌 AI Client Integration Guide (Cursor/Claude)

Configure your target MCP Client to launch this server utility using its command execution settings.

Example: Configuring Cursor

1. Access Cursor Settings (Cmd+, / Ctrl+,).
2. Navigate to Extensions -> MCP settings.
3. Add or modify an MCP server entry.

4. Configure the launch parameters:

   // Cursor settings.json snippet { "mcpServers": { "pg-nexus-core": { // Unique client identifier "description": "Primary PostgreSQL Data Access Hub", "command": "bunx", // Assumes bunx is in PATH "args": [ "postgres-mcp" // For direct script execution: "/path/to/your/postgres-mcp/src/index.ts" ], "enabled": true } } }

5. Save settings and initiate a reload of the MCP services within Cursor.

Claude Desktop Setup

Similar configuration applies to Claude's configuration file (often config.json), ensuring the args field points to the absolute path of the server entry point if not using a globally installed package.

🛠️ Exposed MCP Interfaces

Security Layer

• Network transports (HTTP/SSE) are protected via the X-API-Key header check against MCP_API_KEY if ENABLE_AUTH is true.
• stdio (default) relies on the host execution environment's security.

Resource Endpoints (Introspection)

1. Database Table Index Retrieval

• URI: db://{dbAlias}/schema/tables
• Functionality: Fetches an array of user-defined table names from the specified database alias (defaults to filtering public schema user tables).
• Input Argument: dbAlias (required).
• Output Format: { text: "[\"table1\",\"table2\",...]" }

AI Usage Example: Requesting the resource db://analytics/schema/tables.

2. Table Schema Definition Retrieval

• URI: db://{dbAlias}/schema/{tableName}
• Functionality: Returns the precise column structure (name, type, nullability, default value) for the targeted table.
• Input Arguments: dbAlias (required), tableName (required).
• Output Format: { text: "[{"column_name":"id","data_type":"uuid",...}]" }

AI Usage Example: Describing the structure of resource db://core/schema/user_profiles.

Tool Operations (Actions)

All tools receive the context object (log, reportProgress, session).

---

1. query_tool (Read Operations)

• Purpose: Safe execution of SELECT statements.
• Parameters: statement (SQL string), params (array of literals, optional), dbAlias (optional).
• Result: JSON string representing the array of returned rows.

Example Query:

{ "tool_name": "query_tool", "arguments": { "statement": "SELECT item_id, name FROM catalog WHERE category = $1", "params": ["hardware"], "dbAlias": "core" } }

---

2. execute_tool (Write/Modify Operations)

• Purpose: Executes DML/DDL commands that modify data or structure.
• Parameters: statement (SQL string), params (array of literals, optional), dbAlias (optional).
• Result: A confirmation string detailing affected rows.

Example Execution:

{ "tool_name": "execute_tool", "arguments": { "statement": "DELETE FROM stale_logs WHERE log_date < $1", "params": ["2023-01-01"] } }

---

3. schema_tool (Direct Schema Fetch)

• Purpose: Retrieves detailed column metadata, similar to the resource but callable as a tool.
• Parameters: tableName (string), dbAlias (optional).
• Result: JSON string array detailing column structure.

---

4. transaction_tool (Atomic Batches)

• Purpose: Groups multiple SQL operations into a single, atomic database transaction. Critical for complex data updates.
• Parameters: operations (Array of objects {statement, params}), dbAlias (optional).
• Feedback: Returns a summary object detailing success status and the outcome/error index of the failed operation, if any.

Example Transaction (Success):

{ "tool_name": "transaction_tool", "arguments": { "operations": [ { "statement": "BEGIN TRANSACTION;" }, { "statement": "UPDATE inventory SET count = count - 1 WHERE sku = $1", "params": ["AX100"] }, { "statement": "INSERT INTO audit_log VALUES ($1, 'Inventory Decrement')", "params": ["AX100"] } ] } }

Success Response Example: {"success":true,"results":[{"op":1,"affected":1},{"op":2,"affected":1}]}

🛡️ Security Posture

• Injection Mitigation: Paramount importance is placed on using prepared statements (parameterization) for all data manipulation to nullify SQL Injection risks.
• Principle of Least Privilege (PoLP): Database connection users must be strictly provisioned. Schema listing requires only read access to information_schema.
• TLS/SSL Enforcement: Production deployments must mandate SSL connections via configuration (DB_<ALIAS>_SSL=require).
• Secret Handling: Environment files (.env) must be securely managed and excluded from version control.
• Transport Security: API key authentication protects non-local connections (HTTP/SSE); local stdio relies on the client process integrity.

📜 Licensing

This project is released under the MIT License. Refer to the included LICENSE file for comprehensive terms.

🔖 Version History Summary

1.0.0 (Initial Release)

• First stable release of the multi-database MCP service.
• Comprehensive toolset implemented: Query, Execute, Schema, Transaction.
• Resource endpoints enabled for self-describing schema browsing.
• Secure configuration via environment variables established.