Adding A NOT NULL Field With Sequelize Migrations

I use the Sequelize CLI to create, change, and delete tables in a Node.js app that has a Postgres database. I wrote at lengths short and long about why I do this if you’re curious. But I recently ran into an issue when adding a column to an existing table: how can I add a column with UNIQUE and NOT NULL restraints when there are already rows in the table?

Note: I set up a sqlz bash alias which is used in the examples below (read about it here). Your command will be different unless you set up the alias first.

The standard way to add a column is to use the sqlz migration:generate command to create a new migration, and then use the addColumn QueryInterface method with all of the fields as you need them:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn(
      'Users',
      'favorite_burger',
      {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
        validate: {
          notEmpty: true,
        },
      }
    )
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn(
      'Users',
      'favorite_burger',
    );
  }
};

But since the existing rows won’t have any data (i.e. they will be null) when the migration happens, it will error out and the migration won’t be completed:

ERROR: column "favorite_burger" contains null values

Option 1: Change The Function

One way to fix this could be to change the function to insert data during this migration. This question & answer provide a hint at how to do this, and it’s likely the best option when the existing table already has a lot of rows in it.

In my case though, I only had two rows to worry about, so why bother coming up with a function to generate unique values 😂

Option 2: Make It A Two-Step Process

Instead I decided to do this with two migrations. In the first migration, I added the column without any constraints:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn(
      'Users',
      'favorite_burger',
      {
        type: Sequelize.STRING,
      }
    )
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn(
      'Users',
      'favorite_burger',
    );
  }
};

Then I went directly to my database (can be accessed via the psql command line, but I prefer to use Postico) and added a unique value to each of the two rows’ new columns. Then I created a second migration to add the constraints as needed:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(
      'Users',
      'favorite_burger',
      {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
        validate: {
          notEmpty: true,
        },
      }
    )
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.changeColumn(
      'Users',
      'favorite_burger',
      {
        type: Sequelize.STRING,
      }
    );
  }
};

As a final step, don’t forget to also update the model file with the new column, since sequelize-cli doesn’t update it automatically. And voila! New column exists and everyone’s happy.