How to use it
- Paste a JSON array of objects. One object on its own also works and gives one row.
- Enter the table name (schema.table is fine) and pick MySQL, PostgreSQL or SQLite.
- Choose whether you need CREATE TABLE, INSERT statements or both, and how many rows go in each INSERT.
- Copy the SQL and run it in your database client.
How it works
Columns are all keys found in the objects, in the order they first appear. Each column’s type comes from its values, and a key that is missing in an object gives NULL in that row. Rows are written as multi-row INSERT statements, which all three databases support; big arrays are split by the Rows per INSERT setting.
Numbers are written as they are, booleans as TRUE/FALSE (1/0 in SQLite), and a column of mixed values gets every value as quoted text, so the INSERT matches its TEXT type.
Worked example
[{"id":1,"name":"Ann","tags":["a"]},{"id":2,"name":"Bob"}] with the table users in PostgreSQL gives:
CREATE TABLE "users" (
"id" BIGINT NOT NULL,
"name" TEXT NOT NULL,
"tags" JSONB
);
INSERT INTO "users" ("id", "name", "tags") VALUES
(1, 'Ann', '["a"]'),
(2, 'Bob', NULL);tags is missing from Bob’s object, so the column allows NULL and his row gets NULL.
Tips
- Add a PRIMARY KEY and indexes yourself: the generator cannot know which column is the key.
- Dates in JSON are strings, so they become TEXT; change the type to DATE or TIMESTAMP if the values are ISO dates.
- Integers above 9,007,199,254,740,991 lose precision when JSON is read in JavaScript; keep big IDs as strings in the JSON.
- For CSV data use CSV to SQL INSERT.