Skip to content

Instantly share code, notes, and snippets.

Last active Jun 28, 2021
What would you like to do?
A demonstration of slow sqlite3 bulk inserts in node.js
// Demonstration that bulk insertsin Node.js sqlite3 using prepared statements is very slow.
// Usage: run with one command line argument, one of "db", "reuse", "finalize"
// Details:
var sqlite3 = require('sqlite3').verbose();
var start =;
var db = new sqlite3.Database('inserttest.sqlite');
var mode = process.argv[2], runs = "100";
db.serialize(function() {"begin transaction");"drop table if exists data");"create table data (value integer)");
var stmt = db.prepare("insert into data values (?)");
// Three different methods of doing a bulk insert
for (var i = 0; i < runs; i++) {
if (mode == "db") {"insert into data values (?)", i);
} else if (mode == "reuse") {;
} else if (mode == "finalize") {
stmt = db.prepare("insert into data values (?)");;
} else {
console.log('Command line args must be one of "db", "reuse", "finalize"');
db.close(function() {
// sqlite3 has now fully committed the changes
console.log(( - start) + "ms");

This comment has been minimized.

Copy link
Owner Author

@NelsonMinar NelsonMinar commented Nov 18, 2014

Revision 2 is no longer slow, I had a bug with the prepared statement vs. the transaction. Details at


This comment has been minimized.

Copy link

@lukakoczorowski lukakoczorowski commented Apr 17, 2018

I'd add one more option:
else if ( mode == 'bulk') {

const max = 999; 
const range = Math.min(max, runs-i);
i += Math.min(max-1, runs-i-1);
const values = Array(range).fill().map( (_,i) => i);
const placeholders = => "(?)").join(",");
let sql = 'INSERT INTO data VALUES ' + placeholders;, values);


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment