MCP Firebird

Instructions

Implementation Reference

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

  Zod schema for the tool's input parameters: sql query, 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/tools/database.ts:272-306 (registration)

  Tool definition and registration in the database tools map returned by setupDatabaseTools().
  // Add analyze-query-performance tool 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:277-305 (handler)

  The core handler function for the MCP tool that delegates to analyzeQueryPerformance and handles MCP response format.
  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/db/queries.ts:500-603 (helper)

  Core helper function that executes the query multiple times, measures execution times, performs basic analysis, and returns performance metrics.
  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." }; } };
• src/server/mcp-server.ts:125-143 (registration)

  Final MCP server registration where databaseTools (including analyze-query-performance) are added to the MCP server instance.
  logger.info('Registering tools...'); const databaseTools = setupDatabaseTools(); const metadataTools = setupMetadataTools(databaseTools); const simpleTools = setupSimpleTools(); // Register all tools using the helper function for (const [name, toolDef] of databaseTools.entries()) { registerTool(name, toolDef); } for (const [name, toolDef] of metadataTools.entries()) { registerTool(name, toolDef); } for (const [name, toolDef] of simpleTools.entries()) { registerTool(name, toolDef); } logger.info(`Registered ${databaseTools.size + metadataTools.size + simpleTools.size} tools in total.`);