add new non-null, unique column using Sequelize migration
Tags: node, sequelize, migration
Published: 2020-09-20 17:43:24

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   | bob@mars.com          | 23  | coding  |
| 2  | alice | alice01@jupyter.com   | 25  | dance   |
| 3  | John  | superjohn@earth.com   | 22  | singing |
| 4  | frank | frank@saturn.com      | 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
![migration pseudo code](https://dev-to-uploads.s3.amazonaws.com/i/2pbdjycrv8objl559vmy.png)

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.
```js
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](https://stackoverflow.com/a/8084248)
code idea: [sequelize github issue](https://github.com/sequelize/sequelize/issues/5507#issuecomment-190736150)