Sails.js model - insert or update records

Question

Been trying out Sails.js and I'm writing an app that imports data from a third-party API and saves in into a MySQL table. Basically I'm trying to sync data over to my app for further analysis, updating my records or creating new records as needed.

I've looked through Sails' API and I see methods to find, create and update records but no built-in method to insert/update records based on the situation. Did I overlook something, or will I need to implement this myself?

If I have to implement this myself, does anyone know of a good design pattern for insert/update?

This is what I think it might look like…

_.each(importedRecords, function(record){
  MyModel.find({id: record.id}).exec(function findCB(err, found){
    if(found.length){
      MyModel.update(record.id, task).exec(function(err, updated){
        if(err) { //returns if an error has occured, ie id doesn't exist.
          console.log(err);
        } else {
          console.log('Updated MyModel record '+updated[0].name);
        }
      });
    }else{
       MyModel.create(record).exec(function(err, created){
         if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
           console.log(err);
         } else {
           console.log('Created client record '+created.name);
         }
       });
     }
   });
 });

Am I headed in the right direction, or is there a more elegant solution?

Also, I'm dealing with a lot of different models in this app, which would mean recreating this block of code across each of my models. Is there a way I can extend the base Model object to add this functionality for all models.

Thanks, John

Answer 1

I have rewritten Critical Mash code, so its way less code, and more generic. Now you can call updateOrCreate the same way you call findOrCreate. And it looks like that:

module.exports.models = {
    updateOrCreate: function(criteria, values){
        var self = this; // reference for use by callbacks
        // If no values were specified, use criteria
        if (!values) values = criteria.where ? criteria.where : criteria;

        return this.findOne(criteria).then(function (result){
          if(result){
            return self.update(criteria, values);
          }else{
            return self.create(values);
          }
        });
    }
};

So that way you can write criteria the same way. no need to work on the key, and the code is so much simpler.

Answer 2

Sails 0.10 has findOrCreate(criteria, attributes, callback), see Sails Docs.

criteria is the search criteria for the "find" bit (same syntax as find()).

attributes is the data used if it's not found for the "create" bit (same syntax as create()).

Here's an example:

MyModel.findOrCreate({name:'Walter'},{name:'Jessie'}, function (err, record){ console.log('What\'s cookin\' '+record.name+'?');

Also note that there are other composite query methods documented in the Waterline repository (see the tests for examples) and Waterline documentation:

Each of the following basic methods are available by default on a Collection instance:

• findOne
• find
• create
• update
• destroy
• count

In addition you also have the following helper methods:

• createEach
• findOrCreateEach *<-- Looks like what you need (hint use arrays of criteria / attributes) *
• findOrCreate
• findOneLike
• findLike
• startsWith
• endsWith
• contains

Based on your Collection attributes you also have dynamic finders. So given a name attribute the following queries will be available:

• findOneByName
• findOneByNameIn
• findOneByNameLike
• findByName
• findByNameIn
• findByNameLike
• countByName
• countByNameIn
• countByNameLike
• nameStartsWith
• nameEndsWith
• nameContains

As for overlooking something, well it's out there but it's not in the main Sails documentation yet so the answer is yes and no, so don't sweat it :)

Answer 3

Your solution is right. There is no other way to do this with waterline (the ORM of sails.js). But several databases have functions for this case:

MySQL

REPLACE INTO table SET id = 42, foo = 'bar'; (with a primary or unique key. Pretty shitty if you use auto_increment ;-)

In Waterline you can use the Model.query()-Function to execute direct SQL (see: http://sailsjs.org/#/documentation/reference/waterline/models/query.html)

MongoDB

db.collection.update(
  <query>,
  <update>,
  { upsert: true }
)

The upsert flag means: If you can't update it because you didn't find anything with the query create this element!

In Waterline you can use the Model.native()-Function to execute direct mongoDB-Commands (see: http://sailsjs.org/#/documentation/reference/waterline/models/native.html)

Conclusion

You you need fast execution (and of corse if you have many many request) I would suggest to use the native/sql-functions. But in general I'm really a fan of the flexibility of an ORM-System and every time you use database-specific functions it's harder to handle.

Answer 4

Thanks user3351722, I prefer using the ORM system as well. I just tried implementing the above solution as a general Model method. (Based on Inherit attributes and lifecycle functions of Sails.js models).

I edited config/models.js and added a new function insertOrUpdate that takes the name of the index column, the data I want to insert or update and a callback function.

module.exports.models = {
  insertOrUpdate: function(key, record, CB){
    var self = this; // reference for use by callbacks
    var where = {};
    where[key] = record[key]; // keys differ by model
    this.find(where).exec(function findCB(err, found){
      if(err){
        CB(err, false);
      }
      // did we find an existing record?
      if(found && found.length){
        self.update(record[key], record).exec(function(err, updated){
          if(err) { //returns if an error has occured, ie id doesn't exist.
            CB(err, false);
          } else {
            CB(false, found[0]);
          }
        });
      }else{
        self.create(record).exec(function(err, created){
          if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
            CB(err, false);
          } else {
            CB(false, created);
          }
        });
      }
    });
  }
};

This would only work with tables/collections that have an index. I don't know how to introspect the key name from a model in waterline so I pass in into the field name as a string.

Here's how you might use the method inside a controller…

_.each(clients, function(client){
  Client.insertOrUpdate('client_id', client, function(err, updated){
    if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
      sails.log(err);
    } else {
      sails.log('insertOrUpdate client record ', updated.organization); //+updated[0].name
    }
  });
});

I've tried this method with three different models and so far, so good. They're all MySQL tables and the models all have a defined index. Your milage may very if you're using a different datastore.

If anyone sees a way to improve upon this, please let us know.

Answer 5

This is how I do it: I extend the config/models.js to include the functionality and it checks to see if the adapter has the correct methods. You can call it as a promise or normally.

    var normalize = require('sails/node_modules/waterline/lib/waterline/utils/normalize');
    var hasOwnProperty = require('sails/node_modules/waterline/lib/waterline/utils/helpers').object.hasOwnProperty;
    var defer = require('sails/node_modules/waterline/lib/waterline/utils/defer');
    var noop = function() {};

module.exports.models = {

    /**
     * [updateOrCreate description]
     * @param  {[type]}   criteria [description]
     * @param  {[type]}   values   [description]
     * @param  {Function} cb       [description]
     * @return {[type]}            [description]
    */

    updateOrCreate: function (criteria, values, cb) {
        var self = this; 
        var deferred;

        // Normalize Arguments
        if(typeof cb !== 'function') {
           deferred = defer();
        }
        cb = cb || noop;

        criteria = normalize.criteria(criteria);

        if (criteria === false) {
            if(deferred) {
                deferred.resolve(null);
            }
            return cb(null, []);
        }
        else if(!criteria) {
            if(deferred) {
                deferred.reject(new Error('No criteria or id specified!'));
            }
            return cb(new Error('No criteria or id specified!'));
        }

        // Build Default Error Message
        var errFind = 'No find() method defined in adapter!';
        var errUpdate = 'No update() method defined in adapter!';
        var errCreate = 'No create() method defined in adapter!';

        // Find the connection to run this on
        if(!hasOwnProperty(self.adapter.dictionary, 'find')){
            if(deferred) {
                deferred.reject(errFind);
            }
            return cb(new Error(errFind));
        }
        if(!hasOwnProperty(self.adapter.dictionary, 'update')){ 
            if(deferred) {
                deferred.reject(errUpdate);
            }
            return cb(new Error(errUpdate));
        }
        if(!hasOwnProperty(self.adapter.dictionary, 'create')) {
            if(deferred) {
                deferred.reject(errCreate);
            }
            return cb(new Error(errCreate));
        }

        var connNameFind = self.adapter.dictionary.find;
        var adapterFind = self.adapter.connections[connNameFind]._adapter;

        var connNameUpdate = self.adapter.dictionary.update;
        var adapterUpdate = self.adapter.connections[connNameUpdate]._adapter;

        var connNameCreate = self.adapter.dictionary.create;
        var adapterCreate = self.adapter.connections[connNameCreate]._adapter;

        adapterFind.find(connNameFind, self.adapter.collection, criteria, normalize.callback(function before (err, results){

            if (err) {
                if(deferred) {
                    deferred.reject(err);
                }
                return cb(err);
            }

            if(results && results.length > 0){
                adapterUpdate.update(connNameUpdate, self.adapter.collection, criteria, values, normalize.callback(function afterwards (err, updatedRecords) {
                    if (err) {
                        if(deferred) {
                            deferred.reject(err);
                        }
                        return cb(err);
                    }
                    deferred.resolve(updatedRecords[0]);
                    return cb(null, updatedRecords[0]);
                }));
            }else{
                adapterCreate.create(connNameCreate, self.adapter.collection, values, normalize.callback(function afterwards (err, createdRecord) {
                    if (err) {
                        if(deferred) {
                            deferred.reject(err);
                        }
                        return cb(err);
                    }
                    deferred.resolve(createdRecord);
                    return cb(null, createdRecord);
                }));
            }
        }));

        if(deferred) {
            return deferred.promise;
        }
    }
}