Cannot SELECT anything out of the DB

[ivansopin]

I am on node.js v4.5.0, and when using node-sqlite3 most operations seem to work fine for me: I can create the database, tables, do inserts, and close the database. However, when I try a SELECT in any fashion (db.all(), db.each(), or db.get()) I get an empty row set back, even though the same query does return proper data in sqlite3 CLI.

Here is the code I'm using:

var sql = "SELECT * FROM " + table.name;
console.log("SQL is " + sql);
db.all(sql, function(err, rows) {
    if (err) {
        console.log(err);
        return;
    }
    console.log("We've got " + rows.length + " rows");
    for (var row in rows) {
        console.log(row);
    }
    console.log("Finished processing");
});

I see "SQL is SELECT * FROM

", "We've got 0 rows" and "Finished processing," but nothing else (indicating there's no error). I have also tried listing SELECT fields individually, using a subset of those, and using aliases, but to no avail.

At this point I am stuck. It seems like an issue in the library, and I see no work-around.

[kroggen]

Hi Ivan,

First check the path to the db file. Maybe it is opening another one with an empty table.

[ivansopin]

The database is removed, if exists, recreated, populated, and read in one session as a series of promise actions. I have made sure that the database a) gets removed; b) gets created; and c) gets populated every time. So, no chance of talking to a different database.

[ivansopin]

OK, I managed to solve/work around my problem. The issue stemmed from the way I populate the table: Because I am at development stages, I am using a static text file with fake data, and to make the inserts into the DB more efficient, I am running things in transaction mode:

db.run("BEGIN TRANSACTION") -> db.run("INSERT ...")... -> db.run("END")

My callback function was on the last of these calls. Turns out, when that callback got invoked, the data was not yet visible in the subsequent SELECT. Why is that?

The way I reworked this is to wait for the BEGIN call to finish, then for the last of the INSERTs, and finally for the END. This is the sample code with Promises:

function populateTable(table, file) {
    var promise = new Promise(function(resolve, reject) {
        try {
            db.run("BEGIN TRANSACTION", function(err) {
                if (err) {
                    reject(err);
                } else {
                    LineReader.eachLine(file, function(line, last) {
                        if (!last) {
                            db.run("INSERT INTO " + table + " VALUES (" + line + ")", function(err) {
                                if (err) {
                                    reject(err);
                                }
                            });
                        } else {
                            db.run("INSERT INTO " + table + " VALUES (" + line + ")", function(err) {
                                if (err) {
                                    reject(err);
                                } else {
                                    db.run("END", function(err) {
                                        if (err) {
                                            reject(err);
                                        } else {
                                            resolve();
                                        }
                                    });
                                }
                            });
                        }
                    });
                }
            });
        } catch (err) {
            reject(err);
        }
    });

    return promise;
}

Unfortunately, this is pretty elaborate, and I am not even sure that waiting only for the last of the INSERTs is the right thing to do, but this is temporary code for development efforts, so it'll do for the moment. However, before I close this ticket, can you please explain how individual db.run() commands are ordered? Is it reasonable to expect callback from INSERT #1 before INSERT #10?

[kroggen]

Use db.serialize

[tmcw]

Yep, db.serialize should be the solution to this issue, or if you want to use a flow-control library like async or d3-queue. @ivansopin does db.serialize resolve the syntax problem for you?

[ivansopin]

Yes, it does. Sorry I forgot to close the issue.