add new non-null, unique column using Sequelize migration

This short article is to explain how you can add a new non-null and unique column in an existing Relational database using sequelize migration in nodeJS.

Note: This is for development environments and not recommended for production.

Sometimes while writing any application we might require to add new non-null and unique column in the existing database without dropping the current table, so I tried doing it in my demo project where I'm using sequelize ORM, and I'm writing this article because I couldn't find the complete solution at one place, so here's my attempt at solving this.

Problem description

Consider we have users table with the following columns and now we want to add a new username column which has to be unique and non-null to this without dropping the table.

| id | name  | email                 | age | hobby   |
|----|-------|-----------------------|-----|---------|
| 1  | bob   | [e-mail]          | 23  | coding  |
| 2  | alice | [e-mail]   | 25  | dance   |
| 3  | John  | [e-mail]   | 22  | singing |
| 4  | frank | [e-mail]      | 28  | reading |

Solution

One simple solution is to insert the same email field in username column as both are unique but we'll take general example where this is not possible.

Let's start by creating a migration file using sequelize-cli

npx sequelize migration:create --name=add_username_users

Here's a screen capture of pseudo code

To explain, First we add username column with just unique constraint on its success we write any logic to update every row of newly added username column with any logic you like. I used { type: QueryTypes.SELECT } in SELECT query as we don't need metadata here and to get query results in an array on its success will alter the column to add not-null constraint. For the down part of sequelize migration will just drop the username column.

Here's my final code to add a new column with a randomly generated username, which can be updated later or can be kept as it is during the development environment.

const { QueryTypes } = require('sequelize');
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn('users','username',{
      type: Sequelize.STRING,
      unique: true
    }).then((data) => {
      queryInterface.sequelize
        .query(`SELECT id from users`, {
          type: QueryTypes.SELECT
        })
        .then((rows) => {
          rows.map((row) => {
            queryInterface.sequelize.query(
              `UPDATE users SET username='${Math.random()
                .toString(36).substr(2, 8)}' WHERE id=${row.id};`
            );
          });
          Promise.resolve();
        })
        .then(() => {
          queryInterface.changeColumn('users', 'username', {
            type: Sequelize.STRING,
            allowNull: false
          });
        });
    })
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('users', 'username');
  }
};

Thank you for reading and please share your thoughts on it.

References:
random generation: stackOverflow answer
code idea: sequelize github issue

Comments

[cooervo]

very helpful! sample with async syntax:

module.exports = {
  up: async (queryInterface) =>
    queryInterface.sequelize.transaction(async (transaction) => {

      await queryInterface.addColumn('Solutions', 'slug', {
        type: DataTypes.STRING,
        unique: true,
        transaction,
      });
      const rows = await queryInterface.sequelize.query(
        `SELECT id, name from "Solutions"`,
        {
          type: QueryTypes.SELECT,
          transaction,
        }
      );

      await Promise.all(
        rows.map(async (row, i) => {
          const slug = row.name.toLowerCase().replace(/ /g, '-').trim();
          await queryInterface.sequelize.query(
            `UPDATE "Solutions" SET slug='${slug}' WHERE id='${row.id}';`,
            { transaction }
          );
        })
      );
      await queryInterface.changeColumn(
        'Solutions',
        'slug',
        {
          type: DataTypes.STRING,
          allowNull: false,
        },
        { transaction }
      );
    }),

  down: async (queryInterface) =>
    queryInterface.sequelize.transaction(async (transaction) => {
      await queryInterface.removeColumn('Solutions', 'slug', {
        transaction,
      });
    }),
};

Some slight changes due to postgres syntax

[Shraddharsh]

Great stuff man

[LakmalHarshana]

Really Helpful