Create or Update Sequelize
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){});
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
- This operation is not atomic.
- 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:
- Look at the value returned (i.e. rows_affected) and decide what to do.
- 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 runsINSERT OR IGNORE + UPDATE
, in a single query, so there is no way to know whether the row was inserted or not.
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);