CODEWITHSUNDEEP
javascriptmysqlnode.jspromise
Arlo Guthrie
Arlo Guthrie

Reputation: 1234

node.js looping database inserts

I have an application, with an address object, and a list of people associated with the application.

{
  field1: value,
  field2: value,
  address: {
    street: value,
    apt: value,
    city: value
  },
  owners: [
    {name: value, etc.},
    {name: value, etc.}
  ]
}

I am passing the owners off to a function that loops over the owners and passes the information for each query off to an async function:

async function insertAllOwners(appId, owners) {
  for (var i = 0, len = owners.length; i < len; i++) {
    console.log("insert owner", i);
    await insertOwner(appId, owners[i]);
  }
}

The function being called here turns the query into a promise:

function insertOwner(appId, owner)  {
  let sqlOwner = 'insert into ...';
  return new Promise( ( resolve, reject ) => {
    mySqlClient.query(sqlOwner, [appId, owner.memberType ...], (err, rows) => {
      if ( err )
        return reject( err );
      console.log("rows:", rows);
      resolve( rows );
    } );
  } );
}

The address insert is nested inside the application insert, then the code that calls the owner insert loop is nested inside the address insert. The looping owner insert function is called here:

      if(app.owners) {
        /* insert owners, then commit.  else, just commit.*/
        insertAllOwners(appId, app.owners).then( result => {
          mySqlClient.commit(function(err, result) {
            if (err) { 
              console.error("ERROR DURING COMMIT:", err);
              mySqlClient.rollback(function() {
                throw err;
              });
            }
            else {
              console.log("commit:",result);
            }
          });                  
        });
      }

The output looks perfect, but it doesn't appear to actually commit anything (the new rows are not showing up). Any suggestions? Output of logging is below:

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 1,
  warningCount: 1,
  message: '',
  protocol41: true,
  changedRows: 0
}

address result:
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 35,
  serverStatus: 1,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

insert owner 0
rows:
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 70,
  serverStatus: 1,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

...

insert owner 4
rows:
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 74,
  serverStatus: 1,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

commit:
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 0,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

Upvotes: 0

Views: 524

Answers (1)

Sachin Shah
Sachin Shah

Reputation: 4533

The first thing is that , this is not a good practice to execute a query.

what you are doing to run a for loop and each loop execute each query. Bu this way you give load to mysql. And also it will take longer time execute your API.

I suggest this way.

In for loop : 

for (var i = 0, len = owners.length; i < len; i++) {
   inserData.push(owners[i]); // Add all data in to array ... 
}

After that execute query once with all data. 

connection.query("INSERT INTO TABLE (columns) VLAUES ? " , insertData)...

Upvotes: 1

Related Questions