Using knexjs for insert ignore

Question

I'm having this issue, I have a set of data I want to insert using knexjs. But I want the insertion to ignore duplicate, i.e only insert if data does not exist. I can do this

knex('rates').insert(allRates);

but I was wondering who I can use the ignore modifier if it exists. I also don't want to do knex.raw Thank you.

why the aversion to `raw`? it is part of knex for a reason. Can we use raw to express the data as a set to select from or join against? — Catalyst, Jun 22 '16 at 09:21
I'm facing the same issue, thought of using [this reasoning here](https://stackoverflow.com/a/1011170/2405689) is it a good practise? — ArchNoob, Jan 21 '18 at 13:28

score 14 · Answer 1 · edited Dec 26 '18 at 22:08

14

Knex.js does not provide this functionality. A potential solution is:

knex.raw(knex('rates').insert(allRates).toString().replace('insert', 'INSERT IGNORE'));

It's minimum usage of the raw function, and doesn't force you to convert data manually.

edited Dec 26 '18 at 22:08

Sam

5,762
7
38
56

answered Aug 15 '16 at 05:51

Gabi Lee

845
5
12

vbarbarosh · Answer 2 · 2019-01-23T04:24:15.733

I do it the following way:

knex_insert_ignore(knex, 'rates', allRates);

// insert into `metric_names` (`a`, `b`, `c`, `d`) values (1, 2, DEFAULT, DEFAULT), (DEFAULT, DEFAULT, 3, 4)
// INSERT IGNORE `metric_names` (`a`, `b`, `c`, `d`) values (1, 2, DEFAULT, DEFAULT), (DEFAULT, DEFAULT, 3, 4)
function knex_insert_ignore(knex, table, rows)
{
    if (rows.length == 0) {
        return Promise.resolve();
    }

    const keys = rows_keys(rows);
    const inserts = [];
    const bindings = [table, ...keys];
    for (let i = 0, end = rows.length; i < end; ++i) {
        const row = rows[i];
        const insert = [];
        for (let j = 0, jj = keys.length; j < jj; ++j) {
            const key = keys[j];
            if (row[key] === undefined) {
                insert.push('DEFAULT');
            }
            else {
                insert.push('?');
                bindings.push(row[key]);
            }
        }
        inserts.push(`(${insert})`);
    }
    return knex.raw(`INSERT IGNORE INTO ?? (${keys.slice().fill('??')}) VALUES ${inserts}`, bindings);
}

// Return all keys mentioned in rows
function rows_keys(rows)
{
    const unique = {};
    for (let i = 0, end = rows.length; i < end; ++i) {
        const keys = Object.keys(rows[i]);
        for (let j = 0, jj = keys.length; j < jj; ++j) {
            unique[keys[j]] = true;
        }
    }
    return Object.keys(unique);
}

Using knexjs for insert ignore

2 Answers2