Question

86

Batch update in knex

rated 0 times [ 90] [ 4] / answers: 1 / hits: 43391 / 8 Years ago, fri, november 11, 2016, 12:00:00

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.

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

masonm

Add To Favorites

Follow

Total Points: 167

Total Questions: 87

Total Answers: 103

Location: Rwanda

Member since Wed, Jun 8, 2022

2 Years ago

masonm questions

1 Exceptional regex for email id validation in JavaScript

Mon, May 3, 21, 00:00, 3 Years ago

1 Bootstrap Carousel - Autoplay video on active slide

Sat, Nov 7, 20, 00:00, 4 Years ago

1 TS2322: Type 'number' is not assignable to type 'bigint

Mon, Oct 12, 20, 00:00, 4 Years ago

1 Vue Router passing props to dynamic routes, unclear documentation

Sun, Aug 9, 20, 00:00, 4 Years ago

1 Async await function not executing within an event listener

Wed, Dec 18, 19, 00:00, 5 Years ago

View All

answered 8 Years ago oliverkarle · Accepted Answer

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

});