Inserting multiple rows with multiple columns in Node and sqlite3

Question

I'm trying to insert many different rows into an sqlite database using a single operation. Each of the rows has multiple columns, and I have the data represented as an array of arrays.

I've read the user guide and other tutorials, but all the ones that mention inserting multiple rows work for rows with only a single column.

I'm trying to insert a much larger array, but for testing it I've broken it down into two entries.

let testArtist = [["string", 1, 2, "string"], ["string", 3, 4, "string"]];
let artistQuery = "INSERT INTO artists (artist, numSongs, numAlbums, picture) VALUES (?, ?, ?, ?), (?, ?, ?, ?)";

db.serialize(
    db.run(artistQuery, [testArtist], function(err){
        if(err) throw err;
    });
});

Here's the results of the insert operation

select * from artists;
1||||
2||||

So the AUTOINCREMENT integer ID is being inserted, but the data is not.

score 8 · Answer 1 · answered May 19 '19 at 19:10

EDIT: I figured it out myself guys. What you need to do is flatten the array into a single array.

So: [["string", 1, 2, "string"], ["string", 3, 4, "string"]]

Becomes: ["string, 1, 2, "string", "string", 3, 4, "string"]

You still need to separate the values in the INSERT INTO op, I used the map function for this as is described in tutorials.

let artistPlaceholders = artistRecords.map(() => "(?, ?, ?, ?)").join(', ');
let artistQuery = "INSERT INTO artists (artist, numSongs, numAlbums, picture) VALUES " + artistPlaceholders;
let flatArtist = [];
artistRecords.forEach((arr) => { arr.forEach((item) => { flatArtist.push(item) }) });

db.serialize(function(){
    db.run(artistQuery, flatArtist, function(err){
        if(err) throw err;
    });
});

Where artistRecords is an array in the form of:

[["string", 0, 0, "string"], ["string", 0, 0, "string"], [...]]

If you have an array with multiple levels of nesting, you'll need to modify the flattening function.

This is a great for me, i prefer one insert of 100 rows as 100 inserts of one row. Btw you can use the .flat() method to flatten an array. — Shayana Keiraved Pahal, May 18 '20 at 17:22

bsigma1 · Answer 2 · 2019-09-08T23:50:37.867

I suppose @Chris's own answer the only way to accomplish multiple rows and columns in a single giant INSERT statement (although I'd be interested to hear why it needed to be just one operation).

I've also had a hard time finding examples of sqlite3 in node.js (that's how I ended up here) so I wanted to share a multi-column example that accomplishes the goal above but with multiple operations.

let testArtist = [
   ["string", 1, 2, "string"],
   ["string", 3, 4, "string"]
];

// create the statement for the insertion of just ONE record
let artistQuery = 
   "INSERT INTO artists (artist, numSongs, numAlbums, picture) " +
   "VALUES (?, ?, ? ,?)"; 

// 'prepare' returns a 'statement' object which allows us to 
// bind the same query to different parameters each time we run it
let statement = db.prepare(artistQuery);

// run the query over and over for each inner array
for (var i = 0; i < testArtist.length; i++) {
    statement.run(testArtist[i], function (err) { 
        if (err) throw err;
    });
}

// 'finalize' basically kills our ability to call .run(...) on the 'statement'
// object again. Optional.
statement.finalize();

// If I call statement.run( ... ) here again, I will get an error due 
// to the 'finalize' call above.

If you need a guarantee that all the rows are inserted in order, you can wrap the whole loop in db.serialize( ... ) as @Chris did.

Inserting multiple rows with multiple columns in Node and sqlite3

2 Answers2