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.

Wolcott

Did you resolve this?? – Voltz

In a way I did. – Wolcott

There is fundamentally no way to do non-raw querries BTW: github.com/sequelize/cli/issues/862 – Laspisa

Answer 1

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.
    }
  },

Wedge

This worked perfect for me thanks BenHu. Note that if you have a different seed file for each table then order of seeds is important (Sequelize CLI loads them in alphabetical order) - just rename them to reorder if needed. – Goebel

I see that is a good approach but I dont know why it returns only one record. I would like to get all in a where condition. May you help me? – Lecky

Answer 2

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'],
      );

Toxin

Thank you for this. I didn't find the doc for rawSelect either, and I was wondering why I was getting only one result for a similar query. plain false did the trick. – Shatterproof

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.

Wolcott

It's a good approach but when we use ES6 babel we have problems when using import in sequelize config, model etc.. – Lecky