Bulk Insert into Mysql from Object Array NodeJS

Question

I'm not sure what I'm missing, I thought putting Games in brackets would let me do bulk insert to MySQL. However when I do this it comes back as (OBJECT,OBJECT). If I remove the brackets then I only get one single insert but it works! Any suggestions to do bulk insert?

Short sample of the Array

[{title:xxx,link:https://xxxx,description:xxx.,xxx:xxxxx},{title:xxx,link:https://xxxx,description:xxx.,xxx:xxxxx}]

Javascript

// Writing the Games inside a json file
fs.writeFile("feed.json", JSON.stringify(Games), function(err) {
  if (err) throw err;
  console.log("Saved!");
});

 con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sqlquery  = "INSERT INTO gtable (title, link, description, company) VALUES ?";
  let queryData = {
    sql: sqlquery,
    values: [Games]
  }
  con.query(queryData, function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

What if you try something [like this](https://stackoverflow.com/a/14259347/10431732)? So, something like: `con.query(sqlquery, [Games], function (err, result) { /* ... */ })` — Matt Oestreich, Jul 03 '20 at 00:03

otw · Accepted Answer · 2020-07-03T01:53:15.927

I believe your issue may be that you are passing an array of objects while MySQL is expecting an array of arrays.

Your format is basically:

var sqlquery  = "INSERT INTO gtable (title, link, description, company) VALUES ?";
var values = [
[{title:xxx,link:https://xxxx,description:xxx.,xxx:xxxxx}
{title:xxx,link:https://xxxx,description:xxx.,xxx:xxxxx}]
];

When it should be:

var sqlquery  = "INSERT INTO gtable (title, link, description, company) VALUES ?";
var values = [
[[title1,link1,description1,xxx1],
[title2,link2,description2,xxx2]]
];

You would need to convert each object to an array of just its values. Which you could do with something like this I think:

let sqlValues = Games.map(obj => Object.values(obj));

EDIT: I had the incorrect format for the mapping, fixed now.

score 1 · Answer 2 · answered Jul 03 '20 at 00:52

1

I believe @otw has a solid solution (and an awesome explanation of why this is happening), but to offer an alternative solution you could do something like this:

Games.forEach(game => {
  con.query('INSERT INTO gtable SET ?', game, insertErr => {
    if (insertErr) {
      throw new Error("Error inserting game " + game.title + "! " + insertErr);
    }
  
    console.log(`Inserted ${game.title}!`);
  });
})

answered Jul 03 '20 at 00:52

Matt Oestreich

8,219
3
16
41

as an alternative solution, this works well! thanks! – askmeaquestion1234 Jul 03 '20 at 02:07

Bulk Insert into Mysql from Object Array NodeJS

2 Answers2