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' }

@AlvaroJoao Yes, the code should be just like that other answer. The difference is that I'm using sqlite3 and not mysql — John Palmer, Jul 15 '16 at 03:50
just answered your question... pls upvote and mark as correct answer if I helped you — Alvaro Silvino, Jul 15 '16 at 04:05
Just updated my answer... Try this one and tell me if you did it — Alvaro Silvino, Jul 15 '16 at 05:01

score 4 · Answer 1 · edited Jul 31 '19 at 08:48

4

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

edited Jul 31 '19 at 08:48

sertsedat

3,490
1
25
45

answered Jul 15 '16 at 04:02

Alvaro Silvino

9,441
12
52
80

Hmm. Still doesn't seem to be working, but I have a separate error now. The values I'm inserting have a NOT NULL constraint, which is failing when I insert. Makes me thing that passing in [params] is not working properly. – John Palmer Jul 15 '16 at 04:41
@JohnPalmer I'm almost sure the code is fine, you should print the new error after the ?1,?2 I think I can help – Alvaro Silvino Jul 15 '16 at 04:47
Yeah based on that reference link, it doesn't look like that's the right fix. You can bind the values separately, but you can't pass an array with the values for multiple inserts. – John Palmer Jul 15 '16 at 04:50
I don't think that's valid. It's not using the prepared statement at all, and doesn't give access to all of the ID's at one time once the inserts are complete. I already know I could run inserts sequentially and do something with the ID's one-by-one. But I want to be able to pass them to a callback function in an array at one time. – John Palmer Jul 15 '16 at 05:08

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

1 Answers1