SQL to JSON Converter

SQL to JSON for fixtures and mock data

Seed files and dumps describe data as SQL, but front-end code, API mocks and many test tools want JSON. This sql to json converter reads INSERT INTO statements and returns each inserted row as a JSON object, keyed by column name. Typical uses are turning a database seed into a mock API response, sharing a few rows of production-like data with a front-end developer, or checking what a dump actually contains without restoring it.

SQL literals map naturally to JSON: numbers stay numbers, TRUE and FALSE become booleans, NULL becomes null, and quoted text becomes strings.

Example: a mysqldump excerpt with two tables

-- MySQL dump 10.13
/*!40101 SET NAMES utf8mb4 */;
CREATE TABLE `shop`.`orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer` varchar(80) DEFAULT NULL,
  `total` decimal(8,2) NOT NULL,
  `paid` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `orders` VALUES (1,'Ava',19.90,1),(2,'Liam O\'Neil',5.00,0);
INSERT INTO tags (id, label) VALUES (1, N'café'), (2, NULL);

produces an object with one array per table, in the order the tables first appear:

{
  "orders": [
    { "id": 1, "customer": "Ava", "total": 19.9, "paid": 1 },
    { "id": 2, "customer": "Liam O'Neil", "total": 5, "paid": 0 }
  ],
  "tags": [
    { "id": 1, "label": "café" },
    { "id": 2, "label": null }
  ]
}

(Objects are shown on single lines here for brevity; the tool indents them fully.) The INSERT for orders has no column list, so the names come from the CREATE TABLE; the shop. database prefix and the backticks are removed. The backslash-escaped apostrophe is decoded, and the MySQL version comment is ignored. When the input contains only one table, the output is simply the array of rows.

Type mapping

Column types in CREATE TABLE are not used for conversion. A MySQL tinyint(1) flag therefore stays 1 or 0 rather than becoming a boolean, and a DECIMAL value loses trailing zeros.

Edge cases

• Large integers. Numbers are parsed as JavaScript doubles, so integers with more than 15 or 16 digits lose precision: 12345678901234567890 becomes 12345678901234567000. Quote such IDs in the SQL if you need them exact.
• Missing or extra values. A tuple shorter than the column list fills the rest with null; extra values get keys like column3.
• Array and row constructors such as PostgreSQL's ARRAY['a','b'] are not parsed reliably. Use the text form '{a,b}' or remove those columns before converting.
• Malformed VALUES lists stop the conversion with a message showing the text near the problem, for example a tuple without parentheses.

From seed file to mock API

A common workflow is to take the seed script a back-end team already maintains and serve the same rows from a mock server or a static JSON file while the real endpoint is being built. Paste only the INSERT statements for the resource you need, convert, and save the array as the response body. Because the rows come from the same seed, front-end and back-end work from identical sample data, including edge cases such as NULL fields or names with apostrophes.

Inserts for one table spread across several statements are collected into one array in their original order, so a seed that adds rows in batches still yields a single list. If the mock needs a wrapper such as {"data": [...]}, add it by hand after converting.

Options and next steps

Indent sets 2, 3 or 4 spaces or a tab, and Sort keys orders keys alphabetically at every level, which makes fixtures easier to diff. To explore a large result, open it in the JSON viewer; to generate interfaces for it, use JSON to TypeScript. The opposite conversion is JSON to SQL.

Privacy

Parsing happens in your browser, so dumps with personal data are not uploaded. Load URL and Save & Share are the only features that involve our server.

Convert SQL to JSON in Python

Run the query with the built-in sqlite3 module and serialise the rows with json.dumps. By default each row is a plain tuple without column names, so dumping the result gives arrays of values instead of objects. Setting row_factory = sqlite3.Row makes every row convertible with dict(row), keyed by column name. SQL NULL becomes None in Python and null in the JSON.

import json
import sqlite3

sql = """
CREATE TABLE users (id INTEGER, name TEXT, city TEXT);
INSERT INTO users VALUES (1, 'Ann', 'Paris'), (2, 'Bob, Jr.', NULL);
"""
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row        # rows that know their column names
con.executescript(sql)

rows = [dict(row) for row in con.execute("SELECT * FROM users")]
print(json.dumps(rows, indent=2))

To convert the INSERT statements of a dump without running them, paste the SQL into the converter above.