CODEWITHSUNDEEP
node.jssqlitenode-sqlite3
Chris Hill
Chris Hill

Reputation: 195

Inserting multiple rows with multiple columns in Node and sqlite3

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.

Upvotes: 9

Views: 11540

Answers (3)

Ives.me
Ives.me

Reputation: 2394

I was trying to bulk insert into a many-to-many table and some records would not be inserted. Not a single error. Based on the previous responses, this is what I got:

    //I'm using typescript to run node
    const permissionIds: number[] = [1,2,3]; //hardcoded for the example, populate as you need
    const roleId = 10; //hardcoded

    const placeholders = permissionIds.map(() => '(?, ?)').join(',');
    const insertSql = `INSERT INTO roles_permissions (role, permission) VALUES ${placeholders}`;

    const rolePermissionValues = permissionIds.flatMap((permissionId) => [roleId, permissionId]);

    this.db.serialize(() => {
        this.db.run(insertSql, rolePermissionValues, (err) => {
            if (err) {
                console.error(err.message);
            }
        })
    });

Upvotes: 1

bsigma1
bsigma1

Reputation: 310

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.

Upvotes: 7

Chris Hill
Chris Hill

Reputation: 195

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.

Upvotes: 10

Related Questions