await one query at a time

Question

So I'm trying to build a sequential chain insert to database using nodejs and mssql. Here is the quick overview:

1. Insert at Condition Table
2. Insert at Task Table

and so on..

Here is my code:

    var PODKEY = process.env.PODKEY;
var ADDBY = process.env.ADDBY;
var dateTimeNow = new Date(Date.now()).toISOString().replace(/Z$/, "");

(async function () {
    try {
        DecryptConnectionString(connectionString, 'ProductIpsMasterConnectionString')
            .then(async function () {
                let connectionConfigurationObject = await BuildConnectionConfigurationObject(connectionString);
                await sql.connect(connectionConfigurationObject).then(async function () {
                    GetTenantsInfoByPodKey(PODKEY).then(async function (resultTenantsInfo) {
                        resultTenantsInfo.forEach(async function (tenant) {
                            var transaction = new sql.Transaction();
                            await transaction.begin(async function () {

                                let tenantConnectionConfigurationObject = await BuildConnectionConfigurationObject(tenant.CONNECTIONSTRING);

                                await sql.connect(tenantConnectionConfigurationObject).then(async function () {
                                    console.dir("Connected to Tenant ID: " + tenant.TENANTID);
                                    await InsertCondition(transaction).then(async function (resultCondition) {
                                        console.log("Successfully Inserted, Condition Key: " + resultCondition[0].conditionKey);
                                        await InsertTask(transaction, resultCondition[0].conditionKey).then(async function (resultTask) {
                                            console.log("Successfully Inserted, TaskKey: " + resultTask[0].taskKey);
                                            transaction.commit();
                                        });

                                    }).catch(function (err) {
                                        console.error("Error Inserting Condition: " + err);
                                    });
                                }).catch(function (err) {
                                    console.error("Error connecting to Tenant DB: " + err);
                                });

                            });
                        });
                    }).catch(function (err) {
                        console.error("Error getting Tenants Info: " + err);
                    });
                }).catch(function (err) {
                    console.log('error connecting to Master DB:' + err);
                });
                console.dir("Connected Successfully to Master DB!");
            }).catch(function (err) {
                console.log('error decrypting connection string :' + err);
            });

    } catch (err) {
        console.error("Error Occured: " + err);
    }
})()

function GetTenantsInfoByPodKey(transaction, podKey) {
    return new Promise(function (resolve, reject) {
    new sql.Request(transaction).query(query).then(function (recordset) {
        resolve(recordset)});
    });
}

function InsertCondition(connectionObject, transaction) {
    return new Promise(function (resolve, reject) {
       var query = "insert into table(col1,col2..) values (val1,val2,..);select SCOPE_IDENTITY() AS conditionKey;"
       new sql.Request()
               .query(query, (err, recordset) => {
                resolve(recordset);
                });
     });
}

 function InsertTask(connectionObject, transaction) {
    return new Promise(function (resolve, reject) {
       var query = "insert into table(col1,col2..) values 
   (val1,val2,..);select SCOPE_IDENTITY() AS taskKey;"
       new sql.Request()
               .query(query, (err, recordset) => {
                resolve(recordset);
                });
     });
}

Assuming queries are working fine and inserting but I have a problem with forEach even if I made it for() loop that it doesn't finish the sql.connect and inside method "InsertCondition" it touches it but it looks like that it skips the whole block of sql.connect and proceed to the next iteration.

Question: 1.) How can i make the GetTenantsInfoByPodKey(), sql.connect and InsertCondition() with await properly? waiting for each of those methods to finish first before going to the next? [SOLVED]

2.) It is not reflecting in the DB when I query it manually, however it returns a key inserted from SCOPE_IDENTITY() How can I make sure that it inserts? like it commit first the InsertCondition() then commit the next method inside InsertTask()

Here is the complete app i'm trying: https://jsfiddle.net/25serz3c/1/

Thank you

Answer 1

async function f () {

    for(const tenant of resultTenantsInfo) {
      const connectionObjTenant = BuildConnectionConfigurationObject(tenant.CONNECTIONSTRING);
      await sql.connect(connectionObjTenant);
      const transaction = new sql.Transaction();
      awai transaction.begin()
      const resultCondition = await InsertCondition(transaction)
      console.log("Inserting Condition was successfull. Condition Key: " + resultCondition[0].conditionKey);
      await transaction.commit();
      console.dir("Transaction commited.");
    }
}

f()