MCP Firebird

Instructions

Implementation Reference

• src/db/queries.ts:754-850 (handler)

  Core handler function that implements the logic for analyzing SQL queries to detect potential missing indexes by parsing WHERE, JOIN, and ORDER BY clauses using regex, suggesting CREATE INDEX statements.

  export const analyzeMissingIndexes = async (
      sql: string,
      config = DEFAULT_CONFIG
  ): Promise<{missingIndexes: string[], recommendations: string[], success: boolean, error?: string}> => {
      try {
          // Validate the SQL query to prevent injection
          if (!validateSql(sql)) {
              throw new FirebirdError(
                  `Invalid SQL query: ${sql}`,
                  'VALIDATION_ERROR'
              );
          }
  
          logger.info(`Analyzing missing indexes for query: ${sql.substring(0, 100)}${sql.length > 100 ? '...' : ''}`);
  
          // Parse the SQL query to extract table and column information
          const tablePattern = /\bFROM\s+([\w\.]+)\b/i;
          const wherePattern = /\bWHERE\s+(.+?)(?:\bGROUP BY\b|\bORDER BY\b|\bHAVING\b|$)/is;
          const joinPattern = /\bJOIN\s+([\w\.]+)\s+(?:\w+\s+)?ON\s+(.+?)(?:\bJOIN\b|\bWHERE\b|\bGROUP BY\b|\bORDER BY\b|\bHAVING\b|$)/gis;
          const orderByPattern = /\bORDER BY\s+(.+?)(?:$|;)/i;
  
          // Extract the main table
          const tableMatch = sql.match(tablePattern);
          const mainTable = tableMatch ? tableMatch[1].trim() : null;
  
          // Extract WHERE conditions
          const whereMatch = sql.match(wherePattern);
          const whereConditions = whereMatch ? whereMatch[1].trim() : null;
  
          // Extract JOIN conditions
          const joinMatches = Array.from(sql.matchAll(joinPattern));
          const joinTables: {table: string, condition: string}[] = joinMatches.map(match => ({
              table: match[1].trim(),
              condition: match[2].trim()
          }));
  
          // Extract ORDER BY columns
          const orderByMatch = sql.match(orderByPattern);
          const orderByColumns = orderByMatch ? orderByMatch[1].trim() : null;
  
          // Analyze and generate recommendations
          const missingIndexes: string[] = [];
          const recommendations: string[] = [];
  
          // Check WHERE conditions for potential indexes
          if (whereConditions && mainTable) {
              const whereColumns = extractColumnsFromCondition(whereConditions);
              if (whereColumns.length > 0) {
                  const indexName = `IDX_${mainTable}_${whereColumns.join('_')}`;
                  missingIndexes.push(`CREATE INDEX ${indexName} ON ${mainTable} (${whereColumns.join(', ')});`);
                  recommendations.push(`Consider creating an index on ${mainTable}(${whereColumns.join(', ')}) to improve WHERE clause filtering.`);
              }
          }
  
          // Check JOIN conditions for potential indexes
          for (const join of joinTables) {
              const joinColumns = extractColumnsFromCondition(join.condition);
              if (joinColumns.length > 0) {
                  const tableColumns = joinColumns.filter(col => col.includes(join.table + '.'));
                  if (tableColumns.length > 0) {
                      // Extract just the column names without table prefix
                      const columns = tableColumns.map(col => col.split('.')[1]);
                      const indexName = `IDX_${join.table}_${columns.join('_')}`;
                      missingIndexes.push(`CREATE INDEX ${indexName} ON ${join.table} (${columns.join(', ')});`);
                      recommendations.push(`Consider creating an index on ${join.table}(${columns.join(', ')}) to improve JOIN performance.`);
                  }
              }
          }
  
          // Check ORDER BY for potential indexes
          if (orderByColumns && mainTable) {
              const orderCols = orderByColumns.split(',').map(col => col.trim().split(' ')[0]); // Remove ASC/DESC
              const indexName = `IDX_${mainTable}_ORDER_${orderCols.join('_')}`;
              missingIndexes.push(`CREATE INDEX ${indexName} ON ${mainTable} (${orderCols.join(', ')});`);
              recommendations.push(`Consider creating an index on ${mainTable}(${orderCols.join(', ')}) to improve ORDER BY performance.`);
          }
  
          logger.info(`Missing index analysis complete, found ${missingIndexes.length} potential missing indexes`);
  
          return {
              missingIndexes,
              recommendations,
              success: true
          };
  
      } catch (error: any) {
          const errorMessage = `Error analyzing missing indexes: ${error.message || error}`;
          logger.error(errorMessage);
  
          return {
              missingIndexes: [],
              recommendations: [],
              success: false,
              error: errorMessage
          };
      }
  };

• src/tools/database.ts:341-370 (registration)

  Registers the MCP tool 'analyze-missing-indexes' in the tools map, including description, input schema, and a wrapper handler that calls the core analyzeMissingIndexes function.

  tools.set("analyze-missing-indexes", {
      name: "analyze-missing-indexes",
      description: "Analyzes a SQL query to identify missing indexes that could improve performance",
      inputSchema: AnalyzeMissingIndexesArgsSchema,
      handler: async (request) => {
          const { sql } = request;
          logger.info(`Executing analyze-missing-indexes tool for query: ${sql.substring(0, 50)}...`);
  
          try {
              const result = await analyzeMissingIndexes(sql);
  
              return {
                  content: [{
                      type: "text",
                      text: formatForClaude(result)
                  }]
              };
          } catch (error) {
              const errorResponse = wrapError(error);
              logger.error(`Error analyzing missing indexes: ${errorResponse.error} [${errorResponse.errorType || 'UNKNOWN'}]`);
  
              return {
                  content: [{
                      type: "text",
                      text: formatForClaude(errorResponse)
                  }]
              };
          }
      }
  });

• src/tools/database.ts:47-49 (schema)

  Zod schema defining the input for the tool: a required SQL query string.

  export const AnalyzeMissingIndexesArgsSchema = z.object({
      sql: z.string().min(1).describe("SQL query to analyze for missing indexes")
  });

• src/db/queries.ts:857-872 (helper)

  Helper function used by analyzeMissingIndexes to extract column names from SQL WHERE/JOIN conditions using regex.

  function extractColumnsFromCondition(condition: string): string[] {
      const columns: string[] = [];
  
      // Match patterns like: column = value, column IN (...), column BETWEEN ... AND ...
      const columnPattern = /([\w\.]+)\s*(?:=|>|<|>=|<=|<>|!=|LIKE|IN|BETWEEN|IS)/gi;
      let match;
  
      while ((match = columnPattern.exec(condition)) !== null) {
          const column = match[1].trim();
          if (!columns.includes(column)) {
              columns.push(column);
          }
      }
  
      return columns;
  }