Better-sqlite3: How can I insert a lot of items

[ccpwcn]

I have this JSON:

[
    {"a": 1, "b":2, "c":3},
    {"a": 4, "b":5, "c":6},
    {"a": 7, "b":8, "c":9},
]

How can I insert all the data into the database at one time???

[mandaputtra]

I had this problem too.

[JoshuaWise]

const db = require('better-sqlite3')('my-database.db');

const array = [
    {"a": 1, "b":2, "c":3},
    {"a": 4, "b":5, "c":6},
    {"a": 7, "b":8, "c":9},
];

db.prepare('CREATE TABLE data (a, b, c)').run();
const insert = db.prepare('INSERT INTO data (a, b, c) VALUES (@a, @b, @c)');

for (const obj of array) insert.run(obj);

duplicate of https://github.com/JoshuaWise/better-sqlite3/issues/125

[loveencounterflow]

FWIW I've found that the by far fastest method to insert rows using better-sqlite3 is to precompile an entire insert statement like

insert into table foo ( a, b ) values
( 1, 'helo' ),
( 2, 'world' );

with all the values properly escaped and interpolated into the statement, row by row, and then sending the entire string, as in db.exec( sql ). When I copy file contents into SQLite, I achieve insert rates of well over 50,000 rows per second (each containing a line number and one word as gleaned from Linux distro dictionaries) whereas using a prepared statement only gives me a bit over 2,000~3,000 rows per second (both on an underpowered netbook).

If need be, inserts can be batched, and because I read files in a line-by-line fashion with fs.createReadStream() and readline.createInterface(), the amount of in-memory data can be kept small.

Surprisingly, doing it this way is also faster than piping huge CSV files from the command line, which is fraught with its own set of problems (library version mismatches are among them).

[patarapolw]

I would BEGIN TRANSACTION, and avoid committing too early.

Nicely, better-sqlite3 has db.transaction(() => {})()

It is this way in Python, where commit early is not the default.

with all the values properly escaped and interpolated into the statement, row by row, and then sending the entire string, as in db.exec( sql ).

@loveencounterflow How do I safely do that, because binding with ? is just limited to 999 items?