Update if exists, insert if it doesn't

[adamscybot]

A feature I've really been missing is access to the INSERT ... ON DUPLICATE KEY UPDATE syntax. At the moment, there are 2 queries per save() but in some instances 1 query could replace the SELECT & UPDATE/INSERT operations.

I realise this means the object would not be populated without a further SELECT query, so this would exist more as a utility function. Possibly in Knex?

[tgriesser]

Hey @adamscybot thanks for the ticket. As you pointed out, this is likely something that would go into Knex. I personally haven't had too many cases for really needing an upsert, and it's not something that's consistent across all database dialects. Do you have an example case where this would be useful?

[tgriesser]

Yeah, I think this one is something I'm going to leave off the map for now, but I might look to add a plugin for something like this. But feel free to show me some use cases so I have a better sense for where this might best fit.

[adamscybot]

Most of the time, reading and then letting bookshelf decide to UPDATE or INSERT is fine. However, we have a case where we are pulling in an external data source and dumping it into our db. We then query this external source every day or so. This source either updates existing records or ads new ones (its basically a TV electronic program guide which is a good use case).

As this is 1000's of rows, it would be useful to halve the number of queries for efficiency reasons. This could be even better with batch insert support.

[tgriesser]

Yeah... Knex has batch insert support, by passing an array to the insert method. I'll open a ticket over there for looking into an upsert type implementation.

[danpaul]

I'm really happy with Knex and don't want to complain but since you're asking for use cases for an upsert, I'll post what brought me here. I actually seem to find the need for upsert pretty routinely. The particular problem I'm dealing with now is a log of posts read by users. There is a user id column and a post id column and I can't guarantee that a request to add an entry, hasn't already been made, so, it would be nice to be able add a new row with user id and post id if that row doesn't already exist (or, in my case upsert the identical row).

[danielzurawski]

The reason to use upsert is to avoid deadlocks in a busy system

[jordaaash]

@Playrom Can we reopen this? ON DUPLICATE KEY / ON CONFLICT statements are super important, and the workaround in Bookshelf is super hacky. Here's mine (using flow), as an example:

/* @flow */
'use strict';

import type Collection from 'bookshelf/lib/collection';
import type Model from 'bookshelf/lib/model';
import type QueryBuilder from 'knex/lib/query/builder';
import type Result from 'pg/lib/result';

const Sync = require('bookshelf/lib/sync');

const insert = Sync.prototype.insert;

Sync.prototype.insert = async function (...rest: any[]): void {
    let conflict: ?string | string[] = this.options.conflict;
    if (conflict == null) {
        return await insert.apply(this, rest);
    }
    else {
        const syncing: Model      = this.syncing;
        const query: QueryBuilder = this.query.insert(syncing.format(Object.assign(Object.create(null), syncing.attributes)));

        conflict = arrayWrap(conflict);

        const placeholders: '??'[] = Array(conflict.length).fill('??');
        const idColumn: string     = syncing.idColumn;
        const raw: Raw             = query.client.raw(dedent`
            ON CONFLICT (${ placeholders.join(', ') })
            DO NOTHING
            RETURNING ??
        `, conflict.concat(idColumn));

        const result: Result = await query.client.raw(`${ query.toQuery() } ${ raw.toQuery() }`);

        return result.rows.map(function (row: Object): any {
            return row[idColumn];
        });
    }
};

Usage:

const job: Job = await Job.create(
    { myUniqueColumn: 'someUniqueValue' },
    { conflict: 'my_unique_column' }
);

[mrpoptart]

I've handled this in a BaseModel class, which I extend for all of my models. This was partially stolen from work in https://github.com/bsiddiqui/bookshelf-modelbase, which I couldn't get to work.

const bookshelf = require('../config/bookshelf');

const BaseModel = bookshelf.Model.extend({},
  {
    /**
     * Select a model based on a query
     * @param {Object} selectData
     * @param {Function} [callback]
     * @return {Promise}
     */
    findOne: function (selectData, callback) {
      return this.forge(selectData).fetch(callback);
    },

    /**
     * @param {Object} selectData
     * @param {Object} updateData
     * @returns {Promise}
     */
    upsert: async function (selectData, updateData) {
      const existingModel = await this.findOne(selectData);
      if (existingModel) {
        return await existingModel.set(updateData).save();
      } else {
        return await new this(updateData).save();
      }
    }
  },
);

module.exports = BaseModel;

const BaseModel = require('./BaseModel');

const Account = BaseModel.extend({
  tableName: 'accounts',
  hasTimestamps: true,
});

module.exports = Account;

Usage:

transactions.forEach(async (transaction) => {
  await Transaction.upsert(
    {
      transaction_id: transaction.transaction_id,
    },
    {
      user_id: user_id,
      account_id: transaction.account_id,
      account_owner: transaction.account_owner,
    }
  }
}

[JuliaRakitina]

Still no official solution ?

[singhpratik17]

"Upsert" - anything yet?

[fl0w]

I think it's saner to do INSERT .. ON CONFLICT DO NOTHING RETURNING * (PostgreSQL), but Knex doesn't support it so I wrap my query in a raw that when needed.