Handling sensitive fields with sequelize.js
Tags: javascript, node, orms, sequelize
Published: 2020-07-30 15:56:18

---
title: Handling sensitive fields with sequelize.js
published: true
description: Alternatives to exclude certain fields from query results when using sequelize.js
tags: javascript, nodejs, orms, sequelize
cover_image: https://imgur.com/akVnnz4.png
---

I've started working with sequelize again for the last few months and at first I struggled with a few things like TypeScript support and associations (so many different ways to configure them!) among other features. One of the things that I encountered recently was keeping specific fields (e.g passwords) out of default queries.
I want to walk you through my first solution to have a rationale, elaborate on `hooks` and then move into why it didn't worked as expected.

## Setting up the models
We will go with a good ol' fashioned e-commerce for simplicity.

![UML diagram of the models describing their associations](https://dev-to-uploads.s3.amazonaws.com/i/8a4kdnccia81455l7sco.png)

<figcaption>
UML diagram of the models describing their associations
</figcaption>

The UML diagram above tries to describe the following:
- a company can have many products 
- a product can have many purchase orders
- a user can have many purchase orders
- a product must have one company
- a purchase order must have one user
- a purchase order can have many products


We will define the models altogether but keep in mind that in a real-word scenario I would suggest creating a different file for each model:
```js
const User = sequelize.define('User', {
  firstName: types.string,
  lastName: types.string,
  email: types.string,
  password: types.string
});

const Product = sequelize.define('Product', {
  description: types.string,
  price: types.string,
});

const Company = sequelize.define('Company', {
  name: types.string,
  url: types.string,
});

const PurchaseOrder = sequelize.define('PurchaseOrder', {
  totalCost: {
    type: DataTypes.DOUBLE,
    allowNull: false
  },
});

// one-to-many
Company.hasMany(Product, {
  as: 'products',
  foreignKey: {
    name: 'companyId',
    allowNull: false
  }
});
Product.belongsTo(Company, {
  as: 'company',
  foreignKey: {
    name: 'companyId',
    allowNull: false
  }
});

// one-to-many
User.hasMany(PurchaseOrder, {
  as: 'purchaseOrders',
  foreignKey: {
    name: 'userId',
    allowNull: false
  }
});
PurchaseOrder.belongsTo(User, {
  as: 'user',
  foreignKey: {
    name: 'userId',
    allowNull: false
  }
});

// many-to-many
Product.belongsToMany(PurchaseOrder, {
  through: 'ProductPurchaseOrder',
  as: 'purchaseOrders'
});
PurchaseOrder.belongsToMany(Product, {
  through: 'ProductPurchaseOrder',
  as: 'products'
});

```

## The problem
I've seen in many parts of the codebase that I'm working currently something similar to:
```js
const user = User.findByPk(1);
console.log('User retrieved:', user.toJSON())
```

> Why is that a problem? Just use the exclude property

One might be keen to suggest. Excluding the property for that specific line of code could work but it wouldn't be advocating for _security by default_. If, by human error, someone forgets to exclude the password for the next query it will still be a problem.

# (Not) A Solution
While searching for different alternatives I found myself looking at the list of hooks that Sequelize has available: `afterCreate`, `afterFind`, and many others. I was already using those for something unrelated to the problem in hand and thought it could be a good approach too. After a few tests I added this to my model definition:

```js
const User = sequelize.define('User', {
  /* props */
}, {
  hooks: {
    afterFind: result => {
      if(Array.isArray(result)) {
        for(const elem of result) {
          delete elem.dataValues.password;
        }
      } else {
        delete result.dataValues.password;
      }

      return result;
    }
  },
});
```
Pushed the code my model repository, updated my services and voilá, I was dealing with better query results by default.

# A better solution
After starting to work on a different feature for another model I saw a response from one of my APIs:

```json
"po":{
  "user": {
    "password": "howdy"
  }
```


Needless to say I grabbed my face for a slow but piercing facepalm. It turns out `afterFind` **hooks will not run through queries with associations linking to that model**.

![Visual explanation of when afterFind hooks are executed](https://dev-to-uploads.s3.amazonaws.com/i/9myrytljb6hub0dnbzja.png)
<figcaption>
Visual explanation of when <code>afterFind</code> hooks are executed
</figcaption>

Finally, I found what I think is the best way for dealing with these type of properties: [scopes](https://sequelize.org/master/manual/scopes.html). It's a powerful feature and allowed me to define the following:

```js
const User = sequelize.define('User', {
  /* props */
}, {
  defaultScope: {
    attributes: {
      exclude: ['password']
    }
  }
});
```

All the associations of the `User` model will be able to include users without including the `password` field by default.

Feel free to play with the result on the Replit included here or checkout the repository on Github:
{% github a0viedo/sequelize-sqlite3-example %}
{% replit @a0viedo/sequelize-sqlite3-example %}

_Note to the reader: do not store your passwords in plain text. The examples here are especially crafted to increase the OMG! reaction_.