feat: support include option in bulkInsert

[javiertury]

Pull Request check-list

Please make sure to review and check all of these items:

• Does npm run test or npm run test-DIALECT pass with this change (including linting)?
• Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
• Have you added new tests to prevent regressions?
• Is a documentation update included (if this change modifies existing APIs, or introduces new ones)?
• Did you update the typescript typings accordingly (if applicable)?
• Did you follow the commit message conventions explained in CONTRIBUTING.md?

Description of change

Closes #3277.

This PR makes bulkInsert capable of handling associations. It uses the include option, just like other model methods.

It requires only 1 insert statement for each association model. E.g. 100 instances with 100 children and 100 grandchildren are created in 3 insert statements.

[codecov]

Codecov Report

Merging #11307 into master will decrease coverage by 0.01%.
The diff coverage is 93.38%.

@@            Coverage Diff             @@
##           master   #11307      +/-   ##
==========================================
- Coverage   96.32%   96.31%   -0.02%     
==========================================
  Files          94       94              
  Lines        9069     9142      +73     
==========================================
+ Hits         8736     8805      +69     
- Misses        333      337       +4

Impacted Files	Coverage Δ
lib/model.js	96.46% <93.38%> (-0.23%)	⬇️
lib/dialects/postgres/connection-manager.js	95.77% <0%> (+1.4%)	⬆️

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update a39c63a...819c0ef. Read the comment docs.

[papb]

Hey, thanks for the PR. Can you show a code snippet showing the usage as an example?

[javiertury]

The same as regular create method.

        const Product = this.sequelize.define('Product', {
          title: Sequelize.STRING
        });
        const Tag = this.sequelize.define('Tag', {
          name: Sequelize.STRING
        });

        Product.hasMany(Tag);

        return this.sequelize.sync({ force: true }).then(() => {
          return Product.bulkCreate([{
            id: 1,
            title: 'Chair',
            Tags: [
              { id: 1, name: 'Alpha' },
              { id: 2, name: 'Beta' }
            ]
          }, {
            id: 2,
            title: 'Table',
            Tags: [
              { id: 3, name: 'Gamma' },
              { id: 4, name: 'Delta' }
            ]
          }], {
            include: [{
              model: Tag,
              myOption: 'option'
            }]
          }).then(savedProducts => {
            // ...
          });

[sushantdhiman]

Looks good, can you update typings https://github.com/sequelize/sequelize/blob/master/types/lib/model.d.ts#L707

[sushantdhiman]

For MySQL I have seen this behaviour with updateOnDuplicate #11223 (comment)

[javiertury]

I don't think we can reliably support this unless MySQL adds support for RETURNING * equivalent

That is exactly the root of the problem. PostgreSQL and SQL Server both support RETURNING and therefore don't have any issue.

[javiertury]

@sushantdhiman typings added

[sushantdhiman]

I haven't given new code a deep look but new tests looks good and old one passed :)

[papb]

I haven't given new code a deep look but new tests looks good and old one passed :)

Same from me! 👍

[sushantdhiman]

🎉 This PR is included in version 5.14.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[simonbrent]

Thanks a lot for implementing this feature, it will be very helpful.

I have one observation regarding it though - the options passed to recursiveBulkCreate are as follows:

const includeOptions =  _(Utils.cloneDeep(include))
              .omit(['association'])
              .defaults({
                transaction: options.transaction,
                logging: options.logging
              }).value();

but this means that if I want to validate at every stage of creation, I cannot just set validate: true as a top level option - instead I must set validate: true both at the top level and in every include.

Would you be willing to either allow the includeOptions to default validate to options.validate, or maybe if that is undesirable, support a validateAll option, which is used to set options.validate to true at the start of recursiveBulkCreate?

[javiertury]

I see your point about validation. I'm now wondering why validation is disabled by default. Is bulkCreate assumed to not receive user input? Is it about performance?

@simonbrent, are you using bulkCreate with user input?

[simonbrent]

@javiertury yes I am.
I've got a workaround for now in a beforeBulkCreate hook, which modifies options.include to set validate: options.validate for each entry in the include array.

I assume validation is disabled by default because it can be expensive.

[marinalohova]

Hi, I'm getting
"instance[include.association.accessors.set] is not a function"

My code is as following:

await this.models.products.bulkCreate(products, {
        updateOnDuplicate: ['name', 'description', 'subscriptionId', 'creditId'],
        returning: true,
        include: {
          model: this.models.subscriptions,
          as: 'subscription',
          updateOnDuplicate: ['subscriptionFee', 'subscriptionReoccurrence'],
          returning: true,
        },
      });

What could be the issue? Thanks!

[jeffersonveloso]

Hi, I'm getting
"instance[include.association.accessors.set] is not a function"

My code is as following:

await this.models.products.bulkCreate(products, {
        updateOnDuplicate: ['name', 'description', 'subscriptionId', 'creditId'],
        returning: true,
        include: {
          model: this.models.subscriptions,
          as: 'subscription',
          updateOnDuplicate: ['subscriptionFee', 'subscriptionReoccurrence'],
          returning: true,
        },
      });

What could be the issue? Thanks!

Have any solution for this problem ? @srslyimthebest