Pg MCP Server

🚀 PostgreSQL MCP Server Guide

This guide provides a comprehensive introduction to the PostgreSQL MCP server, including its directory structure, core scripts, protocol implementation, environment variable configuration, security considerations, and usage examples.

📦 Directory Structure

The project structure is as follows:

• dist: Stores compiled JavaScript files.
• src: Contains core library files.
• package.json: Manages dependencies and configures build scripts.
• tsconfig.json: Configures TypeScript compilation.

✨ Core Scripts Explanation

Purpose and Function

These scripts are responsible for initializing and managing PostgreSQL database connections, handling transactions, and providing database operation interfaces.

Main Scripts

index.ts

• Function: Initializes the MCP server.
• Features:
  • Parses command-line arguments.
  • Connects to the PostgreSQL database.
  • Starts the monitoring thread.
  • Handles client requests.

connection-pool.ts

• Function: Manages the database connection pool.
• Features:
  • Creates and manages the database connection pool.
  • Implements a connection reuse mechanism.
  • Sets connection timeout parameters.
  • Monitors connection status.

transaction-monitor.ts

• Function: Monitors transaction execution status.
• Features:
  • Records the transaction start time.
  • Checks for transaction timeouts.
  • Automatically rolls back long-running uncommitted transactions.
  • Provides transaction statistics.

💻 MCP Protocol Implementation

Usage Examples

const { McpServer } = require('./dist/mcp-server');

// Initialize the MCP server
const server = new McpServer({
  connectionString: 'postgresql://username:password@database_address:port/database_name',
  transactionTimeoutMs: 15000,
  maxConnections: 20
});

// Start the server
server.start();

Functional Modules

• Connection Management: Provides an efficient database connection pool to support high-concurrency access.
• Transaction Control: Implements transaction commit and rollback functions and provides a timeout protection mechanism.
• Query Execution: Executes SQL queries and returns result sets.
• Metadata Retrieval: Retrieves database table structure information.

⚙️ Environment Variable Configuration

🔒 Security Considerations

• Ensure that database credentials are not leaked.
• Configure appropriate access control policies.
• Regularly audit database operation logs.
• Use SSL encryption for database connections.

💡 Example Usage Scenarios

Execute a Query

SELECT * FROM users WHERE age > 18;

Execute Data Modification

INSERT INTO users (name, age) VALUES ('Zhang San', 25);

View Table Structure

Get detailed information about the "users" table.

List All Tables

List all tables in the database.

⚠️ Important Notes

• Database users must have the appropriate operation permissions.
• It is recommended to use stricter timeout settings in the production environment.
• Long-running transactions will be automatically rolled back to prevent resource occupation.
• DML operations require explicit commits to take permanent effect.
• Do not store sensitive information in plain text in the configuration file.
• It is recommended to back up the database regularly.

📚 Appendix

Error Codes

Return Value Format

{
  "code": number,
  "data": any,
  "message": string
}

Through the above configuration and usage, you can efficiently and securely manage the PostgreSQL database.