Code generators · 5 min read
JSON to SQL: from a payload to a table you can query
Sometimes the quickest route from a JSON file to a SQL query is to build a table from it. Converting JSON to SQL produces the schema and the rows in one step, which is ideal for fixtures, scratch analysis and seeding a local database — provided you keep it out of your migration history.
Updated
How types map from JSON to SQL
- Strings → `TEXT` (`VARCHAR(n)` when you know the length budget).
- Whole numbers → `INTEGER` / `BIGINT`, decimals → `NUMERIC` rather than floating point, especially for money.
- Booleans → `BOOLEAN`.
- Nested objects and arrays → `JSONB` in PostgreSQL, `JSON` in MySQL 8 — or normalise them into child tables when the data is here to stay.
- Missing keys → nullable columns, never a default that fakes a value.
A generated table is a great starting point and a terrible migration. Write the schema you want, then use the generated SQL as a reference for column names and types.
Escaping is not optional
Strings in JSON routinely contain apostrophes — `O’Brien`, `it's done` — and pasted into SQL by hand they end the string literal early and break the statement. Generated INSERT statements double every quote correctly, which is the single most useful thing the conversion does for you. For bulk inserts, wrap rows in one statement so the database can batch the write instead of committing per line.
Good uses, bad uses
Fixture data for tests, a local scratch copy of an API export, seeding a demo environment and answering a one-off question in SQL all benefit from a JSON to SQL conversion. Production schema management does not: migrations need review, indexes, constraints and a rollback path. Generate the table, then hand-write the migration that ships.
Arrays and objects: flatten or store?
If an array is a fixed list of scalars, a child table with a foreign key keeps queries simple. If it is a genuinely dynamic payload — webhook bodies, third-party metadata — storing it as `JSONB` and indexing the paths you query is the pragmatic choice. Decide by how you will query it: anything you filter or join on belongs in a column.
Run it in the browser
Database exports and API dumps are exactly the payloads that should not be uploaded to an unknown service. The JSON to SQL conversion here happens entirely in the page, so the statement you copy out is built from data that never left your machine, and the tool still works offline.
JSON to SQL — frequently asked questions
Which SQL dialect should I generate?
Target the database you will actually run it against — PostgreSQL for `JSONB`, MySQL 8 for `JSON` and backticked identifiers, SQLite for lightweight local work. Quoting rules and type names differ between them, so regenerating per dialect beats adapting by hand.
Can I insert JSON arrays into a single SQL column?
Yes, in databases with a JSON column type: the array becomes `JSONB` or `JSON` and you query into it with path operators. If you need to join or aggregate the elements, a child table is the better model.
Is generated SQL safe from injection?
Generated statements are for you to run where you choose, and string values are escaped as literals. They are not a substitute for parameterised queries in application code — never build queries by concatenation in a server.
Tools mentioned in this post
Keep reading
6 min read
JSON to TypeScript: types that match reality
Convert JSON to TypeScript interfaces from a real API response — optional keys, null unions, nested types — and keep generated types from drifting.
5 min read
JSON to Go: structs that unmarshal on the first try
Convert JSON to Go structs with correct field names and json tags, nested types included — plus the numeric, null and time traps to watch.
5 min read
CSV to JSON: from a spreadsheet to structured data
Convert CSV to JSON in the browser — header rows, delimiters, quoting rules and type handling explained, with the spreadsheet exports that break parsers.
- Longer read: JSON to TypeScript: Types That Tell the Truth