SQL transaction with JS Array of Objects?

Question

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

not sure which database you're talking about, but if it's postgres you can see here on the bottom of this page how you can do a "bulk insert" https://www.postgresql.org/docs/8.4/dml-insert.html — tclass, Dec 08 '19 at 23:03

score 0 · Answer 1 · answered Dec 08 '19 at 23:58

your problem is that you want to pass an object, as an example {"name":1} to your map, then it will end something like '{"name":1}' and then you apply join, which is wrong.

I thing what you want is the following:

var array = [{
  "name": 1
}, {
  "name": 2
}, {
  "name": 3
}, {
  "name": 4
}, {
  "name": 5
}, {
  "name": 6
}];
let comma_seperated_list_response = array.map(a => "'" + a.name + "'").join(",");
console.log(comma_seperated_list_response)
var sql = 'INSERT INTO DOWNLOAD_LIST(name) VALUES ?';

but if you want to separate your variables and the your values like an SQL call, you can do it like this:

var array = [{
  "name1": 1
}, {
  "name2": 2
}, {
  "name3": 3
}, {
  "name4": 4
}, {
  "name5": 5
}, {
  "name6": 6
}];

let names = '';
let values = '';

array.forEach((entry, i, a) => {
  Object.keys(entry).forEach((k) => {
    let coma = ', ';
    if (i === a.length - 1) {
      coma = ''
    }
    names += `${k}${coma}`;
    values += `${entry[k]}${coma}`
  })
})

var sql = `INSERT INTO DOWNLOAD_LIST(${names}) VALUES (${values})`;
console.log(sql)

score 0 · Accepted Answer · answered Dec 09 '19 at 05:59

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
}

score 0 · Answer 3 · answered Dec 09 '19 at 20:55

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


    let vals = elements.map(Object.values).map(a=>"(" +a+ ")").join(",");
    console.log("vals:"+vals);

I have now reduced the code to 2 lines using Object.values which converts objects to arrays. but still i get an Error:[object Object]

SQL transaction with JS Array of Objects?

3 Answers3