pg-mcp

PostgreSQL MCP (Model Context Protocol) server with multi-database support. This tool allows you to interact with PostgreSQL databases through MCP-compatible clients like Cursor, Claude, and other AI-powered development tools.

Features

• Multi-database support with configuration management
• Connection pooling with automatic cleanup
• Auto-reload configuration on file changes
• Backward compatibility with single database setups
• Secure credential handling

Installation

To install dependencies:

bun install

To run:

bun run src/index.ts

Configuration

Configuration Path

The configuration file location is determined by the PG_MCP_CONFIG_PATH environment variable. If not set, it defaults to:

$HOME/.config/pg-mcp/config.json

You can set a custom configuration path:

export PG_MCP_CONFIG_PATH="/path/to/your/config.json"

Configuration Schema

The configuration file follows this JSON schema:

{
  "databases": {
    "database_name": {
      "url": "postgresql://user:password@host:port/database",
      "ttl": 60000
    }
  },
  "autoReload": true
}

Fields:

• databases: Object containing database configurations
  • database_name: Unique identifier for the database
    • url: PostgreSQL connection URL (required)
    • ttl: Time-to-live for connection pooling in milliseconds (default: 60000)
• autoReload: Enable automatic configuration reloading when the file changes (default: false)

Configuration Example

{
  "databases": {
    "default": {
      "url": "postgresql://user:password@localhost:5432/myapp",
      "ttl": 60000
    },
    "analytics": {
      "url": "postgresql://user:password@analytics-host:5432/analytics",
      "ttl": 120000
    }
  },
  "autoReload": true
}

Migration from POSTGRES_URL

If no configuration file exists but the POSTGRES_URL environment variable is set, pg-mcp will automatically create a default configuration with a single database entry named "default".

Available Tools

pg-mcp provides comprehensive tools for database management and interaction, organized into three categories: configuration management, database querying, and utility functions.

Database Configuration Management Tools

Tools for managing database configurations and connection pooling.

pg_db_list

Description: List all configured databases with their connection details and TTL settings.

Parameters: None

Returns: Array of database configurations with redacted credentials

Example:

{
  "name": "pg_db_list"
}

Response Example:

{
  "result": [
    {
      "name": "default",
      "url": "postgresql://user:***@localhost:5432/myapp",
      "ttl": 60000
    },
    {
      "name": "analytics",
      "url": "postgresql://user:***@analytics-host:5432/analytics",
      "ttl": 120000
    }
  ]
}

pg_db_add

Description: Add a new database configuration to the connection pool.

Parameters:

• name (string, required): Unique identifier for the database
• url (string, required): PostgreSQL connection URL (must be valid URL format)
• ttl (number, optional): Time-to-live for connections in milliseconds (default: 60000)

Returns: Confirmation with the added database configuration (credentials redacted)

Example:

{
  "name": "pg_db_add",
  "arguments": {
    "name": "production",
    "url": "postgresql://user:password@prod-host:5432/myapp",
    "ttl": 30000
  }
}

pg_db_update

Description: Update an existing database configuration. You can update the URL, TTL, or both.

Parameters:

• name (string, required): Identifier of the database to update
• url (string, optional): New PostgreSQL connection URL
• ttl (number, optional): New TTL value in milliseconds

Returns: Confirmation with updated configuration

Example:

{
  "name": "pg_db_update",
  "arguments": {
    "name": "production",
    "ttl": 45000
  }
}

pg_db_remove

Description: Remove a database configuration and close its connection pool.

Parameters:

• name (string, required): Identifier of the database to remove

Returns: Confirmation of removal

Example:

{
  "name": "pg_db_remove",
  "arguments": {
    "name": "staging"
  }
}

pg_db_reload

Description: Reload the database configuration from disk and reconcile the connection pool. Useful after manual configuration file changes or to refresh stale configurations.

Parameters: None

Returns: List of all configured database names after reload

Example:

{
  "name": "pg_db_reload"
}

---

Database Query Tools

Tools for querying and inspecting PostgreSQL databases. All tools support an optional database parameter to select which database to query.

Note on Database Selection:

• When only one database is configured, the database parameter is optional and automatically uses that database
• When multiple databases are configured, you must specify the database parameter
• If neither condition is met, an error will indicate which databases are available

pg_get_server_version

Description: Retrieve the PostgreSQL server version and detailed version information.

Parameters:

• database (string, optional): Name of the database to connect to

Returns: PostgreSQL version information

Example:

{
  "name": "pg_get_server_version",
  "arguments": {
    "database": "production"
  }
}

get_url

Description: Retrieve the connection URL for a specific database (with credentials redacted for security).

Parameters:

• database (string, optional): Name of the database

Returns: Redacted PostgreSQL connection URL

Example:

{
  "name": "get_url",
  "arguments": {
    "database": "default"
  }
}

pg_list_schemas

Description: List all schemas available in the selected database.

Parameters:

• database (string, optional): Name of the database to query

Returns: Array of schema names

Example:

{
  "name": "pg_list_schemas",
  "arguments": {
    "database": "production"
  }
}

Response Example:

{
  "result": ["public", "auth", "api", "analytics"]
}

pg_list_tables

Description: List all tables in a specific schema of the selected database.

Parameters:

• schema (string, required): Name of the schema to query
• database (string, optional): Name of the database to query

Returns: Array of table names in the specified schema

Example:

{
  "name": "pg_list_tables",
  "arguments": {
    "schema": "public",
    "database": "production"
  }
}

Response Example:

{
  "result": ["users", "posts", "comments", "categories"]
}

pg_describe_table

Description: Get detailed information about a specific table, including columns, data types, nullability, defaults, and foreign key constraints.

Parameters:

• schema (string, required): Name of the schema containing the table
• table (string, required): Name of the table to describe
• database (string, optional): Name of the database to query

Returns: Comprehensive table structure with columns and foreign key relationships

Example:

{
  "name": "pg_describe_table",
  "arguments": {
    "schema": "public",
    "table": "users",
    "database": "production"
  }
}

Response Example:

{
  "result": {
    "schema_name": "public",
    "table_name": "users",
    "columns": [
      {
        "column_name": "id",
        "data_type": "integer",
        "is_nullable": false,
        "column_default": "nextval('users_id_seq'::regclass)"
      },
      {
        "column_name": "email",
        "data_type": "character varying",
        "is_nullable": false,
        "column_default": null
      }
    ],
    "foreign_keys": [
      {
        "constraint_name": "posts_user_id_fk",
        "column_name": "id",
        "referenced_table_schema": "public",
        "referenced_table_name": "posts",
        "referenced_column_name": "user_id"
      }
    ]
  }
}

pg_execute_query

Description: Execute a read-only SQL query against the selected database. Queries are restricted to SELECT operations for security.

Parameters:

• query (string, required): SQL SELECT query to execute
• database (string, optional): Name of the database to query against

Returns: Query results as JSON array

Example:

{
  "name": "pg_execute_query",
  "arguments": {
    "query": "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days' LIMIT 10",
    "database": "production"
  }
}

Response Example:

{
  "result": [
    {
      "id": 1,
      "email": "user@example.com",
      "created_at": "2024-10-22T10:30:00Z"
    },
    {
      "id": 2,
      "email": "another@example.com",
      "created_at": "2024-10-23T15:45:00Z"
    }
  ]
}

---

Utility Functions

gen_types (Prompt)

Description: Generate TypeScript type definitions for specified tables. This is an interactive prompt that uses other tools to gather table information.

Parameters:

• schema (string, optional): Schema name containing the tables
• tables (string, optional): Comma-separated list of table names

Returns: TypeScript type definitions based on table structures

Example Usage: To generate TypeScript types for the users and posts tables in the public schema, use:

schema: public
tables: users, posts

---

Complete Workflow Examples

Example 1: Explore a Single Database

When you have only one database configured, you can omit the database parameter:

[
  { "name": "pg_list_schemas" },
  { "name": "pg_list_tables", "arguments": { "schema": "public" } },
  {
    "name": "pg_describe_table",
    "arguments": { "schema": "public", "table": "users" }
  }
]

Example 2: Work with Multiple Databases

When multiple databases are configured, always specify which one to use:

[
  { "name": "pg_db_list" },
  {
    "name": "pg_get_server_version",
    "arguments": { "database": "production" }
  },
  { "name": "pg_list_schemas", "arguments": { "database": "production" } },
  {
    "name": "pg_execute_query",
    "arguments": {
      "database": "analytics",
      "query": "SELECT COUNT(*) FROM events"
    }
  }
]

Example 3: Add and Configure a New Database

[
  {
    "name": "pg_db_add",
    "arguments": {
      "name": "staging",
      "url": "postgresql://user:password@staging-host:5432/app",
      "ttl": 45000
    }
  },
  { "name": "pg_db_list" },
  { "name": "pg_get_server_version", "arguments": { "database": "staging" } }
]

Example 4: Query Multiple Databases

[
  {
    "name": "pg_execute_query",
    "arguments": {
      "database": "production",
      "query": "SELECT COUNT(*) as user_count FROM users"
    }
  },
  {
    "name": "pg_execute_query",
    "arguments": {
      "database": "analytics",
      "query": "SELECT COUNT(*) as event_count FROM events"
    }
  }
]

Connection Pooling

pg-mcp uses a lazy connection pool to manage database connections efficiently:

• Connections are created on-demand when a tool is called
• Connections are tracked with last-used timestamps
• Idle connections are automatically closed based on their TTL
• An idle reaper runs periodically to clean up stale connections
• Pool is automatically reconciled when configuration changes

Auto-Reload

When autoReload is set to true in the configuration, pg-mcp will automatically reload the configuration file when changes are detected. This allows you to add, remove, or modify database configurations without restarting the server.

Building

To build the project for distribution:

bun run build

To compile a standalone executable:

bun run compile

This project was created using bun init in bun v1.2.15. Bun is a fast all-in-one JavaScript runtime.