How to update a record using sequelize for node?

Question

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?

Answer 1

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

See links: docs Link and API Link

Answer 2

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

Answer 3

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

 });

Answer 4

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: 'some@email.com',
    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()

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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]

  })

Answer 11

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

Answer 12

If you're here looking for a way to increment a specific field value in a model...

This worked for me as of sequelize@5.21.3

User.increment("field", {by: 1, where: {id: 1});

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

Answer 13

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

Answer 14

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({req.body},
{where:{ id:1}}
)

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

        
      })

Answer 19

SEQUELIZE FIND AND UPDATE

Using async and await

const approveUser = asyncHandler(async (req, res) => {

  var userID = parseInt(req.params.id);

  const user = await User.findByPk(userID);

  if (!user) throw new Error('No record found');

  const result = await user.update({ isValid: !user.isValid });

  if (result) {
    res.status(201).json({
      result,
    });
  } else {
    res.status(400);
    throw new Error('Invalid data');
  }
});

Answer 20

If you have Defined a UserModel so you can User

let data = await UserModel.update(body, {
  where: {
    id:id,
  },
  individualHooks: true,
});

Answer 21

Update: 2023 (sequelize v6)

The are 2 ways to update item.

1. Search the record and update.

const localeName = req.body.name;
const localeId = req.body.id;

const locale = await Locale.findOne({ where: { id: localeId } });
if (locale) {
    locale.name = localeName;
    await locale.save();
}

2. Update record with single query.

await Locale.update({ name: localeName }, {
  where: {
    id: localeId
  }
});

For the details docs.

https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#simple-update-queries

I find the 1st way is more useful.