Increment a field in model using remote method in loopback?

Question

There is a field called counter in a model and whenever if I call a custom remote method like

Request URL
http://loopback:3000/api/models/increment_counter

Request body 
EMPTY

Response
{
   "counter" : [Value after Increment]
}

Currently to increment First i have to get the counter value from db and increment it one and update the couter value, This involves two queries, Is it possible to do it in a single NodeAPI call like the below mysql query.I am currently using Mysql as DB.

mysql_query("
    UPDATE model 
    SET counter = counter + 1
    WHERE model_id = '1'
");

Thank you

Answer 1

Given the MySQL syntax you want, you seem to need an atomic counter.

database transactions

With the MySQL connector, you can use database transactions. It is supported by the MySQL connector. It's a bit overkill for just atomic counters but it will get the job done.

Here is an example, inside a custom remote method

MyModel.someRemoteMethodForIncrementing = function(callback) {
  // Start the transaction
  MyModel.beginTransaction({
    isolationLevel: MyModel.Transaction.READ_COMMITTED
  }, function(err, tx) {
    // Retrieve the current counter value as part of the transaction
    MyModel.findById(id, {
      transaction: tx
    }, function(err, data) {
      if (err) {
        console.log(err);
        return tx.rollback(function(err) {
          callback(err);
        });
      }

      // Increment the counter as part of the transaction
      var inc = data.counter + 1;

      MyModel.updateAttributes({
        counter: inc
      }, {
        transaction: tx
      }, function(err, newData) {
        if (err) {
          console.log(err);
          return tx.rollback(function(err) {
            callback(err);
          });
        }

        // Commit the transaction to make it happen
        tx.commit(function(err) {
          if (err) return callback(err);
          // Counter should have been incremented
          callback();
        });
      });
    });
  });
};

I haven't tested it but it should work, let me know

extented operators

In the future, you will be able to use the $inc (increment) extended operator but so far it's only supported by MongoDB connector, not MySQL.

Just for reference, here is the syntax (works only with MongoDB)

PUT api/Model/:id?filter={"$inc":{"name":propertyToIncrement, "count":incrementAmount}} 

There is an ongoing PR that I am trying to get landed to get MySQL support, but there are many things to be done before it can get merged.