PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/analyze.ts:30-56 (handler)

  The main handler for the pg_analyze_database tool. Validates input, resolves connection string, executes analysis, and returns JSON-formatted result.

  export const analyzeDatabaseTool: PostgresTool = {
    name: toolDefinition.name,
    description: toolDefinition.description,
    inputSchema: toolDefinition.inputSchema,
    execute: async (args: { connectionString?: string; analysisType?: 'configuration' | 'performance' | 'security'; }, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> => {
      const { connectionString: connStringArg, analysisType } = args;
  
      if (!analysisType || !['configuration', 'performance', 'security'].includes(analysisType)) {
          return {
              content: [{ type: 'text', text: 'Error: analysisType is required and must be one of [\'configuration\', \'performance\', \'security\'].' }],
              isError: true,
          };
      }
  
      const resolvedConnString = getConnectionString(connStringArg);
      const result = await originalAnalyzeDatabase(resolvedConnString, analysisType);
      
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(result, null, 2)
          }
        ]
      };
    },
  };

• src/tools/analyze.ts:19-28 (schema)

  Zod inputSchema definition for the tool parameters: optional connectionString and analysisType.

  const toolDefinition = {
    name: 'pg_analyze_database',
    description: 'Analyze PostgreSQL database configuration and performance',
    inputSchema: z.object({
      connectionString: z.string().optional()
        .describe('PostgreSQL connection string (optional if POSTGRES_CONNECTION_STRING environment variable or --connection-string CLI option is set)'),
      analysisType: z.enum(['configuration', 'performance', 'security']).optional()
        .describe('Type of analysis to perform')
    })
  };

• src/index.ts:225-257 (registration)

  The allTools array includes analyzeDatabaseTool, making it available to the PostgreSQLServer constructor which registers tools for MCP capabilities.

  const allTools: PostgresTool[] = [
    // Core Analysis & Debugging
    analyzeDatabaseTool,
    debugDatabaseTool,
    
    // Schema & Structure Management (Meta-Tools)
    manageSchemaTools,
    manageFunctionsTool,
    manageTriggersTools,
    manageIndexesTool,
    manageConstraintsTool,
    manageRLSTool,
    
    // User & Security Management
    manageUsersTool,
    
    // Query & Performance Management
    manageQueryTool,
    
    // Data Operations (Enhancement Tools)
    executeQueryTool,
    executeMutationTool,
    executeSqlTool,
    
    // Documentation & Metadata
    manageCommentsTool,
    
    // Data Migration & Monitoring
    exportTableDataTool,
    importTableDataTool,
    copyBetweenDatabasesTool,
    monitorDatabaseTool
  ];

• src/index.ts:19-19 (registration)

  Import of analyzeDatabaseTool from './tools/analyze.js' (note: source file is analyze.ts).

  import { analyzeDatabaseTool } from './tools/analyze.js';

• src/tools/analyze.ts:58-80 (helper)

  Core helper function that performs the actual database analysis: connects, retrieves version/settings/metrics/recommendations, and disconnects.

  export async function analyzeDatabase(
    connectionString: string,
    analysisType: 'configuration' | 'performance' | 'security' = 'configuration'
  ): Promise<AnalysisResult> {
    const db = DatabaseConnection.getInstance();
    await db.connect(connectionString);
  
    try {
      const version = await getVersion();
      const settings = await getSettings();
      const metrics = await getMetrics();
      const recommendations = await generateRecommendations(analysisType, settings, metrics);
  
      return {
        version,
        settings,
        metrics,
        recommendations,
      };
    } finally {
      await db.disconnect();
    }
  }