Express/node-postgres pass an object with multiple values to insert query

Question

I'm serializing a form and sending with jQuery AJAX the data to an express route:

The object sent as arrobj is for example:

{ 
  col1: [ 'one', 'two', 'three' ],
  col2: [ 'foo', 'bar', 'baz' ]
}

In the route i have a function that creates a parametrized query string as follows:

function create_insert_SQL(obj, table, returnid) {
    // Loop through OBJ and extract column names
    var cols = ''; // Store column names
    var params = ''; // Store Parameter references eg. $1, $2 etc
    var count = 1;
    for(var p in obj) {
        cols += p + ",";
        params += "$" + count + ",";
        count++;
    };
    var strSQL = "INSERT INTO " + table + " (";
    strSQL += cols.substr(0, cols.length - 1);
    strSQL += ') VALUES (';
    strSQL += params.substr(0, params.length - 1)
    strSQL += ') returning ' + returnid + ' as id';
    return strSQL;
}

This will return insert into mytable (col1,col2) values ($1, $2);

After the query string is created I run the insert query in node-postgres passing the object:

db.query(SQL, arrobj, function (err, result) {
    if (err) {
        res.send(err)
    } else {
        res.send(result.rows[0].id.toString()) // return the inserted value
    }
});

For single values in every key everything works fine and the data is inserted in the table correctly.

How can I make the insert query run for every value of the keys?

Example:

insert into table (col1, col2) values ('one', 'foo');
insert into table (col1, col2) values ('two', 'bar');
insert into table (col1, col2) values ('three', 'baz');

Do not do this thing manually, it is very much error-prone. Instead, see [Multi-row insert with pg-promise](http://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise). — vitaly-t, Mar 06 '17 at 07:33

paqash · Answer 1 · 2017-03-06T08:24:44.953

The first and most tricky part is to invert your object structure so you get rows to insert. Please see the snippet below and run it to see what happens.

After that you can iterate over the columns (for .. in, like you already have) to get the INSERT INTO columns part, and iterate over the rows of the new object to get the values part. It should be easy enough, let me know if you need help there.

var obj = { 
  col1: [ 'one', 'two', 'three' ],
  col2: [ 'foo', 'bar', 'baz' ],
  col3: [ '1', '2', '3' ]
}

//console.log(obj);

var invert = function (obj) {

  var new_obj = [];
  
  // iterate over props
  for (var prop in obj) {
    // iterate over columns
    for(var i=0;i<obj[prop].length;i++) {
    // if a row exists, assign value to it
     if (new_obj[i]) {
      new_obj[i][prop] = obj[prop][i];
     } else {
     // if it doesn't, create it
      var row = {};
      row[prop] = obj[prop][i];
      new_obj.push(row);
     }
    }
  }

  return new_obj;
};

var bulkStatement = function (table, rows) {
  var params = [];
  var chunks = [];
  var statement = 'INSERT INTO ' + table + '(';
  
  for (var prop in rows[0]) {
    statement += prop + ',';
  }
  
  statement = statement.slice(0,-1) + ') VALUES ';
  
  for(var i = 0; i < rows.length; i++) {
    var row = rows[i];
    var valueClause = [];
    for (var prop in row) {
      params.push(row[prop]);
      valueClause.push('$' + params.length);
    }
    chunks.push('(' + valueClause.join(', ') + ')');
  }

  return {
    text: statement + chunks.join(', '),
    values: params
  }
};

var inverted = invert(obj);

console.log(bulkStatement('tableName', inverted));

Yup this is the way to go. The only issue is that it is assigning a serial value to the key of the new object and not the name from original object. Eg 0,1,2 instead of col1, col2 — Fabrizio Mazzoni, Mar 06 '17 at 03:54
I added the SQL generating function as well, please see edit — paqash, Mar 06 '17 at 08:25
@FabrizioMazzoni have you been able to make it work this way? — paqash, Mar 07 '17 at 11:55

Express/node-postgres pass an object with multiple values to insert query

1 Answers1