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