How to Convert JSON to SQL INSERT Statements

Published 2026-09-072 min readCopy link

Migrating seed data, importing an API export into Postgres, loading fixtures — the job is always the same: take a JSON array of objects and turn it into INSERT statements your database will accept without a fight.

Method 1: CLI

Using transmute

$ cat users.json | qf --to sql --table users
INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]');
INSERT INTO users (id, name, email) VALUES (2, 'Bob', '[email protected]');

Column names come from the first object's keys, types are preserved (numbers stay unquoted, strings get proper escaping), and --table names the target table. Pipe it straight into your database:

$ cat users.json | qf --to sql --table users | psql mydb

Using jq

$ jq -r '.[] | "INSERT INTO users VALUES (\(.id), \(.name), \(.email));"' users.json

This one-liner works only while your data stays simple. The moment a name contains an apostrophe (O'Brien) the generated SQL breaks — jq doesn't know SQL quoting rules. If any field is user-entered text, use a tool that escapes properly.

Method 2: Python

import json, sqlite3
rows = json.load(open("users.json"))
db = sqlite3.connect("app.db")
db.executemany(
    "INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
    [(r["id"], r["name"], r["email"]) for r in rows],
)
db.commit()

The parameterized-queries route is the safest if you're inserting into SQLite directly — no string building at all. It only works when you control the database connection, though; if you need a .sql file to hand off, generate statements instead.

Gotchas That Corrupt Data

• Quoting: a single apostrophe in O'Brien breaks naive string concatenation. Correct generators double the quote ('') per the SQL standard.
• NULL vs missing: a missing key should usually become NULL, not the string "null" or an empty column list mismatch.
• Dates: JSON has no date type — ISO strings pass through fine, but make sure the target column isn't typed in a way that rejects them.
• Bulk inserts: thousands of individual statements are slow to replay. Wrap them in a transaction, or use your database's native bulk format (Postgres COPY) for big loads.

• Scripts & handoffs: Transmute CLI — JSON in, escaped SQL out.
• Direct import: Python with parameterized queries.
• Quick checks: free web converter handles JSON ⇄ CSV ⇄ YAML locally in the browser.