I'm creating an utility class for NodeJs to help me manage database transactions.

My idea is to create a method like this:

transactionBlock(params) {
    let _db;
    return mySqlConnector.getConnection(params.db)
        .then(db => {
            _db = db;
            console.log(`beginTransaction`);
            return db.beginTransaction()
        })
        .then(() => {
            return Promise.resolve(_db);
        })
        .catch((e) => {
            console.log(`roolback`);
            _db.rollback();
        })
        .finally(() => {
            console.log(`commit`);
            _db.commit();
        })
}

and use it like this:

const params = {db:"my_db"};
transactionBlock(params)
    .then(db =>{
        console.log(`do query 1`);
        db.insert(....)
        console.log(`do query 2`);
        db.insert(....)
        console.log(`do query 3`);
        db.insert(....)
    })

I was hoping it would work, but obviously the result is the following:

beginTransaction
commit
do query 1
do query 2
do query 3

do you think it is possible to create a function transactionBlock that returns a promise where user can do the queries and finally, if all the queries are all ok, the function transactionBlock do the commits?

I use this: npmjs.com/package/promise-mysql

Thanks bye

CodePudding user response：

The big problem with your current approach is that finally() always runs, whether the promise chain resolves or rejects so you definitely don't want to be committing the transaction there.

I see only one option here... require a callback function representing the body of the transaction.

In general, I'd also advise using async / await syntax for readability.

const transactionBlock = async (connectionName, txBody) => {
  const db = await mySqlConnector.getConnection(connectionName);
  await db.beingTransaction();
  try {
    await txBody(db);
    console.log("commit");
    return db.commit(); // all good, commit
  } catch (err) {
    console.error("rollback", err);
    await db.rollback();
    // keep the rejection chain going by re-throwing the error or a new one
    throw err; // or perhaps `new Error("DB error: "   err.message)`
  }
};

Called like this

try {
  await transactionBlock("my_db", async (db) => {
    console.log(`do query 1`);
    await db.insert(/* ... */);
    console.log(`do query 2`);
    await db.insert(/* ... */);
    console.log(`do query 3`);
    await db.insert(/* ... */);
  });
} catch (err) {
  console.error("oh no, something went wrong", err);
}

If you were using Typescript, the following interfaces and types would ensure smooth operation

type TxBody = (db: Connection) => Promise<void>;
type TransactionBlock = (
  connectionName: string,
  txBody: TxBody
) => Promise<void>;

const transactionBlock: TransactionBlock = async (connectionName, txBody) => {
  // ...
};