CODEWITHSUNDEEP
node.jspostgresqlsql-insert
Robin
Robin

Reputation: 201

How to insert records to two tables connected to each other in nodejs dynamically

This maybe a very simple question, while it took me long time and still failed to figure it out. I need two tables in postgresql and to be updated dynamically in my nodejs app. The table1 has two column(obj_id, obj_name), table2 has more columns while include one column as "obj_id" as the "fk" refer to table1's "obj_id"; I tried to insert one record in table1 and then do some processing and insert the corresponding record to table2. I need the "obj_id" value returned from the 1st insert, and use it together with other values to do the 2nd insert. I am trying to do this with following code, while it always throw error saying (insert or update on table "table2" violates foreign key constraint "rel_table2_table1").

function uploadTable1(name,callback){
  const client = new pg.Client(conString);
  client.connect();
  client.on("drain", function () {
    client.end();
  });

 let insertTable1 = client.query(`INSERT INTO table1 (obj_name) VALUES ($1) RETURNING obj_id`, [name]);
 insertTable1.on("row", function (row,result) {
    callback(row.obj_id);
});

}

function uploadTable2(kk,ff) {
return function (obj_id) {
    const client = new pg.Client(conString);
    client.connect();
    client.on("drain", function () {
        client.end();
    });

    let uploadQuery = client.query("INSERT INTO table2 (kk,ff,obj_id) VALUES ($1,$2, $3)",[kk,ff, obj_id]);
  }
}

I can run these two functions mannually one by one to insert records without problem, while when run them together in the main program as shown below code, I got errors mentioned above.

uploadTable1("obj_name1",uploadTable2("kk1","ff1"));

I checked the table1 has been inserted successfully and the new record "obj_id" value has been passed to the callback function who is doing the insert table2. While stranged thing is they throw the error, seems complaining that "fk" does not exist yet when insert records for table2, at least when running the callback function.

What's your suggestions to solve this conflicts?

Upvotes: 1

Views: 1528

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

I assume you are making two separate connections in two functions. Maybe uploadTable1 transaction is not committed yet, when you try to uploadTable2("kk1","ff1")?..

With this assumption made, try changing uploadTable1 to:

function uploadTable1(name,callback){
  const client = new pg.Client(conString);
  client.connect();
  client.on("drain", function () {
    client.end();
    let insertTable1 = client.query(`INSERT INTO table1 (obj_name) VALUES ($1) RETURNING obj_id`, [name]);
    insertTable1.on("row", function (row,result) {
       callback(row.obj_id);
   });
  });

with idea, that client.end(); will terminate connection "forcing" commit...

Or, consider rewriting the code to run two statements in one session, one transaction instead...

Upvotes: 1

Related Questions