Unhanded rejection error :Transaction query alread

Question

I was trying to check for a value in a table first, and if it exists, delete a row in another table and insert this new data into that table.

I used a transaction with a select, del(), and a insert command

db.transaction(trx => {
  return trx('users')
    .where({ username: user.username })
    .select('username')
    .returning('username')

    .then(retData => {
      retUserName = retData[0];

      db('profile')
        .where({ username: user.username })
        .del()
        .then(retData => {
          return trx
            .insert(profileData)
            .into('profile')
            .returning('*');
        });
    })
    .then(retData => {
      res.json({ ProfileData: profileData });
    })
    .then(trx.commit)
    .catch(trx.rollback);
}).catch(err => res.status(400).json('unable to create profile'));

I get this error Unhanded rejection error:Transaction query already completed

but the data hasn't been added to the table.

Answer 1

You are returning promise from transaction handler callback, which causes transaction to automatically committed / rolled back depending if returned promise resolves / rejects.

https://knexjs.org/#Transactions

Throwing an error directly from the transaction handler function automatically rolls back the transaction, same as returning a rejected promise.

Notice that if a promise is not returned within the handler, it is up to you to ensure trx.commit, or trx.rollback are called, otherwise the transaction connection will hang.

In your code you are mixing those two different ways to use transactions, which causes it to be committed / rolledback twice.