Transaction issue with knexjs, typescript and mariadb

user_202530 0
Viewed 1834

I want to have a transaction, in typescript, to refresh some data in a table. To do so the following steps need to apply:

  1. Truncate all the records from a table
  2. Set the AUTO_INCREMENT to 1
  3. Insert the new records in the table

If something goes wrong I would like to rollback the transaction and not alter the existing records in the db table.

I have tried different things but I think I am missing something and I wish someone can spot what I am doing wrong.

1st Attempt

await knex.transaction(async (trx) => {
  await knex(tableName).truncate().transacting(trx);
  await knex.raw(`ALTER TABLE ${tableName} AUTO_INCREMENT=1;`).transacting(trx);
  await knex(tableName).insert(data).transacting(trx);
  await trx.commit();
});

2nd Attempt

await knex.transaction(async (trx) => {
  try {
    await knex(table).truncate().transacting(trx);
    await knex.raw(`ALTER TABLE ${table} AUTO_INCREMENT=1;`).transacting(trx);
    await knex(table).insert(data).transacting(trx);
    await trx.commit();
  } catch (e) {
    await trx.rollback();
  }
});

3rd Attempt

const promisify = (fn: any) => new Promise((resolve, reject) => fn(resolve));
const trx: knex.Transaction  = <knex.Transaction> await promisify(this.knex.transaction);
try {
  await this.knex(this.table).truncate().transacting(trx);
  await this.knex.raw(`ALTER TABLE ${this.table} AUTO_INCREMENT=1;`).transacting(trx);
  await this.knex(this.table).insert(data).transacting(trx);
  await trx.commit();
  return Promise.resolve(data);
} catch (e) {
  await trx.rollback();
  return Promise.reject(e);
}

Any idea would be really useful.

knex.js mariadb typescript
2 Answers
user_3600600

Don't do it that way. Instead:

CREATE TABLE new LIKE real;
populate `new`
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

If the CREATE or populate fail, then abandon the task; no harm done. (Well, need to DROP TABLE real.)

The RENAME TABLE is very fast, and atomic, and unlikely to fail.

This approach has the advantage that, to other queries, the real table is available and fully populated the entire time.

user_17668310
Related