PostgreSQL MCP Server

Instructions

Implementation Reference

• tools/connectionStatus.ts:19-95 (handler)

  The main handler function for the 'connection-status' tool. Validates input parameters, checks current connection status or performs retry if requested, and returns formatted response with troubleshooting information.

  export async function connectionStatus(
    rawParams: any
  ): McpToolResponse {
    try {
      // Validate and parse parameters
      const params = connectionStatusSchema.parse(rawParams);
      
      debug("Connection status tool called with retry: %s", params.retry);
      
      // If retry is requested, attempt to reconnect
      if (params.retry) {
        debug("Attempting connection retry...");
        const retrySuccess = await retryConnection();
        const status = getConnectionStatus();
        
        return createMcpSuccessResponse({
          action: "retry_attempted",
          connection_status: status.status,
          retry_successful: retrySuccess,
          error: status.error,
          last_attempt: status.lastAttempt,
          message: retrySuccess 
            ? "Database connection retry successful" 
            : `Database connection retry failed: ${status.error}`,
          troubleshooting: retrySuccess ? null : {
            common_issues: [
              "Check if PostgreSQL server is running",
              "Verify DATABASE_URL environment variable is correct", 
              "Ensure database credentials are valid",
              "Check network connectivity to database server",
              "Verify firewall settings allow database connections"
            ],
            next_steps: [
              "Review your database configuration in .env file",
              "Test connection manually with psql or database client",
              "Check database server logs for connection errors",
              "Use connection-status tool with retry: true to test again"
            ]
          }
        });
      }
      
      // Return current status without retry attempt
      const status = getConnectionStatus();
      
      return createMcpSuccessResponse({
        connection_status: status.status,
        error: status.error,
        last_attempt: status.lastAttempt,
        message: status.status === 'connected' 
          ? "Database connection is healthy" 
          : status.status === 'failed'
          ? `Database connection failed: ${status.error}`
          : "Database connection status unknown - no connection attempt made yet",
        retry_available: true,
        troubleshooting: status.status === 'failed' ? {
          common_issues: [
            "Check if PostgreSQL server is running",
            "Verify DATABASE_URL environment variable is correct", 
            "Ensure database credentials are valid",
            "Check network connectivity to database server",
            "Verify firewall settings allow database connections"
          ],
          next_steps: [
            "Review your database configuration in .env file",
            "Test connection manually with psql or database client", 
            "Check database server logs for connection errors",
            "Use connection-status tool with retry: true to attempt reconnection"
          ]
        } : null
      });
      
    } catch (error) {
      debug("Error in connection status tool: %o", error);
      return createMcpErrorResponse("check connection status", error);
    }
  }

• tools/connectionStatus.ts:11-16 (schema)

  Zod schema defining the input parameters for the connection-status tool (retry: boolean, optional, defaults to false).

  // Zod schema for input validation
  export const connectionStatusShape: ZodRawShape = {
    retry: z.boolean().optional().default(false),
  };
  
  export const connectionStatusSchema = z.object(connectionStatusShape);

• index.ts:76-81 (registration)

  Registers the 'connection-status' tool with the MCP server, providing name, description, input schema, and handler function.

  server.tool(
    "connection-status",
    "Check database connection status, view error details, and retry connection. Use retry: true to attempt reconnection when database is unavailable.",
    connectionStatusShape,
    connectionStatus
  );

• tools/utils.ts:186-196 (helper)

  Helper function that returns the current database connection status, any error message, and timestamp of last connection attempt.

  export function getConnectionStatus(): {
    status: ConnectionStatus;
    error: string | null;
    lastAttempt: Date | null;
  } {
    return {
      status: connectionStatus,
      error: connectionError,
      lastAttempt: lastConnectionAttempt,
    };
  }

• tools/utils.ts:198-223 (helper)

  Helper function to attempt database reconnection by executing a test query and updating the global connection status.

  export async function retryConnection(): Promise<boolean> {
    debug("Attempting to retry database connection...");
    lastConnectionAttempt = new Date();
    
    try {
      const result = await executePostgresQuery("SELECT 1 as test");
      const success = result.length === 1 && result[0].test === 1;
      
      if (success) {
        connectionStatus = 'connected';
        connectionError = null;
        debug("Connection retry successful");
        return true;
      } else {
        connectionStatus = 'failed';
        connectionError = "Connection test query returned unexpected result";
        debug("Connection retry failed: unexpected result");
        return false;
      }
    } catch (error) {
      connectionStatus = 'failed';
      connectionError = error instanceof Error ? error.message : String(error);
      debug("Connection retry failed: %o", error);
      return false;
    }
  }