postgresql 使用 node-postgres 更新数据的更简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21759852/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easier way to update data with node-postgres?
提问by Joe
I'm using the superb plugin node-postgres, https://github.com/brianc/node-postgres
我正在使用极好的插件 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?
我有这个更新休息电话。我的表中有大约 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 = (), pr_usercode = () WHERE pr_id=()";
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?
有任何想法吗？
回答by srquinn
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 这样的模块
回答by Humoyun Ahmad
Good answers have already been given, but IMHO not good enough in one aspect, they all lacks good abstraction. I will try to provide more abstracted way of updating your data in postgresusing node-postgres.
已经给出了很好的答案，但恕我直言，一方面还不够好，它们都缺乏很好的抽象。我将尝试提供更抽象的方法来更新postgres使用node-postgres.
It is always good practice to follow official documentation, following code structure was taken from node-postgres, you can extend it however you like:
遵循官方文档总是好的做法，以下代码结构取自node-postgres，您可以随意扩展它：
here is mine, this is where you interact with your database
这是我的，这是您与数据库交互的地方
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 `(, )`
*/
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
};
here is helper methods for CRUD operations, they will prepare query text with prepared values:
这是 CRUD 操作的辅助方法，它们将使用准备好的值准备查询文本：
/**
* tableName: `users`
* conditions: { id: 'joe-unique-id', ... }
* data: { username: 'Joe', age: 28, status: 'active', ... }
*
* "UPDATE users SET field_1 = , field_2 = , field_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 = []) => {...}
And finally you can use this in you route handlers without cluttering your codebase:
最后，您可以在路由处理程序中使用它，而不会弄乱您的代码库：
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" });
}
};
Convenient utilities:
方便实用：
const Utils = {};
Utils.isObject = x => x !== null && typeof x === "object";
Utils.isObjEmpty = obj => Utils.isObject(obj) && Object.keys(obj).length === 0;
回答by clay
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，这对我很有用）
回答by vijay
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 : 'Hyman', 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, deletequery
因此，您可以明智地创建更新、删除查询
