Create or Update Sequelize

I'm using Sequelize in my Nodejs project and I found a problem that I'm having a hard time to solve. Basically I have a cron that gets an array of objects from a server than inserts it on my database as a object ( for this case, cartoons ). But if I already have one of the objects, I have to update it.

Basically I have a array of objects and a could use the BulkCreate() method. But as the Cron starts again, it doesn't solve it so I was needing some sort of update with an upsert true flag. And the main issue: I must have a callback that fires just once after all these creates or updates. Does anyone have an idea of how can I do that? Iterate over an array of object.. creating or updating it and then getting a single callback after?

Thanks for the attention

From the docs, you don't need to query where to perform the update once you have the object. Also, the use of promise should simplify callbacks:

Implementation

function upsert(values, condition) {
    return Model
        .findOne({ where: condition })
        .then(function(obj) {
            // update
            if(obj)
                return obj.update(values);
            // insert
            return Model.create(values);
        })
}

Usage

upsert({ first_name: 'Taku' }, { id: 1234 }).then(function(result){
    res.status(200).send({success: true});
});

Note

1. This operation is not atomic.
2. Creates 2 network calls.

which means it is advisable to re-think the approach and probably just update values in one network call and either:

1. Look at the value returned (i.e. rows_affected) and decide what to do.
2. Return success if update operation succeeds. This is because whether the resource exists is not within this service's responsibility.

You can use upsert It's way easier.

> Implementation details: > > * MySQL - Implemented as a single query INSERT values ON DUPLICATE KEY UPDATE values > * PostgreSQL - Implemented as a temporary function with exception handling: INSERT EXCEPTION WHEN unique_constraint UPDATE > * SQLite - Implemented as two queries INSERT; UPDATE. This means that the update is executed regardless of whether the row already > existed or not > * MSSQL - Implemented as a single query using MERGE and WHEN (NOT) MATCHED THEN Note that SQLite returns undefined for created, no > matter if the row was created or updated. This is because SQLite > always runs INSERT OR IGNORE + UPDATE, in a single query, so there > is no way to know whether the row was inserted or not.

Update 07/2019 now with async/await

async function updateOrCreate (model, where, newItem) {
    // First try to find the record
   const foundItem = await model.findOne({where});
   if (!foundItem) {
        // Item not found, create a new one
        const item = await model.create(newItem)
        return  {item, created: true};
    }
    // Found an item, update it
    const item = await model.update(newItem, {where});
    return {item, created: false};
}

I liked the idea of Ataik, but made it a little shorter:

function updateOrCreate (model, where, newItem) {
    // First try to find the record
    return model
    .findOne({where: where})
    .then(function (foundItem) {
        if (!foundItem) {
            // Item not found, create a new one
            return model
                .create(newItem)
                .then(function (item) { return  {item: item, created: true}; })
        }
         // Found an item, update it
        return model
            .update(newItem, {where: where})
            .then(function (item) { return {item: item, created: false} }) ;
    }
}

Usage:

updateOrCreate(models.NewsItem, {slug: 'sometitle1'}, {title: 'Hello World'})
    .then(function(result) {
        result.item;  // the model
        result.created; // bool, if a new item was created.
    });

Optional: add error handling here, but I strongly recommend to chain all promises of one request and have one error handler at the end.

updateOrCreate(models.NewsItem, {slug: 'sometitle1'}, {title: 'Hello World'})
    .then(..)
    .catch(function(err){});

This might be an old question, but this is what I did:

var updateOrCreate = function (model, where, newItem, onCreate, onUpdate, onError) {
    // First try to find the record
    model.findOne({where: where}).then(function (foundItem) {
        if (!foundItem) {
            // Item not found, create a new one
            model.create(newItem)
                .then(onCreate)
                .catch(onError);
        } else {
            // Found an item, update it
            model.update(newItem, {where: where})
                .then(onUpdate)
                .catch(onError);
            ;
        }
    }).catch(onError);
}
updateOrCreate(
    models.NewsItem, {title: 'sometitle1'}, {title: 'sometitle'},
    function () {
        console.log('created');
    },
    function () {
        console.log('updated');
    },
    console.log);

User.upsert({ a: 'a', b: 'b', username: 'john' })

It will try to find record by hash in 1st param to update it, if it will not find it - then new record will be created

Here is example of usage in sequelize tests

it('works with upsert on id', function() {
	return this.User.upsert({ id: 42, username: 'john' }).then(created => {
		if (dialect === 'sqlite') {
			expect(created).to.be.undefined;
		} else {
			expect(created).to.be.ok;
		}

		this.clock.tick(1000);
		return this.User.upsert({ id: 42, username: 'doe' });
	}).then(created => {
		if (dialect === 'sqlite') {
			expect(created).to.be.undefined;
		} else {
			expect(created).not.to.be.ok;
		}

		return this.User.findByPk(42);
	}).then(user => {
		expect(user.createdAt).to.be.ok;
		expect(user.username).to.equal('doe');
		expect(user.updatedAt).to.be.afterTime(user.createdAt);
	});
});

Sound likes you want to wrap your Sequelize calls inside of an async.each.

This can be done with the custom event emitter.

Assuming your data is in a variable called data.

new Sequelize.Utils.CustomEventEmitter(function(emitter) {
    if(data.id){
        Model.update(data, {id: data.id })
	    .success(function(){
		    emitter.emit('success', data.id );
	    }).error(function(error){
		    emitter.emit('error', error );
	    });
    } else {
	    Model.build(data).save().success(function(d){
		    emitter.emit('success', d.id );
	    }).error(function(error){
		    emitter.emit('error', error );
	    });
    }
}).success(function(data_id){
    // Your callback stuff here
}).error(function(error){
   // error stuff here
}).run();  // kick off the queries

you can use findOrCreate and then update methods in sequelize. here is a sample with async.js

async.auto({
   getInstance : function(cb) {
      Model.findOrCreate({
        attribute : value,
        ...
      }).complete(function(err, result) {
        if (err) {
          cb(null, false);
        } else {
          cb(null, result);
        }
      });
    },
    updateInstance : ['getInstance', function(cb, result) {
      if (!result || !result.getInstance) {
        cb(null, false);
      } else {
        result.getInstance.updateAttributes({
           attribute : value,
           ...
        }, ['attribute', ...]).complete(function(err, result) {
          if (err) {
            cb(null, false);
          } else {
            cb(null, result);
          }
        });
       }
      }]
     }, function(err, allResults) {
       if (err || !allResults || !allResults.updateInstance) {
         // job not done
       } else {
         // job done
     });
});

Here is a simple example that either updates deviceID -> pushToken mapping or creates it:

var Promise = require('promise');
var PushToken = require("../models").PushToken;

var createOrUpdatePushToken = function (deviceID, pushToken) {
  return new Promise(function (fulfill, reject) {
    PushToken
      .findOrCreate({
        where: {
          deviceID: deviceID
        }, defaults: {
          pushToken: pushToken
        }
      })
      .spread(function (foundOrCreatedPushToken, created) {
        if (created) {
          fulfill(foundOrCreatedPushToken);
        } else {
          foundOrCreatedPushToken
            .update({
              pushToken: pushToken
            })
            .then(function (updatedPushToken) {
              fulfill(updatedPushToken);
            })
            .catch(function (err) {
              reject(err);
            });
        }
      });
  });
};