High frequency calls leads to duplicates with findOrCreate in Waterline & Sails

Question

How to handle high frequency updateOrCreate requests with Waterline in Sails for a Postgresql database ?

I tried to use findOrCreate and then update the item, I tried findOne and then update or create the item, I tried to put a beforeCreate, a beforeValidation hook method to check if the item exists but without any success. Should I add an error handler to get errors from the unique index and try again?

In the Waterline docs, there is a warning about it but no direction to solve this problem.

Thank you for any tips.

Answer 1

Should I add an error handler to get errors from the unique index and try again?

That's going to be the only option until such time as Waterline implements transactions. Something like:

// This will hold the found or created user
var user;

// Keep repeating until we find or create a user, or get an error we dont expect

async.doUntil(

    function findOrCreate(cb) {

        // Try findOrCreate
        User.findOrCreate(criteria, values).exec(function(err, _user) {

            // If we get an error that is not a uniqueness error on the
            // attribute we expect collisions on, bail out of the doUntil
            if (err && 
                (
                    !err.invalidAttributes["myUniqueAttribute"] ||
                    !_.find(err.invalidAttributes["myUniqueAttribute"], {rule: 'unique'})
                )
               ) {
               return cb(err);
            } 

            // Otherwise set the user var
            // It may still be undefined if a uniqueness error occurred;
            // this will just cause doUntil to run this function again
            else {
               user = _user;
               return cb();
            }                
        },

    // If we have a user, we are done.  Otherwise go again.
    function test() {return user},

    // We are done!
    function done(err) {
        if (err) {return res.serverError(err);}
        // "user" now contains the found or created user
    }

});

Not the prettiest, but it should do the trick.