CODEWITHSUNDEEP
node.jssqlitepackage
Traveling Tech Guy
Traveling Tech Guy

Reputation: 27811

Wrong query order in Node.js script using SQLite3 package

I'm playing with the node-sqlite3 package and ran into a strange situation. This simple script has a simple logic: find if a table exists, if it does empty it, if not - create it.

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('guy.sqlite');    //':memory:'

var tableExists = false;
db.serialize(function() {
    db.get("SELECT name FROM sqlite_master WHERE type='table' AND name='lorem'", function(error, row) {
        tableExists = (row != undefined);
        console.log("xxxxx " +tableExists);
    });

    console.log("yyyyy " +tableExists);

    if (tableExists) {
        console.log("table exists. cleaning existing records");
        db.run("DELETE FROM lorem", function(error) {
            if (error)
                console.log(error);
        });
    }
    else {
        console.log("creating table")
        db.run("CREATE TABLE lorem (info TEXT)", function(error) {
            if (error.message.indexOf("already exists") != -1) {
                console.log(error);
            }
        });
    }
});

db.close();

But the result I get is:

yyyyy false
creating table
xxxxx true
{ stack: [Getter/Setter],
  arguments: undefined,
  type: undefined,
  message: 'SQLITE_ERROR: table lorem already exists',
  errno: 1,
  code: 'SQLITE_ERROR' }

According to the serialize command documentation, these queries are supposed to run sequentially. Yet clearly the evaluation of tableExists (marked with 'yyy') occurs before a value is set (marked with 'xxx'), causing an attempt to recreate an existing table.
I can handle the error, but I was wondering what's causing this and how to avoid such behavior in the future.

As always, thanks for your time.

Guy

Upvotes: 1

Views: 4906

Answers (2)

Richard Hoffman
Richard Hoffman

Reputation: 729

Example 2 in the documentation explains your problem directly. The tableExists variable will be false in the first run-through while the SELECT function is only queued on the event loop, and won't get run until after the code in serialize() is finished. The solution would be to place the if(tableExists) code inside the callback to SELECT. This defeats the purpose of using serialize, but that's how it works. Writing values are fine because they will get queued up in the order they were called, but something that requires a response via I/O will need a callback and thus run asynchronously.

Also, the SQLITE_MISUSE error comes from the fact that you call db.close() at the end of the script, but outside of the asynchronous callbacks. Even though it's at the end of the script, any asynchronous stuff guts put into a queue that isn't called until after the current function has finished.

Upvotes: 1

Tobias P.
Tobias P.

Reputation: 4665

node.js works in an asynchronous mode, but your code is some strange form form of mixxed synchronous and asynchronous form.

the complete code after the select should be in the callback whie your actual code does not do what you want, tableexists will not have the values you like.

learn loving the asynchronous model of node.js ;)

Upvotes: 2

Related Questions