Upsert in Postgres using node.js

Question

I'm trying to do an insert or update in a postgres database using node.js with pg extension (version 0.5.4).

So far I have this code: (...)

client.query({
            text: "update users set is_active = 0, ip = $1 where id=$2",
            values: [ip,id]
        }, function(u_err, u_result){
            debug(socket_id,"update query result: ",u_result);
                debug(socket_id,"update query error: ",u_err);

                    date_now = new Date();
            var month = date_now.getMonth() + 1;

            if(!u_err){

                client.query({
                    text: 'insert into users (id,first_name,last_name,is_active,ip,date_joined) values' +
                    '($1,$2,$3,$4,$5,$6)',
                    values: [
                            result.id, 
                            result.first_name,
                            result.last_name,
                            1,
                            ip,
                            date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds()
                            ]
                }, function(i_err, i_result){
                    debug(socket_id,"insert query result: ",i_result);
                    debug(socket_id,"insert query error: ",i_err);
                });
            }
        });

The problem is that, although both queries work the problem is always running both instead of only running the insert function if the update fails.

The debug functions in code output something like:

UPDATE

Object { type="update query result: ", debug_value={...}}
home (linha 56)
Object { type="update query error: ", debug_value=null}
home (linha 56)
Object { type="insert query result: "}
home (linha 56)
Object { type="insert query error: ", debug_value={...}}

Insert

Object { type="update query result: ", debug_value={...}}
home (linha 56)
Object { type="update query error: ", debug_value=null}
home (linha 56)
Object { type="insert query result: ", debug_value={...}}
home (linha 56)
Object { type="insert query error: ", debug_value=null}

** EDIT **

ANSWER FROM node-postgres developer:

It's possible to retrieve number of rows affected by an insert and update. It's not fully implemented in the native bindings, but does work in the pure javascript version. I'll work on this within the next week or two. In the mean time use pure javascript version and have a look here:

https://github.com/brianc/node-postgres/blob/master/test/integration/client/result-metadata-tests.js

** END EDIT **

Can anyone help?

Stubblefield

Answer 1

The immediate answer to your question is to use a stored procedure to do an upsert.

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Something like this works fine with the pg module.

client.query({
  text: "SELECT upsert($1, $2, $3, $4, $5, $6)"
  values: [ obj.id, 
            obj.first_name,
            obj.last_name,
            1,
            ip,
            date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds()
          ]
}, function(u_err, u_result){
  if(err) // this is a real error, handle it

  // otherwise your data is updated or inserted properly
});

Of course this assumes that you're using some kind of model object that has all the values you need, even if they aren't changing. You have to pass them all into the upsert. If you're stuck doing it the way you've shown here, you should probably check the actual error object after the update to determine if it failed because the row is already there, or for some other reason (which is real db error that needs to be handled).

Then you've gotta deal with the potential race condition between the time your update failed and the time your insert goes through. If some other function tries to insert with the same id, you've got a problem. Transactions are good for that. That's all I got right now. Hope it helps.

Gate

Hi Marco!! Thanks for the answer! I got in touch with the developer of pg module for node.js and I eddited the question to feature his response! Regarding your answer, this was my initial intention but as I'm developing using heroku and need taps to migrate the database I have a problem as I think taps can't export stored procedures. (I can be wrong though) anyway can you enlighten me about the performance issues on stored procedures? About the concurrency issues I don't think they applybut if you could post some link to ilustrate Transictions it would be perfect... ;) Thanks once again!! – Stubblefield

Answer 2

I had this issue when connecting to a PG instance using the JDBC. The solution I ended up using was:

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

The update does nothing if the record doesn't exist and the insert does nothing if the record does exist. It works pretty well and is an SQL based solution vs a stored procedure.

Here's the initial question: Insert, on duplicate update in PostgreSQL?

Orna

I actually implemented something along those lines too. But I test the response of the first query and only run the second one if no row was afected. This is/was only supported in the native js mode of the github.com/brianc/node-postgres which is what i'm using. Thanks for taking the time to answer!! – Stubblefield

Answer 3

I have an electronic component database to which I add components that I either salvage from e-waste or buy as new, and the way I did it was:

const upsertData = (request, response) => {
  const {
    category, type, value, unit, qty,
  } = request.body;

  pool.query(`DO $$                  
    BEGIN 
        IF EXISTS
            ( SELECT 1
              FROM   elab 
              WHERE  category='${category}'
              AND    type='${type}'
              AND    value='${value}'
              AND    unit='${unit}'
            )
        THEN
            UPDATE elab 
            SET qty = qty + ${qty}
            WHERE   category='${category}'
            AND    type='${type}'
            AND     value='${value}'
            AND     unit='${unit}';
        ELSE
            INSERT INTO elab
            (category, type, value, unit, qty)
            values ('${category}', '${type}', '${value}', '${unit}', ${qty});
        END IF ;
    END
  $$ ;`, (error, results) => {
    if (error) {
      throw error;
    }
    response.status(201).send('Task completed lol');
  });
};

The reason for this was that the only unique column any entry had was the ID, which is automatically updated, none of the other columns are unique only the whole entry is e.g. you can have a 100 kOhm resistor as a potentiometer or a "normal" one - and you can have a potentiometer with different values than 100 kOhm so only the whole entry is unique.

Gamut