Nicholas Kyriakides Nicholas Kyriakides - 27 days ago 8
Javascript Question

Batch update in knex

I'd like to perform a batch update using Knex.js

For example:

'UPDATE foo SET [theValues] WHERE idFoo = 1'
'UPDATE foo SET [theValues] WHERE idFoo = 2'


with values:

{ name: "FooName1", checked: true } // to `idFoo = 1`
{ name: "FooName2", checked: false } // to `idFoo = 2`


I was using node-mysql previously, which allowed multiple-statements. While using that I simply built a mulitple-statement query string and just send that through the wire in a single run.

I'm not sure how to achieve the same with Knex. I can see
batchInsert
as an API method I can use, but nothing as far as
batchUpdate
is concerned.

Note:




  • I can do an async iteration and update each row separately. That's bad cause it means there's gonna be lots of roundtrips from the server to the DB

  • I can use the
    raw()
    thing of Knex and probably do something similar to what I do with node-mysql. However that defeats the whole knex purpose of being a DB abstraction layer (It introduces strong DB coupling)



So I'd like to do this using something "knex-y".

Any ideas welcome.

Answer

You have a good idea of the pros and cons of each approach. I would recommend a raw query that bulk updates over several async updates. Yes you can run them in parallel, but your bottleneck becomes the time it takes for the db to run each update. Details can be found here.

Below is an example of an batch upsert using knex.raw. Assume that records is an array of objects (one obj for each row we want to update) whose values are the properties names line up with the columns in the database you want to update:

var knex = require('knex'),
    _ = require('underscore');

function bulkUpdate (records) {
      var updateQuery = [
          'INSERT INTO mytable (primaryKeyCol, col2, colN) VALUES',
          _.map(records, () => '(?)').join(','),
          'ON DUPLICATE KEY UPDATE',
          'col2 = VALUES(col2),',
          'colN = VALUES(colN)'
      ].join(' '),

      vals = [];

      _(records).map(record => {
          vals.push(_(record).values());
      });

      return knex.raw(updateQuery, vals);
 }

This answer does a great job explaining the runtime relationship between the two approaches.