How to Convert JSON to SQL INSERT Statements

You have a JSON export and need it in a database: seed data, a migration, test fixtures for staging. Pasting values by hand doesn't scale, and most online converters want you to upload data you'd rather keep local. Here's the terminal-first path.

Option 1: The Transmute CLI (free)

Given users.json:

[
  { "name": "Alice", "age": 32 },
  { "name": "Bob",   "age": 25 }
]

$ transmute users.json --output sql --table users
-- Generated by Transmute
INSERT INTO "users" ("name", "age") VALUES
  ('Alice', 32),
  ('Bob', 25);

The same works for CSV and YAML input — pick the format flag that matches your source:

$ transmute people.csv --output sql --table people
$ transmute config.yaml --format yaml --output sql

Shaping rows before they hit the database

Because transformation happens before serialization, you filter, sort and reshape in the same command. Only adults, names only:

$ transmute users.json     --pipe '[{"op":"filter","expr":"item.age > 26"},{"op":"pick","fields":["name"]}]'     --output sql --table adults
-- Generated by Transmute
INSERT INTO "adults" ("name") VALUES
  ('Alice');

This is where a converter beats a one-off script: the same pipeline that cleans the data also decides what gets inserted.

Escaping and types (the part that breaks imports)

• Quotes are escaped correctly. O'Brien becomes 'O''Brien' — the standard SQL doubling, valid in PostgreSQL, MySQL and SQLite. A string-concatenation approach produces broken statements on exactly this input.
• Numbers stay numbers. Numeric-looking strings are emitted unquoted so they insert as real numeric columns; everything else is quoted.
• Nulls are real NULLs. JSON null, missing fields and empty strings all become NULL — not the string "null".
• Booleans become TRUE/FALSE, which PostgreSQL accepts natively; MySQL maps them to its TINYINT convention.

$ transmute mixed.json --output sql
-- Generated by Transmute
INSERT INTO "my_table" ("a") VALUES
  (1),
  (NULL);

Option 2: Python

import json

rows = json.load(open("users.json"))
cols = list(rows[0])
for row in rows:
    vals = ", ".join(
        "NULL" if row.get(c) is None
        else str(row[c]) if isinstance(row.get(c), (int, float))
        else "'" + str(row[c]).replace("'", "''") + "'"
        for c in cols
    )
    print(f'INSERT INTO users ({", ".join(cols)}) VALUES ({vals});')

Zero dependencies — but note what you're signing up for: column inference from the first row (later rows with extra keys silently drop data), manual type handling, and no batching. Fine for ten rows; error-prone for ten thousand.

Why not paste it into an online converter?

Exports headed for a database often contain customer data, tokens or internal IDs. Uploading them to a random website to get SQL back means trusting a third party with exactly the data you were about to put somewhere permanent. A CLI that runs locally has nowhere to send it.

Loading bigger files

A few thousand INSERT lines load fine through any client (psql -f seed.sql). Past tens of thousands of rows, wrap the output in a single transaction so a failure rolls back cleanly instead of leaving half a table:

$ { echo 'BEGIN;'; transmute big.json --output sql; echo 'COMMIT;'; } > seed.sql
$ psql mydb -f seed.sql

Gotchas

• Column set comes from the union of keys across rows — consistent records convert cleanly, ragged ones get NULLs where fields are missing.
• Dates stay strings — quote style is correct, but cast to your column type on load if the target isn't text.
• Reserved words as column names (order, group) are quoted with double quotes, which works in PostgreSQL/SQLite; MySQL needs ANSI_QUOTES mode or backticks.