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
Answer from srquinn on Stack OverflowYou 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
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 postgres using 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:
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 `(
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
};
here is helper methods for CRUD operations, they will prepare query text with prepared values:
/**
* 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) => `
${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) => `
${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;
How to write insert update query in Node Js postgres?
node.js - How do I update json data in postgres sql in nodejs? - Stack Overflow
Update todo in node js postgresql
How to update parameterized SELECT query in node.js with postgres
Videos
How to update todo if I have this than one item in req.body ?
This is just for description, but I don't know how to do it for title and date ?
`router.put("/todos/:id", authorize, async (req, res) => {
try {
const { id } = req.params;
const { description, title, date } = req.body;
const updateTodo = await pool.query(
"UPDATE todos SET description = $1 WHERE todo_id = $2 AND user_id = $3 RETURNING *",
[description, id, req.user.id]
);
if (updateTodo.rows.length === 0) {
return res.json("This todo is not yours");
}
res.json("Todo was updated");} catch (err) { console.error(err.message); } });`
Update from 2023:
You are now able to do this for example with the pg-package and a lock on row-level.
class DBConnector {
constructor(){
const { Client } = require('pg');
this.client = new Client({
user: process.env.DB_USERNAME,
host: process.env.DB_HOST,
database: process.env.DATABASE,
password: process.env.DB_PASSWORD,
port: process.env.DB_PORT,
});
this.client.connect();
}
async acquireLockForRow(table, rowId) {
await this.client.query('START TRANSACTION');
try {
// If it is locked, it throws an error
const res = await this.client.query('SELECT id FROM ' + table + ' WHERE id=' + rowId + ' FOR UPDATE SKIP LOCKED;');
// If there is no lock
if (res.rows[0].id === rowId) {
// Release the lock after 5 minutes (if not already done, just to avoid forgetting to unlock it)
setTimeout(async () => {await this.releaseLock()}, 1000 * 60 * 5);
}
} catch(e) {
return "Could not obtain";
}
}
async releaseLock() {
await this.client.query('COMMIT;');
}
}
As of today, there is no implementation for this within Node JS platform, so your only viable option is to implement it within a Postgres function and then just call it from Node JS.