MCP Firebird

Instructions

Implementation Reference

• src/tools/database.ts:273-306 (registration)

  Registration of the 'analyze-query-performance' MCP tool, including inline handler that parses args, calls the core analyzeQueryPerformance function, handles errors, and formats response for MCP.

  tools.set("analyze-query-performance", {
      name: "analyze-query-performance",
      description: "Analyzes the performance of a SQL query by executing it multiple times and measuring execution time",
      inputSchema: AnalyzeQueryPerformanceArgsSchema,
      handler: async (request) => {
          const { sql, params, iterations } = request;
          logger.info(`Executing analyze-query-performance tool for query: ${sql.substring(0, 50)}...`);
  
          try {
              const result = await analyzeQueryPerformance(
                  sql,
                  params || [],
                  iterations || 3
              );
  
              return {
                  content: [{
                      type: "text",
                      text: formatForClaude(result)
                  }]
              };
          } catch (error) {
              const errorResponse = wrapError(error);
              logger.error(`Error analyzing query performance: ${errorResponse.error} [${errorResponse.errorType || 'UNKNOWN'}]`);
  
              return {
                  content: [{
                      type: "text",
                      text: formatForClaude(errorResponse)
                  }]
              };
          }
      }
  });

• src/tools/database.ts:36-40 (schema)

  Zod input schema for the analyze-query-performance tool defining sql, optional params, and iterations.

  export const AnalyzeQueryPerformanceArgsSchema = z.object({
      sql: z.string().min(1).describe("SQL query to analyze"),
      params: z.array(z.string().or(z.number()).or(z.boolean()).or(z.null())).optional().describe("Parameters for parameterized queries"),
      iterations: z.number().int().positive().default(3).describe("Number of times to run the query for averaging performance")
  });

• src/db/queries.ts:500-603 (helper)

  Core helper function that implements query performance analysis by running the query multiple times, calculating stats (avg/min/max time, row count), and providing basic optimization suggestions.

  export const analyzeQueryPerformance = async (
      sql: string,
      params: any[] = [],
      iterations: number = 3,
      config = DEFAULT_CONFIG
  ): Promise<QueryPerformanceResult> => {
      try {
          // Validate the SQL query to prevent injection
          if (!validateSql(sql)) {
              throw new FirebirdError(
                  `Invalid SQL query: ${sql}`,
                  'VALIDATION_ERROR'
              );
          }
  
          logger.info(`Analyzing query performance with ${iterations} iterations`);
          logger.debug(`Query: ${sql}`);
  
          const executionTimes: number[] = [];
          let rowCount = 0;
          let results: any[] = [];
  
          // Execute the query multiple times and measure performance
          for (let i = 0; i < iterations; i++) {
              const startTime = performance.now();
              results = await executeQuery(sql, params, config);
              const endTime = performance.now();
  
              const executionTime = endTime - startTime;
              executionTimes.push(executionTime);
  
              // Only set rowCount on the first iteration
              if (i === 0) {
                  rowCount = results.length;
              }
  
              logger.debug(`Iteration ${i+1}: ${executionTime.toFixed(2)}ms`);
          }
  
          // Calculate statistics
          const averageTime = executionTimes.reduce((sum, time) => sum + time, 0) / executionTimes.length;
          const minTime = Math.min(...executionTimes);
          const maxTime = Math.max(...executionTimes);
  
          // Basic query analysis
          let analysis = "";
  
          // Check if the query has a WHERE clause
          if (!sql.toLowerCase().includes('where') && rowCount > 100) {
              analysis += "Query doesn't have a WHERE clause and returns many rows. Consider adding filters. ";
          }
  
          // Check for potential full table scans
          if (sql.toLowerCase().includes('select') && !sql.toLowerCase().includes('index') && rowCount > 1000) {
              analysis += "Query might be performing a full table scan. Consider using indexed columns in the WHERE clause. ";
          }
  
          // Check for ORDER BY on non-indexed columns (simplified check)
          if (sql.toLowerCase().includes('order by') && rowCount > 500) {
              analysis += "Query includes ORDER BY which might be slow on large datasets if columns aren't indexed. ";
          }
  
          // Performance assessment
          if (averageTime < 100) {
              analysis += "Performance is good. ";
          } else if (averageTime < 500) {
              analysis += "Performance is acceptable. ";
          } else if (averageTime < 1000) {
              analysis += "Performance could be improved. ";
          } else {
              analysis += "Performance is poor, query optimization is recommended. ";
          }
  
          const result: QueryPerformanceResult = {
              query: sql,
              executionTimes,
              averageTime,
              minTime,
              maxTime,
              rowCount,
              success: true,
              analysis: analysis.trim()
          };
  
          logger.info(`Query analysis complete: Avg=${averageTime.toFixed(2)}ms, Rows=${rowCount}`);
          return result;
  
      } catch (error: any) {
          const errorMessage = `Error analyzing query performance: ${error.message || error}`;
          logger.error(errorMessage);
  
          return {
              query: sql,
              executionTimes: [],
              averageTime: 0,
              minTime: 0,
              maxTime: 0,
              rowCount: 0,
              success: false,
              error: errorMessage,
              analysis: "Query execution failed."
          };
      }
  };