Fredrik Karlsson Fredrik Karlsson - 2 months ago 15
Node.js Question

Nodjs Sqllite3 not flushing to disk

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

Answer

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();
        }  
    );
});
Comments