Method upsert

lib/dialects/abstract/query-interface.js:789–831

* Upsert * * @param {string} tableName table to upsert on * @param {object} insertValues values to be inserted, mapped to field name * @param {object} updateValues values to be updated, mapped to field name * @param {object} where where conditions, which can be used for UPDA

(tableName, insertValues, updateValues, where, options)

   * @returns {Promise<boolean,?number>} Resolves an array with <created, primaryKey>
   */
  async upsert(tableName, insertValues, updateValues, where, options) {
    options = { ...options };

    const model = options.model;

    options.type = QueryTypes.UPSERT;
    options.updateOnDuplicate = Object.keys(updateValues);
    options.upsertKeys = options.conflictFields || [];

    if (options.upsertKeys.length === 0) {
      const primaryKeys = Object.values(model.primaryKeys).map(item => item.field);
      const uniqueKeys = Object.values(model.uniqueKeys).filter(c => c.fields.length > 0).map(c => c.fields);
      const indexKeys = Object.values(model._indexes).filter(c => c.unique && c.fields.length > 0).map(c => c.fields);
      // For fields in updateValues, try to find a constraint or unique index
      // that includes given field. Only first matching upsert key is used.
      for (const field of options.updateOnDuplicate) {
        const uniqueKey = uniqueKeys.find(fields => fields.includes(field));
        if (uniqueKey) {
          options.upsertKeys = uniqueKey;
          break;
        }

        const indexKey = indexKeys.find(fields => fields.includes(field));
        if (indexKey) {
          options.upsertKeys = indexKey;
          break;
        }
      }

      // Always use PK, if no constraint available OR update data contains PK
      if (
        options.upsertKeys.length === 0
        || _.intersection(options.updateOnDuplicate, primaryKeys).length
      ) {
        options.upsertKeys = primaryKeys;
      }

      options.upsertKeys = _.uniq(options.upsertKeys);
    }

    const sql = this.queryGenerator.insertQuery(tableName, insertValues, model.rawAttributes, options);
    return await this.sequelize.query(sql, options);
  }

  /**
   * Insert multiple records into a table