Sequelize/packages/core/test/integration/model/bulk-create.test.js

'use strict';

const { expect } = require('chai');
const {
  beforeEach2,
  createMultiTransactionalTestSequelizeInstance,
  sequelize,
} = require('../support');
const { col, DataTypes, Op } = require('@sequelize/core');

const dialect = sequelize.dialect;
const dialectName = dialect.name;

describe('Model', () => {
  beforeEach(async function () {
    this.customSequelize = await createMultiTransactionalTestSequelizeInstance(this.sequelize);

    this.User = this.customSequelize.define('User', {
      username: DataTypes.STRING,
      secretValue: {
        type: DataTypes.STRING,
        field: 'secret_value',
      },
      data: DataTypes.STRING,
      intVal: DataTypes.INTEGER,
      theDate: DataTypes.DATE,
      aBool: DataTypes.BOOLEAN,
      uniqueName: { type: DataTypes.STRING, unique: true },
    });
    this.Account = this.customSequelize.define('Account', {
      accountName: DataTypes.STRING,
    });
    this.Student = this.customSequelize.define('Student', {
      no: { type: DataTypes.INTEGER, primaryKey: true },
      name: { type: DataTypes.STRING, allowNull: false },
    });
    this.Car = this.customSequelize.define('Car', {
      plateNumber: {
        type: DataTypes.STRING,
        primaryKey: true,
        field: 'plate_number',
      },
      color: {
        type: DataTypes.TEXT,
      },
    });

    await this.customSequelize.sync({ force: true });
  });

  afterEach(function () {
    return this.customSequelize.close();
  });

  describe('bulkCreate', () => {
    if (dialect.supports.transactions) {
      it('supports transactions', async function () {
        const User = this.customSequelize.define('User', {
          username: DataTypes.STRING,
        });
        await User.sync({ force: true });
        const transaction = await this.customSequelize.startUnmanagedTransaction();
        await User.bulkCreate([{ username: 'foo' }, { username: 'bar' }], { transaction });
        const count1 = await User.count();
        const count2 = await User.count({ transaction });
        expect(count1).to.equal(0);
        expect(count2).to.equal(2);
        await transaction.rollback();
      });
    }

    it('should not alter options', async function () {
      const User = this.customSequelize.define('User');
      await User.sync({ force: true });
      const options = { anOption: 1 };
      await User.bulkCreate([{}], options);
      expect(options).to.eql({ anOption: 1 });
    });

    it('should be able to set createdAt and updatedAt if using silent: true', async function () {
      const User = this.customSequelize.define(
        'user',
        {
          name: DataTypes.STRING,
        },
        {
          timestamps: true,
        },
      );

      const createdAt = new Date(2012, 10, 10, 10, 10, 10);
      const updatedAt = new Date(2011, 11, 11, 11, 11, 11);
      const values = Array.from({ length: 10 }).fill({
        createdAt,
        updatedAt,
      });

      await User.sync({ force: true });

      await User.bulkCreate(values, {
        silent: true,
      });

      const users = await User.findAll({
        where: {
          updatedAt: {
            [Op.ne]: null,
          },
        },
      });

      for (const user of users) {
        expect(createdAt.getTime()).to.equal(user.get('createdAt').getTime());
        expect(updatedAt.getTime()).to.equal(user.get('updatedAt').getTime());
      }
    });

    it('should not fail on validate: true and individualHooks: true', async function () {
      const User = this.customSequelize.define('user', {
        name: DataTypes.STRING,
      });

      await User.sync({ force: true });

      await User.bulkCreate([{ name: 'James' }], { validate: true, individualHooks: true });
    });

    it('should not map instance dataValues to fields with individualHooks: true', async function () {
      const User = this.customSequelize.define('user', {
        name: DataTypes.STRING,
        type: {
          type: DataTypes.STRING,
          allowNull: false,
          field: 'user_type',
        },
        createdAt: {
          field: 'created_at',
        },
        updatedAt: {
          field: 'modified_at',
        },
      });

      await User.sync({ force: true });

      await User.bulkCreate(
        [
          { name: 'James', type: 'A' },
          { name: 'Alan', type: 'Z' },
        ],
        { individualHooks: true },
      );
    });

    it('should not insert NULL for unused fields', async function () {
      const Beer = this.customSequelize.define('Beer', {
        style: DataTypes.STRING,
        size: DataTypes.INTEGER,
      });

      await Beer.sync({ force: true });

      await Beer.bulkCreate(
        [
          {
            style: 'ipa',
          },
        ],
        {
          logging(sql) {
            switch (dialectName) {
              case 'postgres':
              case 'ibmi': {
                expect(sql).to.include(
                  'INSERT INTO "Beers" ("id","style","createdAt","updatedAt") VALUES (DEFAULT',
                );

                break;
              }

              case 'db2': {
                expect(sql).to.include(
                  'INSERT INTO "Beers" ("style","createdAt","updatedAt") VALUES',
                );

                break;
              }

              case 'mssql': {
                expect(sql).to.include('INSERT INTO [Beers] ([style],[createdAt],[updatedAt]) ');

                break;
              }

              default: {
                // mysql, sqlite3
                expect(sql).to.include(
                  'INSERT INTO `Beers` (`id`,`style`,`createdAt`,`updatedAt`) VALUES (NULL',
                );
              }
            }
          },
        },
      );
    });

    it('properly handles disparate field lists', async function () {
      const data = [
        { username: 'Peter', secretValue: '42', uniqueName: '1' },
        { username: 'Paul', uniqueName: '2' },
        { username: 'Steve', uniqueName: '3' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ where: { username: 'Paul' } });
      expect(users.length).to.equal(1);
      expect(users[0].username).to.equal('Paul');
      expect(users[0].secretValue).to.be.null;
    });

    it('inserts multiple values respecting the white list', async function () {
      const data = [
        { username: 'Peter', secretValue: '42', uniqueName: '1' },
        { username: 'Paul', secretValue: '23', uniqueName: '2' },
      ];

      await this.User.bulkCreate(data, { fields: ['username', 'uniqueName'] });
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(users[0].secretValue).to.be.null;
      expect(users[1].username).to.equal('Paul');
      expect(users[1].secretValue).to.be.null;
    });

    it('should store all values if no whitelist is specified', async function () {
      const data = [
        { username: 'Peter', secretValue: '42', uniqueName: '1' },
        { username: 'Paul', secretValue: '23', uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(users[0].secretValue).to.equal('42');
      expect(users[1].username).to.equal('Paul');
      expect(users[1].secretValue).to.equal('23');
    });

    it('parses values that come from the database', async function () {
      // Because bulkCreate uses a different code path than create,
      // there was a bug where values coming back from the database
      // weren't being run through the parsers/validators.
      // This test ensures that the bug is fixed.
      // https://github.com/sequelize/sequelize/issues/15640
      const [user] = await this.User.bulkCreate([{ theDate: new Date(), uniqueName: '1' }]);

      expect(user.theDate).to.be.instanceOf(Date);
    });

    it('should set isNewRecord = false', async function () {
      const data = [
        { username: 'Peter', secretValue: '42', uniqueName: '1' },
        { username: 'Paul', secretValue: '23', uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      for (const user of users) {
        expect(user.isNewRecord).to.equal(false);
      }
    });

    it('saves data with single quote', async function () {
      const quote = "Single'Quote";
      const data = [
        { username: 'Peter', data: quote, uniqueName: '1' },
        { username: 'Paul', data: quote, uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(users[0].data).to.equal(quote);
      expect(users[1].username).to.equal('Paul');
      expect(users[1].data).to.equal(quote);
    });

    it('saves data with double quote', async function () {
      const quote = 'Double"Quote';
      const data = [
        { username: 'Peter', data: quote, uniqueName: '1' },
        { username: 'Paul', data: quote, uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(users[0].data).to.equal(quote);
      expect(users[1].username).to.equal('Paul');
      expect(users[1].data).to.equal(quote);
    });

    it('saves stringified JSON data', async function () {
      const json = JSON.stringify({ key: 'value' });
      const data = [
        { username: 'Peter', data: json, uniqueName: '1' },
        { username: 'Paul', data: json, uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(users[0].data).to.equal(json);
      expect(users[1].username).to.equal('Paul');
      expect(users[1].data).to.equal(json);
    });

    it('properly handles a model with a length column', async function () {
      const UserWithLength = this.customSequelize.define('UserWithLength', {
        length: DataTypes.INTEGER,
      });

      await UserWithLength.sync({ force: true });

      await UserWithLength.bulkCreate([{ length: 42 }, { length: 11 }]);
    });

    it('stores the current date in createdAt', async function () {
      const data = [
        { username: 'Peter', uniqueName: '1' },
        { username: 'Paul', uniqueName: '2' },
      ];

      await this.User.bulkCreate(data);
      const users = await this.User.findAll({ order: ['id'] });
      expect(users.length).to.equal(2);
      expect(users[0].username).to.equal('Peter');
      expect(Number.parseInt(Number(users[0].createdAt) / 5000, 10)).to.be.closeTo(
        Number.parseInt(Date.now() / 5000, 10),
        1.5,
      );
      expect(users[1].username).to.equal('Paul');
      expect(Number.parseInt(Number(users[1].createdAt) / 5000, 10)).to.be.closeTo(
        Number.parseInt(Date.now() / 5000, 10),
        1.5,
      );
    });

    it('emits an error when validate is set to true', async function () {
      const Tasks = this.customSequelize.define('Task', {
        name: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        code: {
          type: DataTypes.STRING,
          validate: {
            len: [3, 10],
          },
        },
      });

      await Tasks.sync({ force: true });

      try {
        await Tasks.bulkCreate(
          [{ name: 'foo', code: '123' }, { code: '1234' }, { name: 'bar', code: '1' }],
          { validate: true },
        );
      } catch (error) {
        const expectedValidationError = 'Validation len on code failed';
        const expectedNotNullError = 'notNull violation: Task.name cannot be null';

        expect(error.toString())
          .to.include(expectedValidationError)
          .and.to.include(expectedNotNullError);
        const { errors } = error;
        expect(errors).to.have.length(2);

        const e0name0 = errors[0].errors.get('name')[0];

        expect(errors[0].record.code).to.equal('1234');
        expect(e0name0.type || e0name0.origin).to.equal('notNull violation');

        expect(errors[1].record.name).to.equal('bar');
        expect(errors[1].record.code).to.equal('1');
        expect(errors[1].errors.get('code')[0].message).to.equal(expectedValidationError);
      }
    });

    it("doesn't emit an error when validate is set to true but our selectedValues are fine", async function () {
      const Tasks = this.customSequelize.define('Task', {
        name: {
          type: DataTypes.STRING,
          validate: {
            notEmpty: true,
          },
        },
        code: {
          type: DataTypes.STRING,
          validate: {
            len: [3, 10],
          },
        },
      });

      await Tasks.sync({ force: true });

      await Tasks.bulkCreate([{ name: 'foo', code: '123' }, { code: '1234' }], {
        fields: ['code'],
        validate: true,
      });
    });

    it('should allow blank arrays (return immediately)', async function () {
      const Worker = this.customSequelize.define('Worker', {});
      await Worker.sync();
      const workers = await Worker.bulkCreate([]);
      expect(workers).to.be.ok;
      expect(workers.length).to.equal(0);
    });

    it('should allow blank creates (with timestamps: false)', async function () {
      const Worker = this.customSequelize.define('Worker', {}, { timestamps: false });
      await Worker.sync();
      const workers = await Worker.bulkCreate([{}, {}]);
      expect(workers).to.be.ok;
    });

    it('should allow autoincremented attributes to be set', async function () {
      const Worker = this.customSequelize.define('Worker', {}, { timestamps: false });
      await Worker.sync();

      await Worker.bulkCreate([{ id: 5 }, { id: 10 }]);

      const workers = await Worker.findAll({ order: [['id', 'ASC']] });
      expect(workers[0].id).to.equal(5);
      expect(workers[1].id).to.equal(10);
    });

    if (dialect.supports.schemas) {
      it('should support schemas', async function () {
        const Dummy = this.customSequelize.define(
          'Dummy',
          {
            foo: DataTypes.STRING,
            bar: DataTypes.STRING,
          },
          {
            schema: 'space1',
            tableName: 'Dummy',
          },
        );

        await this.customSequelize.createSchema('space1');
        await Dummy.sync({ force: true });

        await Dummy.bulkCreate([
          { foo: 'a', bar: 'b' },
          { foo: 'c', bar: 'd' },
        ]);
      });
    }

    if (dialect.supports.inserts.ignoreDuplicates || dialect.supports.inserts.onConflictDoNothing) {
      it('should support the ignoreDuplicates option', async function () {
        const data = [
          { uniqueName: 'Peter', secretValue: '42' },
          { uniqueName: 'Paul', secretValue: '23' },
        ];

        await this.User.bulkCreate(data, { fields: ['uniqueName', 'secretValue'] });
        data.push({ uniqueName: 'Michael', secretValue: '26' });

        await this.User.bulkCreate(data, {
          fields: ['uniqueName', 'secretValue'],
          ignoreDuplicates: true,
        });
        const users = await this.User.findAll({ order: ['id'] });
        expect(users.length).to.equal(3);
        expect(users[0].uniqueName).to.equal('Peter');
        expect(users[0].secretValue).to.equal('42');
        expect(users[1].uniqueName).to.equal('Paul');
        expect(users[1].secretValue).to.equal('23');
        expect(users[2].uniqueName).to.equal('Michael');
        expect(users[2].secretValue).to.equal('26');
      });
    } else {
      it('should throw an error when the ignoreDuplicates option is passed', async function () {
        const data = [
          { uniqueName: 'Peter', secretValue: '42' },
          { uniqueName: 'Paul', secretValue: '23' },
        ];

        await this.User.bulkCreate(data, { fields: ['uniqueName', 'secretValue'] });
        data.push({ uniqueName: 'Michael', secretValue: '26' });

        try {
          await this.User.bulkCreate(data, {
            fields: ['uniqueName', 'secretValue'],
            ignoreDuplicates: true,
          });
        } catch (error) {
          expect(error.message).to.equal(
            `${dialectName} does not support the ignoreDuplicates option.`,
          );
        }
      });
    }

    if (dialect.supports.inserts.updateOnDuplicate) {
      describe('updateOnDuplicate', () => {
        it('should support the updateOnDuplicate option', async function () {
          const data = [
            { uniqueName: 'Peter', secretValue: '42' },
            { uniqueName: 'Paul', secretValue: '23' },
          ];

          await this.User.bulkCreate(data, {
            fields: ['uniqueName', 'secretValue'],
            updateOnDuplicate: ['secretValue'],
          });
          const new_data = [
            { uniqueName: 'Peter', secretValue: '43' },
            { uniqueName: 'Paul', secretValue: '24' },
            { uniqueName: 'Michael', secretValue: '26' },
          ];
          await this.User.bulkCreate(new_data, {
            fields: ['uniqueName', 'secretValue'],
            updateOnDuplicate: ['secretValue'],
          });
          const users = await this.User.findAll({ order: ['id'] });
          expect(users.length).to.equal(3);
          expect(users[0].uniqueName).to.equal('Peter');
          expect(users[0].secretValue).to.equal('43');
          expect(users[1].uniqueName).to.equal('Paul');
          expect(users[1].secretValue).to.equal('24');
          expect(users[2].uniqueName).to.equal('Michael');
          expect(users[2].secretValue).to.equal('26');
        });

        describe('should support the updateOnDuplicate option with primary keys', () => {
          it('when the primary key column names and model field names are the same', async function () {
            const data = [
              { no: 1, name: 'Peter' },
              { no: 2, name: 'Paul' },
            ];

            await this.Student.bulkCreate(data, {
              fields: ['no', 'name'],
              updateOnDuplicate: ['name'],
            });
            const new_data = [
              { no: 1, name: 'Peterson' },
              { no: 2, name: 'Paulson' },
              { no: 3, name: 'Michael' },
            ];
            await this.Student.bulkCreate(new_data, {
              fields: ['no', 'name'],
              updateOnDuplicate: ['name'],
            });
            const students = await this.Student.findAll({ order: ['no'] });
            expect(students.length).to.equal(3);
            expect(students[0].name).to.equal('Peterson');
            expect(students[0].no).to.equal(1);
            expect(students[1].name).to.equal('Paulson');
            expect(students[1].no).to.equal(2);
            expect(students[2].name).to.equal('Michael');
            expect(students[2].no).to.equal(3);
          });

          it('when the primary key column names and model field names are different', async function () {
            const data = [
              { plateNumber: 'abc', color: 'Grey' },
              { plateNumber: 'def', color: 'White' },
            ];

            await this.Car.bulkCreate(data, {
              fields: ['plateNumber', 'color'],
              updateOnDuplicate: ['color'],
            });
            const new_data = [
              { plateNumber: 'abc', color: 'Red' },
              { plateNumber: 'def', color: 'Green' },
              { plateNumber: 'ghi', color: 'Blue' },
            ];
            await this.Car.bulkCreate(new_data, {
              fields: ['plateNumber', 'color'],
              updateOnDuplicate: ['color'],
            });
            const cars = await this.Car.findAll({ order: ['plateNumber'] });
            expect(cars.length).to.equal(3);
            expect(cars[0].plateNumber).to.equal('abc');
            expect(cars[0].color).to.equal('Red');
            expect(cars[1].plateNumber).to.equal('def');
            expect(cars[1].color).to.equal('Green');
            expect(cars[2].plateNumber).to.equal('ghi');
            expect(cars[2].color).to.equal('Blue');
          });

          it('when the primary key column names and model field names are different and have unique constraints', async function () {
            const Person = this.customSequelize.define(
              'Person',
              {
                emailAddress: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  primaryKey: true,
                  unique: true,
                  field: 'email_address',
                },
                name: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  field: 'name',
                },
              },
              {},
            );

            await Person.sync({ force: true });
            const inserts = [{ emailAddress: 'a@example.com', name: 'Alice' }];
            const people0 = await Person.bulkCreate(inserts);
            expect(people0.length).to.equal(1);
            expect(people0[0].emailAddress).to.equal('a@example.com');
            expect(people0[0].name).to.equal('Alice');

            const updates = [
              { emailAddress: 'a@example.com', name: 'CHANGED NAME' },
              { emailAddress: 'b@example.com', name: 'Bob' },
            ];

            const people = await Person.bulkCreate(updates, {
              updateOnDuplicate: ['emailAddress', 'name'],
            });
            expect(people.length).to.equal(2);
            expect(people[0].emailAddress).to.equal('a@example.com');
            expect(people[0].name).to.equal('CHANGED NAME');
            expect(people[1].emailAddress).to.equal('b@example.com');
            expect(people[1].name).to.equal('Bob');
          });

          it('when the composite primary key column names and model field names are different', async function () {
            const Person = this.customSequelize.define(
              'Person',
              {
                systemId: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  primaryKey: true,
                  field: 'system_id',
                },
                system: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  primaryKey: true,
                  field: 'system',
                },
                name: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  field: 'name',
                },
              },
              {},
            );

            await Person.sync({ force: true });
            const inserts = [{ systemId: 1, system: 'system1', name: 'Alice' }];
            const people0 = await Person.bulkCreate(inserts);
            expect(people0.length).to.equal(1);
            expect(people0[0].systemId).to.equal(1);
            expect(people0[0].system).to.equal('system1');
            expect(people0[0].name).to.equal('Alice');

            const updates = [
              { systemId: 1, system: 'system1', name: 'CHANGED NAME' },
              { systemId: 1, system: 'system2', name: 'Bob' },
            ];

            const people = await Person.bulkCreate(updates, {
              updateOnDuplicate: ['systemId', 'system', 'name'],
            });
            expect(people.length).to.equal(2);
            expect(people[0].systemId).to.equal(1);
            expect(people[0].system).to.equal('system1');
            expect(people[0].name).to.equal('CHANGED NAME');
            expect(people[1].systemId).to.equal(1);
            expect(people[1].system).to.equal('system2');
            expect(people[1].name).to.equal('Bob');
          });

          it('when the primary key column names and model field names are different and have composite unique constraints', async function () {
            const Person = this.customSequelize.define(
              'Person',
              {
                id: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  primaryKey: true,
                  field: 'id',
                },
                systemId: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  unique: 'system_id_system_unique',
                  field: 'system_id',
                },
                system: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  unique: 'system_id_system_unique',
                  field: 'system',
                },
                name: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  field: 'name',
                },
              },
              {},
            );

            await Person.sync({ force: true });
            const inserts = [{ id: 1, systemId: 1, system: 'system1', name: 'Alice' }];
            const people0 = await Person.bulkCreate(inserts);
            expect(people0.length).to.equal(1);
            expect(people0[0].systemId).to.equal(1);
            expect(people0[0].system).to.equal('system1');
            expect(people0[0].name).to.equal('Alice');

            const updates = [
              { id: 1, systemId: 1, system: 'system1', name: 'CHANGED NAME' },
              { id: 2, systemId: 1, system: 'system2', name: 'Bob' },
            ];

            const people = await Person.bulkCreate(updates, {
              updateOnDuplicate: ['systemId', 'system', 'name'],
            });
            expect(people.length).to.equal(2);
            expect(people[0].systemId).to.equal(1);
            expect(people[0].system).to.equal('system1');
            expect(people[0].name).to.equal('CHANGED NAME');
            expect(people[1].systemId).to.equal(1);
            expect(people[1].system).to.equal('system2');
            expect(people[1].name).to.equal('Bob');
          });

          it('[#12516] when the primary key column names and model field names are different and have composite unique index constraints', async function () {
            const Person = this.customSequelize.define(
              'Person',
              {
                id: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  autoIncrement: true,
                  primaryKey: true,
                  field: 'id',
                },
                systemId: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  field: 'system_id',
                },
                system: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  field: 'system',
                },
                name: {
                  type: DataTypes.STRING,
                  allowNull: false,
                  field: 'name',
                },
              },
              {
                indexes: [
                  {
                    unique: true,
                    fields: ['system_id', 'system'],
                  },
                ],
              },
            );

            await Person.sync({ force: true });
            const inserts = [{ systemId: 1, system: 'system1', name: 'Alice' }];
            const people0 = await Person.bulkCreate(inserts);
            expect(people0.length).to.equal(1);
            expect(people0[0].systemId).to.equal(1);
            expect(people0[0].system).to.equal('system1');
            expect(people0[0].name).to.equal('Alice');

            const updates = [
              { systemId: 1, system: 'system1', name: 'CHANGED NAME' },
              { systemId: 1, system: 'system2', name: 'Bob' },
            ];

            const people = await Person.bulkCreate(updates, {
              updateOnDuplicate: ['systemId', 'system', 'name'],
            });
            expect(people.length).to.equal(2);
            expect(people[0].systemId).to.equal(1);
            expect(people[0].system).to.equal('system1');
            expect(people[0].name).to.equal('CHANGED NAME');
            expect(people[1].systemId).to.equal(1);
            expect(people[1].system).to.equal('system2');
            expect(people[1].name).to.equal('Bob');
          });
        });

        it('should reject for non array updateOnDuplicate option', async function () {
          const data = [
            { uniqueName: 'Peter', secretValue: '42' },
            { uniqueName: 'Paul', secretValue: '23' },
          ];

          await expect(this.User.bulkCreate(data, { updateOnDuplicate: true })).to.be.rejectedWith(
            'updateOnDuplicate option only supports non-empty array.',
          );
        });

        it('should reject for empty array updateOnDuplicate option', async function () {
          const data = [
            { uniqueName: 'Peter', secretValue: '42' },
            { uniqueName: 'Paul', secretValue: '23' },
          ];

          await expect(this.User.bulkCreate(data, { updateOnDuplicate: [] })).to.be.rejectedWith(
            'updateOnDuplicate option only supports non-empty array.',
          );
        });

        if (dialect.supports.inserts.conflictFields) {
          it('should respect the conflictAttributes option', async function () {
            const Permissions = this.customSequelize.define(
              'permissions',
              {
                userId: {
                  type: DataTypes.INTEGER,
                  allowNull: false,
                  field: 'user_id',
                },
                permissions: {
                  type: new DataTypes.ENUM('owner', 'admin', 'member'),
                  allowNull: false,
                  default: 'member',
                },
              },
              {
                timestamps: false,
              },
            );

            await Permissions.sync({ force: true });

            // We don't want to create this index with the table, since we don't want our sequelize instance
            // to know it exists.  This prevents it from being inferred.
            await this.customSequelize.queryInterface.addIndex('permissions', ['user_id'], {
              unique: true,
            });

            const initialPermissions = [
              {
                userId: 1,
                permissions: 'member',
              },
              {
                userId: 2,
                permissions: 'admin',
              },
              {
                userId: 3,
                permissions: 'owner',
              },
            ];

            const initialResults = await Permissions.bulkCreate(initialPermissions, {
              conflictAttributes: ['userId'],
              updateOnDuplicate: ['permissions'],
            });

            expect(initialResults.length).to.eql(3);

            for (let i = 0; i < 3; i++) {
              const result = initialResults[i];
              const exp = initialPermissions[i];

              expect(result).to.not.eql(null);
              expect(result.userId).to.eql(exp.userId);
              expect(result.permissions).to.eql(exp.permissions);
            }

            const newPermissions = [
              {
                userId: 1,
                permissions: 'owner',
              },
              {
                userId: 2,
                permissions: 'member',
              },
              {
                userId: 3,
                permissions: 'admin',
              },
            ];

            const newResults = await Permissions.bulkCreate(newPermissions, {
              conflictAttributes: ['userId'],
              updateOnDuplicate: ['permissions'],
            });

            expect(newResults.length).to.eql(3);

            for (let i = 0; i < 3; i++) {
              const result = newResults[i];
              const exp = newPermissions[i];

              expect(result).to.not.eql(null);
              expect(result.id).to.eql(initialResults[i].id);
              expect(result.userId).to.eql(exp.userId);
              expect(result.permissions).to.eql(exp.permissions);
            }
          });

          describe('conflictWhere', () => {
            const options = {
              conflictWhere: { time_deleted: null },
              conflictAttributes: ['user_id', 'foreign_id'],
              updateOnDuplicate: ['user_id', 'foreign_id', 'time_deleted'],
            };

            const vars = beforeEach2(async () => {
              const Memberships = sequelize.define(
                'memberships',
                {
                  // ID of the member (no foreign key constraint for testing purposes)
                  user_id: DataTypes.INTEGER,
                  // ID of what the member is a member of
                  foreign_id: DataTypes.INTEGER,
                  time_deleted: DataTypes.DATE,
                },
                {
                  createdAt: false,
                  updatedAt: false,
                  deletedAt: 'time_deleted',
                  indexes: [
                    {
                      fields: ['user_id', 'foreign_id'],
                      unique: true,
                      where: { time_deleted: null },
                    },
                  ],
                },
              );

              await Memberships.sync({ force: true });

              return { Memberships };
            });

            it('should insert items with conflictWhere', async () => {
              const { Memberships } = vars;

              const memberships = Array.from({ length: 10 })
                .fill()
                .map((_, i) => ({
                  user_id: i + 1,
                  foreign_id: i + 20,
                  time_deleted: null,
                }));

              const results = await Memberships.bulkCreate(memberships, options);

              for (let i = 0; i < 10; i++) {
                expect(results[i].user_id).to.eq(memberships[i].user_id);
                expect(results[i].team_id).to.eq(memberships[i].team_id);
                expect(results[i].time_deleted).to.eq(null);
              }
            });

            it('should not conflict with soft deleted memberships', async () => {
              const { Memberships } = vars;

              const memberships = Array.from({ length: 10 })
                .fill()
                .map((_, i) => ({
                  user_id: i + 1,
                  foreign_id: i + 20,
                  time_deleted: new Date(),
                }));

              let results = await Memberships.bulkCreate(memberships, options);

              for (let i = 0; i < 10; i++) {
                expect(results[i].user_id).to.eq(memberships[i].user_id);
                expect(results[i].team_id).to.eq(memberships[i].team_id);
                expect(results[i].time_deleted).to.not.eq(null);
              }

              results = await Memberships.bulkCreate(
                memberships.map(membership => ({
                  ...membership,
                  time_deleted: null,
                })),
                options,
              );

              for (let i = 0; i < 10; i++) {
                expect(results[i].user_id).to.eq(memberships[i].user_id);
                expect(results[i].team_id).to.eq(memberships[i].team_id);
                expect(results[i].time_deleted).to.eq(null);
              }

              const count = await Memberships.count();

              expect(count).to.eq(20);
            });

            it('should upsert existing memberships', async () => {
              const { Memberships } = vars;

              const memberships = Array.from({ length: 10 })
                .fill()
                .map((_, i) => ({
                  user_id: i + 1,
                  foreign_id: i + 20,
                  time_deleted: i % 2 ? new Date() : null,
                }));

              let results = await Memberships.bulkCreate(memberships, options);

              for (let i = 0; i < 10; i++) {
                expect(results[i].user_id).to.eq(memberships[i].user_id);
                expect(results[i].team_id).to.eq(memberships[i].team_id);
                if (i % 2) {
                  expect(results[i].time_deleted).to.not.eq(null);
                } else {
                  expect(results[i].time_deleted).to.eq(null);
                }
              }

              for (const membership of memberships) {
                membership.time_deleted;
              }

              results = await Memberships.bulkCreate(
                memberships.map(membership => ({
                  ...membership,
                  time_deleted: null,
                })),
                options,
              );

              for (let i = 0; i < 10; i++) {
                expect(results[i].user_id).to.eq(memberships[i].user_id);
                expect(results[i].team_id).to.eq(memberships[i].team_id);
                expect(results[i].time_deleted).to.eq(null);
              }

              const count = await Memberships.count({ paranoid: false });

              expect(count).to.eq(15);
            });
          });

          if (dialect.supports.inserts.onConflictWhere) {
            describe('conflictWhere', () => {
              const options = {
                conflictWhere: { time_deleted: null },
                conflictAttributes: ['user_id', 'foreign_id'],
                updateOnDuplicate: ['user_id', 'foreign_id', 'time_deleted'],
              };

              const vars = beforeEach2(async () => {
                const Memberships = sequelize.define(
                  'memberships',
                  {
                    // ID of the member (no foreign key constraint for testing purposes)
                    user_id: DataTypes.INTEGER,
                    // ID of what the member is a member of
                    foreign_id: DataTypes.INTEGER,
                    time_deleted: DataTypes.DATE,
                  },
                  {
                    createdAt: false,
                    updatedAt: false,
                    deletedAt: 'time_deleted',
                    indexes: [
                      {
                        fields: ['user_id', 'foreign_id'],
                        unique: true,
                        where: { time_deleted: null },
                      },
                    ],
                  },
                );

                await Memberships.sync({ force: true });

                return { Memberships };
              });

              it('should insert items with conflictWhere', async () => {
                const { Memberships } = vars;

                const memberships = Array.from({ length: 10 })
                  .fill()
                  .map((_, i) => ({
                    user_id: i + 1,
                    foreign_id: i + 20,
                    time_deleted: null,
                  }));

                const results = await Memberships.bulkCreate(memberships, options);

                for (let i = 0; i < 10; i++) {
                  expect(results[i].user_id).to.eq(memberships[i].user_id);
                  expect(results[i].team_id).to.eq(memberships[i].team_id);
                  expect(results[i].time_deleted).to.eq(null);
                }
              });

              it('should not conflict with soft deleted memberships', async () => {
                const { Memberships } = vars;

                const memberships = Array.from({ length: 10 })
                  .fill()
                  .map((_, i) => ({
                    user_id: i + 1,
                    foreign_id: i + 20,
                    time_deleted: new Date(),
                  }));

                let results = await Memberships.bulkCreate(memberships, options);

                for (let i = 0; i < 10; i++) {
                  expect(results[i].user_id).to.eq(memberships[i].user_id);
                  expect(results[i].team_id).to.eq(memberships[i].team_id);
                  expect(results[i].time_deleted).to.not.eq(null);
                }

                results = await Memberships.bulkCreate(
                  memberships.map(membership => ({
                    ...membership,
                    time_deleted: null,
                  })),
                  options,
                );

                for (let i = 0; i < 10; i++) {
                  expect(results[i].user_id).to.eq(memberships[i].user_id);
                  expect(results[i].team_id).to.eq(memberships[i].team_id);
                  expect(results[i].time_deleted).to.eq(null);
                }

                const count = await Memberships.count();

                expect(count).to.eq(20);
              });

              it('should upsert existing memberships', async () => {
                const { Memberships } = vars;

                const memberships = Array.from({ length: 10 })
                  .fill()
                  .map((_, i) => ({
                    user_id: i + 1,
                    foreign_id: i + 20,
                    time_deleted: i % 2 ? new Date() : null,
                  }));

                let results = await Memberships.bulkCreate(memberships, options);

                for (let i = 0; i < 10; i++) {
                  expect(results[i].user_id).to.eq(memberships[i].user_id);
                  expect(results[i].team_id).to.eq(memberships[i].team_id);
                  if (i % 2) {
                    expect(results[i].time_deleted).to.not.eq(null);
                  } else {
                    expect(results[i].time_deleted).to.eq(null);
                  }
                }

                for (const membership of memberships) {
                  membership.time_deleted;
                }

                results = await Memberships.bulkCreate(
                  memberships.map(membership => ({
                    ...membership,
                    time_deleted: null,
                  })),
                  options,
                );

                for (let i = 0; i < 10; i++) {
                  expect(results[i].user_id).to.eq(memberships[i].user_id);
                  expect(results[i].team_id).to.eq(memberships[i].team_id);
                  expect(results[i].time_deleted).to.eq(null);
                }

                const count = await Memberships.count({ paranoid: false });

                expect(count).to.eq(15);
              });
            });
          }
        }
      });
    }

    if (dialect.supports.returnValues) {
      describe('return values', () => {
        it('should make the auto incremented values available on the returned instances', async function () {
          const User = this.customSequelize.define('user', {});

          await User.sync({ force: true });

          const users0 = await User.bulkCreate([{}, {}, {}], {
            returning: true,
          });

          const actualUsers0 = await User.findAll({ order: ['id'] });
          const [users, actualUsers] = [users0, actualUsers0];
          expect(users.length).to.eql(actualUsers.length);
          for (const [i, user] of users.entries()) {
            expect(user.get('id')).to.be.ok;
            expect(user.get('id'))
              .to.equal(actualUsers[i].get('id'))
              .and.to.equal(i + 1);
          }
        });

        it('should make the auto incremented values available on the returned instances with custom fields', async function () {
          const User = this.customSequelize.define('user', {
            maId: {
              type: DataTypes.INTEGER,
              primaryKey: true,
              autoIncrement: true,
              field: 'yo_id',
            },
          });

          await User.sync({ force: true });

          const users0 = await User.bulkCreate([{}, {}, {}], {
            returning: true,
          });

          const actualUsers0 = await User.findAll({ order: ['maId'] });
          const [users, actualUsers] = [users0, actualUsers0];
          expect(users.length).to.eql(actualUsers.length);
          for (const [i, user] of users.entries()) {
            expect(user.get('maId')).to.be.ok;
            expect(user.get('maId'))
              .to.equal(actualUsers[i].get('maId'))
              .and.to.equal(i + 1);
          }
        });

        it('should only return fields that are not defined in the model (with returning: true)', async function () {
          const User = this.customSequelize.define('user');

          await User.sync({ force: true });

          await this.customSequelize.queryInterface.addColumn(
            'users',
            'not_on_model',
            DataTypes.STRING,
          );

          const users0 = await User.bulkCreate([{}, {}, {}], {
            returning: true,
          });

          const actualUsers0 = await User.findAll();
          const [users, actualUsers] = [users0, actualUsers0];
          expect(users.length).to.eql(actualUsers.length);
          for (const user of users) {
            expect(user.get()).not.to.have.property('not_on_model');
          }
        });

        it('should return fields that are not defined in the model (with returning: ["*"])', async function () {
          const User = this.customSequelize.define('user');

          await User.sync({ force: true });

          await this.customSequelize.queryInterface.addColumn(
            'users',
            'not_on_model',
            DataTypes.STRING,
          );

          const users0 = await User.bulkCreate([{}, {}, {}], {
            returning: [col('*')],
          });

          const actualUsers0 = await User.findAll();
          const [users, actualUsers] = [users0, actualUsers0];
          expect(users.length).to.eql(actualUsers.length);
          for (const user of users) {
            expect(user.get()).to.have.property('not_on_model');
          }
        });
      });
    }

    describe('enums', () => {
      it('correctly restores enum values', async function () {
        const Item = this.customSequelize.define('Item', {
          state: { type: DataTypes.ENUM(['available', 'in_cart', 'shipped']) },
          name: DataTypes.STRING,
        });

        await Item.sync({ force: true });
        await Item.bulkCreate([
          { state: 'in_cart', name: 'A' },
          { state: 'available', name: 'B' },
        ]);
        const item = await Item.findOne({ where: { state: 'available' } });
        expect(item.name).to.equal('B');
      });
    });

    it('should properly map field names to attribute names', async function () {
      const Maya = this.customSequelize.define('Maya', {
        name: DataTypes.STRING,
        secret: {
          field: 'secret_given',
          type: DataTypes.STRING,
        },
        createdAt: {
          field: 'created_at',
        },
        updatedAt: {
          field: 'updated_at',
        },
      });

      const M1 = { id: 1, name: 'Prathma Maya', secret: 'You are on list #1' };
      const M2 = { id: 2, name: 'Dwitiya Maya', secret: 'You are on list #2' };

      await Maya.sync({ force: true });
      const m0 = await Maya.create(M1);
      expect(m0.createdAt).to.be.ok;
      expect(m0.id).to.be.eql(M1.id);
      expect(m0.name).to.be.eql(M1.name);
      expect(m0.secret).to.be.eql(M1.secret);

      const [m] = await Maya.bulkCreate([M2]);

      // only attributes are returned, no fields are mixed
      expect(m.createdAt).to.be.ok;
      expect(m.created_at).to.not.exist;
      expect(m.secret_given).to.not.exist;
      expect(m.get('secret_given')).to.be.undefined;
      expect(m.get('created_at')).to.be.undefined;

      // values look fine
      expect(m.id).to.be.eql(M2.id);
      expect(m.name).to.be.eql(M2.name);
      expect(m.secret).to.be.eql(M2.secret);
    });

    describe('handles auto increment values', () => {
      it('should return auto increment primary key values', async function () {
        const Maya = this.customSequelize.define('Maya', {});

        const M1 = {};
        const M2 = {};

        await Maya.sync({ force: true });
        const ms = await Maya.bulkCreate([M1, M2], { returning: true });
        expect(ms[0].id).to.be.eql(1);
        expect(ms[1].id).to.be.eql(2);
      });

      it('should return supplied values on primary keys', async function () {
        const User = this.customSequelize.define('user', {});

        await User.sync({ force: true });

        const users0 = await User.bulkCreate([{ id: 1 }, { id: 2 }, { id: 3 }], {
          returning: true,
        });

        const actualUsers0 = await User.findAll({ order: [['id', 'ASC']] });
        const [users, actualUsers] = [users0, actualUsers0];
        expect(users.length).to.eql(actualUsers.length);

        expect(users[0].get('id')).to.equal(1).and.to.equal(actualUsers[0].get('id'));
        expect(users[1].get('id')).to.equal(2).and.to.equal(actualUsers[1].get('id'));
        expect(users[2].get('id')).to.equal(3).and.to.equal(actualUsers[2].get('id'));
      });

      it('should return supplied values on primary keys when some instances already exists', async function () {
        const User = this.customSequelize.define('user', {});

        await User.sync({ force: true });

        await User.bulkCreate([{ id: 1 }, { id: 3 }]);

        const users = await User.bulkCreate([{ id: 2 }, { id: 4 }, { id: 5 }], { returning: true });

        expect(users.length).to.eql(3);

        expect(users[0].get('id')).to.equal(2);
        expect(users[1].get('id')).to.equal(4);
        expect(users[2].get('id')).to.equal(5);
      });
    });

    describe('virtual attribute', () => {
      beforeEach(function () {
        this.User = this.customSequelize.define('user', {
          password: {
            type: DataTypes.VIRTUAL,
            validate: {
              customValidator: () => {
                throw new Error('always invalid');
              },
            },
          },
        });
      });

      it('should validate', async function () {
        try {
          await this.User.sync({ force: true });

          await this.User.bulkCreate([{ password: 'password' }], { validate: true });

          expect.fail();
        } catch (error) {
          expect(error.errors.length).to.equal(1);
          expect(error.errors[0].message).to.match(/.*always invalid.*/);
        }
      });

      it('should not validate', async function () {
        await this.User.sync({ force: true });

        const users0 = await this.User.bulkCreate([{ password: 'password' }], { validate: false });

        expect(users0.length).to.equal(1);

        const users = await this.User.bulkCreate([{ password: 'password' }]);

        expect(users.length).to.equal(1);
      });
    });
  });
});