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." }; } };