sequelize: Virtual Getter not working without returning the dependent column

What you are doing?

I want to retrieve a computed column (“cipher”) which is the encrypted value of another column (“secret”). Unfortunately, I am not able to exclude the “secret” column from the result set without breaking the computation of “cipher”.

var Sequelize = require('sequelize')

var sequelize = new Sequelize('sqlite://', {storage: 'test.sqlite'})

var User = sequelize.define('User', 
  { 
    name: Sequelize.TEXT,
    secret: Sequelize.TEXT

    // Alternative: define cipher2 as a VIRTUAL column including 'secret' as dependent field
    // --> exclude: ['secret'] doesn't have any effect at all

    // , cipher2: {
    //   type: new Sequelize.VIRTUAL(Sequelize.TEXT, ['secret']),
    //   get: function() {
    //     return this.getDataValue('cipher');
    //   }
    // }

  },
  {
    getterMethods: {
      cipher: function(){ 
        var secret = this.getDataValue('secret');
        var encrypted = "Encrypted value of " + secret;

        return encrypted; 
      }
    }
});

sequelize.sync({ force: true }).then(function(){

    User.bulkCreate([{ name: 'Hans', secret: 'Uschi123' },{ name: 'Franz', secret: 'Gudrun456' }]).then(function(){

        User.find({ 
          attributes: {
            exclude: ['secret'] 
          }
        })
        .then(function(user){
            console.log(JSON.stringify(user,null,'  '));
        });

    })

})

What do you expect to happen?

Expected output is as follows:

{
  "cipher": "Encrypted value of Uschi123",
  "id": 1,
  "name": "Hans",
  "createdAt": "2017-02-11T16:52:39.687Z",
  "updatedAt": "2017-02-11T16:52:39.687Z"
}

What is actually happening?

{
  "cipher": "Encrypted value of undefined",
  "id": 1,
  "name": "Hans",
  "createdAt": "2017-02-11T16:52:39.687Z",
  "updatedAt": "2017-02-11T16:52:39.687Z"
}

If you comment out line 39 (exclude: ['secret']) the output is as follows:

{
  "cipher": "Encrypted value of Uschi123",
  "id": 1,
  "name": "Hans",
  "secret": "Uschi123",
  "createdAt": "2017-02-11T16:56:51.213Z",
  "updatedAt": "2017-02-11T16:56:51.213Z"
}

Dialect: sqlite Database version: 3.1.8 Sequelize version: 3.30.2

About this issue

Original URL
State: open
Created 7 years ago
Reactions: 12
Comments: 16 (3 by maintainers)

Most upvoted comments

I have this issue too and I found a tricky way to solve it.

I have a virtual fullName field and it has two dependency as firstName and lastName

fullName: {
  type: new DataTypes.VIRTUAL(DataTypes.STRING, ['firstName', 'lastName']),
  get() {
    return `${this.firstName} ${this.lastName}`;
  }
}

I can delete firstName and lastName fields from returned object as below:

fullName: {
  type: new DataTypes.VIRTUAL(DataTypes.STRING, ['firstName', 'lastName']), 
  get() {
    const fullName = `${this.firstName} ${this.lastName}`;

    delete this.dataValues.firstName;
    delete this.dataValues.lastName;

    return fullName;
  }
}

But there is a problem in this solution. Whenever you pull fullName field, firstName and lastName fields will be deleted.

To detect when we exclude this fields, we create extra alias fields in query like below.

Staff.findAll({
  attributes: ['id', 'fullName', ['firstName', 'excludeFirstName'], ['lastName', 'excludeLastName'] ]
});

And add extra condition in get method of fullName field. We check if there exist a field like ‘excludeFirstName’, then delete both firstName field and excludeFirstName field that we created after. Same as lastName field.

fullName: {
  type: new DataTypes.VIRTUAL(DataTypes.STRING, ['firstName', 'lastName']),
  get() {
    const fullName = `${this.firstName} ${this.lastName}`;

    if('excludeFirstName' in this.dataValues){
      delete this.dataValues.firstName;
      delete this.dataValues.excludeFirstName;
    }

    if('excludeLastName' in this.dataValues){
      delete this.dataValues.lastName;
      delete this.dataValues.excludeLastName;
    }

    return fullName;
  }
}

Now we can filter dependency fields if we want.

I hope it helps 😃

ErtugrulBEKTIK on Aug 14, 2020

Overriding toJSON to delete the field only works if you never want that attribute ever. Using the attributes or include/exclude fields when you make the call is where you would naturally want to declare what you want returned. Maybe there needs to be a “returnedFields” or some parameter that can be included that can be used which fields should be returned in the call, but that just feels excessive since that’s what the attributes are. Sequelize really should just know if a field is required because a virtual field needs it but is specifically excluded from the attributes then it shouldn’t be included in the returned JSON.

This issue was labeled as a feature, not a bug.

scottbeacher on May 28, 2022

Yeah, right. That’s what I had to do as well and it didn’t feel correct too.

As I am writing this I am thinking about creating a custom getter method that sets the secret column to null. Not nice as well but just wanted to mention another approach that might do the trick as a workaround.

xLarry on Feb 13, 2018