title: Sqlite3 - node js insert multiple rows in two tables, lastID not working
body: <p>I know there are many solutions provided regarding multiple insertion in <code>sqlite3</code> but I am looking for efficient method in which data is getting inserted into two tables and data of second table is dependent on first table. This is a <code>node js</code> application.</p>
  
  <p>I have two tables <code>programs</code> and <code>tests</code> in sqlite3. Table <code>tests</code> contains id of <code>programs</code> i.e. one program can contains multiple tests.</p>
  
  <p><strong>The suggested method on official page</strong> of <code>sqlite3</code> module is as follows:</p>
  
  <pre><code>var sqlite3 = require('sqlite3').verbose();
  var db = new sqlite3.Database(':memory:');
  
  db.serialize(function() {
    db.run("CREATE TABLE lorem (info TEXT)");
  
    var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
    for (var i = 0; i &lt; 10; i++) {
        stmt.run("Ipsum " + i);
    }
    stmt.finalize();
  
    db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {
        console.log(row.id + ": " + row.info);
    });
  });
  
  db.close();
  </code></pre>
  
  <p>As In my requirement I have to insert data in tow tables so <strong>I am using the following code:</strong></p>
  
  <pre><code>var programData = resp.program_info; // contains complete data of programs and tests
  db1.run("INSERT INTO programs (`parent_prog_id`, `prog_name`, `prog_exercises`, `prog_orgid`, `prog_createdby`, `prog_created`, `prog_modified`, `prog_status`)  VALUES (?,?,?,?,?,?,?,?)",prog.parent_program_id,prog.programName, JSON.stringify(prog), req.session.org_id, prog.created_by, prog.created_at, prog.updated_at, prog.program_status,function(err){
  if(err){
         throw err;
  }else{
  var count           = 1;
  var step2PostedData = prog;
  for (i in step2PostedData.testsInfo) {
    var c = 0;
  
     for(j in step2PostedData.testsInfo[i]){
  
      var obj = Object.keys(step2PostedData.testsInfo[i])[c];
  
      db1.prepare("INSERT INTO `tests` ( `parent_name`,`test_name`, `test_alias`, `sequences`, `duration`, `prog_id`, `org_id`, `test_createdby`,  `test_created`, `test_modified`, `test_status`) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
      .run(count, obj,
           step2PostedData.testsInfo[i][j].alias,
           step2PostedData.testsInfo[i][j].sequences,
           step2PostedData.testsInfo[i][j].duration,
           this.lastID, // using this I am getting program id
           req.session.org_id, 
           prog.created_by, 
           prog.created_at, 
           prog.updated_at,
           prog.program_status);
  
      c++;
      count++;
      }
  }
  </code></pre>
  
  <p><strong>Now, my query is If I am using the suggested method then I am not getting last inserted program id from <code>programs</code> table.</strong> without callback.</p>
  
  <p>e.g. If I use the following code;</p>
  
  <pre><code>var stmt = db1.prepare("INSERT INTO programs (`parent_prog_id`, `prog_name`, `prog_exercises`, `prog_orgid`, `prog_createdby`, `prog_created`, `prog_modified`, `prog_status`)  VALUES (?,?,?,?,?,?,?,?)";
  
  var stmt2 = db1.prepare("INSERT INTO `tests` ( `parent_name`,`test_name`, `test_alias`, `sequences`, `duration`, `prog_id`, `org_id`, `test_createdby`, `test_created`, `test_modified`, `test_status`) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
  
  programData.forEach(function(prog){
      // Inserts data in programs table
      stmt.run(prog.parent_program_id,prog.programName, JSON.stringify(prog), req.session.org_id, prog.created_by, prog.created_at, prog.updated_at, prog.program_status);
  
       for (i in step2PostedData.testsInfo) {
         var c = 0;
         for(j in step2PostedData.testsInfo[i]){
           var obj = Object.keys(step2PostedData.testsInfo[i])[c];
           stmt2.run(
                     count, 
                     obj, 
                     step2PostedData.testsInfo[i][j].alias,
                     step2PostedData.testsInfo[i][j].sequences,  
                     step2PostedData.testsInfo[i][j].duration,
                     'what should be there',// How to get last program inserted ID there   
                     req.session.org_id,
                     prog.created_by, 
                     prog.created_at, 
                     prog.updated_at,
                     prog.program_status);
        } // inner for loop ends
    } // outer for loop ends
    stmt.finalize();
    stmt2.finalize();
  });
  </code></pre>
  
  <ul>
  <li><p>If I use <code>this.lastID</code> that returns null, obviously as no callback is
  now.</p></li>
  <li><p>If I use <code>sqlite3_last_insert_rowid()</code> then 
  sqlite3_last_insert_rowid is not defined error.</p></li>
  <li><p>If I use <code>last_insert_rowid()</code> then last_insert_rowid() is not
  defined.</p></li>
  </ul>
  
  <p><strong>Query:</strong></p>
  
  <p>How can I insert last inserted program id there, Currently I am getting last program id as null?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>If and only if using callback is the way or method to get the last ID then I will keep my code running as it is currently. Can anyone please suggest how can I increase the speed of insertion.</p>
  
  <p>Thank you!</p>