Need help with transactions

[mastermunj]

Hi,

I am trying to work on data seed, but want to use transactions.

The process is:

• Begin Transaction
• Truncate Table
• Insert Data
• Commit
  In case of any error, rollback.

Somehow, when an error occurs during insert, rollback does not revert the table to the state it was before the transaction began.

Here's the code.

await Database.transaction(async (trx) => {
  await trx.truncate(SmsTemplate.table);

  try {
    for (const [, values] of data.entries()) {
      await SmsTemplate.create(values, { client: trx });
    }
  } catch (e) {
    console.log(e);
    await trx.rollback();
    process.exit();
  }
  await trx.commit();
});

In nutshell, the truncate table effect remains even after rollback.

Am I doing something wrong?

[thetutlage]

If by original state you mean the truncate action doesn't undo, then it depends upon the database you use. In MYSQL truncate is a DDL statement and hence transaction has no impact on it. More here https://dba.stackexchange.com/a/81970

[mastermunj]

Cool. I changed the truncate query with the following and it works now.

await trx.from(SmsTemplate.table).delete();