find records in sequelize seeds

Question

I've been trying to write some seeds for my project, but I've ran into a bit of a snag.

I've got a many-to-many relation with my users and roles table. So, when I'm seeding the database I need to add a record with correct ids into my join table. In order to do that I need to find user by email and role by name and get the ids and that's the problem. I can't find any good documentation on the sequelize site. I'm using the sequelize-cli for the seeding and migrating things. I get as a parameter a queryInterface, but I can't find any example or mention what this thing can actually do. Just some simple examples just got me through the migrating (somehow) and what I was able to find on google.

I've resolved this by using a "dirty trick" I'd say...

// user_seeds.js
up: function (queryInterface, Sequelize) {
  return queryInterface.bulkInsert(table, [{
    id: 1,
    name: 'John doe',
    email: '[email protected]',
    created_at,
    updated_at
}], {});

// roles_seeds.js
up: function (queryInterface, Sequelize) {
  return queryInterface.bulkInsert(table, [{
    id: 1,
    name: 'admin',
    created_at,
    updated_at
  }, {
    id: 2,
    name: 'user',
    created_at,
    updated_at
}]);

//user_roles_seeds.js
up: function (queryInterface, Sequelize) {
    return queryInterface.bulkInsert(table, [{
    employee_id: 1,
    role_id: 1
  }]);
},

Don't like this solution since it may be troublesome in the future should I'd want to run the seeds again and forget about how this works. There should be a way for me to query the database using this queryInterface. I was wondering if one of you had ran into this issue and solved it, if so, please share.

Answer 1

I can't add a comment, although this is an edit to the accepted answer here.

Add plain: false to the query options to return all matching entries. Sequelize doesn't seem to have a doc for rawSelect. But looking at the repo, it basically constructs a raw query from the params provided. For more info https://sequelize.org/master/manual/raw-queries.html

So it should look like this:

const users = await queryInterface.rawSelect(
      'Users',
        {
          where: {
            age: null,
          },
          plain: false,
        },
        ['id'],
      );

Answer 2

Yes you can use the queryInterface to query database.

  async up(queryInterface, Sequelize) {
    const user = await queryInterface.rawSelect('User', {
      where: {
        name: 'John doe',
      },
    }, ['id']);

    if(!user) {
       // do bulkInsert stuff.
    }
  },

Answer 3

This might not be the best practice to resolve this issue, but I wasn't able to find anything better and it was good enough to keep me satisfied.

Basically what I did is required the actual model for the needed tables and use the normal Sequelize API to find the records.

So basically, just punch

const { User } = require('./models');

up: function (queryInterface, Sequelize) {
  return User.findOrCreate({
    where: { email: '[email protected]' },
    defaults: {
      name: 'John Doe'
    }
  });

And so on and so forth. Hope this helps someone, if someone has better way of doing this, please share.