Waterline ORM equivalent of insert on duplicate key update

Question

I have a table user_address and it has some fields like

attributes: {
  user_id: 'integer',
  address: 'string' //etc.
}

currently I'm doing this to insert a new record, but if one exists for this user, update it:

UserAddress
  .query(
    'INSERT INTO user_address (user_id, address) VALUES (?, ?) ' +
      'ON DUPLICATE KEY UPDATE address=VALUES(address);',
    params,
    function(err) {
       //error handling logic if err exists
    }

Is there any way to use the Waterline ORM instead of straight SQL queries to achieve the same thing? I don't want to do two queries because it's inefficient and hard to maintain.

Answer 1

The answer above is less than ideal. It also has the method as part of the attributes for the model, which is not correct behavior.

Here is what the ideal native solution looks like that returns a promise just like any other waterline model function would:

module.exports = {
  attributes: {
    user_id: 'integer',
    address: 'string'
  },
  updateOrCreate: function (user_id, address) {
    return UserAddress.findOne().where({user_id: user_id}).then(function (ua) {
      if (ua) {
        return UserAddress.update({user_id: user_id}, {address: address});
      } else {
        // UserAddress does not exist. Create.
        return UserAddress.create({user_id: user_id, address: address});
      }
    });
  }
}

Then you can just use it like:

UserAddress.updateOrCreate(id, address).then(function(ua) {
   // ... success logic here
}).catch(function(e) {
   // ... error handling here
});