Fastpostgresmcp

🚀 MCP Server PostgreSQL Version

The MCP (Multi-Connection Proxy) Server is a proxy service that supports multiple database protocols and provides a rich set of tools and resources for managing database connections. This document details how to use the MCP Server to interact with a PostgreSQL database.

🚀 Quick Start

The MCP Server offers a seamless way to interact with PostgreSQL databases. Follow the steps below to get started.

📦 Installation

Quick Installation

npm install mcp-server-postgres --save

Configuration Steps

1. Create a Configuration File:
   • Create a new config.json file.
   • Add the following configuration content:

     {
       "database": {
         "connections": {
           "main": {
             "type": "postgres",
             "host": "localhost",
             "port": 5432,
             "username": "db_user",
             "password": "db_password"
           }
         },
         "defaultConnection": "main"
       },
       "authentication": {
         "secretKey": "your_secret_key_here",
         "enableSSL": true
       }
     }
     

   • Add config.json to the .env file:

     echo "MCP_CONFIG=path/to/config.json" >> .gitignore
     

Start the Service

node index.js --config config.json

✨ Features

Database Connection Pool Management

• Create a Connection Pool: Initialize a PostgreSQL connection pool using the DBConnectionFactory class.

  const connectionFactory = new DBConnectionFactory({
    host: 'localhost',
    port: 5432,
    username: 'db_user',
    password: 'db_password'
  });
  

• Get a Connection:

  const connection = await connectionFactory.getConnection('main');
  

Execute Queries

Simple Query

const query = new QueryBuilder('users')
  .select(['id', 'username'])
  .where('active = true');

const result = await connection.query(query);

Parameterized Query

const params = {
  username: 'john_doe',
  status: 'active'
};

const query = new QueryBuilder('users')
  .select(['id', 'email'])
  .where('username = :username AND status = :status', params);

const result = await connection.query(query);

Handle Transactions

await connection.beginTransaction();
try {
  // Execute multiple SQL operations
  await connection.execute("UPDATE orders SET status = 'processing'");
  await connection.execute("INSERT INTO order_items SELECT * FROM temp_order_items");
} catch (error) {
  await connection.rollback();
  throw error;
}

💻 Usage Examples

Query Tool QueryTool

• Execute a Single Query:

  const queryTool = new QueryTool(connection);
  const result = await queryTool.query("SELECT * FROM users WHERE id = $1", [123]);
  

• Execute Multiple Queries:

  const results = await queryTool.execute([
    "DELETE FROM sessions WHERE expires_at < NOW()",
    "UPDATE users SET last_login = NOW() WHERE username = 'admin'"
  ]);
  

Transaction Tool TransactionTool

• Wrap in a Transaction:

  const transactionTool = new TransactionTool(connection);
  await transactionTool.run(operations => {
    return Promise.all([
      connection.execute("INSERT INTO orders (user_id, order_date) VALUES ($1, NOW())", [userId]),
      connection.execute("UPDATE users SET last_order = NOW() WHERE id = $1", [userId])
    ]);
  });
  

📚 Documentation

Resource Management - Templated Queries

• Define a Template:

  const queryTemplate = new QueryTemplate({
    name: 'getUsers',
    text: 'SELECT * FROM users WHERE active = true',
    parameters: []
  });
  

• Execute the Template:

  await queryTemplate.execute(connection);
  

Security Considerations

Prevent SQL Injection

• Use parameterized queries to avoid directly concatenating SQL statements.
• Avoid using aggregate functions like string_agg or array_agg to prevent injection risks.

Database Permissions

• Principle of Least Privilege: Assign the minimum necessary permissions to each database user.
• Information Schema Access: Ensure that the information schema user for table and column listing has appropriate read permissions.

Transport Layer Security

• Enforce SSL/TLS encryption in the production environment. It is recommended to set the SSL mode to require or more strict.

Monitoring and Logging

Performance Monitoring

• Connection Pool Status: Monitor the number of active and idle connections.
• Query Latency: Record the execution time of each query to analyze bottlenecks.

Logging

• Configure winston or other logging libraries to output to a file or a centralized logging service (e.g., ELK).

Common Issues

Connection Pool Exhaustion

• Symptom: The request queue grows longer, and the response time increases.
• Solution:
  • Increase the maximum number of connections, but consider the database load.
  • Configure reasonable timeout settings to avoid zombie connections.

SSL Errors

• Problem: Unable to establish a secure connection.
• Troubleshooting:
  • Confirm the validity of the certificate and the integrity of the link.
  • Check if the sslmode configuration is correct.

Upgrade Guide

Update Dependencies

npm update mcp-server-postgres --save

Migrate Configuration

• Check the configuration options of the new version and update the config.json file.
• Restart the service to ensure the new configuration takes effect.

More Resources

• Official Documentation: MCP Official Website
• Sample Code: GitHub Repository
• Community Support: Join the Discord Community for help.

With the above guide, you can successfully deploy and use the MCP Server to interact efficiently with a PostgreSQL database.