I'm relatively new to knex and transactions. Basically I have to perform a bunch of batch inserts and updates across different POSTGRES tables and wanted to understand the best possible way to do it. Is the following a correct approach for this? I'm especially struggling with the batch-update part.
let fieldsToInsert = [{name:'Mike', age: 57, weight:66, gender:'M'}, ...]
let _user = {user_id:99, city:6}
return db.transaction(trx => {
return trx.insert(fieldsToInsert)
.into("users")
.then(() => {
const queries = fieldsToInsert.map((user) => {
db("biodata").update({"name": user.name}).where({"id": user.age})
})
return Promise.all(queries)
}).then(() => {
const queries = fieldsToInsert.map((user) => {
db("weight_and_gender".update({"weight": user.weight, "gender": user.gender}))
})
return Promise.all(queries)
})
}).catch((err) => {
console.log(err)
})
await db.transaction(async trx => {
const insertedRows = await trx('users').insert(fieldsToInsert).returning('*');
for (let row in insertedRow) {
await trx('biodata').update({name : row.name}).where('id', row.id);
await trx('weight_and_gender').update({weight : row.weight, gender: row.gender}).where('id', row.id);
}
});
Trying to run updates in parallel with Promise.all does not help anything. DB driver will buffer those anyways and run them sequentially in either way if they are executed in the same transaction.