Knexjs If not exist insert else update

Question

How can this code be optimized? I don't want to call where 2 times... Is it possible to have better query than this?

    return self.db.clientDevices.where(device).then(function (rows) {
        if (rows.length != 1) {
            device.key = value;
            self.db.clientDevices.insert(device).then();
        } else {
            self.db.clientDevices.where(device).update(device).then();
        }
    });

score 9 · Answer 1 · answered Jul 20 '18 at 13:42

Assuming you have an unique key (post_id in this example) and an array of objects (blogPosts), you can use the following helper method:

function insertOrUpdate(tableName, blogPosts){
    return knex.transaction(trx => {
        let queries = blogPosts.map(tuple =>
          trx.raw(util.format('%s ON CONFLICT (post_id) DO UPDATE SET %s',
            trx(tableName).insert(tuple).toString().toString(),
            trx(tableName).update(tuple).whereRaw(`'${tableName}'.post_id = '${tuple.post_id}'`).toString().replace(/^update\s.*\sset\s/i, '')
          ))               
          .transacting(trx)
        );
        return Promise.all(queries).then(trx.commit).catch(trx.rollback);
    })
}

And invoke it as such:

insertOrUpdate('posts', [ 
    { post_id : 1, content: 'Blog Post 1' },
    { post_id : 2, content: 'Blog Post 2' } 
]);

score 9 · Answer 2 · answered Jan 25 '21 at 15:44

Not sure when it was added, by knex now has an onConflict method: http://knexjs.org/#Builder-merge. I wrote an "upsert" function this way and it seems to work fine with mysql:

module.exports = (table, idKey, data) => require('../knex')(table)
  .insert(data)
  .onConflict(idKey)
  .merge()
  .catch( err => {
    console.error(`There was an error upserting the "${table}" table by ${idKey}:`, err)
    throw err
  })

score 5 · Answer 3 · answered Dec 12 '18 at 16:04

Just fixed @Sebyddd regex, remove format and change logic from "ON CONFLICT" to "ON DUPLICATE KEY UPDATE": https://gist.github.com/hinex/017c7c98c4a163d766fe2191a65fd944

const insertOrUpdate = (tableName, rows) => {

    return DB.transaction((trx) => {

        const queries = rows.map((tuple) => {

            const insert = trx(tableName).insert(tuple).toString()
            const update = trx(tableName).update(tuple).toString().replace(/^update(.*?)set\s/gi, '')

            return trx.raw(`${insert} ON DUPLICATE KEY UPDATE ${update}`).transacting(trx)
        })

        return Promise.all(queries).then(trx.commit).catch(trx.rollback)
    })
}

score 3 · Answer 4 · answered Feb 25 '16 at 06:46

3

try ON DUPLICATE KEY UPDATE with raw sql because knex does not support this right now.

see: https://github.com/tgriesser/knex/issues/701

answered Feb 25 '16 at 06:46

Sinux

1,728
3
15
28

score 3 · Answer 5 · answered Apr 10 '19 at 14:49

If you are using PostgreSQL, as there is no ON DUPLICATE KEY UPDATE. So in PostgreSQL you should use ON CONFLICT ("id") DO UPDATE SET :

const insertOrUpdate = (knex, tableName, data) => {
  const firstData = data[0] ? data[0] : data;

  return knex().raw(
    knex(tableName).insert(data).toQuery() + ' ON CONFLICT ("id") DO UPDATE SET ' +
      Object.keys(firstData).map((field) => `${field}=EXCLUDED.${field}`).join(', ')
  );
};

If you are using Objection.js (knex's wrapper) then (don't forget to import knex in this case):

const insertOrUpdate = (model, tableName, data) => {
  const firstData = data[0] ? data[0] : data;

  return model.knex().raw(
    knex(tableName).insert(data).toQuery() + ' ON CONFLICT ("id") DO UPDATE SET ' +
      Object.keys(firstData).map((field) => `${field}=EXCLUDED.${field}`).join(', ')
  );
};

Knexjs If not exist insert else update

5 Answers5