I'm having problem with sqlite3 not flush to disk. The code I'm using is below. My total filelist are over 470k and the program tends to use several gigabytes of memory. while the program is running test.db is 0 bytes and no journal is used. It only starts to write to disk when db.close()
is running.
var fs = require('fs');
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('test.db');
db.serialize(function () {
db.run("BEGIN;");
db.run("CREATE TABLE if not exists Files (name TEXT);");
db.run("COMMIT;");
var files = fs.readdirSync("./files/");
console.log("File list completed: " + files.length);
for (var i = 0; i < files.length; i++) {
db.run("INSERT INTO Files VALUES (?);",files[i]);
}
});
db.close();
I have have tried to remove db.run("BEGIN;");
and db.run("COMMIT;");
but it does not help.
Bug?
I'm reporting this as a bug on github
I'm think that there is a problem with transaction
and db.serialize
.
db.serialize
is uncontrolled code. I don't know when it's useful.
Try control flow like below
var fs = require('fs');
var sqlite3 = require('sqlite3');
var async = require('async');
var db = new sqlite3.Database('test.db');
async.series ([
function(cb) {
db.run('CREATE TABLE if not exists Files (name TEXT)', cb);
},
function(cb) {
db.run('begin transaction', cb);
},
function(cb) {
var files = fs.readdirSync("./files/");
async.each(
files,
function(file, cb) { db.run('INSERT INTO Files VALUES (?)', file, cb); },
cb
);
},
function(cb) {
db.run('commit transaction', cb);
}
],
function(err) {
if (err) {
console.log(err);
db.run('rollback transaction'); // can fail if error occurs on create table
}
db.close();
}
)
If you don't need insert all rows or nothing
that you can try next code
var fs = require('fs');
var sqlite3 = require('sqlite3');
var async = require('async');
var db = new sqlite3.Database('test.db');
db.run('CREATE TABLE if not exists Files (name TEXT)', function (err) {
if (err)
return console.log(err);
var files = fs.readdirSync("./files/");
async.eachSeries(
files,
function(file, cb) { db.run('INSERT INTO Files VALUES (?)', file, cb); },
function(err) {
console.log((err) ? err : 'Done');
db.close();
}
);
});