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

Konstantin Tarkus · Answer 4 · 2022-12-01T17:30:26.190

const db = knex({/* config */});

const records = [
  { id: "1", col1: "hello", col2: "world"],
  { id: "2", col1: "ohayo", col2: "minasan"]
];

const { sql, bindings } = db.table("table").insert(records).toSQL();

// MySQL
// --------------
await db.raw(
  ` ${sql} ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2)`,
  bindings
);

// PostgreSQL
// --------------
await db.raw(
  ` ${sql} ON CONFLICT ("id") DO UPDATE
    SET col1 = EXCLUDED.col1,
        col2 = EXCLUDED.col2`,
  bindings
);

score 0 · Answer 5 · answered Dec 02 '22 at 10:40

2022 Update

I now use onConflict for this one. This will work for PostgreSQL, MySQL, and SQLite databases

Let's say I have a table with Primary key or unique Key called key and value

How to use it:

await knex('workspaces_settings')
  .insert({
    key,
    value,
    created_at: new Date(),
    updated_at: new Date(),
  })
  .onConflict(['key', 'value'])
  .merge();

This will make it so if the key-value combination exist, it would simply update the values based on the field inside the insert function.

For multiple values

You simply just need to pass an array in the insert instead:


await knex('workspaces_settings')
  .insert([
    {
      key: '1',
      value: '1',
      created_at: new Date(),
      updated_at: new Date(),
    },
    {
      key: '2',
      value: '2',
      created_at: new Date(),
      updated_at: new Date(),
    },
  ])
  .onConflict(['key', 'value'])
  .merge();

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

5 Answers5

2022 Update

How to use it:

For multiple values

Linked