PG-Promise - transaction logic for two databases

Question

How can I distribute transactions between two different DBs with PG-Promise? Commits or rollbacks should be applied to both DBs and if one fails, the other should revert changes

I have been using something like this but I am not sure if it works:

try {
    await firstDb.tx(async (firstDbTask) => {
      await secondDb.tx(async (secondDbTask) => {
        // second db stuff
      })
      // first db stuff
    });

    return true;
} catch (err) {    
    return err;
}

Answer 1

Synchronizing transaction logic for multiple databases isn't trivial, but doable. And the solution is basically all about correct use of promises, not much else...

We make our multi-database transactions expose their final inner state via an external promise, so we can cross-align transaction outcome to that state:

let firstTx, secondTx;

firstTx = new Promise((resolve, reject) => {
    firstDb.tx(async t => {
        // do your queries here first...

        // success, signal after all the queries:
        resolve(/*null or whatever data*/);

        // Align COMMIT-ROLLBACK logic with the other transaction:
        await secondTx;
    }).catch(reject);
});

secondTx = new Promise((resolve, reject) => {
    secondDb.tx(async t => {
        // do your queries here first...

        // success, signal at the end:
        resolve(/*null or whatever data*/);

        // Align COMMIT-ROLLBACK logic with the other transaction:
        await firstTx;
    }).catch(reject);
});

await Promise.all([firstTx, secondTx]); // finish transactions logic

This approach ensures that if either of the transactions fails, both will roll back. And COMMIT can only happen either for both transactions or none.

---

Note however, that the solution above is a little loose in relation to the state of the transactions, i.e. after we call Promise.all there, both transactions have finished executing their logic, but the resulting COMMIT / ROLLBACK haven't finished executing yet.

In case you need full closure on both transactions, you have to follow up with a separate await for the actual transactions to end, as shown below:

let firstTx, secondTx, tx1, tx2;

firstTx = new Promise((resolve, reject) => {
    tx1 = firstDb.tx(async t => {
        // do your queries here first...

        // success, signal after all the queries:
        resolve(/*null or whatever data*/);

        // Align COMMIT-ROLLBACK logic with the other transaction:
        await secondTx;
    }).catch(reject);
});

secondTx = new Promise((resolve, reject) => {
    tx2 = secondDb.tx(async t => {
        // do your queries here first...

        // success, signal at the end:
        resolve(/*null or whatever data*/);

        // Align COMMIT-ROLLBACK logic with the other transaction:
        await firstTx;
    }).catch(reject);
});

await Promise.all([firstTx, secondTx]); // finish transactions logic

await Promise.all([tx1, tx2]); // finish transactions execution

// All COMMIT-s or ROLLBACK-s have been executed.

Note that the above provision only matters when both transactions succeed with a COMMIT. When either one fails, the first await will throw, so the second one won't execute, which is important because in case of failure, tx1 or tx2 may still be undefined. That's why we have two separate await-s there in the end.