使用node-postgres更新数据的更简单方法？

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

我正在使用精湛的插件节点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?

我有这个更新休息电话。我的桌子上有大约30列。有没有更简单的方法来更新这些呢?

/*
 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?

我这里只有三列。当我写完所有30列时,它将很难维护。必须是一个简单的行更新req.body中的所有列的方法?

Any ideas?

3 个解决方案

#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

或者,如果ORM是你需要的东西,因为你会像上面那样做很多,你应该检查像Knex.js这样的模块

#2

I like to use knexjs, which works with postgre. It's also a fun javascript way to write queries (without all that nasty SQL-string manipulation).

我喜欢使用knexjs,它适用于postgre。它也是一种有趣的javascript编写查询方式(没有所有令人讨厌的SQL字符串操作)。

Take for example this method, that stores some contact information. The JSON schema of that contact information is defined elsewhere (also useful when I validate). The result is a code-generated query, which contains only columns passed in.

以此方法为例,它存储一些联系信息。该联系信息的JSON模式在别处定义(在我验证时也很有用)。结果是代码生成的查询,其中仅包含传入的列。

function saveContactInfo( inputs, callback ) {
  var setObj = {};
  for( var property in inputs.contact )
  {
    //assumes properties are same as DB columns, otherwise need to use some string-mapping lookup.
    setObj[ property ] = inputs.contact[property];
  }
  setObj[ "LastModified" ] = new Date();

  var query = knex( "tblContact" ).update( setObj ).where( "contactId", inputs.contact.contactId );
  //log.debug("contactDao.saveContactInfo: " + query.toString());
  query.exec( function(err, results ){
    if(err) return callback(err);
    //Return from DB is usually an array, so return the object, not the array.
    callback( null, results[0] );
  });    
}

Knexjs also has some nifty postgre-only options (which would have been useful for me, had I not been using MySQL)

Knexjs还有一些漂亮的postgre-only选项(如果我没有使用MySQL,那对我来说会很有用)

#3

Create Insert Query

创建插入查询

exports.createInsertQuery = (tablename, obj) => {
    let insert = 'insert into ' + tablename;
    let keys = Object.keys(obj);
    let dollar = keys.map(function (item, idx) { return '$' + (idx + 1); });
    let values = Object.keys(obj).map(function (k) { return obj[k]; });
    return {
        query: insert + '(' + keys + ')' + ' values(' + dollar + ')',
        params: values
    }
}

Usage

let data = {firstname : 'hie' , lastname : 'jack', age : 4}
let yo = createInsertQuery('user',data) 

client.query(yo.query, yo.params ,(err,res) =>{
 console.log(res)
})

So like wise you can create update , delete query

因此,您可以创建更新,删除查询

#1