How to add multiple rows using "Insert ... ON DUPLICATE KEY UPDATE" using knex

Question

so I've been playing around with knex lately, however I found myself on a situation where I don't know what to do anymore.

so I have this query:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
[
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
]);

And for some reasons It throws me an error Expected 2 bindings, saw 3.

I tried making it:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
);

No error this time, but it only inserts the first array.

I also tried making the values an object:

[
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
]

But still no luck.

Checkout this post https://stackoverflow.com/questions/40543668/batch-update-in-knex They Show you how to do it. — Amrith M, Jul 05 '17 at 15:12
yep, I already checked that, in fact I commented on the answer regarding the data structure of the "records" since it is not quite clear what it looks like. — I am L, Jul 05 '17 at 15:14

score 11 · Answer 1 · answered Jul 12 '17 at 16:23

I wrote this code to insert/update either a single row as an object or multiple rows as an array of objects:

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(", "));
}

skiilaa · Accepted Answer · 2017-07-05T15:45:24.300

If you only need to insert a fix number rows at a time, you could try this:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?), (?, ?, ?) ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world', 'val2', 'ohayo', 'minasan'],
);

If you don't know how many you need to insert at a time, it is possible to write a script that adds (?, ?, ?), as many times as needed.

var questionMarks = "";
var values = [];
var rows = [
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
];
rows.forEach(function(value, index){
    questionMarks += "("
    Object.keys(value).forEach(function(x){
         questionMarks += "?, ";
         values.push(value[x]);
    });
    questionMarks = questionMarks.substr(0, questionMarks.length - 2);
    questionMarks += "), ";
});
questionMarks = questionMarks.substr(0, questionMarks.length - 2); //cut off last unneeded comma and space
knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES " + questionMarks + " ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", values);

I'm including a script to calculate how much `(?, ?, ?), ` is needed. — skiilaa, Jul 05 '17 at 15:26
Awesome! It worked! Thanks! I will just use loops to add (?, ?, ?) and array push for the values since I don't know the exact number of rows to be inserted. — I am L, Jul 05 '17 at 15:28

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

The Nathan's solution won't work 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 (and 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(', ')
  );
};

How to add multiple rows using "Insert ... ON DUPLICATE KEY UPDATE" using knex

3 Answers3

Linked