Easier way to update data with node-postgres?

Question

I'm using the superb plugin node-postgres, https://github.com/brianc/node-postgres

I have this update rest call. I have a about 30 columns in my in my table. Is there any easier way to update these then this way?

/*
 Post /api/project/products/:pr_id HTTP/1.1
 */
exports.updateProduct = function(req, res){
  pg.connect(cs, function(err, client, done) {
    var query = "UPDATE products SET pr_title = ($1), pr_usercode = ($2) WHERE pr_id=($3)";
    client.query(query, [req.body.pr_title, req.body.pr_usercode, req.params.pr_id], function(err, result) {
      if (handleErr(err, done)) return;
      done();
      sendResponse(res, result.rows[0]);
    })
  });
};

I only have three columns here. It will be messy and hard to maintain when I write all 30 columns. Must be a way where just with a simple line update all columns in req.body?

Any ideas?

Answer 1

You could always roll out a function like so:

function updateProductByID (id, cols) {
  // Setup static beginning of query
  var query = ['UPDATE products'];
  query.push('SET');

  // Create another array storing each set command
  // and assigning a number value for parameterized query
  var set = [];
  Object.keys(cols).forEach(function (key, i) {
    set.push(key + ' = ($' + (i + 1) + ')'); 
  });
  query.push(set.join(', '));

  // Add the WHERE statement to look up by id
  query.push('WHERE pr_id = ' + id );

  // Return a complete query string
  return query.join(' ');
}

And then use it as such:

/*
 Post /api/project/products/:pr_id HTTP/1.1
 */
exports.updateProduct = function(req, res){
  pg.connect(cs, function(err, client, done) {

    // Setup the query
    var query = updateProductByID(req.params.pr_id, req.body);

    // Turn req.body into an array of values
    var colValues = Object.keys(req.body).map(function (key) {
      return req.body[key];
    });

    client.query(query, colValues, function(err, result) {
      if (handleErr(err, done)) return;
      done();
      sendResponse(res, result.rows[0]);
    });
  });
};

Or, if an ORM is something you need because you'll be doing a lot like the above, you should check out modules like Knex.js