Sequelize migration add "IF NOT EXISTS" to addIndex and addColumn

Question

Is there a way to force Sequelize.js to add IF NOT EXISTS to the Postgres SQL created by the queryInterface.addColumn and queryInterface.addIndex methods?

According to the Postgres Docs this is supported for Alter Table Add Column as well as Create Index

I have looked through the Sequelize.js docs without any luck, and I have tried to go through the code to figure out how the SQL is generated, but I have not had any luck yet.

---

A bit of background, or "Why"

I am trying to create a migration strategy for an existing postgres instance, and I have currently created a Sequelize migration set which migrates from "nothing" to the current schema. Now I would like to simply get this up and running on my production server where all of the data already exists such that the next time I create a migration, I can run it.

All of this works well for every queryInterface.createTable because the IF NOT EXISTS is automatically added.

Answer 1

I had a similar issue, except in my case I was only interested in addColumn IF NOT EXIST.

You can achieve this with a two step solution, using queryInterface.describeTable. Given the table name the function will return the table definition which contains all the existing columns. If the column you need to add does not exist then call the queryInterface.addColumn function.

const tableName = 'your_table_name';

queryInterface.describeTable(tableName)
  .then(tableDefinition => {
    if (tableDefinition.yourColumnName) {
      return Promise.resolve();
    }

    return queryInterface.addColumn(
      tableName,
      'your_column_name',
      { type: Sequelize.STRING } // or a different column
    );
  });

Answer 2

addColumn function comes from queryGenerator method called addColumnQuery, which accepts three parameters - table, key and dataType. With use of them it creates a query, which looks like that

let query = `ALTER TABLE ${quotedTable} ADD COLUMN ${quotedKey} ${definition};`;

So, as you can see, there is no option to add the IF NOT EXISTS clause to the query string. The same concerns the addIndex method unfortunately. However, you can use plain query in order to perform some atypical operations

queryInterface.sequelize.query(...);

Answer 3

A small working example:

module.exports = {
  /**
   * @description Up.
   * @param {QueryInterface} queryInterface
   * @return Promise<void>
   */
  up: async (queryInterface) => {
    const tableDefinition =  await queryInterface.describeTable('group');
    const promises = [];

    return queryInterface.sequelize.transaction((transaction) => {
      if (!tableDefinition.column1) {
        promises.push(queryInterface.addColumn(
          'group',
          'column1',
          {
            type: queryInterface.sequelize.Sequelize.STRING,
            allowNull: true,
          },
          {transaction},
        ));
      }

      if (!tableDefinition.oauth2_token_expire_at) {
        promises.push(queryInterface.addColumn(
          'group',
          'column2',
          {
            type: queryInterface.sequelize.Sequelize.DATE,
            allowNull: true,
          },
          {transaction},
        ));
      }

      return Promise.all(promises);
    });
  },
  /**
   * @description Down.
   * @param {QueryInterface} queryInterface
   * @return Promise<void>
   */
  down: (queryInterface) => {
    ...
  },
};

Answer 4

The statement if (!tableDefinition.yourColumnName) won't be able to check if column exists. Correct way is

return queryInterface.describeTable(tableName).then(tableDefinition => {
            if (!tableDefinition[columnName]){
                return queryInterface.addColumn(tableName, columnName, {
                    type: Sequelize.JSON
                });
            } else {
                return Promise.resolve(true);
            }
        });