Iterating through database records in node.js

Question

I'm looking to learn node.js and mongodb which look suitable for something I'd like to make. As a little project to help me learn I thought I'd copy the "posts" table from a phpbb3 forum I have into a mongodb table so I did something like this where db is mongodb database connection, and client is a mysql database connection.

db.collection('posts', function (err, data) {
    client.query('select * from phpbb_posts",  function(err, rs) {
         data.insert(rs);
    });

this works ok when I do it on small tables, but my posts table has about 100000 rows in and this query doesn't return even when I leave it running for an hour. I suspect that it's trying to load the entire database table into memory and then insert it.

So what I would like to do is read a chunk of rows at a time and insert them. However I can't see how to read a subset of the rows in node.js, and even more of a problem, I can't understand how I can iterate through the queries one at a time when I only get notification via a callback that it's finished.

Any ideas how I can best do this? (I'm looking for solutions using node.js as I'd like to know how to solve this kind of problem, I could no doubt do it easily some other way)

Two questions: 1. Is that a typo in your "select"? I see a single quote and a double quote. 2. What happens when you do a limit 10? or a limit 1? Did this process at least work for small data sets? — Gates VP
– Gates VP, Commented Mar 3, 2011 at 22:09
Yes sorry, I simplified the query slightly for posting and made a typo. I can do a limit 10 or even 1000 and that works ok. But it gets slower and slower very quickly after that. — jcoder
– jcoder, Commented Mar 3, 2011 at 22:36

schaermu · Accepted Answer · 2011-03-04 15:06:56Z

1

You could try using the asnyc library by caolan. The library implements some async flow control methods to handle the caveats of a callback-oriented programming style as it is in node.js.

For your case, using the whilst method could work out, using LIMIT queries against mysql and inserting them into mongodb.

Example (not tested, as i have no testdata available, but i think you'll get the idea)

var insertCount = 0;
var offset = 0;

// set this to the overall recordcound from mysql
var recordCount = 0;

async.whilst(
  // test condition callback
  function () { return insertCount < recordCount; },

  // actual worker callback
  function (callback) {
    db.collection('posts', function (err, data) {
      client.query('select * from phpbb_posts LIMIT ' + insertCount + ',1000',  function(err, rs) {
        data.insert(rs);

        // increment by actually fetched recordcount (res.length?)
        insertCount += res.length;

        // trigger flow callback
        callback();
      });
    });
  },

  // finished callback
  function (err) {
    // finished inserting data, maybe check record count in mongodb here
  }
});

As i already mentioned, this code is just adapted from an example of the async library readme. But maybe it is an option for adding such amounts of database records from mysql to mongo.

answered Mar 4, 2011 at 15:06

schaermu

13.5k2 gold badges41 silver badges33 bronze badges

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

1 Comment

jcoder Over a year ago

That looks interesting, I'll look into it.

Collectives™ on Stack Overflow

Iterating through database records in node.js

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related