CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Thiago Miranda de Oliveira
Thiago Miranda de Oliveira

Reputation: 5920

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

Upvotes: 78

Views: 169946

Answers (10)

Alvaro Silvino
Alvaro Silvino

Reputation: 9733

You can use upsert It's way easier.

An example from this answer:

const [record, created] = await Model.upsert(
  { id: 1, name: 'foo' }, // Record to upsert
  { returning: true }     // Return upserted record
);

And an example from the documentation (note {returning: true} is the default value, so can be omitted):

const [instance, created] = await MyModel.upsert({});

Insert or update a single row. An update will be executed if a row which matches the supplied values on either the primary key or a unique key is found. Note that the unique index must be defined in your sequelize model and not just in the table. Otherwise you may experience a unique constraint violation, because sequelize fails to identify the row that should be updated.

Implementation details:

  • MySQL - Implemented with ON DUPLICATE KEY UPDATE
  • PostgreSQL - Implemented with ON CONFLICT DO UPDATE. If update data contains PK field, then PK is selected as the default conflict key. Otherwise first unique constraint/index will be selected, which can satisfy conflict key requirements.
  • SQLite - Implemented with ON CONFLICT DO UPDATE
  • MSSQL - Implemented as a single query using MERGE and WHEN (NOT) MATCHED THEN

Upvotes: 32

Eli Zatlawy
Eli Zatlawy

Reputation: 1012

2022 update:

You can use the upsert function:

https://sequelize.org/api/v6/class/src/model.js~model#static-method-upsert

Insert or update a single row. An update will be executed if a row which matches the supplied values on either the primary key or a unique key is found. Note that the unique index must be defined in your sequelize model and not just in the table. Otherwise you may experience a unique constraint violation, because sequelize fails to identify the row that should be updated.

Implementation details:

MySQL - Implemented with ON DUPLICATE KEY UPDATE`

PostgreSQL - Implemented with ON CONFLICT DO UPDATE. If update data contains PK field, then PK is selected as the default conflict key. Otherwise first unique constraint/index will be selected, which can satisfy conflict key requirements.

SQLite - Implemented with ON CONFLICT DO UPDATE

MSSQL - Implemented as a single query using MERGE and WHEN (NOT) MATCHED THEN Note that Postgres/SQLite returns null for created, no matter if the row was created or updated

Upvotes: 3

Taku
Taku

Reputation: 5928

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.

Upvotes: 114

Nikolay Podolnyy
Nikolay Podolnyy

Reputation: 1091

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);
    });
});

Upvotes: 11

Simon Fakir
Simon Fakir

Reputation: 1802

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){});

Upvotes: 25

Ateik
Ateik

Reputation: 2586

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);

Upvotes: 12

Zorayr
Zorayr

Reputation: 24892

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);
            });
        }
      });
  });
};

Upvotes: 0

Mohammad Rahchamani
Mohammad Rahchamani

Reputation: 5220

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
     });
});

Upvotes: 1

Jeff Ryan
Jeff Ryan

Reputation: 585

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

Upvotes: 2

Dan Kohn
Dan Kohn

Reputation: 34327

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

Upvotes: 2

Related Questions