How to safely ‘UPSERT’ in Postgresql with NodeJS

One of the coolest features of Postgresql is INSERT ON CONFLICT, it is a way for PostgreSQL to handle unique constraint violation or attempting to insert a record with duplicate key. In the basic terms a INSERT ON CONFLICT statement can be expressed in this way

INSERT INTO customers (fname, lname, email) VALUES (‘Marga’, ‘Tan’, ‘xxxx@gmail.com') ON CONFLICT (email) DO NOTHING;

The SQL code above tells us that any conflict on email field with existing record with the same email value, do not insert. Another way of using ON CONFLICT

INSERT INTO customers (fname, lname, email) VALUES (‘Marga’, ‘Tan’, ‘xxxx@gmail.com') ON CONFLICT (email) UPDATE SET fname = EXCLUDED.fname, lname = EXCLUDED.lname;

The SQL snippet above updates an existing record with the same email value. Now, let’s add what other unique identifiers this customer has in the database if we’re running an app for an auto repair shop.

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

INSERT INTO customers (fname, lname, email, plate_number) VALUES (‘Marga’, ‘Tan’, ‘xxxx@gmail.com', 'UOK-123') ON CONFLICT (email, plate_number) UPDATE SET fname = EXCLUDED.fname, lname = EXCLUDED.lname, plate_number = EXCLUDED.plate_number;

We can not have two arguments on ON CONFLICT clause (that’s sounds about right in real life right?), so we’re “trapped” in this dilemma on what to do for Oracle database there is even a more complicated MERGE which is similar to UPSERT. The problem actually with these mechanisms is they actually don’t ‘UPSERT’, rather they do ‘INDATE’, you know what I mean? Most UPSERT actions will attempt an insert transaction first before doing an update. And mostly likely will result to annoying and time-wasting problems. However, an age old but not a very well-known practice does the true meaning of ‘UPSERT’. We’ll use NodeJS’ pg library syntax:

let insertQuery = {};
let params = [‘Marga’, ‘Tan’, ‘xxxx@gmail.com', 'UOK-123'];
insertQuery.text = 'INSERT INTO customers (fname, lname, email, plate_number) VALUES ($1, $2, $3, $4)';
insertQuery.values = params;
let updateQuery = {};
updateQuery.text = 'UPDATE customers SET fname = $1, lname = $2, email = $3, plate_number = $4 WHERE email = $5';
updateQuery.value = [‘Marga’, ‘Tan’, ‘xxxx@gmail.com', 'UOK-123', 'xxxx@gmail.com'];
(async () => {
   const client = await myPool.connect(); //assuming myPool is initialized somewhere
   await client.query (updateQuery, (err, result)=>{
      try {
        if (err) throw err;
        if (result.rowCount > 0){
           console.log ('Rows affected: ', result.rowCount);
           return;
         } else {
           client.query(insertQuery, (error, res) =>{
           try {
             if (error) throw error;
             console.log ('Rows affected:', res.rowCount);
           }catch(er){
             console.log(er);
            }finally {
               //do something here
            }
          });
        }
       }catch (e){
         console.log(e);
        }finally{
           client.release();
         }
   });
  })().catch(e => console.log(e));

The code above may look cumbersome but really simple, it will update the record and if the record does not exist as indicated by the rowCount property, then it will insert if the rowCount is zero. This solution works in all types of databases.