connector/node.js collection.add().execute() seems to only work once

Question

I am testing MySQL Document Store. In order to properly compare with our relational tables, I am attempting to transform our current table into a collection. There approximately 320K records in the table that I wish to export and add to a new collection. I am attempting to use the Connector/Node.js to do this. To avoid blowing it up, I am attempting to add 10K records at a time, but only the first 10K records are inserted. I have confirmed that it is the first 10K records, it is not overwriting each iteration. And those 10K records are correctly structured.

const mysqlx = require('@mysql/xdevapi');
const config = {
    password: 'notMyPassword',
    user: 'notMyUser',
    host: 'notMyHost',
    port: 33060,
    schema: 'sample'
};
var mySchema;
var myCollection;
var recCollection = [];
mysqlx.getSession(config).then(session => {
    mySchema = session.getSchema('sample');
    mySchema.dropCollection('sample_test');
    mySchema.createCollection('sample_test');
    myCollection = mySchema.getCollection('sample_test');
    var myTable = mySchema.getTable('sampledata');
    return myTable.select('FormDataId','FormId','DateAdded','DateUpdated','Version','JSON').orderBy('FormDataId').execute();
    }).then(result => {
        console.log('we have a result to analyze...');
        var tmp = result.fetchOne();
        while(tmp !== null && tmp !== '' && tmp !== undefined){
            var r = tmp;
            var myRecord = {
                'dateAdded': r[2],
                'dateUpdated': r[3],
                'version': r[4],
                'formId': r[1],
                'dataId': r[0],
                'data': r[5]
                };
            recCollection.push(myRecord);
            if (recCollection.length >= 10000){
                console.log('inserting 10000');
                try {
                    myCollection.add(recCollection).execute();
                } catch(ex){
                    console.log('error: ' + ex);
                }
                recCollection.length = 0;
            }
            tmp = result.fetchOne();
        }
        
    });

Answer 1

It looks like an issue related to how you handle asynchronous execution. The execute() method of a CollectionAdd statement is asynchronous and it returns a Promise.

In a while loop, unless you "await" for the execution to finish, the construct is not able to handle it for you, even though the first call to the asynchronous method always goes through. That's why it only adds the first 10k documents.

You also need to be careful with APIs like createCollection() and dropCollection() because they are also asynchronous and return back a Promise similarly.

Using your own example (without looking into the specifics) it can be something like the following:

const mysqlx = require('@mysql/xdevapi');

const config = {
  password: 'notMyPassword',
  user: 'notMyUser',
  host: 'notMyHost',
  port: 33060,
  schema: 'sample'
};

// without top-level await
const main = async () => {
  const session = await mysqlx.getSession(config);

  const mySchema = session.getSchema('sample');
  await mySchema.dropCollection('sample_test');

  const myCollection = await mySchema.createCollection('sample_test');
  const myTable = mySchema.getTable('sampledata');

  const result = await myTable.select('FormDataId', 'FormId', 'DateAdded', 'DateUpdated', 'Version', 'JSON')
    .orderBy('FormDataId')
    .execute();

  const tmp = result.fetchOne();
  while (tmp !== null && tmp !== '' && tmp !== undefined) {
    let r = tmp;
    let myRecord = {
      'dateAdded': r[2],
      'dateUpdated': r[3],
      'version': r[4],
      'formId': r[1],
      'dataId': r[0],
      'data': r[5]
    };

    recCollection.push(myRecord);

    if (recCollection.length >= 10000){
      console.log('inserting 10000');
    
      try {
        await myCollection.add(recCollection).execute();
      } catch (ex) {
        console.log('error: ' + ex);
      }

      recCollection.length = 0;
    }

    tmp = result.fetchOne();
  }
}

main();

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js