How can I perform a bulk insert using sqlite3 in node.js?

Question

I need to insert 10 rows to a sqlite3 table, and once the insertions are done, pass the 10 id's of the new rows to a callback function in an array.

My problem is that I can't figure out how to make a prepared statement perform multiple inserts at once. I found this post on how to do this with mySQL.

Bulk Inserts in mySQL

But this doesn't work with sqlite. My code is below:

params = [[1,2],[3,4],[5,6],[7,8]]

stmt = db.prepare("INSERT INTO test (num1, num2) VALUES (?)");
stmt.all([params], function(err, res) {
    if(err) {
        console.log(err);
        return;
    } else {
        createdIDs = []
        for (i in result) {
            createdIDs.push(result[i].id);
        }
        nextFunction(createdIDs);
    }
});

Which gives the following error:

Error: SQLITE_ERROR: 1 values for 2 columns
at Error (native)

The table's schema is like this:

db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

Edit: Using Alvaro's solution, I now get this error message:

{ [Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: test.num1] errno: 19, code: 'SQLITE_CONSTRAINT' }

Answer 1

You have to enumerate the values in the order of their appearance:

db.run("INSERT INTO test (num1, num2) VALUES (?1,?2)");

That's why only one variable is detected instead of the expected two.

Reference here.

One more way to do it:

// create the table
db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

var params = [[1,2],[3,4],[5,6],[7,8]];
db.serialize(function() {
    db.run("begin transaction");

    for (var i = 0; i < params.length; i++) {
        db.run("insert into data(num1, num2) values (?, ?)", params[i][0], params[i][1]);
    }

    db.run("commit");
});

reference: https://gist.github.com/NelsonMinar/2db6986d5b3cda8ad167