How to update a record using sequelize for node?

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?

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

---

Update 2016-03-09

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

Using async/await

try {
  const result = await Project.update(
    { title: 'a very different title now' },
    { where: { _id: 1 } }
  )
  handleResult(result)
} catch (err) {
  handleError(err)
}

http://docs.sequelizejs.com/en/latest/api/model/#updatevalues-options-promisearrayaffectedcount-affectedrows

I have not used Sequelize, but after reading its documentation, it's obvious that you are instantiating a new object, that's why Sequelize inserts a new record into the db.

First you need to search for that record, fetch it and only after that change its properties and update it, for example:

Project.find({ where: { title: 'aProject' } })
  .on('success', function (project) {
    // Check if record exists in db
    if (project) {
      project.update({
        title: 'a very different title now'
      })
      .success(function () {})
    }
  })

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

 });

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

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

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

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: tx, where: {id: 2}});
})

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

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

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]

  })

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

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

REF: https://github.com/sequelize/sequelize/issues/7268

hi to update the record it very simple

1. sequelize find the record by ID (or by what you want)
2. then you pass the params with result.feild = updatedField
3. if the record doesn'texist in database sequelize create a new record with the params
4. watch the exemple for more understand Code #1 test that code for all version under V4

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

I used update method to update my record.

1. models is a .js file where your models place
2. users is model name
3. update is build in function provided by sequelize.
4. I'm updating name and city into users table where id equal to 1

> models.users.update( > { > "name":'sam', > "city":'USA' > }, > where:{ > id:1 > } > )

Method-1

If you change the value of some field of an instance, calling save again will update it accordingly:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
jane.name = "Ada";
// the name is still "Jane" in the database
await jane.save();
// Now the name was updated to "Ada" in the database!

Method-2

You can update several fields at once with the set method:

const jane = await User.create({ name: "Jane" });

jane.set({
  name: "Ada",
  favoriteColor: "blue"
});
// As above, the database still has "Jane" and "green"
await jane.save();
// The database now has "Ada" and "blue" for name and favorite color

Method-3

Note that the save() here will also persist any other changes that have been made on this instance, not just those in the previous set call. If you want to update a specific set of fields, you can use update:

const jane = await User.create({ name: "Jane" });
jane.favoriteColor = "blue"
await jane.update({ name: "Ada" })
// The database now has "Ada" for name, but still has the default "green" for favorite color
await jane.save()
// Now the database has "Ada" for name and "blue" for favorite color

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

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

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

var whereStatement = {};

  whereStatement.id = req.userId;

  if (whereStatement) {
    User.findOne({
      where: whereStatement
    })
      .then(user => {

        if (user) {
          
          var updateuserdetails = {
            email: req.body.email,
            mobile: req.body.mobile,
            status: req.body.status,
            user_type_id: req.body.user_type_id
          };

          user.update(
            updateuserdetails
          )
            .then(function () {
              res.status(200).send({ message: 'Success...' });
            })
            .catch(err => {
              res.status(500).send({ message: err.message });
            });
        }

        
      })