JSON to SQL INSERT Generator
Last updated:
Jsonic's JSON to SQL INSERT generator converts a JSON array of objects into multi-row SQL INSERT statements. Each JSON object becomes one row. String values are single-quoted with internal quotes escaped by doubling. Numbers stay as literals. Booleans become TRUE or FALSE. Null becomes NULL. Nested objects and arrays are serialized as JSON strings. A commented-out CREATE TABLE schema is generated above the INSERT, inferring column types from the first object. The output works with MySQL, PostgreSQL, and SQLite. All processing runs in your browser with no data upload.
How to convert JSON to SQL INSERT statements
- Paste a JSON array of objects (or a single JSON object) into the left panel, or click Example to load a sample.
- Optionally change the table name (default: my_table).
- Click Generate.
- Copy the output and run it in your database client (psql, MySQL Workbench, sqlite3, etc.).
- Remove the -- comment markers from the CREATE TABLE block if you need to create the table first.
FAQ
What JSON shape does the tool expect?
The tool expects a JSON array of objects, where each object becomes one row in the INSERT statement. It also accepts a single JSON object, which produces a one-row INSERT. All objects should share the same keys — columns are taken from the first object in the array, and missing keys in later rows produce NULL.
How are SQL values escaped?
Strings are single-quoted, with internal single quotes escaped by doubling (so it's becomes 'it''s'). Numbers are written as literals without quotes. Booleans become TRUE or FALSE. JSON null becomes SQL NULL. Nested objects and arrays are serialized to a JSON string and single-quoted.
What databases does the output work with?
The generated multi-row INSERT VALUES syntax is supported by MySQL 5.7+, PostgreSQL 9.4+, and SQLite 3.x. PostgreSQL uses TRUE/FALSE for booleans natively; MySQL also supports 1/0 as boolean literals. The JSON column type (for nested objects) is available in MySQL 5.7+, PostgreSQL (as JSONB), and SQLite 3.38+ with the json extension.
How do I import JSON data into PostgreSQL?
For small datasets, paste the generated INSERT directly into psql or pgAdmin. For large datasets, the COPY command is significantly faster than INSERT — export your JSON to CSV first using jq, then use COPY table FROM file.csv CSV HEADER. For JSON columns specifically, use the JSONB type and cast with ::jsonb.
How do I handle large JSON files?
Most databases have statement-length or parameter-count limits. For thousands of rows, split the JSON array into batches of 500–1000 rows and generate a separate INSERT per batch. MySQL supports LOAD DATA INFILE for CSV imports. PostgreSQL COPY is the fastest bulk-load path. SQLite handles large INSERTs best inside a transaction (wrap with BEGIN; … COMMIT;).
How do I avoid SQL injection with user data?
The generated INSERT is for data migration and seeding workflows only — it is not safe to use as a template for dynamic queries in application code. In application code, always use parameterized queries (prepared statements) and never concatenate user input into SQL strings. The escaping here is for static, trusted data only.
Can I use JSON directly in SQL without converting?
Yes. MySQL has a JSON column type with JSON_EXTRACT() and the ->> operator. PostgreSQL has JSONB with rich operators like ->, ->>, @>, and the jsonb_each() function. SQLite 3.38+ includes json_extract() and related functions. Use native JSON columns when your schema is flexible or when querying nested fields directly in SQL.
How do I convert the CREATE TABLE comment into a real schema?
Remove the -- comment markers at the start of each line and run the resulting CREATE TABLE statement in your database client. Then adjust the inferred types as needed — for example, change TEXT to VARCHAR(255) for known-length strings, INTEGER to SERIAL or BIGINT for auto-incrementing or large IDs, and add PRIMARY KEY, NOT NULL, UNIQUE, and INDEX constraints to match your data model.