Node.js ( knex / bootshelf helper)

js - knex_bulkupdate

'use strict';
/*
  CUSTOM METHODS TO EXTEND ORM
*/
module.exports = {

  /*
    var instance = new orm.bulkUpsert({
      knex: knex,
      model: 'modelName',
      update: ['uniqueField','field1','field2']
    });

    instance.add( [ 'uniqueValue','value1','value2' ] );

    instance.save( () => {
      res.send(data);
    });
  */

  // Upsert many fields with many values.

  bulkUpsert: class bulkUpsert {

    constructor(config) {

      this.knex = config.knex;
      this.values = '';
      this.fields = config.update;
      this.model = config.model;
      this.hasSomethingToSave = false;

    }

    add(fields) {

      this.values += this.values ? ',' : '';

      fields.forEach((f, i) => {

        this.values += ( i ) ? `,` : `(`;

        this.values += ( f == null ) ? `''` : `'${f}'`; // nulls to empty

      });

      this.values += ')';

      this.hasSomethingToSave = true;

    }

    save(cb) {

      let sql = 'INSERT INTO `' + this.model + '` (';
      let update = '';

      this.fields.forEach((f, i) => {

        if (i) {

          sql += `,${f}`;
          update += `,${f}=VALUES(${f})`;

        } else {

          sql += f;
          update += `${f}=VALUES(${f})`;

        }

      });

      sql += `) VALUES ${this.values} ON DUPLICATE KEY UPDATE ${update}`;

      if (this.hasSomethingToSave) {

        this.knex.raw(sql).then(() => cb(null, { success: true }));

      } else {

        cb(null, { success: 'no hubo cambios.' });

      }

    }

  }
};