Understanding Sequelize database migrations and seeds

Question

I'm trying to wrap my head around Sequelize's migrations and how they work together with seeds (or maybe migrations and seeds in general).
I set up everything to get the migrations working.

First, lets create a users table:

// migrations/01-create-users.js
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("Users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      email: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("Users");
  }
};

Fine. If I want to seed an (admin) user, I can do this as follows:

// seeders/01-demo-user.js
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert(
      "Users",
      [
        {
          email: "[email protected]"
        }
      ],
      {}
    );
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete("Users", null, {});
  }
};

Then to make the magic happen, I do:

$ sequelize db:migrate

Which creates the users table in the database. After running the migrations, seeding is the next step, so:

$ sequelize db:seed:all

Tataa, now I have a user in the users database. Great.

But now I want to add firstname to the users table, so I have to add another migration:

// migrations/02-alter-users.js
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn("Users", "firstname", {
      type: Sequelize.STRING
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn("Users", "firstname");
  }
};

Running migrations again would only run the second one because it was saved in the database that the first one was already executed. But by default sequelize re-runs all seeders. So should I adjust the seeders/01-demo-user.js or change the default behavior and also store the seeders in the DB and create a new one that just updates the firstname?

What if firstname couldn't be null, then running migrations first and then the old version of seeders/01-demo-user.js would throw an error because firstname can't be null.

Re-running seeders leads to another problem: there is already a user with the [email protected] email. Running it a second time would duplicate the user. Or do I have to check for things like this in the seeder?

Previously, I just added the user-account in the migration so I could be sure when it was added to the DB and when I had to update it. But someone told me I was doing it all wrong and that I have to use seeders for tasks like this.

Any help/insights much appreciated.

Answer 1

In my opinion, a seeder is something, that is intended to run only once, while the migration is something that you add layer by layer to your DB structure continuously.

I would use seeders for populating some lookups or other data that most likely is not going to change, or test data. In the sequelize docs it's said, that "Seed files are some change in data that can be used to populate database table with sample data or test data."

If you want to make some dynamic data updates when data structure has already changed, you can run raw queries directly in your migrations if needed. So, if you, for instance, added some column in the up method, you can update the rows in the DB according to your business logic, e.g.:

// Add column, allow null at first
await queryInterface.addColumn("users", "user_type", {
    type: Sequelize.STRING,
    allowNull: true
});

// Update data
await queryInterface.sequelize.query("UPDATE users SET user_type = 'simple_user' WHERE is_deleted = 0;");

// Change column, disallow null
await queryInterface.changeColumn("users", "user_type", {
    type: Sequelize.STRING,
    allowNull: false
});

There is also an interesting discussion on this topic in Google Groups. I hope, this helps you.