sqlite3 db.close() called before db.serialized() finishes

Question

i have a sqlite3 nested query case. Was hoping to push each query result to a json array and return it back. But always get "Error: SQLITE_MISUSE: Database handle is closed" for the 2nd select call. Seems the db.close() gets called before the 2nd query.

Why is this, i thought serialize can take care of this. How to fix it please ?

var getMyDbInfo = function(callback) {
    var db = new sqlite3.Database("MyDB.sqlite3");

    db.serialize(function() {

        var myJsonObj = {};

        db.each("select * from Table1",
            function(err, row) {
                console.log("\n---- 0 ----\n");
                // calculate doorId from row
                doorId = ...

                db.all("select * from Table2 where ID=" + doorId,
                       function(err, row2) {
                           console.log("---- 6 ----\n");
                           if(err) {
                               console.log("-- ERR: " + err);
                           } else {
                               console.log(row2);
                               var myJsonElem = {ID:row.ID,
                                   DoorName: row2.DoorName,
                                   TimeSpec: row2.TimeSpec };

                               myJsonObj.data.push(myJsonElem);
                           }
                       }
               );
            }
        );
        callback(null, myJsonObj);
    });

    console.log("---- 10 ----\n");
    db.close();

};

Answer 1

The db.all() call is nested in the callback function of db.each(). Per the docs, db.serialize() only serializes calls made directly in the db.serialize() callback function. As far as db.serialize() is concerned, its job is done as soon as the callback for db.each() is called, as there are no more inline calls to make, and so db.close() is executed.

A solution here - Get rid of db.serialize() since you're only calling db.each() inside it, and use a second "completion" callback function for db.each() that calls db.close() after db.each() has run through all rows.

If need to make more DB calls after db.each(), add a db.serialize() inside the db.each() completion callback and continue from there.

var getMyDbInfo = function(callback) {
    var db = new sqlite3.Database("MyDB.sqlite3");

    var myJsonObj = {};

    db.each("select * from Table1",
        function(err, row) {
            console.log("\n---- 0 ----\n");
            // calculate doorId from row
            doorId = ...

            db.all("select * from Table2 where ID=" + doorId,
                    function(err, row2) {
                        console.log("---- 6 ----\n");
                        if(err) {
                            console.log("-- ERR: " + err);
                        } else {
                            console.log(row2);
                            var myJsonElem = {ID:row.ID,
                                DoorName: row2.DoorName,
                                TimeSpec: row2.TimeSpec };

                            myJsonObj.data.push(myJsonElem);
                        }
                    }
            );
        },
        function (err, rows) {
            callback(null, myJsonObj);
            db.close();
            console.log("---- 10 ----\n");
        }
    );

};