How to maintain multiple connections with postgresql in Node.js?

Question

I am used Node.js in a task where I need to migrate data between two Postgresql databases. The idea is more or less the following:

1. Connect to Bank A.
2. Connect to Bank B.
3. Return all records from A.
4. Insert all A Records not yet present in B.
5. Update a field in all records that have been copied.
6. Close all connections.

My question is how to keep the two connections open simultaneously. I believe I need to do so because the data, as I said, will transit between two banks, and I do not think it is a good idea to keep opening and closing the connection in Bank B for each A record found. I’m using the package pg as an interface with DB. Someone has already had to do something like this and/or would know how to do it?

Answer 1

Hi. I believe this can be solved more or less like this:

var pg = require('pg');
//bancoA
var conString = "postgres://username:password@localhost/database";
var client1 = new pg.Client(conString);
//bancoB
var conString = "postgres://username:password@localhost/database";
var client2 = new pg.Client(conString);

Now you have connection to both banks.

To return all records from A:

    var query = client1.query('select * from table');
    //Acho que isso teria que ser feito tabela por tabela
    var rows = [];
    query.on('row', function(row) {
      rows.push(row);
    });
   //Esse codigo precisa ser testado. 
   //Nao tenho absoluta certeza de que isso funciona dessa forma. 
   //Teoricamente a variavel rows estaria com todos os registros
   //que o postgres tinha na tabela.

From this point on, we have to define which logic will be used to define which values will be entered in the B-database, since only the records not yet present.

I believe the best way to do this is to make the same select no client2. From there we would have a value for the variable rows with the values already inserted in the Bancob table. And then it would be necessary only a simple logic to delete the values of rowsDeA which already exist in rowsDeB.

It is worth remembering that, depending on the size of the tables and the periodicity that this will happen, this code can be extremely costly. If the idea is to make it a continuous run (to maintain a backup server, or something like that), maybe it is better to create a logic that inserts the values in Bancob at the same time as in Bancoa.

One more Dlisclaimer: I’m not sure this will work, but it’s worth the test :)