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
– John Palmer, Commented Jul 15, 2016 at 3:50
just answered your question... pls upvote and mark as correct answer if I helped you — Alvaro Silvino
– Alvaro Silvino, Commented Jul 15, 2016 at 4:05

sertsedat · Accepted Answer · 2019-07-31 08:48:19Z

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, 2019 at 8:48

sertsedat

3,6101 gold badge28 silver badges45 bronze badges

answered Jul 15, 2016 at 4:02

Alvaro Silvino

9,83314 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Palmer Over a year ago

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.

Alvaro Silvino Over a year ago

@JohnPalmer I'm almost sure the code is fine, you should print the new error after the ?1,?2 I think I can help

John Palmer Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related