CODEWITHSUNDEEP
node.jssqlite
PA.
PA.

Reputation: 29339

how to correctly serialize db.prepare in nodejs sqlite3

I am trying to insert some data that depends on previously queried data, I serialize the query and the insert, but I can't get the correct results.

I build this simple example to show my error. It just queries for the number of rows in the table, and inserts it back to the table. 

db.serialize(function() {
  var count=0;
  db.each("SELECT count(rowid) FROM info", function(err, row) {
    count = row['count(rowid)'];
  });

  var stmt = db.prepare("INSERT INTO info VALUES (?)");
  stmt.run('Lorem ipsum '+(count+1));
  stmt.finalize();

});

if I run this code three times and inspect the data, I just get the string "Lorem ipsum 1" three times.

Additionally, if I add some query code after the stmt.finalize instruction

  db.each("SELECT count(rowid) FROM info", function(err, row) {
    count = row['count(rowid)'];
    console.log('count info',count);
  });
  db.each("SELECT rowid AS id, info FROM info", function(err, row) {
    console.log(row.id + ": " + row.info)
  });

the query serializes correctly and I get the expected count but still the bad inserts (in the example I ran the insert code five times)

count info 5
1: Lorem ipsum 1
2: Lorem ipsum 1
3: Lorem ipsum 1
4: Lorem ipsum 1
5: Lorem ipsum 1

Could you explain this behavior of db.prepare()? and can you propose the correction?

Upvotes: 0

Views: 4517

Answers (2)

Sauce
Sauce

Reputation: 46

This is an old question, but since I had the same question and came across this page years later, I thought I'd take a stab at my understanding:

db.serialize will serialize the queries. That means that if there's a query running, other queries will be queued up and run later, so you get an assurance that the queries run in the order they were presented. But that doesn't mean the JS code itself is blocked. Your code assumes that db.serialize causes database calls to be blocking (synchronous). I believe the problem is here:

var count=0;
[...] 
var stmt = db.prepare("INSERT INTO info VALUES (?)");
stmt.run('Lorem ipsum '+(count+1));

specifically, this second statement will be queued up to run after the previous statement. However, while that is true of the statement, the evaluation of (count+1) already happened. That means that count was initialized to 0, a (slow) database query was run, and a statement was queued up using count+1. Since the computation of count+1 didn't wait for the query to finish, count's original value (0) is used and you are effectively queuing up:

INSERT INTO info VALUES ('Lorem ipsum ' + (0 + 1))

which explains why you always see "Lorem ipsum 1".
I believe the fix for this is to queue up the second sql statement in the callback of the first. Only then will you be assured that count will have been updated correctly.

Of course, another fix would be to construct an INSERT statement that uses SQL to compute the count directly, but I'm assuming that you created this artificial example to prove a point.

Sauce

Upvotes: 3

Aboriginal
Aboriginal

Reputation: 394

I am not sure after 2 months this answer is still useful.

First of all to insert "'Lorem ipsum '+(count+1)" we need to have iteration.

Secondly SQL query needs to return columns.

db.serialize(function() {
      db.run("CREATE TABLE IF NOT EXISTS info (info TEXT)"); 

      var stmt = db.prepare("INSERT INTO info VALUES (?)");
      for (i = 0 ; i < 5 ; i++) {  // an iteration is required to insert data into the table
          stmt.run('Lorem ipsum '+ i);
        }

      stmt.finalize();

      db.each("SELECT rowid AS id, info FROM info", function(err, row) {  // columns should be retrieved 
         console.log(row.id + ": " + row.info);
      });
});

For more info please refer to docs

Upvotes: 0

Related Questions