Convert JSON to SQLite

Q: How do I convert JSON to SQLite?

Drop your file on the converter at the top of this page. It finds the header, reads every value and writes the SQLite script, listing each fix it made. Download the script and run it with sqlite3.

Q: What shape does the JSON file need?

An array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON). Each key becomes a column.

Q: Which SQLite types does it use?

Yes and no values become BOOLEAN, whole numbers INTEGER, decimals NUMERIC(p,s), dates DATE, dates with a time DATETIME and text TEXT. Sizes come from the longest value in each column.

Q: Does it keep leading zeros in SQLite?

Yes. A code such as 000481 stays text, so SQLite stores it as TEXT and the script writes it as '000481'.

Q: Can it load the data into SQLite without a script?

Yes, for free: intosql load creates the SQLite database file and loads the rows in. The converter on this page also downloads a ready .sqlite database.

Q: How big can the JSON file be?

NDJSON, one object per line, is read as it streams in, so files of several gigabytes work in the browser. A JSON document is read whole, so its limit is the memory the browser gives this page. The desktop app and the command line take files of any size.

Drop your JSON file to get a SQLite script with CREATE TABLE and INSERT statements. Each column gets a type, and every fix is listed with its row and column.

OutputHeader rowDates

4 rows converted, 2 fixes, none rejected

Your fileSQL2 fixes

The first rows of pedidos_2024.json, with each fix marked
Row
2	000481	Peña Muñoz	2024-02-03T10:15:00Z	1234.5	true
3	000482	José Ángel Ruiz	2024-02-15T16:40:00Z	99.9	false	Entrega en la tarde
4	000483	Zoë Ortíz	2024-02-29T09:05:00Z	12	true
5	000484	Ñuño Castañeda	2024-03-11T13:30:00Z	980.75	sí	Factura con "RFC"
6
7
8
9
10
11

-- Written by IntoSQL for SQLite.

BEGIN;

CREATE TABLE pedidos_2024 (
    pedido TEXT NOT NULL,
    cliente TEXT NOT NULL,
    fecha DATETIME NOT NULL,
    total NUMERIC(6,2) NOT NULL,
    pagado BOOLEAN NOT NULL,
    notas TEXT
);

INSERT INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES
('000481', 'Peña Muñoz', '2024-02-03 10:15:00+00:00', 1234.5, 1, NULL),
('000482', 'José Ángel Ruiz', '2024-02-15 16:40:00+00:00', 99.9, 0, 'Entrega en la tarde'),
('000483', 'Zoë Ortíz', '2024-02-29 09:05:00+00:00', 12, 1, NULL),
('000484', 'Ñuño Castañeda', '2024-03-11 13:30:00+00:00', 980.75, 1, 'Factura con "RFC"');

COMMIT;

Fixes

Leading zeros kept in "pedido": 000481 stays 000481, stored as text.
"pagado" holds yes and no words (sí), stored as boolean.

Your file is converted on this computer and never uploaded.

Other samples:

From JSON to SQLite, column by column

The sample above, pedidos_2024.json, as IntoSQL writes it for SQLite: what each column holds in the file, the type it gets and how its first value lands in the script.

In the file	First value	Column	SQLite type	In the script
pedido	000481	pedido	TEXT	'000481'
cliente	Peña Muñoz	cliente	TEXT	'Peña Muñoz'
fecha	2024-02-03T10:15:00Z	fecha	DATETIME	'2024-02-03 10:15:00+00:00'
total	1234.5	total	NUMERIC(6,2)	1234.5
pagado	true	pagado	BOOLEAN	1
notas	-	notas	TEXT	NULL

Load the script into SQLite

The download is a plain .sql file. Run it with SQLite's own tools.

From a terminal

$sqlite3 pedidos_2024.db < pedidos_2024.sqlite.sql

The database file is created if it doesn't exist. The converter also downloads a ready .sqlite database, with no command at all.

From a database tool

In DB Browser for SQLite, choose Execute SQL, open the script and run it.

Without a script

The command line loads the file straight into a SQLite database, without writing a script. It's free.

$intosql load pedidos_2024.json --into pedidos_2024.db

What to know about JSON files and SQLite

What IntoSQL reads in JSON files

The file can be an array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON).
Each key becomes a column. Objects and arrays inside a value are kept as JSON text.
Numbers keep their exact digits, and ISO 8601 dates become dates or timestamps.

What to know about SQLite

Each column gets the type its values need, like INTEGER, NUMERIC, TEXT, DATE or BOOLEAN; yes and no are stored as 1 and 0.
Decimals with more than 15 digits stay TEXT, so no digit is rounded away.
Download the SQLite script, or a ready .sqlite database built in your browser.

How each kind of value is stored in SQLite

Value	SQLite
Yes and no	BOOLEAN
Whole numbers	INTEGER
Large whole numbers	INTEGER
Decimals	NUMERIC(p,s)TEXT
Floating-point numbers	REAL
Dates	DATE
Times of day	TIME
Dates with a time	DATETIME
Dates with a time and offset	DATETIME
Text	TEXT

Questions about JSON to SQLite

Not answered here? Write to us from the contact page and a person replies. Contact page

How do I convert JSON to SQLite?

Drop your file on the converter at the top of this page. It finds the header, reads every value and writes the SQLite script, listing each fix it made. Download the script and run it with sqlite3.

What shape does the JSON file need?

An array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON). Each key becomes a column.

Which SQLite types does it use?

Yes and no values become BOOLEAN, whole numbers INTEGER, decimals NUMERIC(p,s), dates DATE, dates with a time DATETIME and text TEXT. Sizes come from the longest value in each column.

Does it keep leading zeros in SQLite?

Yes. A code such as 000481 stays text, so SQLite stores it as TEXT and the script writes it as '000481'.

Can it load the data into SQLite without a script?

Yes, for free: intosql load creates the SQLite database file and loads the rows in. The converter on this page also downloads a ready .sqlite database.

How big can the JSON file be?

NDJSON, one object per line, is read as it streams in, so files of several gigabytes work in the browser. A JSON document is read whole, so its limit is the memory the browser gives this page. The desktop app and the command line take files of any size.