sequelizejs update doesn't ignore null values

Question

code to update :

            return db.models.sprout.update({
                first_name: args.input.patch.first_name,
                last_name: args.input.patch.last_name,
                date_of_birth: args.input.patch.date_of_birth,
                image_url: args.input.patch.image_url,
                updated_by: args.input.user_id
            },{ where: { sprout_id : args.input.sprout_id }}).then((rowsUpdated) => {

none of the fields have not null constraint in table.

this should ideally update only, values which are provided, in the args.input.patch i provided only image_url

i get this error. notNull Violation: sprout.first_name cannot be null,\nnotNull Violation: sprout.last_name cannot be null

weird, the insert works the way i want, only inserts whichever is present

            return db.models.sprout.create({
                sprout_id: uuidv4(),
                first_name: args.input.sprout.first_name,
                last_name: args.input.sprout.last_name,
                date_of_birth: args.input.sprout.date_of_birth,
                image_url: args.input.sprout.image_url,
                created_by: args.input.user_id
            })

if i dont give image_url or any other field , insert works fine, and ignore the null, but the update doesn't .

How can i make update to ignore the null values.

Can you show the model for sprout... because the error is seems to be due to the parameter `allownull` which is set as true... — Ashh, Jan 18 '18 at 16:34
return db.define('sprout', { sprout_id: { type: DataTypes.STRING, primaryKey: true }, first_name: { type: DataTypes.STRING, allowNull: false }, last_name: { type: DataTypes.STRING, allowNull: false }, date_of_birth: { type: DataTypes.DATE }, image_url: { type: DataTypes.STRING }, .... — raaone7, Jan 20 '18 at 00:33
good point ashish. WoW, very good understanding, it was because of the allownull being set to false on certain fields. i tried after removing it, and it worked as expected. Both ashish and Michael solution worked. I prefer Ashish solution, since it is more native, and filtering is done by sequelize as opposed to custom code (thanks Michael for the code) , i saw in sequilze code, they do similar thing — raaone7, Jan 20 '18 at 00:59
`@user2178209` Mark correct at least one answer... so the people can also get their solution... — Ashh, Jan 20 '18 at 11:49

score 1 · Answer 1 · answered Jan 18 '18 at 15:09

There is such thing in sequelize: omitNull, you should set it to true. Please check it in the docs: http://docs.sequelizejs.com/class/lib/model.js~Model.html

However here: https://github.com/sequelize/sequelize/issues/2352 engineer from their team said that "omitNull was a dirty hack, there are better ways to solve it now." and he didn't recommend the right approach.

Personally, I recommend you to clear null fields from your object before passing it to sequelize. Please look at this thread: Remove blank attributes from an Object in Javascript

Hi Andrew, did try omitNull: true , didn't work as expected. Andrew's solution worked and i think its more tidy, pass only whats required. — raaone7, Jan 20 '18 at 00:28

score 1 · Answer 2 · answered Jan 19 '18 at 10:00

I suggest only passing the fields you actually want to update. This will make the code a lot more readable.

Pass the fields into a function like below. This function will return a new object only containing the fields that are not null

const getFieldsToUpdate = (fields) => {
  return {
    ...fields.first_name && { first_name: fields.first_name },
    ...fields.last_name && { last_name: fields.last_name },
    ...fields.date_of_birth && { date_of_birth: fields.date_of_birth },
    ...fields.image_url && { image_url: fields.image_url },
    ...fields.user_id && { updated_by: fields.user_id },
  }
};

const fieldsToUpdate = getFieldsToUpdate(args.input.patch);
return db.models.sprout.update(fieldsToUpdate, { 
  where: { sprout_id : args.input.sprout_id }
})
  .then((rowsUpdated) => {...

Thanks heaps Michael, the above solution is neat and tidy , and worked as expected. — raaone7, Jan 20 '18 at 00:27

score 1 · Answer 3 · answered Jan 20 '18 at 11:50

1

The error is seems to be due to the parameter allownull which is set as true...

answered Jan 20 '18 at 11:50

Ashh

44,693
14
105
132

score 0 · Answer 4 · answered Jan 20 '18 at 00:59

good point ashish. WoW, very good understanding, it was because of the allownull being set to false on certain fields. i tried after removing it, and it worked as expected. Both ashish and Michael solution worked. I prefer Ashish solution, since it is more native, and filtering is done by sequelize as opposed to custom code (thanks Michael for the code) , i saw in sequilze code, they do similar thing

sequelizejs update doesn't ignore null values

4 Answers4