Postgres Query

🚀 MCP PostgreSQL Query Server

An implementation of a Model Context Protocol (MCP) server for querying PostgreSQL databases in read-only mode, designed to work with Claude Desktop and other MCP clients.

🚀 Quick Start

This project implements a Model Context Protocol (MCP) server with the following features:

1. Provide a secure read-only interface to access PostgreSQL databases.
2. Integrate with Claude Desktop via the MCP protocol.
3. Validate SQL queries to ensure that only SELECT queries are executed.
4. Provide query timeout protection (10 seconds).

✨ Features

• Read-only Database Access: Only SELECT queries are allowed to ensure security.
• Query Validation: Prevent potentially harmful SQL operations.
• Timeout Protection: Queries that run for more than 10 seconds will be automatically terminated.
• MCP Protocol Support: A complete implementation of the Model Context Protocol.
• JSON Response Formatting: Query results are returned in a structured JSON format.

📦 Installation

Prerequisites

• Node.js (v14 or higher)
• npm (provided with Node.js)
• PostgreSQL database (connection details provided via the command line)

Steps

# Clone the repository
git clone https://github.com/RathodDarshil/mcp-postgres-query-server.git
cd mcp-postgres-query-server

# Install dependencies
npm install

# Build the project
npm run build

💻 Usage Examples

Connecting to Claude Desktop

You can configure Claude Desktop to automatically start and connect to the MCP server:

1. Access the Claude Desktop configuration file:

   • Open Claude Desktop.
   • Go to Settings > Developer > Edit Configuration.
   • This will open the configuration file in the default text editor.

2. Add the postgres-query server to the mcpServers section of claude_desktop_config.json:

{
    "mcpServers": {
        "postgres-query": {
            "command": "node",
            "args": [
                "/path/to/your/mcp-postgres-query-server/dist/index.js",
                "postgresql://username:password@hostname:port/database"
            ]
        }
    }
}

3. Replace /path/to/your/ with the actual path to your project.
4. Replace the PostgreSQL connection string with your actual database credentials.
5. Save the file and restart Claude Desktop. The MCP server should now appear in the MCP server selection dropdown in Settings.

Example Configuration

Here is a complete example of a configuration file with postgres-query:

{
    "mcpServers": {
        "postgres-query": {
            "command": "node",
            "args": [
                "/Users/darshilrathod/mcp-servers/mcp-postgres-query-server/dist/index.js",
                "postgresql://user:password@localhost:5432/mydatabase"
            ]
        }
    }
}

Updating the Configuration

To update your Claude Desktop configuration:

1. Open Claude Desktop.
2. Go to Settings > Developer > Edit Configuration.
3. Make changes to the configuration file.
4. Save the file.
5. Restart Claude Desktop for the changes to take effect.
6. If you have updated the MCP server code, make sure to rebuild it using npm run build before restarting.

📚 Documentation

API

Tool: query-postgres

Execute a read-only SQL query on the configured PostgreSQL database.

Parameters:

• query (string): The SELECT SQL query to execute.

Response:

• A JSON object containing:
  • rows: The result set rows.
  • rowCount: The number of rows returned.
  • columns: The result columns of the query.

Example

{
  "query": "SELECT * FROM users LIMIT 5",
  "result": {
    "rows": [
      { "id": 1, "name": "Alice" },
      { "id": 2, "name": "Bob" }
    ],
    "rowCount": 2,
    "columns": ["id", "name"]
  }
}

Development

• Project Structure:

  • src/: Contains the core server implementation.
  • dist/: The build output directory.
  • package.json: Project dependencies and scripts.
  • README.md: Project documentation.

• Contribution Guidelines:

  • Before submitting code, ensure that tests are run via npm scripts.
  • Follow the project's code style guidelines.

Security Considerations

• Ensure the security of database credentials and avoid storing them in plain text in the code.
• Use environment variables or configuration management tools to manage sensitive information.
• Regularly audit and update server security policies.