javascript - Batch update in knex

Question

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 1

I needed to perform a batch update inside a transaction (I didn't want to have partial updates in case something went wrong). I've resolved it the next way:

// I wrap knex as 'connection'
return connection.transaction(trx => {
    const queries = [];
    users.forEach(user => {
        const query = connection('users')
            .where('id', user.id)
            .update({
                lastActivity: user.lastActivity,
                points: user.points,
            })
            .transacting(trx); // This makes every update be in the same transaction
        queries.push(query);
    });

    Promise.all(queries) // Once every query is written
        .then(trx.commit) // We try to execute all of them
        .catch(trx.rollback); // And rollback in case any of them goes wrong
});