detect_schema

Instructions

Implementation Reference

• packages/mcp/src/tools.ts:247-274 (handler)

  Main handler function for the 'detect_schema' tool. Routes to either detectFromSql or detectFromJson based on input args.

  async function handleDetectSchema(
    args: Record<string, unknown>
  ): Promise<ToolResult> {
    const { sql, sample_json } = args;
  
    if (!sql && !sample_json) {
      return err(
        "Either 'sql' (SQL CREATE TABLE statements) or 'sample_json' (JSON sample data) is required."
      );
    }
  
    if (sql && typeof sql === "string") {
      const schema = detectFromSql(sql);
      if (schema.tables.length === 0) {
        return err(
          "Could not detect any tables from the provided SQL. Make sure it contains valid CREATE TABLE statements."
        );
      }
      return ok(schema);
    }
  
    if (sample_json) {
      const schema = detectFromJson(sample_json);
      return ok(schema);
    }
  
    return err("Invalid input. Provide 'sql' as a string or 'sample_json' as an object.");
  }

• src/lib/engine/schema-detector.ts:43-105 (handler)

  Parses SQL CREATE TABLE statements into MockHero TableDefinition format. Handles inline REFERENCES and CHECK IN constraints.

  export function detectFromSql(sql: string): { tables: TableDefinition[] } {
    const tables: TableDefinition[] = [];
  
    // Match CREATE TABLE statements
    const tableRegex = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:`|")?(\w+)(?:`|")?\s*\(([\s\S]*?)(?:\);|\)\s*;)/gi;
    let match;
  
    while ((match = tableRegex.exec(sql)) !== null) {
      const tableName = match[1].toLowerCase();
      const columnsBlock = match[2];
      const fields: FieldDefinition[] = [];
  
      // Parse individual columns (skip constraints)
      // Split on commas that are NOT inside parentheses — prevents breaking
      // types like DECIMAL(10,2), NUMERIC(12,4), VARCHAR(255), etc.
      const lines = splitColumnsBlock(columnsBlock);
  
      for (const line of lines) {
        if (/^\s*(PRIMARY\s+KEY|FOREIGN\s+KEY|UNIQUE|CHECK|CONSTRAINT|INDEX)/i.test(line)) {
          continue;
        }
  
        const colMatch = line.match(/^(?:`|")?(\w+)(?:`|")?\s+(\w+(?:\([^)]*\))?)/i);
        if (!colMatch) continue;
  
        const colName = colMatch[1].toLowerCase();
        const sqlType = colMatch[2].toUpperCase();
  
        const mapped = mapSqlType(colName, sqlType);
        const field: FieldDefinition = {
          name: colName,
          type: mapped.type,
          ...(mapped.params ? { params: mapped.params } : {}),
        };
  
        // Check for inline REFERENCES (foreign key)
        const refMatch = line.match(/REFERENCES\s+(?:`|")?(\w+)(?:`|")?\s*\((?:`|")?(\w+)(?:`|")?\)/i);
        if (refMatch) {
          field.type = "ref";
          field.params = { table: refMatch[1].toLowerCase(), field: refMatch[2].toLowerCase() };
        }
  
        // Check for CHECK IN constraint (enum detection)
        const checkMatch = line.match(/CHECK\s*\(\s*\w+\s+IN\s*\((.*?)\)\s*\)/i);
        if (checkMatch) {
          field.type = "enum";
          field.params = {
            values: checkMatch[1].split(",").map((v) => v.trim().replace(/'/g, "")),
          };
        }
  
        fields.push(field);
      }
  
      tables.push({
        name: tableName,
        count: 50,
        fields,
      });
    }
  
    return { tables };
  }

• src/lib/engine/schema-detector.ts:171-190 (handler)

  Infers MockHero field definitions from a sample JSON record. Uses key names and value types to detect field types.

  export function detectFromJson(sample: Record<string, unknown>): { tables: [{ name: string; count: number; fields: FieldDefinition[] }] } {
    const fields: FieldDefinition[] = [];
  
    for (const [key, value] of Object.entries(sample)) {
      const inferred = inferTypeFromValue(key, value);
      fields.push({
        name: key,
        type: inferred.type,
        ...(inferred.params ? { params: inferred.params } : {}),
      });
    }
  
    return {
      tables: [{
        name: "records",
        count: 50,
        fields,
      }],
    };
  }

• src/app/api/v1/schema/detect/route.ts:17-67 (handler)

  API route handler for POST /api/v1/schema/detect. Validates API key, enforces Pro+ tier gating, and delegates to detectFromSql/detectFromJson.

  export async function POST(request: Request) {
    try {
      // 1. Validate API key
      const user = await validateApiKey(request);
      if (!user) {
        return unauthorizedError();
      }
  
      // 2. Feature gate — Pro+ only
      if (user.tier === "free") {
        return forbiddenFeatureError("Schema detection", "Pro");
      }
  
      // 3. Parse request body
      let body: Record<string, unknown>;
      try {
        body = await request.json();
      } catch {
        return validationError("Invalid JSON in request body");
      }
  
      const { sql, sample_json } = body;
  
      if (!sql && !sample_json) {
        return validationError("Provide either 'sql' (CREATE TABLE statement) or 'sample_json' (example JSON record)");
      }
  
      // 4. Detect schema
      if (typeof sql === "string") {
        if (sql.length > 50_000) {
          return validationError("SQL input must be 50,000 characters or fewer");
        }
        const schema = detectFromSql(sql);
        return NextResponse.json({ schema });
      }
  
      if (sample_json && typeof sample_json === "object") {
        // Reject arrays — sample_json must be a plain object (single record)
        if (Array.isArray(sample_json)) {
          return validationError("'sample_json' must be a single JSON object (not an array). Pass one example record.");
        }
        const schema = detectFromJson(sample_json as Record<string, unknown>);
        return NextResponse.json({ schema });
      }
  
      return validationError("'sql' must be a string, 'sample_json' must be an object");
    } catch (err) {
      console.error("Schema detect error:", err);
      return internalError();
    }
  }

• packages/mcp/src/tools.ts:108-127 (schema)

  Input schema definition for the 'detect_schema' tool: accepts optional 'sql' (string) and 'sample_json' (object) parameters.

  {
    name: "detect_schema",
    description:
      "Convert a SQL CREATE TABLE statement or JSON sample into MockHero's schema format. Use this to quickly generate test data for an existing database.",
    inputSchema: {
      type: "object",
      properties: {
        sql: {
          type: "string",
          description:
            "One or more SQL CREATE TABLE statements to convert into MockHero schema format.",
        },
        sample_json: {
          type: "object",
          description:
            "A sample JSON object (or array of objects) to infer a schema from. The keys become field names, and values are used to detect types.",
        },
      },
    },
  },

• packages/mcp-server/src/index.ts:190-245 (registration)

  Registration of 'detect_schema' as an MCP server tool with zod validation and proxying to the API endpoint.

  server.tool(
    "detect_schema",
    `Convert an existing database schema or JSON sample into MockHero's schema format.
  
  Send a SQL CREATE TABLE statement and get back the structured schema ready to use
  with generate_test_data. Or send a sample JSON record and MockHero will infer
  the field types.
  
  This is useful when you have migration files or an existing database and want to
  generate test data that matches your schema without manually writing the definition.`,
    {
      sql: z
        .string()
        .optional()
        .describe("SQL CREATE TABLE statement(s) to convert"),
      sample_json: z
        .record(z.string(), z.unknown())
        .optional()
        .describe("Example JSON record to infer schema from"),
    },
    async (params) => {
      if (!params.sql && !params.sample_json) {
        return {
          content: [
            {
              type: "text" as const,
              text: "Error: provide either 'sql' (CREATE TABLE statements) or 'sample_json' (example record).",
            },
          ],
        };
      }
  
      const res = await apiCall("POST", "/api/v1/schema/detect", params);
  
      if (!res.ok) {
        return {
          content: [
            {
              type: "text" as const,
              text: `API error (${res.status}): ${JSON.stringify(res.data, null, 2)}`,
            },
          ],
          isError: true,
        };
      }
  
      return {
        content: [
          {
            type: "text" as const,
            text: JSON.stringify(res.data, null, 2),
          },
        ],
      };
    }
  );