Knex Transaction - Using Await - Not executing 2nd SQL Statement

Question

I am using knex 0.19.4 in node js 10.x. I have 2 SQL statements - Insert and Update which has to happen as a transaction.

// Using Var, so that below code has access to this variable
var sqlStageInsert = kx('stage').insert({
  officeid: 'OFF000',
  taskid: 'T002',
});


var sqlTaskPcUpdate = kx('task')
  .update({ pc: 100})
  .where('task.taskno', taskno)
  .limit(1);

1st Try - 2nd SQL sqlTaskPcUpdate Not getting Executed

const sqlUpdateInsert = kx.transaction(function (trx) {
  sqlStageInsert.transacting(trx)
    .then(function () {
      console.log(sqlTaskPcUpdate.toString()); // This is outputing correct SQL
      return sqlTaskPcUpdate.transacting(trx);
    })
    .then(trx.commit)
    .catch(trx.rollback);
});
await sqlUpdateInsert;

2nd Try - Getting error Transaction query already complete. This is based on Commit/rollback a knex transaction using async/await

await kx.transaction(async (trx) => {
  try {
    await sqlStageInsert.transacting(trx);
    await sqlTaskPcUpdate.transacting(trx);
    trx.commit();
  } catch (error) {
    trx.rollback();
    throw error;
  }
});

It still looks callback-based? Are you sure `.transaction` returns a Promise? — CertainPerformance, Sep 14 '19 at 09:10

score 1 · Answer 1 · answered Sep 21 '19 at 18:18

I would suggest you to try inserting the data in stage table first and then retrieve a common value which belongs to both the table for applying in where class of updating task table(Assuming both the table contain any one common column having same data).

Please note that as per knexjs.org website, knex.transaction() uses returning statement with respect to PostgreSQL to insert/ update more than one table to maintain consistency, however MySQL won't support transaction due to which I'm using return values in below code.

FYR, http://knexjs.org/#Transactions

Please refer below code snippet for your reference :

db.transaction(trx => {
  trx.insert({
    officeid: 'OFF000',
    taskid: 'T002'
  })
  .into('stage')
  .then(() => {
    return trx('stage').where({taskid}).then(resp => resp[0].taskno)
  })
  .then(stageTaskno => {
    return trx('task')
          .update({pc: 100})
          .where({stageTaskno})
          .limit(1)
          .then(resp => {
            resp.json(resp)
          })
  })
  .then(trx.commit)
  .catch(trx.rollback)
});

hope this is helpful, cheers!

Knex Transaction - Using Await - Not executing 2nd SQL Statement

1 Answers1