I am currently working on a nodeJS application that will soon need to write simultaneously on two PostgreSQL databases. This is meant for redudancy purposes: the writing operations must be made in the same time on both databases, or rollbacked if there is anything wrong with one of the databases.
In order to achieve that, I need to rely on a transaction manager that would (or at least could) use two-phase commit in the persisting process.
Currently, the application uses an out-of-the-box solution to perform actions on the current database (i.e. the pg-promise package, which for now stands as the application's transaction manager).
To put it all in a nutshell, I am going to need every persisting action in the Node application to be performed on two databases simultaneously.
For now, I merely use the pg-promise to persist data on one database like on this example:
db.one(mySQLRequest)
but I only found to create a db object for one specific database at a time.
I have found no example for such a vast problematic on a NodeJS RESTful application, but I suspect it might be a rather common issue. And I would be glad if it was not necessary that I recode the whole transaction manager on my own...
If you have any lead about how I could allow my NodeJS application to perform two-phase commits, it would be greatly appreciated. :)
nodeJS application that will soon need to write simultaneously on several PostgreSQL databases
Within pg-promise each Database object contains a separate connection pool, with unique connection.
So when communicating with several databases you simply create one Database object for each, and in this scenario it is also a good idea to make use of the Database Context parameter, which can be anything.
Example
const pgp = require('pg-promise')(/* initialization options */);
const db1 = pgp(connection1, dbContext1);
const db2 = pgp(connection2, dbContext2);
const db3 = pgp(connection3, dbContext3);
I have to implement a two-phase commit in the persisting process
This would have to be a custom implementation that depends on the business logic of your application. The library cannot help you there, as there is no such thing as inter-database transaction, you will have to implement it on your own.
I even thought of rewriting some parts of the pg-promise module myself
I don't see what it can give you. The parts that can be automated for transactions are already all there, and the parts for the inter-database integrity are all on your side.