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.
INSERT
statements in Node. Then upload that file straight into your SQLITE database. I do not believe that your approach is meant for ETL. \$\endgroup\$this.dbpath
to a pre-seeded .sqlite file for that matter \$\endgroup\$