How to convert json to sqlite insert statements
- Step 1Prepare a JSON array of flat objects — Provide a top-level array
[{...},{...}](a single object becomes a one-row table). If your fixture nests rows under a key, extract the array with json-path-extractor ($.rows[*]) first. - Step 2Drop the file on the converter — Drag a
.jsonfile onto the dropzone..xlsx/.xls/.odsare also accepted (first sheet → JSON array). A raw.csvisn't parsed as rows; convert it with csv-to-json first. - Step 3Name the table and select SQLite — Type the destination in Table name (default
data) and click the SQLite dialect button. Identifiers use double quotes, booleans emitBOOLEANwithTRUE/FALSE, and decimals type asREAL. - Step 4Exclude internal keys — List keys to drop in Exclude columns, comma-separated (e.g.
_meta, syncToken). They're removed from the CREATE TABLE and every INSERT row, keeping the embedded schema lean. - Step 5Set CREATE TABLE and conflict handling — Keep Include CREATE TABLE on for a fresh table, off for INSERT-only against an existing migration target. IF NOT EXISTS is the right default for migration runners. Enable ON CONFLICT DO NOTHING for re-runnable seeds on a populated database.
- Step 6Generate and load into SQLite — Click Generate SQL, then Copy or Download SQL. Load with
sqlite3 app.db < output.sql, or pass the string tobetter-sqlite3'sdb.exec()/ your migration framework. The stats line confirms rows, columns, and statement count.
Real SQLite options and what they emit
Converter controls mapped to SQLite output. Defaults are the tool's shipped values.
| Option | Effect in SQLite output | Default |
|---|---|---|
| Table name | Double-quoted target, e.g. CREATE TABLE IF NOT EXISTS "users" | data |
| Dialect = SQLite | Double-quoted identifiers; BOOLEAN columns with TRUE/FALSE; decimals typed REAL | PostgreSQL (you must switch) |
| Exclude columns | Comma-separated keys dropped from CREATE TABLE and every INSERT row | empty |
| Include CREATE TABLE | Prepends the CREATE TABLE block; off = INSERTs only | On |
| IF NOT EXISTS | Adds IF NOT EXISTS — the safe default for migration re-runs | On |
| ON CONFLICT DO NOTHING | Appends ON CONFLICT DO NOTHING (no target, table-wide skip) | Off |
JSON types vs SQLite declared type (and affinity)
SQLite uses type affinity, not strict typing. The declared type below is what appears in CREATE TABLE; the affinity is how SQLite actually stores it.
| JSON value(s) | Declared SQLite type | Storage / value |
|---|---|---|
All true/false | BOOLEAN | NUMERIC affinity; TRUE/FALSE stored as 1/0 |
| All whole numbers | INTEGER | INTEGER affinity; 42 |
| Any decimal | REAL | REAL affinity; 9.5 |
| Strings, dates, UUIDs | TEXT | TEXT affinity; '2026-01-01' |
| Nested object/array | TEXT | JSON-stringified; '{"k":1}' |
null everywhere / key absent | TEXT | value NULL |
Free vs Pro limits
JSON to SQL is a Pro tool; limits come from the json family in the tier table.
| Limit | Free | Pro |
|---|---|---|
| Max input file size | 2 MB | 100 MB |
| Files per run | 1 | 10 |
| Rows per INSERT batch | 100 (fixed) | 100 (fixed) |
Cookbook
JSON in, SQLite out — copied from the converter so the double-quoting, BOOLEAN handling, and conflict clause are exactly what you'll load into your embedded database.
App fixture seed with booleans
ExampleA settings fixture for a mobile app. SQLite declares BOOLEAN (NUMERIC affinity) and stores the TRUE/FALSE keywords as 1/0.
Input:
[
{"key": "dark_mode", "enabled": true},
{"key": "telemetry", "enabled": false}
]
Output (SQLite):
CREATE TABLE IF NOT EXISTS "settings" (
"key" TEXT,
"enabled" BOOLEAN
);
INSERT INTO "settings" ("key", "enabled")
VALUES
('dark_mode', TRUE),
('telemetry', FALSE);Re-runnable migration with ON CONFLICT DO NOTHING
ExampleA migration runner that re-applies a seed on every app launch needs idempotence. The conflict toggle appends a table-wide DO NOTHING so existing rows aren't duplicated or errored.
Config: ON CONFLICT DO NOTHING enabled
Output (SQLite):
INSERT INTO "countries" ("code", "name")
VALUES
('US', 'United States'),
('GB', 'United Kingdom') ON CONFLICT DO NOTHING;
-- Requires a PRIMARY KEY/UNIQUE on the target table to
-- detect conflicts. No target column is specified.Nested config stored as TEXT (use json_extract later)
ExampleA nested object is stringified into a TEXT column. SQLite's JSON1 functions can read it back at query time.
Input:
[{"id": 1, "layout": {"cols": 3, "compact": true}}]
Output (SQLite):
CREATE TABLE IF NOT EXISTS "widgets" (
"id" INTEGER,
"layout" TEXT
);
INSERT INTO "widgets" ("id", "layout")
VALUES
(1, '{"cols":3,"compact":true}');
-- Query with JSON1: json_extract(layout, '$.cols')INSERT-only into an existing migrated table
ExampleWhen your migration framework already created the table (with PRIMARY KEY, WITHOUT ROWID, etc.), turn off Include CREATE TABLE to emit inserts alone.
Config: Include CREATE TABLE = off, dialect = SQLite
Output (SQLite):
INSERT INTO "users" ("id", "name")
VALUES
(1, 'Ada'),
(2, 'Linus');
-- Your own schema (keys, indexes, WITHOUT ROWID) is left
-- entirely intact.Single object becomes a one-row table
ExampleIf you pass a single JSON object instead of an array, it is treated as one row — handy for seeding a single config record.
Input:
{"id": 1, "app_version": "2.4.0", "first_run": false}
Output (SQLite):
CREATE TABLE IF NOT EXISTS "app_state" (
"id" INTEGER,
"app_version" TEXT,
"first_run" BOOLEAN
);
INSERT INTO "app_state" ("id", "app_version", "first_run")
VALUES
(1, '2.4.0', FALSE);Errors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
BOOLEAN is a declared type, not a native SQLite type
By designSQLite has no real BOOLEAN type. The converter declares the column BOOLEAN (which gets NUMERIC affinity) and emits the TRUE/FALSE keywords, supported since SQLite 3.23. They're stored as 1/0. On a very old SQLite, TRUE/FALSE may not be recognised — store booleans as 1/0 integers in the source JSON for maximum compatibility.
Declared types are affinities, not constraints
ExpectedBecause SQLite uses dynamic typing, the INTEGER/REAL/TEXT types in the CREATE TABLE are affinities — SQLite won't reject a string in an INTEGER column. This is normal SQLite behaviour; the generated schema is correct, but don't expect the strict type enforcement you'd get from Postgres or MySQL.
ON CONFLICT DO NOTHING has no target and needs a constraint
By designThe appended ON CONFLICT DO NOTHING has no (column) target — a valid table-wide skip — but it only skips rows when the table has a PRIMARY KEY or UNIQUE constraint to detect the conflict against. With Include CREATE TABLE on, the generated table has no such constraint, so add one (or run INSERT-only against a table that has one).
No PRIMARY KEY or WITHOUT ROWID in output
By designThe CREATE TABLE lists columns and types only — no PRIMARY KEY, no WITHOUT ROWID, no indexes. For an embedded app you usually want a primary key; add it to the DDL, or create the table in your migration framework and use INSERT-only output here.
Nested JSON stored as TEXT
By designObjects and arrays are JSON-stringified into a TEXT column. SQLite's JSON1 extension can read them back with json_extract(col, '$.path'), but they are not separate columns. To get flat columns, run the JSON through json-flattener before converting.
Raw CSV dropped instead of JSON
Invalid inputOnly .json and spreadsheet files (.xlsx/.xls/.ods) become rows here. A .csv is fed to JSON.parse and fails. Convert it with csv-to-json first, then run the array through this tool.
Empty array yields a comment, not an error
ExpectedAn empty [] produces -- Empty array, no statements generated and zero statements. If your migration emits nothing, check that your extraction returned rows — a 0 in the stats line is the giveaway.
Date/UUID strings stay TEXT
ExpectedISO dates and UUIDs are strings, so they become TEXT columns and are single-quoted. SQLite stores dates as TEXT/INTEGER/REAL by convention anyway, so this is fine — use SQLite's date functions on the TEXT value when querying.
File over the size limit
Upgrade requiredFree tier caps input at 2 MB, Pro at 100 MB. A larger seed file is rejected before conversion. Split it into smaller arrays and load them sequentially in your migration runner.
Frequently asked questions
Does SQLite actually support the BOOLEAN type it generates?
SQLite has no native boolean type, but it accepts a column declared BOOLEAN (treated as NUMERIC affinity) and recognises the TRUE/FALSE keywords from version 3.23 onward, storing them as 1/0. The generated output is valid on any modern SQLite. For very old versions, store booleans as 1/0 integers in your source JSON instead.
Will the declared column types be enforced?
Not strictly. SQLite uses type affinity rather than rigid typing — an INTEGER-declared column will still accept a string. The generated CREATE TABLE is correct and idiomatic, but if you need hard type enforcement you'd add CHECK constraints by hand, which the tool does not generate.
Can I run the output through better-sqlite3 or expo-sqlite?
Yes. The multi-row INSERT and CREATE TABLE IF NOT EXISTS statements work with better-sqlite3's db.exec(), node:sqlite, expo-sqlite, and tauri-plugin-sql. For migration frameworks, generate INSERT-only output (turn off Include CREATE TABLE) so it slots into your existing schema migrations.
Does it add a PRIMARY KEY or indexes?
No. The CREATE TABLE has columns and inferred types only — no PRIMARY KEY, WITHOUT ROWID, or indexes. Add them to your DDL, or create the table in your migration code and use INSERT-only output. The ON CONFLICT DO NOTHING option also relies on a PRIMARY KEY/UNIQUE existing on the target.
How is the conflict clause written for SQLite?
Enabling the toggle appends ON CONFLICT DO NOTHING to the INSERT, with no conflict target — a valid table-wide skip in SQLite. It only skips rows when a PRIMARY KEY or UNIQUE constraint exists on the table. It is not an upsert (ON CONFLICT ... DO UPDATE); rewrite by hand if you need that.
What happens to nested objects in my JSON?
They are JSON-stringified into a TEXT column, e.g. '{"cols":3}'. You can read them back with SQLite's JSON1 functions like json_extract(col, '$.cols'). To store each nested key as its own column instead, flatten with json-flattener before converting.
How many rows per INSERT statement?
100, a fixed batch size with no UI control. A 250-row array becomes three INSERT statements (plus the CREATE TABLE). The stats line shows the total statement count after generating.
How are apostrophes escaped?
By doubling the single quote — O'Brien becomes 'O''Brien', the SQL-standard escape SQLite uses. It applies to all string values and to stringified nested JSON, so apostrophes never break a fixture.
Can I seed several tables from one file?
No — each run produces one table. Split your JSON by entity and run the converter once per table (changing the Table name), then concatenate the .sql outputs in the order your foreign keys require.
Is anything uploaded to a server?
No. Conversion runs entirely in your browser, so app fixtures and local data stay on the device — important for offline-first and privacy-focused apps. Only an anonymous run counter (no content) is recorded for dashboard stats.
Can I feed it a spreadsheet of fixture data?
Yes. .xlsx, .xls, and .ods files are accepted — the first sheet is read into a JSON array of row objects, then converted to SQLite SQL. A raw .csv, however, must go through csv-to-json first.
How do I handle dates in SQLite from this output?
Date strings stay as TEXT (single-quoted). SQLite conventionally stores dates as ISO-8601 text and uses functions like date()/strftime() on them, so the TEXT column works as-is. If you want INTEGER Unix timestamps, convert the dates in your source JSON before running the tool.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.