在knex中批量更新 [英] Batch update in knex

问题描述

我想使用 Knex.js 执行批量更新

例如：

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

值：

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

我用 node-mysql ，它允许多个语句。使用它时，我只是构建了一个多语句查询字符串，只需在一次运行中通过线路发送。

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.

我不知道如何实现相同的Knex。我可以看到 batchInsert 作为我可以使用的API方法，但是只涉及 batchUpdate 。

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.

• 我可以进行异步迭代并分别更新每一行。这很糟糕，因为这意味着从服务器到数据库会有很多往返

• 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

我可以使用 raw（） Knex的东西，可能做类似于我对node-mysql的操作。然而，这破坏了作为DB抽象层的整个knex目的（它引入了强大的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)

所以我'我喜欢用knex-y来做这件事。

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

欢迎任何想法。

推荐答案

我需要在事务中执行批量更新（我不想在出现问题时进行部分更新）。
我已经通过下一个方式解决了这个问题：

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
});

这篇关于在knex中批量更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！