MySQL 创建或更新续集

Create or Update Sequelize

提问by Thiago Miranda de Oliveira

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.

我在我的 Nodejs 项目中使用 Sequelize，我发现了一个我很难解决的问题。基本上我有一个从服务器获取对象数组的 cron，然后将它作为对象插入到我的数据库中（在这种情况下，卡通）。但是，如果我已经拥有其中一个对象，则必须对其进行更新。

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?

基本上我有一个对象数组，并且可以使用 BulkCreate() 方法。但是当 Cron 再次启动时，它并没有解决它，所以我需要使用 upsert true 标志进行某种更新。主要问题是：在所有这些创建或更新之后，我必须有一个只触发一次的回调。有谁知道我该怎么做？迭代对象数组..创建或更新它，然后获得一个回调？

Thanks for the attention

谢谢关注

回答by tsuz

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

从docs，where一旦拥有对象，您就不需要查询来执行更新。此外，promise 的使用应该简化回调：

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.

1. 此操作不是原子操作。
2. 创建 2 个网络调用。

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.

1. 查看返回的值（即rows_affected）并决定要做什么。
2. 如果更新操作成功，则返回成功。这是因为资源是否存在不在本服务的职责范围内。

回答by Alvaro Joao

You can use upsertIt's way easier.

你可以使用upsert它更容易。

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 THENNotethat 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.

实施细则：

• MySQL - 作为单个查询实现 INSERT values ON DUPLICATE KEY UPDATE values
• PostgreSQL - 作为具有异常处理的临时函数实现： INSERT EXCEPTION WHEN unique_constraint UPDATE
• SQLite - 作为两个查询实现INSERT; UPDATE。这意味着无论该行是否已存在，都会执行更新
• MSSQL - 使用单个查询实现MERGE and WHEN (NOT) MATCHED THEN请注意，无论该行是创建还是更新，SQLite 都会为 created 返回 undefined。这是因为 SQLite 总是INSERT OR IGNORE + UPDATE在单个查询中运行，因此无法知道该行是否被插入。

回答by Simon Fakir

Update 07/2019now with async/await

现在使用 async/await更新 07/2019

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:

我喜欢 Ataik 的想法，但把它缩短了一点：

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

回答by Ateik

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

回答by Dan Kohn

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

听起来你想将 Sequelize 调用包装在async.each 中。

回答by Jeff Ryan

This can be done with the custom event emitter.

这可以通过自定义事件发射器来完成。

Assuming your data is in a variable called data.

假设您的数据位于名为 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

回答by Nikolay Podolnyy

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

它将尝试通过第一个参数中的哈希查找记录以更新它，如果找不到它 - 那么将创建新记录

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

回答by Zorayr

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

这是一个更新 deviceID -> pushToken 映射或创建它的简单示例：

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

回答by Mohammad Rahchamani

you can use findOrCreateand then updatemethods in sequelize. here is a sample with async.js

您可以在续集中使用findOrCreate然后update方法。这是一个带有 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
     });
});