Nodejs node-sqlite3 run callback not working

Problem

I am trying to perform a delete of a row in sqlite db using nodejs and node-sqlite3 package.

When I run the delete command, and manually check the entries, I can see that the query successfully deleted that row but I cant seem to write the code that confirms this.

This is the query

db.run("DELETE FROM Table1 WHERE id=? AND username=?", [id, user], function(error) {
console.log(error);
});

Regardless of a wrong or right input, it outputs null to the console. If the right details are given, it deletes it and prints null, if wrong id and user are given, it still prints null.

Any ideas on what might be wrong?

Thanks

Problem courtesy of: Kartik

Solution

There is nothing wrong in the node and node-sqlite3 behaviour here. Here are two parts to explain first regarding node and other regarding Sqlite.

Node

Your callback is getting called after execution of the statement. So nothing wrong here, since your callback is getting called (as proved by 'null' as output).

Sqlite

Delete query in Sqlite deletes if condition given in where clause evaluates to true, otherwise nothing is deleted.

Referring from node-sqlite3 documentation's Database#run api:

callback (optional): If given, it will be called when an error occurs during any step of the statement preparation or execution, and after the query was run. If an error occurred, the first (and only) parameter will be an error object containing the error message. If execution was successful, the first parameter is null.

So, in your case query execution succeeds without any error, resulting in error argument to callback function null as you see in output.

Further, if you want to check if any row was actually removed, you can use changes property as mentioned in the documentation:

If execution was successful, it contains two properties named "lastID" and "changes" which contain the value of the last inserted row ID and the number of rows affected by this query respectively. Note that "lastID" only contains valid information when the query was a successfully completed INSERT statement and "changes" only contains valid information when the query was a successfully completed UPDATE or DELETE statement. In all other cases, the content of these properties is inaccurate and should not be used. The .run() function is the only query method that sets these two values; all other query methods such as .all() or .get() don't retrieve these values.

Hope it helps...

Solution courtesy of: Shubhansh

Discussion

To my prevoius question, the problem was that I've used fat arrow for callback declaration. From javascript documentation I've discovered that in arrow function (fat arrow ), this has lexical scope and so this result undefined and not valued as in library documentation said. Using otherwise anonimous function, this is bounded in dynamic scope and so this.changes is valued.

Now, with code as below, is ok:

var sql = 'update recipes set stars = stars + 1 where id = ?';

db.run(sql,
    [
    1 // id = 1 execute update - if id = 11111 do nothing
    ], function(err) {
        if(err)
            throw err;

        console.log("VALUE CHANGES: " + this.changes + " - " + util.inspect(this, { showHidden: false, depth: null }));

        if(this.changes == 1)
            console.log("WORK DONE");
        else
            console.log("NOTHING DONE");

    });

Here more explanations: https://github.com/mapbox/node-sqlite3/issues/606

Discussion courtesy of: Massimo Cappellano

I have the some problem with nodejs 4.3 and sqlite3: 3.1.1.

The problem is that I want to separate when update has effect on one row from when update has no effect on none row (without doing select first).

In my example I have a row with an id = 1. I would like to know that when I do an update with an id = 1111, no row has been affected.

I have used the callback on database.run but it seems not valorizing value of this.changes when update is executed on a row in the database.

var sql = 'update recipes set stars = stars + 1 where id = ?';

db.run(sql,
    [
    1 // id = 1 execute update - if id = 11111 do nothing
    ], (err) => {
        if(err)
            throw err;

        console.log("VALUE CHANGES: " + this.changes + " - " + util.inspect(this, { showHidden: false, depth: null }));

        if(this.changes == 1)
            console.log("WORK DONE");
        else
            console.log("NOTHING DONE");

    });

this.changes is undefined even when update is executed correctly (the same when I use an id, for example 1111111, not in the db).

There is a bug or I don't have understood how use callback?

Result on console is: VALUE CHANGES: undefined - { db: Database { open: true, filename: './dindin.sqlite', mode: 65542 } }

the same if id = 1 and execute update on one row or id= 1111111 and execute update on none row.

Discussion courtesy of: Massimo Cappellano

I had similar problem, callbacks just would not fire. Period. The problem was that elsewhere I was calling process.exit(1), so my code was exiting before the the callbacks had a chance to return.

Search for process.exit, that may (or may not) save you hours of debugging and blaming sqlite :)

Off the topic: What bugs my mind is why they all-cap ID in lastID. It's not like it's an abbreviation like SQL or USA. It stands for Identification, which is one word.

Discussion courtesy of: Division by Zero

This recipe can be found in it's original form on Stack Over Flow.