Sequelize: seed with associations

Question

I have 2 models, Courses and Videos, for example. And Courses has many Videos.

// course.js
'use strict';

module.exports = (sequelize, DataTypes) => {
  const Course = sequelize.define('Course', {
    title: DataTypes.STRING,
    description: DataTypes.STRING
  });

  Course.associate = models => {
    Course.hasMany(models.Video);
  };

  return Course;
};


// video.js
'use strict';

module.exports = (sequelize, DataTypes) => {
  const Video = sequelize.define('Video', {
    title: DataTypes.STRING,
    description: DataTypes.STRING,
    videoId: DataTypes.STRING
  });

  Video.associate = models => {
    Video.belongsTo(models.Course, {
      onDelete: "CASCADE",
      foreignKey: {
        allowNull: false
      }
    })
  };

  return Video;
};

I want to create seeds with courses which includes videos. How can I make it? I don't know how to create seeds with included videos.

mcranston18 · Accepted Answer · 2018-10-06T14:51:13.280

You can use Sequelize's queryInterface to drop down to raw SQL in order to insert model instances that require associations. In your case, the easiest way would to create one seeder for courses and videos. (One note: I don't know how you are defining your primary and foreign key so I am making an assumption that the videos table has a field course_id.)

module.exports = {
  up: async (queryInterface) => {
    await queryInterface.bulkInsert('courses', [
      {title: 'Course 1', description: 'description 1', id: 1}
      {title: 'Course 2', description: 'description 2', id: 2}
    ], {});

    const courses = await queryInterface.sequelize.query(
      `SELECT id from COURSES;`
    );

    const courseRows = courses[0];

    return await queryInterface.bulkInsert('videos', [
      {title: 'Movie 1', description: '...', id: '1', course_id: courseRows[0].id}
      {title: 'Movie 2', description: '...', id: '2', course_id: courseRows[0].id},
      {title: 'Movie 3', description: '...', id: '3', course_id: courseRows[0].id},
    ], {});
  },

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

in the `down` section, shouldn't we delete videos column before the courses column because of the foreign key constraint. Sorry to be picky though. — sameer manek, Sep 03 '18 at 15:38
please checkout https://stackoverflow.com/questions/52227663/sequelize-cannot-seed-association-values-in-db-table — sameer manek, Sep 07 '18 at 19:24
Latest version will throw 'await is only valid in async function' — Jeffery ThaGintoki, Sep 22 '19 at 23:13
`queryInterface.sequelize.query` was not working for me so I used `queryInterface.rawSelect` instead — Zeeshan Ahmad Khalil, Jul 05 '23 at 12:04

score 9 · Answer 2 · answered Sep 22 '20 at 17:32

When passing { returning: true } in the options field of bulkInsert it will return the created objects.

    let createdOjects = await queryInterface.bulkInsert("table_name", data_to_be_inserted, { returning: true });

Also, you may pass an array with the fields you are interested in, e.g. the ID { returning: ['id'] } and this will return an array of IDs of the created objects

    let createdIds = await queryInterface.bulkInsert("table_name", data_to_be_inserted, { returning: ['id'] });

You can loop through the returned objects/ids and insert the nested objects using bulkInsert as well.

Sample code:

module.exports = {
  up: async (queryInterface) => {
    let courses = ["..."]
    let videos = ["..."]
    let videoIds = await queryInterface.bulkInsert("courses", courses, { returning: ["id"] });
    
    //add courseId to each video object -- depends on your scheme

     await queryInterface.bulkInsert("videos", videos);
  },

  down: async (queryInterface) => {
    await queryInterface.bulkDelete("videos", null, {});
    await queryInterface.bulkDelete("courses", null, {});
  },
};

NOTE: the `{ returning: true }` option is [only available for POSTGRES](https://sequelize.org/master/class/lib/model.js~Model.html#static-method-bulkCreate). If you're using a different dialect/database, it is ignored — morphatic, Oct 28 '20 at 21:32

score 1 · Answer 3 · answered Nov 25 '19 at 20:54

The bulkInsert method returns a promise that is resolved with an ID of the first inserted item. We can use this information to insert videos. It can look like this:

function getId( firstId, items, needly ) {
    for ( let i = 0; i < items.length; i++ ) {
        if ( items[i].title === needly ) {
            return firstId + i;
        }
    }

    return null;
}

exports.up = async ( queryInterface, Sequelize ) => {
    const courses = [
        {
            title: 'Course 1',
            description: '...',
        },
        {
            title: 'Course 2',
            description: '...',
        },
        {
            title: 'Course 3',
            description: '...',
        },
        {
            title: 'Course 4',
            description: '...',
        },
        {
            title: 'Course 5',
            description: '...',
        },
    ];

    const firstId = await queryInterface.bulkInsert( 'courses', courses, {} );
    const course2Id = getId( firstId, courses, 'Course 2' );
    const course5Id = getId( firstId, courses, 'Course 5' );

    return queryInterface.bulkInsert( 'categories', [
        { title: 'Video 1', description: '...', courseId: course2Id },
        { title: 'Video 2', description: '...', courseId: course2Id },
        { title: 'Video 3', description: '...', courseId: course5Id },
        { title: 'Video 4', description: '...', courseId: course5Id },
        { title: 'Video 5', description: '...', courseId: course5Id },
    ], {} );
};

exports.down = async ( queryInterface ) => {
    await queryInterface.bulkDelete( 'videos', null, {} );
    await queryInterface.bulkDelete( 'courses', null, {} );
}

Should mention that this technique will only work with incrementing integer IDs, but it's good to know that bulkInsert will resolve to the first inserted ID. — Hunter Mueller, Apr 16 '20 at 04:17

Sequelize: seed with associations

3 Answers3

Linked