I'm creating a RESTful API with NodeJS, express, express-resource, and Sequelize that is used to manage datasets stored in a MySQL database.
I'm trying to figure out how to properly update a record using Sequelize.
I create a model:
module.exports = function (sequelize, DataTypes) {
return sequelize.define('Locale', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
locale: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
validate: {
len: 2
}
},
visible: {
type: DataTypes.BOOLEAN,
defaultValue: 1
}
})
}
Then, in my resource controller, I define an update action.
In here I want to be able to update the record where the id matches a req.params
variable.
First I build a model and then I use the updateAttributes
method to update the record.
const Sequelize = require('sequelize')
const { dbconfig } = require('../config.js')
// Initialize database connection
const sequelize = new Sequelize(dbconfig.database, dbconfig.username, dbconfig.password)
// Locale model
const Locales = sequelize.import(__dirname + './models/Locale')
// Create schema if necessary
Locales.sync()
/**
* PUT /locale/:id
*/
exports.update = function (req, res) {
if (req.body.name) {
const loc = Locales.build()
loc.updateAttributes({
locale: req.body.name
})
.on('success', id => {
res.json({
success: true
}, 200)
})
.on('failure', error => {
throw new Error(error)
})
}
else
throw new Error('Data not provided')
}
Now, this does not actually produce an update query as I would expect.
Instead, an insert query is executed:
INSERT INTO `Locales`(`id`, `locale`, `createdAt`, `updatedAt`, `visible`)
VALUES ('1', 'us', '2011-11-16 05:26:09', '2011-11-16 05:26:15', 1)
So my question is: What is the proper way to update a record using Sequelize ORM?
This question is related to
mysql
node.js
express
sequelize.js
I did it like this:
Model.findOne({
where: {
condtions
}
}).then( j => {
return j.update({
field you want to update
}).then( r => {
return res.status(200).json({msg: 'succesfully updated'});
}).catch(e => {
return res.status(400).json({msg: 'error ' +e});
})
}).catch( e => {
return res.status(400).json({msg: 'error ' +e});
});
public static update(values: Object, options: Object): Promise>
check documentation once http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-update
Project.update(
// Set Attribute values
{ title:'a very different title now' },
// Where clause / criteria
{ _id : 1 }
).then(function(result) {
//it returns an array as [affectedCount, affectedRows]
})
I think using UPDATE ... WHERE
as explained here and here is a lean approach
Project.update(
{ title: 'a very different title no' } /* set attributes' value */,
{ where: { _id : 1 }} /* where criteria */
).then(function(affectedRows) {
Project.findAll().then(function(Projects) {
console.log(Projects)
})
This solution is deprecated
failure|fail|error() is deprecated and will be removed in 2.1, please use promise-style instead.
so you have to use
Project.update(
// Set Attribute values
{
title: 'a very different title now'
},
// Where clause / criteria
{
_id: 1
}
).then(function() {
console.log("Project with id =1 updated successfully!");
}).catch(function(e) {
console.log("Project update failed !");
})
And you can use
.complete()
as well
Regards
hi to update the record it very simple
result.feild = updatedField
const sequelizeModel = require("../models/sequelizeModel"); const id = req.params.id; sequelizeModel.findAll(id) .then((result)=>{ result.name = updatedName; result.lastname = updatedLastname; result.price = updatedPrice; result.tele = updatedTele; return result.save() }) .then((result)=>{ console.log("the data was Updated"); }) .catch((err)=>{ console.log("Error : ",err) });
Code for V5
const id = req.params.id; const name = req.body.name; const lastname = req.body.lastname; const tele = req.body.tele; const price = req.body.price; StudentWork.update( { name : name, lastname : lastname, tele : tele, price : price }, {returning: true, where: {id: id} } ) .then((result)=>{ console.log("data was Updated"); res.redirect('/'); }) .catch((err)=>{ console.log("Error : ",err) });
And for people looking for an answer in December 2018, this is the correct syntax using promises:
Project.update(
// Values to update
{
title: 'a very different title now'
},
{ // Clause
where:
{
id: 1
}
}
).then(count => {
console.log('Rows updated ' + count);
});
If Model.update
statement does not work for you, you can try like this:
try{
await sequelize.query('update posts set param=:param where conditionparam=:conditionparam', {replacements: {param: 'parameter', conditionparam:'condition'}, type: QueryTypes.UPDATE})
}
catch(err){
console.log(err)
}
If you're here looking for a way to increment a specific field value in a model...
This worked for me as of [email protected]
User.increment("field", {by: 1, where: {id: 1});
I used update method to update my record.
models.users.update( { "name":'sam', "city":'USA' }, where:{ id:1 } )
Using async and await in a modern javascript Es6
const title = "title goes here";
const id = 1;
try{
const result = await Project.update(
{ title },
{ where: { id } }
)
}.catch(err => console.log(err));
you can return result ...
There are two ways you can update the record in sequelize.
First, if you have a unique identifier then you can use where clause or else if you want to update multiple records with the same identifier.
You can either create the whole object to update or a specific column
const objectToUpdate = {
title: 'Hello World',
description: 'Hello World'
}
models.Locale.update(objectToUpdate, { where: { id: 2}})
Only Update a specific column
models.Locale.update({ title: 'Hello World'}, { where: { id: 2}})
Second, you can use find a query to find it and use set and save function to update the DB.
const objectToUpdate = {
title: 'Hello World',
description: 'Hello World'
}
models.Locale.findAll({ where: { title: 'Hello World'}}).then((result) => {
if(result){
// Result is array because we have used findAll. We can use findOne as well if you want one row and update that.
result[0].set(objectToUpdate);
result[0].save(); // This is a promise
}
})
Always use transaction while updating or creating a new row. that way it will roll back any updates if there is any error or if you doing any multiple updates:
models.sequelize.transaction((tx) => {
models.Locale.update(objectToUpdate, { transaction: t, where: {id: 2}});
})
I have used sequelize.js
, node.js
and transaction
in below code and added proper error handling if it doesn't find data it will throw error that no data found with that id
editLocale: async (req, res) => {
sequelize.sequelize.transaction(async (t1) => {
if (!req.body.id) {
logger.warn(error.MANDATORY_FIELDS);
return res.status(500).send(error.MANDATORY_FIELDS);
}
let id = req.body.id;
let checkLocale= await sequelize.Locale.findOne({
where: {
id : req.body.id
}
});
checkLocale = checkLocale.get();
if (checkLocale ) {
let Locale= await sequelize.Locale.update(req.body, {
where: {
id: id
}
});
let result = error.OK;
result.data = Locale;
logger.info(result);
return res.status(200).send(result);
}
else {
logger.warn(error.DATA_NOT_FOUND);
return res.status(404).send(error.DATA_NOT_FOUND);
}
}).catch(function (err) {
logger.error(err);
return res.status(500).send(error.SERVER_ERROR);
});
},
January 2020 Answer
The thing to understand is that there's an update method for the Model and a separate update method for an Instance (record). Model.update()
updates ALL matching records and returns an array see Sequelize documentation. Instance.update()
updates the record and returns an instance object.
So to update a single record per the question, the code would look something like this:
SequlizeModel.findOne({where: {id: 'some-id'}})
.then(record => {
if (!record) {
throw new Error('No record found')
}
console.log(`retrieved record ${JSON.stringify(record,null,2)}`)
let values = {
registered : true,
email: '[email protected]',
name: 'Joe Blogs'
}
record.update(values).then( updatedRecord => {
console.log(`updated record ${JSON.stringify(updatedRecord,null,2)}`)
// login into your DB and confirm update
})
})
.catch((error) => {
// do seomthing with the error
throw new Error(error)
})
So, use Model.findOne()
or Model.findByPkId()
to get a handle a single Instance (record) and then use the Instance.update()
Since sequelize v1.7.0 you can now call an update() method on the model. Much cleaner
For Example:
Project.update(
// Set Attribute values
{ title:'a very different title now' },
// Where clause / criteria
{ _id : 1 }
).success(function() {
console.log("Project with id =1 updated successfully!");
}).error(function(err) {
console.log("Project update failed !");
//handle error here
});
You can use Model.update() method.
With async/await:
try{
const result = await Project.update(
{ title: "Updated Title" }, //what going to be updated
{ where: { id: 1 }} // where clause
)
} catch (error) {
// error handling
}
With .then().catch():
Project.update(
{ title: "Updated Title" }, //what going to be updated
{ where: { id: 1 }} // where clause
)
.then(result => {
// code with result
})
.catch(error => {
// error handling
})
Since version 2.0.0 you need to wrap your where clause in a where
property:
Project.update(
{ title: 'a very different title now' },
{ where: { _id: 1 } }
)
.success(result =>
handleResult(result)
)
.error(err =>
handleError(err)
)
The latest version actually doesn't use success
and error
anymore but instead uses then
-able promises.
So the upper code will look as follows:
Project.update(
{ title: 'a very different title now' },
{ where: { _id: 1 } }
)
.then(result =>
handleResult(result)
)
.catch(err =>
handleError(err)
)
try {
const result = await Project.update(
{ title: 'a very different title now' },
{ where: { _id: 1 } }
)
handleResult(result)
} catch (err) {
handleError(err)
}
Source: Stackoverflow.com