What This Tool Does
This bidirectional tool parses SQL `INSERT INTO` or `CREATE TABLE` statements and extracts the data/schema into a JSON array of objects. Conversely, it takes a JSON array and generates batched or individual SQL `INSERT` statements tailored to your database dialect.
Inputs Explained
- Direction: SQL to JSON, or JSON to SQL INSERT.
- Dialect: Select MySQL, PostgreSQL, SQLite, SQL Server, or Standard. This affects quoting.
- Table Name: The target table name for generated INSERTs.
- Input Text: Paste your SQL dump or JSON payload.
How It Works
SQL to JSON uses regex to extract column names from the `INSERT` header and maps them to the values in the `VALUES` clause, parsing strings, numbers, and NULLs appropriately. JSON to SQL iterates over the JSON objects, maps keys to columns, and safely formats the values according to SQL literal rules.
Formula / Logic Used
SQL ↔ JSON Converter
Convert SQL statements to JSON or generate SQL inserts from JSON data.
Step-by-Step Example
JSON Input:
[{"id": 1, "name": "O'Connor", "active": true}]
SQL Output (PostgreSQL):
INSERT INTO "my_table" ("id", "name", "active") VALUES (1, 'O''Connor', TRUE);
Use Cases
- Mock Data: Generate SQL test fixtures from JSON datasets.
- Data Migration: Move data from a NoSQL JSON document store into a relational database.
- API Debugging: Extract records from a SQL dump to feed into an API testing tool.
- Seeding Databases: Easily create migration scripts from static JSON files.
Assumptions and Limitations
- The SQL parser is regex-based, not a full AST parser. It handles standard INSERT statements but may fail on complex nested function calls (e.g. `VALUES (NOW(), geomFromText(...))`).
- It does not execute the SQL; it merely transforms the text format.
- When generating SQL from JSON, complex nested objects are stringified as JSON strings, which might not match your database's strict column types.
- This tool does NOT sanitize user input against SQL injection. Do not use this output directly in production without parameterized queries.
Frequently Asked Questions
How do I generate SQL UPDATE statements from JSON?
In our SQL <-> JSON converter, switch the mode to "JSON to SQL UPDATE", paste your JSON array, and specify the table name plus the primary key column. Each JSON object becomes an UPDATE statement keyed on the PK: UPDATE users SET name = 'Ramesh', city = 'Agra' WHERE id = 42; You can choose to skip null fields, quote all strings, or use prepared-statement placeholders (? or :name). For batch updates, we wrap statements in a transaction so a partial failure rolls back. Always run on a backup or staging DB first — there's no undo for a bad UPDATE in production.
How do I escape single quotes in SQL strings generated from JSON?
SQL standard says: double the single quote. So O'Brien becomes 'O''Brien' inside an SQL string literal. Our converter does this automatically, so a JSON value of O'Brien outputs as 'O''Brien' in the SQL. Some databases also accept backslash escaping ('O\'Brien' in MySQL with default settings), but the doubled-quote approach is portable across MySQL, PostgreSQL, SQLite, and SQL Server. The much safer pattern is parameterised queries — pass the value as a parameter and let the driver handle escaping. Our tool's "Use parameters" mode outputs ? placeholders plus a separate values array.
How do I convert a CSV-like SQL dump to JSON?
If your SQL dump is INSERT statements, our converter parses the column list and values directly. Paste it into the SQL to JSON pane and you'll get a JSON array of objects keyed by column name. For raw CSV-style outputs (no INSERT wrapper), use our CSV Viewer first to convert CSV to JSON, then drop into your workflow. For very large dumps (1GB+), use mysqldump --tab to get TSV files, then process them with csvkit or a small Python script — browser-based tools struggle with that scale. Always check character encoding (UTF-8) so accented Indian names don't get garbled.
How do I handle NULL values when converting between SQL and JSON?
SQL has a true NULL keyword distinct from empty strings or zeros. JSON has null. Our converter maps them one-to-one: SQL NULL becomes JSON null; JSON null becomes SQL NULL. A common bug is converting JSON null to the string "NULL" in SQL — that creates a four-character string, not a real null. Conversely, an empty JSON string "" should become SQL '', not NULL. We expose toggles for "treat empty string as NULL" and "treat missing field as NULL" so you can match your database schema's expectations. Be especially careful with NOT NULL columns.
Sources and References
- ISO/IEC 9075 - SQL Standard
- MySQL String Literals
- OWASP SQL Injection Prevention (Use Parameterization!)