src/app/api/sql/explain/route.ts - main - dupleymi-amp/sql-trainer

src/app/api/sql/explain/route.ts
96 строк4 KB
Maxim Dupley
modified: eslint.config.mjs
31 июл 2026, 11:14
31 июл 2026, 11:14db62f0d
import { NextRequest, NextResponse } from 'next/server';
import { explainQuery } from '@/lib/sql-engine';
import { getTaskById } from '@/lib/training-tasks';
import { parseAndValidate } from '@/lib/validation';
import { rateLimit, getClientIdentifier, RATE_LIMIT_WINDOWS } from '@/lib/rate-limit';
import { sqlExplainSchema, VALID_DB_TYPES } from '@/lib/sql-schema';
import { validateTrainingSql } from '@/lib/sql-safety';
import { apiServerError } from '@/lib/api-error';
import { auth } from '@/lib/auth-internal';

/**
 * Analyze EXPLAIN plan and return performance suggestions.
 */
function analyzePlan(plan: string, sql: string): string[] {
  const suggestions: string[] = [];
  const planLower = plan.toLowerCase();
  const sqlUpper = sql.toUpperCase();

  // Full table scan detection
  if (planLower.includes('scan') && !planLower.includes('index')) {
    suggestions.push('Full table scan detected. Consider adding an index to speed up the query.');
  }

  // JOIN without index
  if (planLower.includes('join') && planLower.includes('scan')) {
    suggestions.push('JOIN without index may be slow. Add indexes on join columns.');
  }

  // DISTINCT or GROUP BY might be slow
  if (sqlUpper.includes('DISTINCT') || sqlUpper.includes('GROUP BY')) {
    if (planLower.includes('scan')) {
      suggestions.push('DISTINCT/GROUP BY with full scan may be slow. Consider indexes.');
    }
  }

  // ORDER BY without index
  if (sqlUpper.includes('ORDER BY') && !planLower.includes('index')) {
    suggestions.push('ORDER BY may use filesort. An index on sorted columns will speed it up.');
  }

  // Subquery detected
  if (sqlUpper.includes('SELECT') && sqlUpper.indexOf('SELECT') !== sqlUpper.lastIndexOf('SELECT')) {
    suggestions.push('Subquery detected. Consider using JOIN for potential performance improvement.');
  }

  // LIKE with leading wildcard
  if (sqlUpper.match(/LIKE\s+['"]%/)) {
    suggestions.push('LIKE with leading wildcard % does not use indexes. Try full-text search instead.');
  }

  return suggestions;
}

export async function POST(request: NextRequest) {
  try {
    const session = await auth();
    if (!session?.user?.id) {
      return NextResponse.json({ success: false, error: 'Unauthorized' }, { status: 401 });
    }

    // Rate limit: 15 explain requests per minute per client
    const clientId = getClientIdentifier(request, session.user.id);
    const limitResult = await rateLimit(`explain:${clientId}`, { max: 15, windowMs: RATE_LIMIT_WINDOWS.oneMinute });
    if (!limitResult.success) {
      return NextResponse.json({ success: false, error: 'Too many requests. Please wait' }, { status: 429 });
    }

    const parsed = await parseAndValidate(request, sqlExplainSchema);
    if ('response' in parsed) return parsed.response;

    const { sql, dbType, taskId } = parsed.data;

    // Validate SQL safety — same rules as the execute endpoint
    const blockReason = validateTrainingSql(sql);
    if (blockReason) {
      return NextResponse.json({ success: false, error: blockReason }, { status: 403 });
    }

    const task = getTaskById(taskId);
    if (!task) {
      return NextResponse.json({ success: false, error: 'Task not found' }, { status: 404 });
    }

    const effectiveDbType = VALID_DB_TYPES.includes(dbType as (typeof VALID_DB_TYPES)[number]) ? dbType : 'sqlite';
    const result = explainQuery(sql, task.schema, effectiveDbType);

    if (result.success && result.plan) {
      const suggestions = analyzePlan(result.plan, sql);
      return NextResponse.json({ ...result, suggestions });
    }

    return NextResponse.json(result);
  } catch (err: unknown) {
    return apiServerError('SQL explain', undefined, err);
  }
}