JSON to SQL INSERT — C# source

// json-to-sql — pure JSON → SQL INSERT converter. C# port (canonical TS:
// src/lib/jsonToSql.ts; Go twin: cli/json-to-sql). Mirrors the JS/PHP ports:
// System.Text.Json preserves first-seen property order and Number raw text,
// so column unions and literals match the TS reference. Errors return through
// the Result record — never thrown.
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Encodings.Web;
using System.Text.Json;

public static class JsonToSql
{
    public sealed record Options(string Table, string Dialect = "standard", bool QuoteIdentifiers = true);
    public sealed record Result(bool Ok, string Sql, int Rows, string? Error);
    // Escape for a single-quoted SQL literal: '' doubling everywhere; MySQL
    // mode additionally escapes the backslash and control bytes that end input.
    static string EscapeSqlString(string s, string d) {
        var o = s.Replace("'", "''");
        if (d == "mysql") o = o.Replace("\\", "\\\\").Replace("\0", "\\0")
            .Replace("\n", "\\n").Replace("\r", "\\r").Replace("\x1a", "\\Z");
        return o;
    }
    // Render a decoded JSON value as a SQL literal.
    static string SqlLiteral(JsonElement v, string d) => v.ValueKind switch {
        JsonValueKind.Null => "NULL",
        JsonValueKind.True => d == "mysql" ? "1" : "TRUE",
        JsonValueKind.False => d == "mysql" ? "0" : "FALSE",
        JsonValueKind.Number => v.GetRawText(), // source token, String(n)-safe
        JsonValueKind.String => $"'{EscapeSqlString(v.GetString()!, d)}'",
        _ => $"'{EscapeSqlString(Compact(v), d)}'", // object/array → JSON text
    };
    // Compact JSON re-emission matching JSON.stringify: no whitespace, raw
    // non-ASCII and '/' (UnsafeRelaxedJsonEscaping is the JSON_UNESCAPED_* analogue).
    static string Compact(JsonElement v) {
        using var ms = new MemoryStream();
        using (var w = new Utf8JsonWriter(ms, new JsonWriterOptions { Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping }))
            v.WriteTo(w);
        return Encoding.UTF8.GetString(ms.ToArray());
    }
    // Quote per dialect, or leave bare when quoting is disabled. Sanitize first:
    // [^A-Za-z0-9_] -> '_', defaulting to "tbl" when nothing usable remains.
    static string QuoteIdent(string n, string d, bool q) => !q ? n : d == "mysql" ? $"`{n}`" : $"\"{n}\"";
    static string SanitizeIdent(string name) {
        var cleaned = new string(Array.ConvertAll(name.ToCharArray(),
            c => c is (>= 'a' and <= 'z') or (>= 'A' and <= 'Z') or (>= '0' and <= '9') or '_' ? c : '_'));
        return cleaned.Length == 0 ? "tbl" : cleaned;
    }
    // Convert a JSON document into a single multi-row INSERT. A bare object is
    // one row; an array is many. Heterogeneous rows unify via first-seen columns.
    public static Result JsonToInsert(string json, Options opts) {
        JsonDocument doc;
        try { doc = JsonDocument.Parse(json); } catch (JsonException e) { return new Result(false, "", 0, e.Message); }
        using (doc) {
            var root = doc.RootElement;
            var rows = root.ValueKind == JsonValueKind.Array ? root.EnumerateArray().ToList()
                     : new List<JsonElement> { root };
            if (rows.Count == 0) return new Result(false, "", 0, "No rows to insert.");
            if (rows.Any(r => r.ValueKind != JsonValueKind.Object)) return new Result(false, "", 0, "Rows must be objects.");
            var d = opts.Dialect; var q = opts.QuoteIdentifiers;
            var table = QuoteIdent(SanitizeIdent(opts.Table), d, q);
            var cols = new List<string>(); // first-seen union of every row's keys
            foreach (var r in rows) foreach (var p in r.EnumerateObject()) if (!cols.Contains(p.Name)) cols.Add(p.Name);
            var colList = string.Join(", ", cols.Select(c => QuoteIdent(c, d, q)));
            var lines = rows.Select(r => $"  ({string.Join(", ", cols.Select(c => r.TryGetProperty(c, out var v) ? SqlLiteral(v, d) : "NULL"))})");
            return new Result(true, $"INSERT INTO {table} ({colList}) VALUES\n{string.Join(",\n", lines)};", rows.Count, null);
        }
    }
    public static void Main() {
        var r = JsonToInsert("[{\"id\":1,\"name\":\"O'Hara\"},{\"id\":2,\"age\":30.5,\"tags\":[\"a\",\"b\"]}]", new Options("users"));
        Console.WriteLine(r.Sql); // INSERT INTO "users" ("id", "name", "age", "tags") VALUES ...
    }
}