[Skip to the solution]

A Brief Description of Sequelize and ORMs

Sequelize is a great Object Relational Mapping (ORM) framework for NodeJS. One awesome benefit to ORMs is they preserve relationships between objects, making data models much easier to work with. For example, let’s say you have two tables, suppliers and products, and each product belongs to a supplier:

select  * from suppliers;
  id   |       name       | phoneNumber
-------+------------------+-------------
 cm001 | Tim's Textile Co | 555-1212
 bd001 | Bags by Barb     | 555-3434

select  * from products;
 id |     name     | price | supplierId
----+--------------+-------+----------
 01 | blue jeans   | 49.00 | cm001
 02 | argyle socks |  7.00 | cm001
 03 | tote bag     | 25.00 | bd001

With an ORM framework like Sequelize, you can ask for the associated data models anytime you run a query.

Here’s a command to look for a product with the name “tote bag”. It also tells Sequelize to include all the associated supplier data:

const toteBag = await Product.findOne({
  where: {name: 'tote bag'},
  include: [{model: Supplier}]
});

{
   "id":"03",
   "name":"tote bag",
   "price":"25",
   "supplierId":"bd001",
   "supplier":{
      "id":"bd001",
      "name":"Bags by Barb",
      "phoneNumber":"555-3434"
   }
}

If that wasn’t cool enough, you can also nest include conditions within each other and query for even the most sophisticated of data relationships.

Migrations

Sequelize also offers support for database migrations. I’m a big fan of using migration files to rebuild the database because they rebuild the schema in a way that’s deterministic. Plus, I don’t have to worry about updating a database initialization script every time I make a change.

With a database migration framework, the act of making the schema change is equivalent to updating a database initialization script. Migrations are also better at preserving a clean history of changes as each edit is usually saved to a new file.

The problem with adding a NOT NULL column to a table with data

This is kind of self explanatory. The problem is you can’t add a NOT NULL column to a database unless you have a default value. Without a default value, the schema update fails on the NOT NULL constraint.

Here’s a migration file that tries to do this:

module.exports = {
 up: (queryInterface, Sequelize) => queryInterface.addColumn('products', 'countryOfOrigin', {
   type: Sequelize.STRING,
   allowNull: false
 }),
 down: (queryInterface, Sequelize) => queryInterface.removeColumn('products', 'countryOfOrigin')
};

If you try to execute this, you’re not going to have a good time.

ERROR: column “countryOfOrigin” contains null values

What about just adding a default value?

Adding a default value is easy, but what if your default value relies on a conditional? What if it’s a particularly complex conditional?

An Initial Approach: Allow null values, fix existing rows, turn null constraint on

One way to get this to work is by adding the column and allowing null values. Then, in either a separate migration file or a manual/scripted process, update the column for the existing rows. Lastly, make another migration file and update the column so it is NOT NULL.

I saw several articles recommend this approach and I really don’t like it. The step to update the existing rows should really be part of the migration file, otherwise it could easily be lost or accidentally skipped over. This approach also has three distinct steps spread across three different operations/files. It would be better if everything was part of a single migration file.

Can all these steps fit in a single script? Yes!

What if all the steps from the previous approach could fit into one file? That would be a much cleaner solution.The good news is it can be done!

The up and down exports in Sequelize migrations file are really just async functions that should return a promise, meaning the operations can be chained.

Then it’s just a matter of updating the existing rows. Depending on your business logic, this part has the potential to be quite complex. I personally like to using the query function to execute raw SQL.

module.exports = {
  up: (queryInterface, Sequelize) => 
    // Add the new column
    queryInterface.addColumn('products', 'countryOfOrigin', {type: Sequelize.STRING})
    
    .then(async() =>
      // Set countryOfOrigin = 'Italy' for all the products by supplier 'bd001'
      await queryInterface.sequelize.query(`update "products" set "countryOfOrigin" = 'Italy' where "supplierId" = 'bd001'`))
    .then(async() =>
      // Set countryOfOrigin to USA for the rest of the products
      await queryInterface.sequelize.query(`update "products" set "countryOfOrigin" = 'USA' where "countryOfOrigin" is null`))
    .then(async() =>
      // Add the NOT NULL constraint
      queryInterface.changeColumn('products', 'countryOfOrigin', {type: Sequelize.STRING, allowNull: false})),
  down: (queryInterface, Sequelize) => queryInterface.removeColumn('products', 'countryOfOrigin')
};

One more thing! Update the data model

The migration file doesn’t make any changes to the data model so the final step is to add the new column there.

Here’s what the updated Product data model would look like:

module.exports = (sequelize, DataTypes) => {
 
 const Product = sequelize.define('products', {
   id: {
     type: DataTypes.STRING,
     primaryKey: true,
   },
   name: DataTypes.STRING,
   price: DataTypes.NUMERIC,
   supplierId: DataTypes.STRING,
   countryOfOrigin: DataTypes.STRING // new field
 });
 
 Product.associate = (models) => {
   Product.belongsTo(models.Supplier, {foreignKey: 'supplierId'});
 };
 
 return Product;
};

Acknowledgements & Further Reading

Here’s the Github issue that showed me how to chain commands in a migration file. I hope others find it as useful as I did.

Also, for those that would like to learn more about database migrations, this is a great article.