Seeding a file with fake records - doing concurrent updates

Question

I need to seed a database with user data.

When trying to write records to a file using sqlite3 with node, I tried writing the logic in a naive manner, without any error checks - but it was failing with numerous SQLITE_BUSY database locked errors.

Then I added code to retry in case of an error, but was insufficient - ended up with corrupt tables, and so I added transactions as suggested on freenode.

Now, it works. However the problem is that it is pathetically slow - takes up to 5 minutes for around 50 records. Also, I need to pass in a retries parameter of roughly number_of_users * 2 in order for the process to complete successfully or else it bails out. Something is not correct, but I cannot pin it down.

What is wrong?

//in the constructor ...
function TestController (api_url_root,number_of_users,number_of_devices) {
...
    this.user_write_q = async.queue(function (task,callback) {
        that.registerUserWithAPI(task.username, task.email, task.password, task.runAfterAllUsersAdded, callback);
    }, 1);

    this.user_write_q.drain = function() {
        console.log("Finished adding all users - queue drained");
    }
...
}


TestController.prototype.addUsers = function (callback_after_all_users_have_been_added) {

    this.pending_db_writes_for_user = this.number_of_users;
    fs.appendFileSync(this.user_list_file_path,"user email, password, api_key, api_secret\n");

    for(var i=0;i < this.number_of_users;i++) {
        var username    = Faker.Name.findName();
        var useremail   = Faker.Internet.email();
        var password    = Faker.Lorem.words(1)[0];
        this.user_write_q.push({username: username, email: useremail, password: password, runAfterAllUsersAdded: callback_after_all_users_have_been_added});
        console.log("Remaining requests: " + that.user_write_q.length());
    }
};


TestController.prototype.registerUserWithAPI = function (username, useremail, password, callback_after_all_users_have_been_added, queueCallback) {

    var controller = this;

    var post_options = {
        host: this.api_url_root,
        path: '/users/'+ useremail,
        method: 'POST',
        headers: {
            'Content-type': 'application/json'
        }
    }

    var post_req = http.request(post_options, function (res) {
            var data = "";
            res.setEncoding('utf8');
            res.on('data', function(chunk) {
                data += chunk;
            });

            res.on('end', function() {
                var dat = JSON.parse(data);
               controller.writeUserToLocalDatabase(controller.number_of_users*2,username,useremail,password,dat.api_key,dat.api_secret, queueCallback, callback_after_all_users_have_been_added);
               });
        });

    var post_data = '{"name":"'+username+'","password":"'+ password + '"}';
    post_req.write(post_data);
    post_req.end();
}


TestController.prototype.writeUserToLocalDatabase = function (retries,username,useremail,password,api_key,api_secret, queueCallback, callback_after_all_users_have_been_added) {
    var dbase = this.connectToDb();
    var controller = this;

    if (retries < 0) {
        console.log("Bailing out of writeUserToLocalDatabase after max retry attempts");
        return;
    }

    dbase.run("BEGIN TRANSACTION");

    var stmt = dbase.prepare("insert into user_table values(?,?,?,?,?)", function(err) {
          if(err !== null) {
            console.log("Error when trying to write user to database in prepare! "+err);
            dbase.run("ROLLBACK");
            dbase.close(function(err){if(err !== null) {console.log("Close failed in in prepare "+err);}});
            controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret, queueCallback, callback_after_all_users_have_been_added);
         }
    });

    stmt.run(username,useremail,password,api_key,api_secret, function(err) {
        if(err !== null) {
            //console.log("Error when trying to write user to database in run! "+err);
            stmt.finalize();
            dbase.run("ROLLBACK");
            dbase.close(function(err){if(err !== null) {console.log("Close failed in in run "+err);}});
            controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret,queueCallback,  callback_after_all_users_have_been_added);
        }
        else {
            stmt.finalize(function(err) {
                if(err !== null && err !== undefined) {
                    console.log("Error when trying to write user to database in finalize ! "+err);
                    dbase.run("ROLLBACK");
                    dbase.close(function(err){if(err !== null) {console.log("Close failed in in finalize "+err);}});
                    controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret, queueCallback, callback_after_all_users_have_been_added);
                }
                else {
                    dbase.run("COMMIT");
                    controller.appendToUserFile(useremail,password,api_key,api_secret);
                    controller.pending_db_writes_for_user--;
                    if (controller.pending_db_writes_for_user <= 0) {
                        console.log("Finished writing all users, now invoking db.close with callback");
                        queueCallback();
                        dbase.close(callback_after_all_users_have_been_added);
                    }
                    else {
                        console.log("Remaining users to add: "+controller.pending_db_writes_for_user);
                        dbase.close(function(err){if(err !== null) {console.log("Close failed in after adding user "+err);}});
                        queueCallback();
                    }
                }
            });
        }
    });
}

EDIT --- The code works acceptably. I managed to improve the performance by orders of magnitude by using async.queue with a queue length of 1 - now about 5 records per second as compared to 20 seconds per record. However this remains an open question about the best way to do concurrent writes to an sqlite3 database from node.

Answer 1

From a once over:

• The constructor could use some rework:

  • You are not using any of the parameters, you might as well not declare them
  • It would be cleaner to just send task to registerUserWithAPI since it has almost all the info
  • lowerCamelCase is the standard
  • The magical constant 1 should be documented
    

  so you could have something like this instead:

  function TestController () {
  
      var concurrency = 1; //Only allow one task at a time
  
      this.userCreationQueue = async.queue(function (task,callback) {
          that.registerUserWithAPI(task, callback);
      }, concurrency );
  
      this.userCreationQueue.drain = function() {
        console.log("Finished adding all users - queue drained");
      }
  }
  

  A minor side note, perhaps your test is super slow because of that concurrency setting ?

• You are writing a lot of code, with really length names to a) determine when the last record is written b) what to do in that case. In my mind, that is what drain was designed for. The database connection should have been managed in function TestController and should have been closed in drain. This would make your code leaner..

• Also, please fix indentation, var post_req is a bit of a mess

In the end, it seems like you have been troubleshooting this code a lot, and you have left a number of things in there because you were debugging with console.log statements. You should fix that. Also, you should try to loosen up the concurrency value; 1 at a time, is not a good test.