MCP Firebird

Instructions

Implementation Reference

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

  Core handler function that performs static analysis on the SQL query to identify potential missing indexes by parsing WHERE, JOIN, and ORDER BY clauses.
  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:47-49 (schema)

  Zod input schema for the analyze-missing-indexes tool defining the required SQL query parameter.
  export const AnalyzeMissingIndexesArgsSchema = z.object({ sql: z.string().min(1).describe("SQL query to analyze for missing indexes") });
• src/tools/database.ts:340-370 (registration)

  Tool registration in the database tools map, including the MCP tool handler which wraps and delegates to the core analyzeMissingIndexes function.
  // Add analyze-missing-indexes tool 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/db/queries.ts:857-872 (helper)

  Helper function used by analyzeMissingIndexes to parse SQL conditions and extract column names for index recommendations.
  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; }