Track existing Sequelize seed files

Sequelize manages the database table state by saving the name of the migration or seeder file in table SequelizeMeta or SequelizeData respectively.
Prerequisite: It is believed that you already know about Sequelize and have seeders setup. if you don't, please go through my Tutorial on Seeding data with Sequelize.

The problem

We started maintaining a Sequelize project that has seeders file(20200115143840-add-requesttypes.js) in the code without the seeder files been tracked by Sequelize. We needed to add more seeder data(files). But when we did, Sequelize runs the whole seeder file again, thereby duplicating the already existing seeder data, and adding the new ones.

This was a great problem for us and we needed to fix this issue in production.

We realised that the config file which allows sequelize to track seeders file was not added

module.exports = {
    .......
    seederStorage: 'sequelize'
}

Adding this configuration at this time means Sequelize would still run the old seeder file(s) again(this is what we wanted to prevent) and run the new ones

The Solutions

There were two ways we could fix this.

• Directly in the Database.
• Update the backend code.

Directly in the Database

The simplest way to fix this and still continue to use Sequelize is to go to the production database(any other environment the app is connected to)

Create a table SequelizeData with column name and type string

CREATE TABLE SequelizeData (
  name varchar(255) not null,
);

Add the name(s) of the existing seeder file. In our case there was only one existing seeder file

INSERT INTO SequelizeData
    (name)
VALUES ('20200115143840-add-requesttypes.js');

Now go to your Sequelize config file and add seederStorage

module.exports = {
    .......
    seederStorage: 'sequelize'
}

Now you can add new seeder files without any issues. Remember to do the same thing for all environment database e.g QA, DEV and any other

Sequelize

To fix the problem, we needed to be able to allow Sequelize to track the existing seed files, as well as track any new seed file that would be added.

First of all, we need to create a table SequelizeData


so we add a migration file to create the table

export default {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('sequelizeData', {
      name: {
        type: Sequelize.STRING,
        allowNull: false,
      },
    });
  },
  down: queryInterface => queryInterface.dropTable('sequelizeData')
};


Then we populate the name of the existing seeder files into the table(to prevent Sequelize from running them when the seed script run on start)

Next, we check If the seed data exist in the currency table and add it if it does not(note that this is done to allow for any new person that is setting up the project so a to make the roles seeder file run as it will not exist in the DB)

export default {
  up: async (queryInterface) => {
    const currencyExist = await queryInterface.rawSelect(‘Currency, {
      where: {
        name: ‘USD,
      },
    },
, ['id']);

    if(!currencyExist) {
      await queryInterface.bulkInsert('sequelizeData', [
        {
          name: '20200115143840-users.js',
        },
      ], {})}
    },

  down: async (queryInterface) => {
    await queryInterface.bulkDelete('sequelizeData', null, {});
  }
};

Next is to set the seederStorage in our sequelize config file

module.exports = {
  .....
  seederStorage: 'sequelize'
}

Now let take a look at the code.
The first thing we do is to check if the requestType data are in the RequestTypes table. If it is there then we don’t want to run this seed else we'll run it.

If it does not exist(our current case) add the names of the existing seeder file into the sequelizeData.
This is needed(important) to allow anyone setting up the project from scratch to be able to run the seed file(without any issues).

Now Sequelize is tracking the existing seeders files, we can go ahead to add new seeder files and everything would be fine.

Also, anyone onboarding onto the project can set up the project locally without any issues

Now on the project we were working on, What we ended up doing is to fix it with the code. There are so many environments (production, QA, Staging, dev and more). If we want to fix it in the DB, we have to grow through all those Databases to fix it.
Writing the code to fix it makes sense for us.

NOTE *** This implementation needs to be thoroughly tested as any small mistake can lead to devastating errors and issues

Lastly, A good understanding of database and raw SQL syntax and use cases will always help in solving complex database problems(irrespective of the tool in use).

Thank you.
Idris Kelani