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');
  }