SQL transaction with JS Array of Objects?

Say i have an Array of Objects

var array = [{"name":1},{"name":2},{"name":3},{"name":4},{"name":5},{"name":6}];

Now i create a comma seperated list

      let comma_seperated_list_response = array.map(a => "'" + a + "'").join(",");

    var sql= 'INSERT INTO DOWNLOAD_LIST(name) VALUES ?';

To pass it to sql.transaction?

   app.db.transaction(function (tx) {
        tx.executeSql(sql, [comma_seperated_list_response],
            function (tx2, results2) {   
                console.log({results2})
                debugger;
            }, app.onError);
    });

is there any other way then to loop through the array and pass object each by one or can i pass bulk array? When i pass the comma_seperated_list_response it says Error object Object

Answer

If every object into your array should be one row into the table, then what you are trying to do is SQL Insert Multiple Rows. That is, we are inserting multiple objects using only one insert command. So, each object may have many attributes, not just name, but also age, for example.

So, for every object, you have to create the SQL tuple with the values (name, age).

Finally, in the SQL script, they must be grouped in a comma-separated list of values in a command like this:

INSERT INTO DOWNLOAD_LIST(name,age) VALUES 
(1,11),
(2,22),
(3,33),
(4,44),
(5,55),
(6,66);

One possible way to do it is like the code below:

var elements = [{
  "name": 1,
  "age":11
}, {
  "name": 2,
  "age": 22
}, {
  "name": 3,
  "age": 33
}, {
  "name": 4,
  "age": 44
}, {
  "name": 5,
  "age": 55
}, {
  "name": 6,
  "age": 66
}];

let attributes = ["name","age"]; 
let attributes_list = attributes.join(",");
let values = elements.map(
  element => {
    attributes = [element.name, element.age]
    return "(" + attributes.join(",") + ")";
  }
).join(",n");
var sql = `INSERT INTO DOWNLOAD_LIST(${attributes_list}) VALUES ${values};`;
console.log(sql);

But, this is still fragile to SQL Injection. So, let’s try something similar to these suggestions from What are Best Practices for preventing SQL injection in node-mysql?:

    var elements = [{
      "name": 1,
      "age":11
    }, {
      "name": 2,
      "age": 22
    }, {
      "name": 3,
      "age": 33
    }, {
      "name": 4,
      "age": 44
    }, {
      "name": 5,
      "age": 55
    }, {
      "name": 6,
      "age": 66
    }];

    let attributes = ["name","age"];
    let binds = elements.map(
      (element,key) => {
        attributes = [':name_'+key, ':age_'+key]
        return "(" + attributes.join(",") + ")";
      }
    ).join(",n");
    let attributes_list = attributes.join(",");
    
    let values = elements.map(
      (element,key) => {
        let key_name = ':name_' + key;
        let key_age = ':age_' + key;
        let obj = {};
        obj[key_name] = element.name;
        obj[key_age] = element.age;
        return obj;
      }
    );
    let valuescombined = values.reduce(
      (a,b) => {
         return {...a,...b};
      }
    );
    var sql = `INSERT INTO DOWNLOAD_LIST(${attributes_list}) VALUES ${binds};`;
    console.log(sql);
    console.log(valuescombined);

This command should create the bind SQL command and the values pointing to each value:

INSERT INTO DOWNLOAD_LIST(:name_5,:age_5) VALUES (:name_0,:age_0),
(:name_1,:age_1),
(:name_2,:age_2),
(:name_3,:age_3),
(:name_4,:age_4),
(:name_5,:age_5);

{
  ":name_0": 1,
  ":age_0": 11,
  ":name_1": 2,
  ":age_1": 22,
  ":name_2": 3,
  ":age_2": 33,
  ":name_3": 4,
  ":age_3": 44,
  ":name_4": 5,
  ":age_4": 55,
  ":name_5": 6,
  ":age_5": 66
}