DEV Community: Idris Kelani

Seed different data for the different environment with Sequelize.

Idris Kelani — Thu, 07 Sep 2023 21:17:58 +0000

Sequelize is a promise-based Node.js ORM with rich features that make database connection and management simple and easy

Data migration and seeding are important to connect and manage the database and application information.

Sequelize seeding makes it easy to populate data in the database. This is needed especially for the smooth running of the application where some default information is needed for the successful operation of the application e.g. roles, currencies, user-types and others.

This Tutorial is born out of the challenges I faced at my workplace when trying to seed in some data for only the development and Quality assurance environment(for testing purposes only) without pushing the same data to the staging and production environment.

I am here to discuss how I dynamically loaded the data for different environments using the environment name (as defined in my .env file) as a sub-folder in the seeders folder.

Let's get started

I assume you already have a node-express-Sequelize setup with es6 syntax. If you haven’t, you can search tutorials online to do so.

Also, I believe that you have set up Sequelize with seeders in your project. If you have not, please go through this tutorial to do so.

Setup
It is expected that you already have a .sequelizerc file in your application.
The first step is to go into your seeders folder and create sub-folders for the different environments that you have.
Take a look at the sample structure of my seeders folder

From the image above, I created the different sub-folder names in the seeders folder: using the names of the different environments that exist on the project as sub-folder names. i.e. development, QA, production and others. (Note that if you don't create the sub-folder for any environment, on the first sequelize seed run on that environment, the seeder sub-folder for that environment will be created).

Now edit your .sequelizerc file to this


const dotenv = require('dotenv');
dotenv.config();
const path = require('path');


module.exports = {
  config: path.resolve('./database/config', 'config.js'),
  'models-path': path.resolve('./database/models'),
  'seeders-path': path.resolve(`./database/seeders/${process.env.NODE_ENV}`),
  'migrations-path': path.resolve('./database/migrations')
};

Firstly we imported and set dotenv module to make sure the the environment variables are loaded at the time sequelize is running the seeders files.


const dotenv = require('dotenv');
dotenv.config();

Now lets focus on the seeders-path

'seeders-path': path.resolve(`./database/seeders/${process.env.NODE_ENV}`),

Here we am pointing the location in which sequelize would pickup the files to the name of the environment.


sequelize db:seed:all

Finally when we run the above code in different environment, only the data present for that environment files will be inserted. i.e if we run sequelize db:seed:all in development environment, sequelize would run seeder files present in the /database/seeders/development folder.

With this approach the different data can be seeded for different application environment.

Note(side effects)
When you create a squelize seed using the sequelize command
sequelize seed:create --name currency-types
Sequelize would create the seed file in the development environment only. So

So if you want to create the seed file for other environment…. There are two ways you can do that
1 Create the seed file in the development folder(like we did) and move/copy it to the seeders sub-folder or
2 change your NODE_ENV to the environment, run the create seed script.

By using this approach, it is very important to make sure that the right environment variable value is set in NODE_ENV as setting a wrong value especially when project is already live can lead to duplicated data

Lastly, this tutorial was written in 2021 and I decided to publish it in late 2023 when I couldn't find any tutorial online that specifically tackles this issue. This method did the job for me but if you find a better way to handle seeding data for different environments on sequelize please reach out to me or comment below

Thanks for reading this 🤗

I am idris kelani 🤗

Seeding data with Sequelize

Idris Kelani — Wed, 12 May 2021 00:45:02 +0000

Just like how we use Git to version control source code, we use migrations and seeders to manage the state of our database schemas.

Sequelize manages the state of the database by saving the name of the migration and seeder(if any) files in the table SequelizeMeta or SequelizeData respectively

We would be looking at the Seeders and how to use them.

When working with Sequelize, you sometimes realize that you need to add static data to the application, e.g, roles, currencies, user types and lots more.

In this tutorial, we would be seeding(adding) the currency of USD and EUR into the Currencies database table of a node js application.

We assume that you already have a node-express-Sequelize project set up. If you haven't, look for tutorials online to do that.

When done with the setup, make sure you have the path to the location of the seeders file in your .sequelizerc file. If it's not there please add it.

const path = require('path');

module.exports = {
  ......
  "seeders-path": path.resolve('./yourdirectory/seeders'),
};

Next is to make sure Sequelize is tracking the seeders file by adding this to the Sequelize config

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

The next step is to make sure the Currencies table already exist if it doesn't: create a migration script to add it.

sequelize model:create --name Currency --attributes name:string

Next, Update the Migration file

export default {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Currencies', {
      id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: queryInterface => queryInterface.dropTable('Currencies')
};

And the Model file

export default (sequelize, DataTypes) => {
  const Currency = sequelize.define('Currency', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  },
  {
    paranoid: true,
  });
  return Currency;
};

Now We want to add data of USD and EUR to the created Currencies table.

We would create a seed file in the seeders folder by running the command

sequelize seed:generate --name add-currencies

This command generates a seeder file.
The file generated has a name with the current timestamp at the beginning of the file name e.g

20210129170418-add-currency.js.

Sequelize compares the timestamp of the seeders file to determine the order of running the seeder file.

Next, We go-ahead to update the file.

export default {
  up: async (queryInterface) => queryInterface.bulkInsert('Currencies', [
    {
      name: 'USD',
      createdAt: new Date(),
      updatedAt: new Date()
    },
    {
      name: 'EUR',
      createdAt: new Date(),
      updatedAt: new Date()
    },
  ], {}),
  down: async (queryInterface) => {
    await queryInterface.bulkDelete('Currencies', {[Op.or]: [{name: 'USD'}, {name: 'EUR'}]});
  }
};

Now let's take a look at the above code

The up section is the section to add the data into the database table

the queryInterface.bulkInsert adds the data into the Currencies table

The down section: If one decides to remove the data after it has been added, this section will handle that.

Now we have added the script to add seed data

The final step is to run the command or add your start script

sequelize db:seed:all

Now we have successfully seeded the USD and EUR into the Currencies table 🎉🎉🎉🎉🎉🎉🎉🎉

if we decide that we want to remove all the data that has been added in the seeders file, we would run the command

sequelize db:seed:undo:all

sequelize db:seed:undo --name 20210129170418-add-currency.js

Visit the Sequelize documentation here

Thanks for reading this 🤗

Idris Kelani 🤗

Track existing Sequelize seed files

Idris Kelani — Wed, 12 May 2021 00:44:05 +0000

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