UPSERT data from NodeJS to PostgreSQL table

If you have a json array of key value pairs, and would like to UPSERT them into a PostgreSQL table, then the following helper function will come in handy. We’ll put the helper function in a file (pg-helper.js). The code for it is given below:

'use strict';

const pgp = require('pg-promise')({
  /* initialization options */
  capSQL: true // capitalize all generated SQL
});


const credentials = {
    user: "user",
    host: "abc.pqrs.com",
    database: "postgres",
    password: "password",
    port: 5432,
  };


exports.bulk_upsert = async function(columns_list, conflict_list, table_name, data_to_insert, conflict_update = true){
  
  const cs = new pgp.helpers.ColumnSet(columns_list, { table: table_name });
  var conflict_columns_list = conflict_list.split(',');

  const db = pgp(credentials);
  //UPSERT query

  var onConflict = ' ON CONFLICT('+conflict_list+') DO NOTHING'
  if(conflict_update){
    onConflict = ' ON CONFLICT('+conflict_list+') DO UPDATE SET ' +
            cs.assignColumns({from: 'EXCLUDED', skip: conflict_columns_list});
  }
  const query = pgp.helpers.insert(data_to_insert, cs) + onConflict;

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

Make sure to overwrite the credentials with the correct values in the above code. We are using the pg-promise package for our postgres integration. As you can see, bulk_upsert is the function of interest here. It asks for the list of columns that will be updated, the list of columns on which to check for conflicts (make sure to have a UNIQUE constraint on this set of columns), the table name, the data to be inserted, and the behavior on conflict.

By default, the bulk_upsert function updates the row if a conflict is encountered (it updates all columns of the row except the ones on which the conflict is encountered, see the skip argument in the cs. assignColumns function). However, you can also choose to ignore the conflicted rows by setting conflict_update to false. In this case, the conflicted row will not be inserted or updated, just discarded.

The code below shows an example of using the bulk_upsert function

let pg_helper = require('./pg_helper')

let columns_list = ['name','roll_no','year','grade','created_at']


exports.handler = async function (event, context) {

  let name = event.body.name;
  let roll_no = event.body.roll_no;
  let year = event.body.year;
  let grade = event.body.grade;

  let data_to_insert = [ //This could have multiple entries, one for each row
      {
        roll_no: roll_no,
        name:name,
        year: year,
        grade: grade
        created_at: new Date(),
      }
    ]
    let conflict_list = "roll_no,year"
    if (data_to_insert.length > 0) {
      await rds_helper.bulk_upsert(columns_list, conflict_list, 'student_details', data_to_insert, true)
    }
}

As you can see, in the example above, whenever a request is received, the data is either inserted in the database table, or updated if the roll_no and year combination is already present. It is mandatory that there is a UNIQUE constraints on the (roll_no, year) set.

That’s it. That’s your UPSERT function

---

I hope you liked this article. For more articles on IoT in general, check out https://iotespresso.com/