SQL Dump to JSON: Parsing, Redaction and Batching

1. Problem: dump values are not simple comma-separated text

INSERT statements contain embedded commas, escaped quotes, NULLs, decimals, and multiple VALUE tuples. Naive string splitting breaks records.

2. Minimal reproduction: obtain parsed rows first

The type below represents a parser that completes lexical and dialect handling.

type ParsedRow = {
  table: string;
  columns: string[];
  values: unknown[];
};

const parsed: ParsedRow[] = [
  { table: "users", columns: ["id", "email", "active"], values: [7, "ada@example.test", true] },
  { table: "users", columns: ["id", "email", "active"], values: [8, null, false] },
];

console.log(parsed[0].values);

3. Root cause: SQL parser and mock generation are distinct phases

Parsers resolve dialects, quotes, comments, and VALUE tuples. Mock generators align columns, apply policies, and format output. Mixing both into a single regex makes errors untraceable.

4. Recommendation: apply field policies after parsing

Convert each row into column-value objects, then apply field-level policies. Unknown tables or columns report as pending instead of silent deletion.

5. Complete code: redact and produce batch JSON

This code processes parsed rows, masking emails with fixed rules. Batch sizes are controlled via parameters.

type ParsedRow = { table: string; columns: string[]; values: unknown[] };
const parsed: ParsedRow[] = [
  { table: "users", columns: ["id", "email", "active"], values: [7, "ada@example.test", true] },
  { table: "users", columns: ["id", "email", "active"], values: [8, null, false] },
];
type Redactor = (column: string, value: unknown) => unknown;

function redactEmail(column: string, value: unknown): unknown {
  if (column === "email" && typeof value === "string") return "redacted@example.test";
  return value;
}

function rowsToMock(rows: ParsedRow[], limit: number, redact: Redactor) {
  if (!Number.isInteger(limit) || limit < 1) throw new RangeError("limit must be positive integer");
  const count = Math.min(rows.length, limit);
  const result: Array<Record<string, unknown>> = [];
  for (let i = 0; i < count; i++) {
    const row = rows[i];
    const item: Record<string, unknown> = {};
    for (let c = 0; c < row.columns.length; c++) {
      const name = row.columns[c];
      item[name] = redact(name, row.values[c]);
    }
    result.push(item);
  }
  return result;
}

console.log(rowsToMock(parsed, 2, redactEmail));

6. Incorrect approaches

Assuming regex parsing handles complex INSERT statements. Converting binary data into strings causing corruption.

7. Boundaries: NULLs, binary, and long text

NULL values must not implicitly become empty strings in JSON. BLOB/CLOB columns should use Base64 or Hex.

8. Verification

Compare SQL row counts with JSON array lengths. Assert primary-key uniqueness, foreign-key references, null ratios, type ranges, and non-recoverability of sensitive source values.

9. FAQ

How should SQL DATE types be converted? Convert to ISO 8601 UTC strings.

Can tens of thousands of INSERTs be batched? Yes, using stream chunking.

How are AUTO_INCREMENT primary keys handled? Retain primary keys in mock data to preserve relations.

10. Summary

SQL dump to JSON mock is a multi-phase process. Isolate parser, redaction, and batching to produce safe, clean test datasets.