PostgreSQL multi-row updates in Node.js

by

The example below is based on library pg-promise, and its method helpers.update:

// library initialization, usually placed in its own module:
const pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL
});

const db = pgp(/*your connection details*/);

// records to be updated:
const updateData = [
    {id: 1, value: 1234},
    {id: 2, value: 5678},
    {id: 3, value: 91011}
];

// declare your ColumnSet once, and then reuse it:
const cs = new pgp.helpers.ColumnSet(['?id', 'value'], {table: 'fit_ratios'});

// generating the update query where it is needed:
const update = pgp.helpers.update(updateData, cs) + ' WHERE v.id = t.id';
//=> UPDATE "fit_ratios" AS t SET "value"=v."value"
//   FROM (VALUES(1,1234),(2,5678),(3,91011))
//   AS v("id","value") WHERE v.id = t.id

// executing the query:
await db.none(update);

This method of generating multi-row updates can be characterized as:

  • very fast, as it relies on type ColumnSet that implements smart caching for query generation
  • completely safe, as all data types are going through the library’s query formatting engine to make sure everything is formatted and escaped correctly.
  • very flexible, due to advanced ColumnConfig syntax supported for the columns definition.
  • very easy to use, due to the simplified interface implemented by pg-promise.

Note that we use ? in front of column id to indicate that the column is part of the condition, but not to be updated. For complete column syntax see class Column and ColumnConfig structure.

Related question: Multi-row insert with pg-promise.

More Related Contents:

  1. ECONNREFUSED for Postgres on nodeJS with dockers
  2. Webpack 4 “size exceeds the recommended limit (244 KiB)”
  3. log all queries that mongoose fire in the application
  4. Combine two OR-queries with AND in Mongoose
  5. How do I change the timeout on a jasmine-node async spec
  6. How to install NodeJS LTS on Windows as a local user (without admin rights)
  7. How to test nodejs backend code with Karma (testacular)
  8. Upload a file to Amazon S3 with NodeJS
  9. sh: 1: node: Permission denied
  10. Waiting for user to enter input in Node.js
  11. Make node.js not exit on error
  12. Route.get() requires callback functions but got a “object Undefined”
  13. Multiple View paths on Node.js + Express
  14. multiple worker/web processes on a single heroku app
  15. SendGrid API Key is not working. “The provided authorization grant is invalid, expired or revoked”
  16. Mongoose – finding subdocuments by criteria
  17. Getting express server to accept CORS request
  18. NodeJS Websocket how to reconnect when server restarts
  19. Node.js – How to get my external IP address in node.js app?
  20. req.session is undefined using express-session
  21. How to resolve fs.existsSync is not a function
  22. Meteor Node Process CPU Usage Nears 100%
  23. How to downgrade to a previous Node version
  24. How to “emitter.setMaxListeners()”?
  25. Express.js – any way to display a file/dir listing?
  26. Error at connecting to MongoDb Atlas Server
  27. How can I retrieve a user’s public IP address via Amazon API Gateway + Lambda (node)
  28. Mongoose retrieving data without _id field
  29. How to use “IN” statement in FilterExpression using array – dynamodb
  30. How do I render an EJS template file in Node.js?

Leave a Comment