Wrong query order in Node.js script using SQLite3 package

Question

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

Why is the code to delete/create the table not inside the callback that checks if the table exists? — Dan Grossman
– Dan Grossman, Commented Jul 29, 2011 at 2:37
Tried that and got the following error: 'SQLITE_MISUSE: Database handle is closed' — Traveling Tech Guy
– Traveling Tech Guy, Commented Jul 29, 2011 at 2:38

Tobias P. · Accepted Answer · 2011-07-29 09:34:06Z

2

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 ;)

answered Jul 29, 2011 at 9:34

Tobias P.

4,6722 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Hoffman · Accepted Answer · 2011-07-29 14:59:43Z

1

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.

edited Jul 29, 2011 at 14:59

answered Jul 29, 2011 at 14:48

Richard Hoffman

7293 silver badges10 bronze badges

2 Comments

Traveling Tech Guy Over a year ago

Moved the condition into the SELECT function and commented the db.close() - now it works. But if I do want the db.close() statement, where would I put it so it'd execute in the desired order? Tried in and out of .serialize() function and got the MISUSE error.

Richard Hoffman Over a year ago

Basically, after all asynchronous commands are finished. This is where the difficult part comes in though, if you need the script to do a lot more than just one simple create table. Understanding asynchronous programming and continuations is a bit of a learning curve at first, and one of the things that new noders always trip up on. There are a lot of libraries that try and make this process simpler. Look at Step . And a good read is Node.js Flow Control

Collectives™ on Stack Overflow

Wrong query order in Node.js script using SQLite3 package

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related