使用节点发布更新数据的更简单方法

Question

我正在使用出色的插件node-postgres，https://github.com/brianc/node-postgres

我有这个更新休息电话。我的表中有大约 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]);
    })
  });
};

我这里只有三列。当我写所有 30 列时，它会很混乱且难以维护。一定是一种仅用简单的行更新 req.body 中所有列的方法吗？

有什么想法吗？

Answer 1

您可以随时推出如下函数：

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(' ');
}

然后按如下方式使用它：

/*
 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]);
    });
  });
};

或者，如果您需要ORM，因为您将做很多类似的事情，那么您应该查看像Knex这样的模块.js

Answer 2

已经给出了很好的答案，但恕我直言，在一个方面还不够好，它们都缺乏良好的抽象。我将尝试提供更抽象的方式来使用 node-postgres postgres更新您的数据。

遵循官方文档始终是一种很好的做法，以下代码结构取自node-postgres，您可以随心所欲地扩展它：

这是我的，这是您与数据库交互的地方

const { Pool } = require("pg");
const connection = require("./connection.json");
const pool = new Pool(connection);
const { insert, select, remove, update } = require("./helpers");

/**
 * The main mechanism to avoid SQL Injection is by escaping the input parameters.
 * Any good SQL library should have a way to achieve this.
 * PG library allows you to do this by placeholders `($1, $2)`
 */
module.exports = {
  query: (text, params, callback) => {
    const start = Date.now();
    return pool.query(text, params, (err, res) => {
      const duration = Date.now() - start;
      console.log("executed query", { text, duration, rows: res.rowCount });
      callback(err, res);
    });
  },
  getClient: callback => {
    pool.connect((err, client, done) => {
      const query = client.query;
      // monkey patch the query method to keep track of the last query executed
      client.query = (...args) => {
        client.lastQuery = args;
        return query.apply(client, args);
      };
      // set a timeout of 5 seconds, after which we will log this client's last query
      const timeout = setTimeout(() => {
        console.error("A client has been checked out for more than 5 seconds!");
        console.error(
          `The last executed query on this client was: ${client.lastQuery}`
        );
      }, 5000);
      const release = err => {
        // call the actual 'done' method, returning this client to the pool
        done(err);
        // clear our timeout
        clearTimeout(timeout);
        // set the query method back to its old un-monkey-patched version
        client.query = query;
      };
      callback(err, client, release);
    });
  },
  /**
   * Updates data
   *
   * entity: table name, e.g, users 
   * conditions: { id: "some-unique-user-id", ... }
   * fields: list of desired columns to update { username: "Joe", ... }
   */
  updateOne: async (entity, conditions, fields) => {
    if (!entity) throw new Error("no entity table specified");
    if (Utils.isObjEmpty(conditions))
      throw new Error("no conditions specified");
    let resp;   
    const { text, values } = update(entity, conditions, fields);
    try {
      rs = await pool.query(text, values);
      resp = rs.rows[0];
    } catch (err) {
      console.error(err);
      throw err;
    }
    return resp;
  },
  createOne: async (entity, data) => {
  },
  deleteOne: async (entity, conditions, data) => {
  },
  findAll: async (entity, conditions, fields) => {
  },
  // ... other methods
};

这是 CRUD 操作的辅助方法，它们将准备查询 具有预准备值的文本：

/**
 * tableName: `users`
 * conditions: { id: 'joe-unique-id', ... }
 * data: { username: 'Joe', age: 28, status: 'active', ... }
 *
 *  "UPDATE users SET field_1 = $1, field_2 = $2, field_3 = $3, ... ( WHERE ...) RETURNING *";
 */
exports.update = (tableName, conditions = {}, data = {}) => {
  const dKeys = Object.keys(data);
  const dataTuples = dKeys.map((k, index) => `${k} = $${index + 1}`);
  const updates = dataTuples.join(", ");
  const len = Object.keys(data).length;
  let text = `UPDATE ${tableName} SET ${updates} `;
  if (!Utils.isObjEmpty(conditions)) {
    const keys = Object.keys(conditions);
    const condTuples = keys.map((k, index) => `${k} = $${index + 1 + len} `);
    const condPlaceholders = condTuples.join(" AND ");
    text += ` WHERE ${condPlaceholders} RETURNING *`;
  }
  const values = [];
  Object.keys(data).forEach(key => {
    values.push(data[key]);
  });
  Object.keys(conditions).forEach(key => {
    values.push(conditions[key]);
  });
  return { text, values };
};
exports.select = (tableName, conditions = {}, data = ["*"]) => {...}
exports.insert = (tableName, conditions = {}) => {...}
exports.remove = (tableName, conditions = {}, data = []) => {...}

最后，您可以在路由处理程序中使用它而不会混乱 您的代码库：

const db = require("../db");
/**
 *
 */
exports.updateUser = async (req, res) => {
  try {
    console.log("[PUT] {api/v1/users}");
    const fields = {
      name: req.body.name,
      description: req.body.description,
      info: req.body.info
    };
    const userId = req.params.id;
    const conditions = { id: userId };
    const updatedUser = await db.updateOne("users", conditions, fields);
    if (updatedUser) {
      console.log(`team ${updatedUser.name} updated successfully`);
      return res.json(updatedUser);
    }
    res.status(404).json({ msg: "Bad request" });
  } catch (err) {
    console.error(err);
    res.status(500).send({ msg: "Server error" });
  }
};

方便的实用程序：

const Utils = {};
Utils.isObject = x => x !== null && typeof x === "object";
Utils.isObjEmpty = obj => Utils.isObject(obj) && Object.keys(obj).length === 0;

Answer 3

我喜欢使用knexjs，它适用于postgre。这也是一种有趣的JavaScript方法来编写查询（没有所有讨厌的SQL字符串操作）。

以此方法为例，它存储了一些联系信息。该联系信息的 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 还有一些漂亮的仅 postgre 选项（如果我没有使用 MySQL，这对我很有用）

Answer 4

我的快速示例：

async update(objectToSave) {
    const dbID = objectToSave.id;
    //get obj values only
    const args = Object.values(objectToSave);
    //get obj keys
    const keys = Object.keys(objectToSave).join(',');
    //build sql arg heys string like '$1,$2,$3'
    const argKeys = Object.keys(objectToSave).map((obj,index) => `$${index+1}`).join(',');        
    const query = `UPDATE table SET (${keys}) = (${argKeys}) WHERE id = ${dbID}`;
    try {
        const res = await client.query(query, args)
        return true;
    } catch (err) {
        console.log(err.stack)
        return false;
    }
}

Answer 5

创建插入查询

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
    }
}

用法

let data = {firstname : 'hie' , lastname : 'jack', age : 4}
let yo = createInsertQuery('user',data) 
client.query(yo.query, yo.params ,(err,res) =>{
 console.log(res)
})

所以像明智一样，你可以创建更新，删除查询