JSON to SQL INSERT: generate statements without writing a mapper

You have a JSON array of records and you need them in a database. The obvious path is writing an INSERT for each row by hand, or wiring up a mapper library. For a one-off migration or a quick seed, both feel like too much work for something that should be mechanical. Generating INSERT statements from JSON is mechanical — but “mechanical” hides a handful of traps that produce silently-wrong SQL.

The basic shape

Given this:

[ { "id": 1, "name": "Ada", "active": true }, { "id": 2, "name": "Grace", "active": false } ]

the generated SQL is the obvious thing:

INSERT INTO table (id, name, active) VALUES
(1, 'Ada', true),
(2, 'Grace', false);

Two decisions happen implicitly here, and both are where bugs live: how to map JSON types to SQL literals, and how to escape string values.

The traps

1. null vs the string “null”. JSON null should become SQL NULL. But a JSON string that literally contains null — "name": "null" — must become the string 'null', not NULL. A converter that blurs this will silently write WHERE name = NULL bugs into your data. The rule: JSON type decides, not the text.

2. Booleans. JSON has true/false. SQL dialects differ: PostgreSQL uses true/false, MySQL uses 1/0 (though TRUE/FALSE work), older SQL Server uses 1/0. A converter needs to know the target dialect, or you’ll generate SQL that’s valid in one database and fails in another.

3. String escaping. A name like O'Brien becomes 'O''Brien' — single quotes must be doubled, not backslash-escaped, in standard SQL (backslash escaping is a MySQL extension). Strings containing newlines, backslashes, or quotes are exactly where hand-written mappers break and generated SQL stays correct.

4. Numbers. JSON 123 → SQL 123 is fine, but large integers, floats, and scientific notation (1e10) need care. And a number stored as a string "123" must stay a quoted string, or you’ll change the type on the way in.

5. Object keys as columns. Nested objects need a decision: flatten to user_name, serialize to a JSON column, or skip. A one-size-fits-all mapper guesses here — and guesses wrong for your schema.

How to generate safely

• Pick a dialect first — at minimum, standard, PostgreSQL, or MySQL — because booleans and escaping rules differ.
• Feed it JSON that’s already clean — validate first, then convert.
• Escape strings properly — doubling quotes, never trusting raw input.
• Use NULL for JSON null, and quote everything that’s actually a string.

A JSON to SQL converter that takes a table name, targets a dialect, and produces a single multi-row INSERT (or individual statements) handles all of this consistently — which is exactly the thing that goes wrong when you do it by hand for the hundredth row.

The security question

Generated INSERTs for your database are a storage concern, but the same escaping rules are what protect you from SQL injection in general. A converter that produces properly escaped literals is demonstrating the correct discipline; a converter that dumps raw values is a code smell. And since a client-side tool runs in your browser, neither the JSON nor the generated SQL leaves your machine — which matters when the payload is a real customer table.

The short version

Generating INSERTs from JSON is a solved problem — the pitfalls are the type mapping (null vs "null"), the dialect, and string escaping. Get those right once, and you’ve written the last hand-rolled mapper you’ll need.