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

user_2784493 0
Viewed 9452

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.

knex.js node.js mysql
3 Answers

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