How to bulk "upsert" in bookshelf?

Question

I need to import the data from a CSV file (around 20K rows) to my database. Some of the rows may already exist in the database, thus they only need to be updated, but the new rows must be inserted. If any of the operations fail, the transaction must be cancelled.

How can I do this? This is the code I was using:

var vehicle = {
                    id: row.id,
                    lastUpdate: moment(row.lastUpdate, 'DD/MM/YYYY - HH:mm').toDate(),
                    version: row.version,
                    color: row.color,
                    location: row.location,
                    status: row.status
                };

                Vehicle.forge({
                    id: row.id
                })
                    .save(vehicle, { transacting: t, patch: true })
                    .then(model => {
                        console.log('*************' + vehicle.id);
                    })
                    .catch(Vehicle.NoRowsUpdatedError, err => {
                        // There are no rows for this chassis, so let's insert it
                        Vehicle.forge(vehicle)
                            .save(null, { transacting: t, method: 'insert' })
                            .then(model => {
                                console.log('++++++++++++++' + vehicle.id);
                            })
                            .catch(err => {
                                console.log(`INSERT ERROR: ${err.message}`);
                                t.rollback();
                                return res.json({ status: false, count: 0, error: err.message });
                            });
                    })
                    .catch(err => {
                        console.log(`UPDATE ERROR: ${err.message}`);
                        t.rollback();
                        return res.json({ status: false, count: 0, error: err.message });
                    });

This code is in a for loop, but it fails in the second iteration, probably because of concurrency between the promises.

I also tried to add a custom function to my model file, but it says that the function does not exist.

let bookshelf = require('./base');

var Vehicle,
    Vehicles;

Vehicle = bookshelf.Model.extend({
    tableName: 'vehicles',

    /**
     * Insert a model based on data
     * @param {Object} data
     * @param {Object} [options] Options for model.save
     * @return {Promise(bookshelf.Model)}
     */
    create: function (data, options) {
        return this.forge(data).save(null, options);
    },

    /**
     * Select a model based on a query
     * @param {Object} [query]
     * @param {Object} [options] Options for model.fetch
     * @param {Boolean} [options.require=false]
     * @return {Promise(bookshelf.Model)}
     */
    findOne: function (query, options) {
        options = extend({ require: true }, options);
        return this.forge(query).fetch(options);
    },

    /**
     * Select a model based on data and update if found, insert if not found
     * @param {Object} selectData Data for select
     * @param {Object} updateData Data for update
     * @param {Object} [options] Options for model.save
     */
    upsert: function (selectData, updateData, options) {
        return this.findOne(selectData, extend(options, { require: false }))
            .bind(this)
            .then(function (model) {
                return model
                    ? model.save(
                        updateData,
                        extend({ patch: true, method: 'update' }, options)
                    )
                    : this.create(
                        extend(selectData, updateData),
                        extend(options, { method: 'insert' })
                    )
            });
    }
});

Vehicles = bookshelf.Collection.extend({
    model: Vehicle
});

module.exports = {
    Vehicle: bookshelf.model('Vehicle', Vehicle),
    Vehicles: bookshelf.collection('Vehicles', Vehicles)
};

Answer 1

Instead of using bookshelf, you could utilize knex directly to do this. Just grab the instance of knex you pass to bookshelf, and you can use it like this:

knex.transaction((trx) => {
  return Bluebird.map(vehicles, vehicle => {
    const insert = knex('vehicles').insert(vehicle).toString();
    delete vehicle.id;
    const update = knex('vehicles').update(vehicle).toString();
    const set = update.substring(18);
    return trx.raw(`${insert} ON CONFLICT (id) DO UPDATE ${set}`);
  });
});

We can utilize Knex's handy toString method to generate most of our raw query for us; in this way, we can do an upsert even though it's not directly supported by Knex. Bluebird's map function is perfect for cleanly handling an array of data like this and would let you await having to loop through it altogether.