SQL INSERT to JSON Converter

Convert SQL INSERT … VALUES statements into a JSON array of row objects, with numbers, NULLs and booleans typed correctly.

MySQL-style backslash escapes (\', \n, \\)

JSON indentation

Turn backslash escapes off for PostgreSQL, SQLite or SQL Server dumps, where a backslash inside a string is an ordinary character.

SQL INSERT statements

JSON

How to use this tool

Paste one or more INSERT INTO … VALUES statements — for example from mysqldump, a seed file or a migration — or click Sample.
Leave MySQL-style backslash escapes on for MySQL/MariaDB dumps (where \' is an escaped quote); turn it off for PostgreSQL, SQLite or SQL Server, where a backslash is an ordinary character.
Click Convert to JSON. With one table you get an array of row objects; with several tables you get an object keyed by table name.
Check the messages: skipped statements are listed with their number and line, and values that needed a judgement call (expressions, DEFAULT, very large integers) are listed as warnings.
Copy or download rows.json for fixtures, mocks or tests.

Example

Multi-row INSERT with NULL, DEFAULT and a function call

SQL

INSERT INTO products (id, name, price, tags, updated_at) VALUES
  (1, 'O''Reilly mug', 12.5, NULL, NOW()),
  (2, 'Sticker', -0.5e1, 'sale', DEFAULT);

JSON

[
  {
    "id": 1,
    "name": "O'Reilly mug",
    "price": 12.5,
    "tags": null,
    "updated_at": "NOW()"
  },
  {
    "id": 2,
    "name": "Sticker",
    "price": -5,
    "tags": "sale",
    "updated_at": null
  }
]

Common use cases

Turning a mysqldump excerpt into JSON fixtures for unit or end-to-end tests.
Creating mock API responses from seed data in a migration file.
Inspecting the rows in an INSERT-heavy SQL file without importing it into a database.
Moving small reference tables (countries, roles, settings) into a JSON config.
Feeding rows into the JSON to CSV converter for a spreadsheet.

Supported SQL and how values are converted

The converter tokenises your SQL and splits it into statements on semicolons outside strings and comments. Statements numbered in the report count only non-empty statements. Supported forms are INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] table [(columns)] VALUES (…), (…), REPLACE INTO … and SQLite's INSERT OR REPLACE / INSERT OR IGNORE. Table and column names may be bare, "double-quoted", ` backticked or [bracketed], and qualified names like public.users are kept with the dot. A trailing ON DUPLICATE KEY UPDATE, ON CONFLICT or RETURNING` clause is ignored with a warning. Rows from several statements into the same table are combined.

Values are typed as follows. Single-quoted strings become JSON strings, with '' decoded to a single quote; with backslash escapes on, \', \", \\, \n, \r, \t, \0, \b and \Z are decoded as MySQL does. N'…' and charset-prefixed strings are plain strings, and PostgreSQL E'…' strings always use backslash escapes. Unquoted numbers — integers, decimals, negatives and exponents like 1.2e3 — become JSON numbers, except integers above 2^53 − 1, which are kept as strings so no digits are lost. NULL becomes null and TRUE/FALSE become booleans; MySQL stores booleans as 0/1, which stay numbers.

Some values have no literal meaning outside the database. DEFAULT becomes null with a warning, because the real default lives in the table schema. Function calls and expressions such as NOW(), CURRENT_TIMESTAMP, CONCAT('a', 'b') or '{}'::jsonb are kept as their raw SQL text in a string, also with a warning. Hex and bit literals (0xFF, X'FF', B'101') are kept as raw text. Double-quoted values in a VALUES list are read as strings, MySQL-style, with a warning, because in standard SQL they would be column names.

Anything that is not INSERT … VALUES is reported, not guessed at: INSERT … SELECT, INSERT … DEFAULT VALUES, MySQL's INSERT … SET, CREATE TABLE, UPDATE and so on are listed with their statement number and line and skipped, while the rest of the input is still converted. A row whose number of values differs from the column list is reported the same way. SET, LOCK TABLES and UNLOCK TABLES statements from mysqldump, --, # and /* */ comments, and MySQL /*! … */ conditional comments are ignored with an informational note. Without a column list, keys are named column1, column2… in VALUES order.

Limitations

Only literal INSERT … VALUES rows are converted; it does not execute SQL, so expressions are not evaluated and INSERT … SELECT cannot be resolved.
Column types from the schema are unknown, so a quoted number stays a string and a 0/1 flag stays a number.
COPY … FROM stdin blocks in PostgreSQL dumps are not supported; export with pg_dump --inserts instead.
# always starts a comment outside strings (MySQL behaviour), which differs from PostgreSQL's # operator.
Input is limited to 2 million characters.

Frequently asked questions

Why is the output an object instead of an array?

When the input inserts into more than one table, rows are grouped by table name so they do not get mixed up. With a single table the output is a plain array of row objects.

Should I enable backslash escapes?

Yes for MySQL and MariaDB dumps, which escape quotes as \'. No for PostgreSQL (standard strings), SQLite and SQL Server, where 'C:\temp' contains a literal backslash. If a MySQL dump fails with an unterminated string, the option is probably off.

What happens to NOW() or CURRENT_TIMESTAMP?

They are kept as the raw text (for example "NOW()") and listed as warnings, because their value depends on when the database runs the statement.

Why are some large IDs strings?

JavaScript numbers lose precision above 2^53 − 1 (9007199254740991). Integers beyond that are kept as strings so the exact digits survive.

Can I convert a whole database dump?

You can paste a dump of up to 2 million characters. CREATE TABLE and other non-INSERT statements are listed as skipped, SET/LOCK/UNLOCK lines are ignored, and every INSERT … VALUES row is converted. To tidy the SQL first, use the SQL Formatter.