JSON to SQL INSERT — Ruby source

# json-to-sql — pure JSON → SQL INSERT converter. Ruby port (canonical TS:
# src/lib/jsonToSql.ts; Go twin: cli/json-to-sql). Mirrors the JS/PHP ports:
# stdlib JSON in, one multi-row INSERT out; heterogeneous rows unify via the
# first-seen column union. Errors return through the Result hash — never
# raised (matches the TS contract).
require 'json'

module JsonToSql
  module_function

  # Escape for a single-quoted SQL literal: '' doubling everywhere; MySQL mode
  # additionally escapes the backslash and statement-terminating control bytes.
  # Block-form gsub keeps replacements literal (no '\0' whole-match pitfalls).
  def escape_sql_string(s, dialect = 'standard')
    out = s.gsub("'", "''")
    return out unless dialect == 'mysql'
    out.gsub('\\') { '\\\\' }.gsub("\0") { '\0' }.gsub("\n") { '\n' }
       .gsub("\r") { '\r' }.gsub("\x1a") { '\Z' }
  end

  # Render a decoded JSON value as a SQL literal.
  def sql_literal(v, dialect)
    return 'NULL' if v.nil?
    return dialect == 'mysql' ? (v ? '1' : '0') : (v ? 'TRUE' : 'FALSE') if v == true || v == false
    return v.to_s if v.is_a?(Integer)
    # Float: integral values drop the ".0" so literals match JS String(n).
    return v.finite? ? (v == v.truncate ? v.truncate.to_s : v.to_s) : 'NULL' if v.is_a?(Float)
    return "'#{escape_sql_string(v, dialect)}'" if v.is_a?(String)
    "'#{escape_sql_string(JSON.generate(v), dialect)}'" # object/array → JSON text
  end

  def quote_ident(name, dialect, quote)
    return name unless quote
    dialect == 'mysql' ? "`#{name}`" : "\"#{name}\""
  end

  # Reduce to [A-Za-z0-9_], defaulting to "tbl" when nothing usable remains.
  def sanitize_ident(name)
    cleaned = name.gsub(/[^A-Za-z0-9_]/, '_')
    cleaned.empty? ? 'tbl' : cleaned
  end

  # Convert a JSON document into a single multi-row INSERT. A bare object is
  # one row; an array is many. Missing values are emitted as NULL.
  def json_to_insert(json, table:, dialect: 'standard', quote_identifiers: true)
    data = JSON.parse(json)
    rows = data.is_a?(Array) ? data : [data]
    return { ok: false, sql: '', rows: 0, error: 'No rows to insert.' } if rows.empty?
    return { ok: false, sql: '', rows: 0, error: 'Rows must be objects.' } unless rows.all?(Hash)

    tbl = quote_ident(sanitize_ident(table), dialect, quote_identifiers)
    cols = rows.flat_map(&:keys).uniq # first-seen union; Hash preserves order
    col_list = cols.map { |c| quote_ident(c, dialect, quote_identifiers) }.join(', ')
    values = rows.map { |row| "  (#{cols.map { |c| sql_literal(row[c], dialect) }.join(', ')})" }
    { ok: true, sql: "INSERT INTO #{tbl} (#{col_list}) VALUES\n#{values.join(",\n")};",
      rows: rows.length, error: nil }
  rescue JSON::ParserError => e
    { ok: false, sql: '', rows: 0, error: e.message }
  end
end

if __FILE__ == $PROGRAM_NAME
  r = JsonToSql.json_to_insert(
    '[{"id":1,"name":"O\'Hara"},{"id":2,"age":30.5,"tags":["a","b"]}]', table: 'users'
  )
  puts r[:sql] # INSERT INTO "users" ("id", "name", "age", "tags") VALUES ...
end