MCP Firebird

Instructions

Implementation Reference

• src/tools/database.ts:42-45 (schema)

  Zod input schema for the get-execution-plan tool defining sql and optional params
  export const GetExecutionPlanArgsSchema = 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") });
• src/tools/database.ts:309-338 (registration)

  Registration of the get-execution-plan tool in setupDatabaseTools, including inline handler that delegates to getExecutionPlan
  tools.set("get-execution-plan", { name: "get-execution-plan", description: "Gets the execution plan for a SQL query to understand how the database will execute it", inputSchema: GetExecutionPlanArgsSchema, handler: async (request) => { const { sql, params } = request; logger.info(`Executing get-execution-plan tool for query: ${sql.substring(0, 50)}...`); try { const result = await getExecutionPlan(sql, params || []); return { content: [{ type: "text", text: formatForClaude(result) }] }; } catch (error) { const errorResponse = wrapError(error); logger.error(`Error getting execution plan: ${errorResponse.error} [${errorResponse.errorType || 'UNKNOWN'}]`); return { content: [{ type: "text", text: stringifyCompact(errorResponse) }] }; } } });
• src/db/queries.ts:613-704 (handler)

  Core getExecutionPlan function implementation that handles validation and returns detailed explanation why execution plans are not retrievable via Node.js drivers, with tool recommendations
  export const getExecutionPlan = async ( sql: string, params: any[] = [], config = getGlobalConfig() || DEFAULT_CONFIG ): Promise<ExecutionPlanResult> => { try { // Validate the SQL query to prevent injection if (!validateSql(sql)) { throw new FirebirdError( `Invalid SQL query: ${sql}`, 'VALIDATION_ERROR' ); } logger.info(`Getting execution plan for query: ${sql.substring(0, 100)}${sql.length > 100 ? '...' : ''}`); // Only SELECT queries are supported for execution plan analysis if (!sql.trim().toUpperCase().startsWith('SELECT')) { throw new FirebirdError( `Only SELECT queries are supported for execution plan analysis`, 'UNSUPPORTED_OPERATION' ); } // Check if we're using the native driver by checking DriverFactory const { DriverFactory } = await import('./driver-factory.js'); const driverInfo = await DriverFactory.getDriverInfo(); logger.debug('Driver info for execution plan', { driverType: driverInfo.current, nativeAvailable: driverInfo.nativeAvailable }); // Note: node-firebird-driver-native and node-firebird do not expose a direct method to get execution plans // The Firebird API has isc_dsql_sql_info with isc_info_sql_get_plan (constant value: 22), but this is not // exposed in the high-level driver interfaces. The low-level node-firebird-native-api would be needed, // but it's complex to use directly and would require significant refactoring. // // Research findings: // - FDB (Python driver) uses: isc_dsql_sql_info(statement_handle, [isc_info_sql_get_plan]) // - node-firebird-driver-native Statement object does not have getPlan() or getInfo() methods // - node-firebird (pure JS) does not support SET PLANONLY or SET PLAN commands (isql-specific) // // Recommendation: Use Firebird tools for execution plan analysis: // - isql: SET PLANONLY ON; <query>; SET PLANONLY OFF; // - FlameRobin: GUI tool with built-in plan visualization // - IBExpert: Commercial tool with advanced plan analysis logger.info('Execution plan retrieval is not available through Node.js Firebird drivers'); logger.info('The drivers do not expose the isc_dsql_sql_info API needed to get execution plans'); logger.info('Recommendation: Use Firebird tools like isql, FlameRobin, or IBExpert for execution plans'); // Return informative message return { query: sql, plan: "Execution plan not available", planDetails: [], success: true, analysis: "Node.js Firebird drivers do not expose the execution plan API.\n\n" + "The Firebird API provides isc_dsql_sql_info with isc_info_sql_get_plan (constant: 22) " + "to retrieve execution plans, but neither node-firebird-driver-native nor node-firebird " + "expose this functionality in their high-level interfaces.\n\n" + "To view execution plans, please use one of these Firebird tools:\n\n" + "1. isql (Firebird command-line tool):\n" + " SET PLANONLY ON;\n" + " <your query>;\n" + " SET PLANONLY OFF;\n\n" + "2. FlameRobin (Free GUI tool):\n" + " - Open SQL Editor\n" + " - Enter your query\n" + " - Click 'Show Plan' button\n\n" + "3. IBExpert (Commercial GUI tool):\n" + " - SQL Editor with built-in plan visualization\n" + " - Advanced plan analysis features\n\n" + "Alternatively, you can use the 'analyze-query-performance' tool to measure " + "query execution time and identify performance issues." }; } catch (error: any) { const errorMessage = `Error getting execution plan: ${error.message || error}`; logger.error(errorMessage); return { query: sql, plan: "", planDetails: [], success: false, error: errorMessage, analysis: "Failed to get execution plan." }; } };
• src/db/queries.ts:64-71 (schema)

  TypeScript interface defining the structure of execution plan results
  export interface ExecutionPlanResult { query: string; plan: string; planDetails: any[]; success: boolean; error?: string; analysis: string; }
• src/db/queries.ts:709-744 (helper)

  Helper function to analyze execution plans (currently unused as plans can't be retrieved)
  function analyzePlan(plan: string): string { const analysis: string[] = []; const upperPlan = plan.toUpperCase(); // Check for NATURAL scans (table scans without index) if (upperPlan.includes('NATURAL')) { analysis.push('⚠️ NATURAL scan detected - table is being scanned without using an index. Consider adding an index for better performance.'); } // Check for INDEX usage const indexMatches = plan.match(/INDEX\s+\(([^)]+)\)/gi); if (indexMatches && indexMatches.length > 0) { analysis.push(`✅ Using ${indexMatches.length} index(es): ${indexMatches.join(', ')}`); } // Check for JOIN operations if (upperPlan.includes('JOIN')) { analysis.push('🔗 Query contains JOIN operations'); } // Check for SORT operations if (upperPlan.includes('SORT')) { analysis.push('📊 SORT operation detected - may impact performance on large datasets'); } // Check for FILTER operations if (upperPlan.includes('FILTER')) { analysis.push('🔍 FILTER operation detected - rows are being filtered after retrieval'); } if (analysis.length === 0) { return 'Plan retrieved successfully. The query appears to be optimized.'; } return analysis.join('\n'); }