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 Overflow
Top answer
1 of 5
29

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

2 of 5
9

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;
🌐
Stack Overflow
stackoverflow.com › questions › 41477987 › update-query-in-postgresql-using-nodejs
node.js - update query in postgresql using nodejs - Stack Overflow
function updateQuery(tablename, id, data, condition) { var query = ['UPDATE']; query.push(tablename) query.push('SET'); var set = []; Object.keys(data).forEach(function(key, i) { if ((key == 'university_id') || (key == 'name') || (key == 'branch') ...
Discussions

How to write insert update query in Node Js postgres?
There was an error while loading. Please reload this page · I have an array of objects data: assignmentData More on github.com
🌐 github.com
1
March 22, 2020
node.js - How do I update json data in postgres sql in nodejs? - Stack Overflow
my data structure schema is like users( id serial primary key, data json ); I want to update data key. I'm using node-postgres library and So far I have tried something like this: pg.conn... More on stackoverflow.com
🌐 stackoverflow.com
Update todo in node js postgresql
Here is the syntax documentation for UPDATE: https://www.postgresql.org/docs/14/sql-update.html So basically you need to SET field1=$1, field2=$2, etc... More on reddit.com
🌐 r/node
4
0
October 28, 2022
How to update parameterized SELECT query in node.js with postgres
I have a problem with fetching specific columns from the database. I want to fetch balance for a user with provided user_id and currency. And I'm getting >[ { '?column?': 'USD' } ] for provided More on stackoverflow.com
🌐 stackoverflow.com
🌐
Dirask
dirask.com › posts › Node-js-PostgreSQL-Update-query-jMmJdj
Node.js - PostgreSQL Update query
UPDATE query in Node.js. Note: at the end of this article you can find database preparation SQL queries. const { Client } = require('pg'); const client = new Client({ host: '127.0.0.1', user: 'postgres', database: 'database_name', password: 'password', port: 5432, }); const updateUser = async (userName, userRole, userId) => { const query = `UPDATE "users" SET "name" = $1, "role" = $2 WHERE "id" = $3`; try { await client.connect(); // gets connection await client.query(query, [userName, userRole, userId]); // sends queries return true; } catch (error) { console.error(error.stack); return false; } finally { await client.end(); // closes connection } }; updateUser('Christopher', 'admin', '2').then(result => { // userName, userRole, userId if (result) { console.log('User updated'); } });
🌐
YouTube
youtube.com › devnami
Node.JS How to UPDATE query in PostgreSQL Database - YouTube
Learn How to UPDATE query on PostgreSQL Database in Node.js.
Published   March 8, 2019
Views   1K
🌐
GitHub
github.com › brianc › node-postgres › issues › 2143
How to write insert update query in Node Js postgres? · Issue #2143 · brianc/node-postgres
March 22, 2020 - I wanted to insert into table and my query is look like: const loadAssignmentStatus = { name: 'insert-assignment-records', text: 'INSERT INTO public.ntnx_ramp_task_status (task_id, emp_id, status_id)' + ' SELECT task_id, emp_id, status' + ' FROM jsonb_to_recordset($1) AS t (task_id int, emp_id int, status int)' + ' ON CONFLICT (task_id, emp_id) DO UPDATE SET status_id = SELECT status FROM jsonb_to_recordset($2) AS a (status int)', values: [], }; router.post('/', async (req, res) => { console.log(req.body) const assignmentData = req.body try { const {rowCount} = await db.query(loadAssignmentStatus, [JSON.stringify(assignmentData), JSON.stringify(assignmentData.status)]) console.log(rowCount) res.send(rowCount) } catch (err) { console.log("error: ", err); } });
Author   brianc
🌐
Reddit
reddit.com › r/node › update todo in node js postgresql
r/node on Reddit: Update todo in node js postgresql
October 28, 2022 -

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); } });`

Find elsewhere
🌐
YouTube
youtube.com › watch
Update Data in PostgreSQL database using Node JS and Postman tutorial | REST API - YouTube
How to update or put data by using id in postgresql database in rest api using node js and postman is shown #restapi #nodejs #postgresql
Published   July 15, 2024
🌐
Stack Overflow
stackoverflow.com › questions › 72916465 › how-to-update-parameterized-select-query-in-node-js-with-postgres
How to update parameterized SELECT query in node.js with postgres
0 Postgres, nodejs SELECT query · 4 How do I update json data in postgres sql in nodejs? 2 Postgresql UPDATE on multiple columns in the same table · 2 PostgreSQL UPDATE query with Node.js throws error · 4 Update multiple columns and values node postgres ·
🌐
Stack Overflow
stackoverflow.com › questions › 76641497 › how-to-update-all-table-in-postgresql
node.js - How to update all table in postgreSQL - Stack Overflow
Probably quickest way is to download the new weekly inventory into a temporary/staging table and use that in a sub-select to update the existing table like : update existing_table as e set (inventory, area, description) = (select inventory, ...
🌐
GitHub
gist.github.com › dennbagas › 8f1fe6a7ef77dd39f6ba6f4ee4a88970
Postgres UPDATE multiple Row and Value Node.js · GitHub
Postgres UPDATE multiple Row and Value Node.js. GitHub Gist: instantly share code, notes, and snippets.
🌐
Stack Overflow
stackoverflow.com › questions › 61494634 › node-js-postgres-update-json-column-with-sql-query
Node js + Postgres, update Json column with SQL query
I need to specifically update the coreEssentials array with another value, so I have been using the set 'json' = ? SQL query where I paste in the entire column with my changes in the specific field and it works (manually in the db). The only issues are, I need to do make a SQL call to SELECT the json column for a specific id first, (long story, but a backend application generates some data into the JSON (the coreEssentials key/object I need to update) then puts it into the data, then after I need to update). I was doing this manually in my Postgresql GUI (DBbeaver) and my query simply looks like this:
🌐
Medium
jaredpogi.medium.com › how-safely-upsert-in-postgresql-with-nodejs-44487b5aa90d
How safely UPSERT in Postgresql with NodeJS | by Jared Odulio | Medium
June 23, 2019 - INSERT INTO customers (fname, lname, email, plate_number) VALUES (‘Marga’, ‘Tan’, ‘xxxx@gmail.com', 'UOK-123') ON CONFLICT (email) UPDATE SET fname = EXCLUDED.fname, lname = EXCLUDED.lname, plate_number = EXCLUDED.plate_number; If the plate_number field above also has unique constraint defined, the ON CONFLICT will throw an exception and and will implicitly ROLLBACK the transaction. Postgresql will not allow us to do this
🌐
Stack Overflow
stackoverflow.com › questions › 49150477 › update-postgresql-database-with-node-js
Update PostgreSQL database with Node.js - Stack Overflow
March 7, 2018 - MyRow.rows.forEach(function(row){ name_.push(row.name); id_.push(row.id); }); var updateAccount = 'UPDATE myTable SET myRow =$1 '+ 'where id = $2' client.query(updateAccount,[name_.toString(),id_.toString()],false).then(function(err,result){ if (err) { console.log(err.stack); } else { console.log(name_) } }); ... My guess is that you should enclose your values in single quotes. var updateAccount = "UPDATE myTable SET myRow = '$1' "+ "where shema.id = '$2'" In your example, your values are interpreted as column names, so PostgreSQL asks you to specify a table containing these.
🌐
w3resource
w3resource.com › PostgreSQL › snippets › postgresql-node-setup.php
PostgreSQL with Node.js: Setup, Configuration, and CRUD Examples
December 23, 2024 - // Function to update a user’s email const updateUser = async (id, newEmail) => { const updateQuery = `UPDATE users SET email = $1 WHERE id = $2 RETURNING *`; try { const res = await client.query(updateQuery, [newEmail, id]); console.log('User ...
🌐
JavaScript in Plain English
javascript.plainenglish.io › how-to-write-postgresql-queries-for-node-js-714393c3fc04
How to Write PostgreSQL Queries for Node.js | by Ryan Flynn | JavaScript in Plain English
April 1, 2021 - We then send a json response with the data we just updated. app.post("/todos", async (req, res) => {try{const {description} = req.body;//This takes our description that we sent in and inserts into the todo tableconst newTodo = await pool.query("INSERT INTO todo (description) VALUES ($1) RETURNING *", [description]);res.json(newTodo.rows[0]);} catch(error){console.log(error.message)}});
🌐
Stack Overflow
stackoverflow.com › questions › 71567663 › how-to-update-multiple-rows-in-node-postgresql-with-prepared-statement
node.js - How to update multiple rows in node postgresql with prepared statement - Stack Overflow
March 22, 2022 - const query = ` UPDATE user AS u SET point = u2.point FROM ( VALUES ($1, $2) ($3, $4) ) AS u2( id, point) WHERE u2.id = u.id`; Or for Node versions earlier than 4+ (ES6) you may use:
🌐
node-postgres
node-postgres.com › features › queries
Parameterized query
1 month ago - The query config object allows for a few more advanced scenarios: PostgreSQL has the concept of a prepared statement. node-postgres supports this by supplying a name parameter to the query config object. If you supply a name parameter the query execution plan will be cached on the PostgreSQL server on a per connection basis.