rails-pg-extras

This MCP server exposes Rails PG Extras functionality via an HTTP SSE interface. It allows an LLM-powered agent to query PostgreSQL metadata, identify bottlenecks, analyze query performance, and optionally run EXPLAIN or EXPLAIN ANALYZE on bottleneck queries. The server uses the rails-pg-extras-mcp gem to surface metadata endpoints and integrates with mcp-remote so you can feed the data into an LLM environment. To begin, start your Rails app with the RailsPgExtrasMcp::App mounted at an endpoint (e.g., /pg_extras_mcp) and ensure you have a read-only user if you plan to use EXPLAIN ANALYZE features.

Usage flow:

• Run the MCP server (via the provided SSE endpoint) and ensure it is reachable at the configured URL (http://localhost:3000/pg_extras_mcp/sse).
• On the client side, configure mcp-remote (via the provided npm command) to point to the SSE URL and, if desired, set an Authorization header for access control.
• In your LLM environment, invoke the MCP server through the configured mcp-remote pipeline to ask questions about PostgreSQL metadata, performance metrics, and query plans. If EXPLAIN ANALYZE is enabled, you can request more detailed analysis of bottleneck queries.
• Optional: enable explain features by setting environment variables described in the README to allow the LLM to generate and validate query plans against the database.

Prerequisites:

• Ruby and Bundler installed on the host running Rails.
• A Rails application where you can mount an MCP app.
• Node.js and npm installed for mcp-remote (optional if you use a different MCP runner).

Step-by-step installation:

1. Add the MCP gem to your Rails project: bundle add rails-pg-extras-mcp

2. Mount the MCP app in your Rails routes (config/routes.rb): mount RailsPgExtrasMcp::App.build, at: "pg_extras_mcp"

   Optional: provide authorization by passing options when building the app: opts = { auth_token: "secret" } mount RailsPgExtrasMcp::App.build(opts), at: "pg_extras_mcp"

3. Review and adapt options for production (reference fast-mcp options): opts = { allowed_origins: [ /../ ], allowed_ips: [ "" ], auth_token: "secret", localhost_only: false } mount RailsPgExtrasMcp::App.build(opts) at: "pg_extras_mcp"

4. Install mcp-remote to enable LLM integration from your environment: npm install -g mcp-remote

5. Configure the MCP server mapping in your MCP client (example): { "mcpServers": { "pg-extras": { "command": "npx", "args": [ "mcp-remote", "http://localhost:3000/pg_extras_mcp/sse", "--header", "Authorization: secret" ] } } }

Tips and caveats:

• For EXPLAIN ANALYZE features to work, enable them via environment variables: PG_EXTRAS_MCP_EXPLAIN_ENABLED and PG_EXTRAS_MCP_EXPLAIN_ANALYZE_ENABLED, both set to 'true'.
• If you enable advanced query analysis, ensure the connected database user has read-only access or is appropriately restricted; the execution context for explanations runs within a transaction that is rolled back.
• Consider setting a dedicated read-only role for MCP access if EXPLAIN features are not required.
• Ensure the endpoint URL in your mcp-remote configuration matches your deployed MCP app route and that any authorization tokens are kept secure.
• This MCP setup is designed to work with Rails and the rails-pg-extras gem; if you upgrade Rails or gem versions, verify compatibility notes in the gem documentation.