knex: ON DUPLICATE KEY UPDATE ?

let insert = knex(table).insert(data)
delete data.id 
let update = knex(table).update(data)
let query = util.format('%s on duplicate key update %s', 
  insert.toString(), update.toString().replace(/^update ([`"])[^\1]+\1 set/i, ''))
return knex.raw(query)

This version handles multi-record inserts. Remove the type annotations if you’re not using Typescript.

function insertOrUpdate(knex: Knex, tableName: string, data: any) {
  const firstData = data[0] ? data[0] : data;
  return knex.raw(knex(tableName).insert(data).toQuery() + " ON DUPLICATE KEY UPDATE " +
    Object.getOwnPropertyNames(firstData).map((field) => `${field}=VALUES(${field})`).join(", "));
}

I’ve wrote tiny lib, that adds onDuplicateUpdate function to Knex QueryBuilder. https://github.com/felixmosh/knex-on-duplicate-update

Utility funciton to generate upsert query Usage: upsert(data, 'tableName', [], ['colName']);

exports.upsert = (t, tableName, columnsToRetain, conflictOn) => {
    const insert = knex(tableName)
        .insert(t)
        .toString();
    const update = knex(tableName)
        .update(t)
        .toString();
    const keepValues = columnsToRetain.map((c) => `"${c}"=EXCLUDED."${c}"`).join(',');
    const conflictColumns = conflictOn.map((c) => `"${c.toString()}"`).join(',');
    let insertOrUpdateQuery = `${insert} ON CONFLICT( ${conflictColumns}) DO ${update}`;
    insertOrUpdateQuery = keepValues ? `${insertOrUpdateQuery}, ${keepValues}` : insertOrUpdateQuery;
    insertOrUpdateQuery = insertOrUpdateQuery.replace(`update "${tableName}"`, 'update');
    insertOrUpdateQuery = insertOrUpdateQuery.replace(`"${tableName}"`, tableName);
    return Promise.resolve(knex.raw(insertOrUpdateQuery));
};

Thought this might help someone:

var data= {
    uuid: req.params.uuid
    ,accountcode: req.params.account
    , comment: req.params.feedback || null
    ,createdAt : new Date()
    ,updatedAt : new Date()
    };
var query = knex('callfeedback').insert(data).toString();
query += ' on duplicate key update ' + knex.raw('updatedAt= ?, comment = ?',[new Date(),req.params.feedback]);

knex.raw(query).then()...

I’m using this to insert-or-update in knex (ES6):

updatePage(page) {
  const noop = () => undefined

  const { id } = page
  const content = { id, content: page }

  const result = await client('pages')
    .where({ id })
    .update(content)
    .catch(noop)
  || await client('pages')
    .insert(content)
    .catch(::console.error)

  console.log(result)
}

If someone could collect info how each database does this, maybe it could be specified what kind of API would be good for knex. If support is very different on every dialect, it might be better to have different API for each dialect.

This is pretty common request, so in this case separate implementations could be valid solution compared to unusable abstraction which works well only on one platform.

ON DUPLICATE KEY UPDATE It´s not supported by SQLITE.

@kibertoad version 0.21.14 working perfectly. Thks.

code sample:

const { knex } = require('../../config/db');
const { error } = require('../../helpers/logger');

const batchUpsert = async (users) => {
  try {
    await knex('users').insert(users).onConflict('email').merge();
  } catch (err) {
    error(err);
  }
};

module.exports = {
  batchUpsert,
};

Sorry about that.

@dotnil thx for the code. arent u missing ) closing util.format? anyways it works with it

mine is more generic.

From: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Desired output:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Input:

knex.raw('INSERT INTO table (a,b,c) values (?, ?, ?) ON DUPLICATE KEY UPDATE c=c+1', [1, 2, 3]);

It is possible to UPSERT multiple values at one time:

  let url = new URL('https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest')
  url.search = new URLSearchParams({start:1, limit:100, convert:'USD'})
  let response = await fetch(url, {headers:{'X-CMC_PRO_API_KEY':'xxxxxxxxxxxxxxxxxxxxxxx'}})
  let data = await response.json()
  let coins = data.data.map(coin => {
    return {
      'id' : coin.id,
      'symbol' : coin.symbol,
      'name' : coin.name,
      'slug' : coin.slug,
      'ranking': coin.cmc_rank,
      'price': coin.quote.USD.price,
      'volume24h': coin.quote.USD.volume_24h,
      'change1h': coin.quote.USD.percent_change_1h,
      'change24h': coin.quote.USD.percent_change_24h,
      'change7d': coin.quote.USD.percent_change_7d,
      'marketCap': coin.quote.USD.market_cap,
    }
  })
  let columns = Object.keys(coins[0]).join(',')
  let values = coins.reduce((acc, current) => {
    let values = Object.values(current).map(x => `'${x}'`).join(',')
    return acc + `(${values}),`
  }, '')
  values = values.slice(0,-1)
  knex.raw(`
    INSERT INTO coins (${columns})
    VALUES ${values} ON DUPLICATE KEY UPDATE 
    name=VALUES(name),
    slug=VALUES(slug),
    ranking=VALUES(ranking),
    price=VALUES(price),
    volume24h=VALUES(volume24h),
    change1h=VALUES(change1h),
    change24h=VALUES(change24h),
    change7d=VALUES(change7d),
    marketCap=VALUES(marketCap)
  `)
  .catch((err) => {
    console.log('UPSERT error:', err.message);
  })
  .then(() => console.log('UPSERT done'))

@ivoneijr Please try version ^0.21.14, and if that still doesn’t work, please share the code you are using and the error that you are getting.

@NathanJPhillips it´s possible to make similar code with “batchInsert”?

Here a quick example about how to insert batch data or update few fields.

const { knex } = require('../../config/db');
const { error } = require('../../helpers/logger');

const batchUpsert = async (users) => {
  try {
    const query = `${knex('users')
      .insert(users)
      .toQuery()} ON CONFLICT (email) DO UPDATE SET active = EXCLUDED.active, name = EXCLUDED.name;`;

    await knex.raw(query);
  } catch (err) {
    error(err);
  }
};

module.exports = {
  upsertBatch,
};

Knew has now a built in support for this, no more query concats 🎉

@NathanJPhillips it´s possible to make similar code with “batchInsert”?

Here a quick example about how to insert batch data or update few fields.

const { knex } = require('../../config/db');
const { error } = require('../../helpers/logger');

const batchUpsert = async (users) => {
  try {
    const query = `${knex('users')
      .insert(users)
      .toQuery()} ON CONFLICT (email) DO UPDATE SET active = EXCLUDED.active, name = EXCLUDED.name;`;

    await knex.raw(query);
  } catch (err) {
    error(err);
  }
};

module.exports = {
  upsertBatch,
};

@leventov do you happen to have an example of how one might use your function? Having difficulties using it here. Thanks in advance! And while I have your attention, any chance this can be used for batch style inserts?

Here’s an improvement of @j0h’s version from https://github.com/knex/knex/issues/701#issuecomment-488075856 for Postgres. It doesn’t require bothering with the constraint name, and re-uses inserted values via the special excluded table, that appears to work (unlike the original version) e. g. when an integer is inserted as JSONB.

/**
 * Knex Postgres INSERT or UPDATE
 * @param {string} table - table name
 * @param {Object} keys - constraint key/value object (insert)
 * @param {Object} payload - row data values (insert and/or update)
 * @returns {Promise} - knex raw response
 */
async function insertOrUpdateQuery(table, keys, payload) {
  const update = Object.keys(payload)
    .map(key => knex.raw("?? = EXCLUDED.??", [key, key]))
    .join(", ")

  const constraint = Object.keys(keys)
    .map(key => knex.raw("??", key))
    .join(", ")

  const sql = `? ON CONFLICT (${constraint}) 
               DO ${(update && `UPDATE SET ${update}`) || "NOTHING"};`
  return knex.raw(sql, [
    knex
      .insert({
        ...keys,
        ...payload,
      })
      .into(table),
  ])
}

@dcbasso I’ve made one for postgress but perhaps you could adapt it for mysql: https://gist.github.com/adnanoner/b6c53482243b9d5d5da4e29e109af9bd

@NathanJPhillips it´s possible to make similar code with “batchInsert”?

@shikasta-kashti Ah, that’s because in your query the table name is wrapped with " instead of `. Give this regex a try /^update ([`"])[^\1]+\1 set/i