MySQL 8.0 and the Document Store highlight brand new client tools such as the Shell and Connector/Node.js, both providing a JavaScript implementation of the X DevAPI. Although, for most cases, the public interface tends to be similar on both (barring some small details), there is one notable exception.

The way you handle result sets in a Node.js app using Connector/Node.js is a bit different from the way you do it using an interactive session or a non-interactive JS script in the Shell.

Executing an operation in the MySQL server using the Shell is a purely synchronous task from the user standpoint since the underlying code is C++ (multi-threaded) whereas, due to the way Node.js works (single-threaded with event loop), an application that uses Connector/Node.js has to do the same by resorting to some asynchronous construct.

In this specific case, Connector/Node.js uses Promises. So, every time we call execute(), the method returns back a Promise object which will eventually resolve to a Result instance. Currently, for retrieval operations such as collection.find(), table.select() and session.sql(), and to avoid buffering data at the client layer, that object only contains details about the operation status (generated warnings, number of affected items, etc.) and not really the result set data. So, it is expected that, at the same time, the application also provides a callback as the argument of execute() which will be called for each element of the result set, and can be used itself to buffer data in some sort of application-level data-structure. In a way, this is somewhat similar to how a Node.js streaming API would work.

This is always the case, no matter the operation boundaries that are additionally defined like the number of results that are picked by the result set (with limit()) or the number of results that are skipped in that same result set (with offset()).

The following Connector/Node.js examples use the async/await syntax, which is available since Node.js v7.6.0.

Document Store

Assume you have a collection mySchema.myCollection like the following:

[{
  _id: '1',
  name: 'foo'
}, {
  _id: '2',
  name: 'bar'
}]

Using the Shell, you can retrieve all the documents in the collection with:

var collection = session.getSchema('mySchema').getCollection('myCollection');

var docs = collection.find().fetchAll();
print(docs); // [{ _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }]

// alternatively using a shortcut
collection.find(); // [{ _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }]

// alternatively, fetching one document at a time
var docs = [];
var result = collection.find();

while (doc = result.fetchOne()) {
  docs.push(doc);
}

print(docs); // [{ _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }]

On the other hand, to retrieve those documents using Connector/Node.js, you will have to resort to something like:

var mysqlx = require('@mysql/xdevapi');
var config = { /* connection options */ };

var docs = [];

(async function () {
  var session = await mysqlx.getSession(config);
  var collection = session.getSchema('mySchema').getCollection('myCollection');

  var result = await collection.find().execute(doc => docs.push(doc));

  console.log(result.getWarnings()); // []
  console.log(docs); // [{ _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }]
})();

Additional details can be found in the documentation.

Relational Tables

The behavior is the same with relational tables, with the added bonus that you can also retrieve details about the metadata of each column that is included in the result set.

So, assuming you have a table mySchema.myTable like the following:

+-----+-------+
| _id | name  |
+-----+-------+
| "1" | "foo" |
| "2" | "bar" |
+-----+-------+

Using the Shell, you can retrieve all the rows in the table with:

var table = session.getSchema('mySchema').getTable('myTable');

var rows = table.select().fetchAll();
print(rows); // [['1', 'foo'], ['2', 'bar']]

// alternatively using a shortcut
table.select();
// +-----+-------+
// | _id | name  |
// +-----+-------+
// | "1" | "foo" |
// | "2" | "bar" |
// +-----+-------+

// alternatively, fetching one document at a time
var rows = [];
var result = table.select().execute();

while (doc = result.fetchOne()) {
  rows.push(doc);
}

print(rows); // [['1', 'foo'], ['2', 'bar']]

Retrieving column metadata can be done with:

var columns = [];
var metadata = result.getColumns();

for (var i = 0; i < metadata.length; ++i) {
  columns.push(metadata[i].getColumnName());
}

print(columns); // ['_id', 'name']

Whereas, using Connector/Node.js, the column metadata is retrieved via an additional callback.

var mysqlx = require('@mysql/xdevapi');
var config = { /* connection options */ };

var rows = [];
var columns = [];

(async function () {
  var session = await mysqlx.getSession(config);
  var table = session.getSchema('mySchema').getTable('myTable');

  var result = await table.select().execute(row => rows.push(row), column => columns = columns.concat(column));

  console.log(result.getWarnings()); // []
  console.log(rows); // [['1', 'foo'], ['2', 'bar']]
  console.log(columns.map(column => column.getColumnName())); // [['_id', 'name']]
})();

Additional details can be found in the documentation.

SQL

The raw SQL API is mostly similar to the relational table CRUD API, with a notable caveat. If you execute a SQL statement that leads to multiple result sets, for instance, calling a PROCEDURE that runs multiple queries, you will need to keep track of new result sets by checking every time the metadata callback is invoked.

Given the same table as before, assuming there is a procedure proc defined like:

DELIMITER //
CREATE PROCEDURE proc()
BEGIN
  SELECT _id AS s1_c1, name AS s1_c2 FROM myTable;
  SELECT '3' as s2_c1, 'baz' AS s2_c2;
END//

Using the Shell, the result sets returned by calling the procedure can be handled like:

var rows = [];
var columns = [];

var res = session.sql('CALL proc').execute();

do {
  var metadata = res.getColumns();
  var data = res.fetchAll();

  if (data.length) {
    rows = rows.concat(data);

    var tmp = [];

    for (var i = 0; i < metadata.length; ++i) {
      tmp.push(metadata[i].getColumnLabel());
    }

    columns.push(tmp);
  }

} while (res.nextResult());

print(rows); // [[['1', 'foo'], ['2', 'bar']], [['3', 'baz']]]
print(columns); // [['s1_c1', 's1_c2'], ['s2_c1', 's2_c2']]

Whereas with Connector/Node.js, the same thing can be achieved with:

var mysqlx = require('@mysql/xdevapi');
var config = { /* connection options */ };

var rows = [];
var columns = [];

(async function () {
  var i = 0;
  var session = await mysqlx.getSession(config);
  
  await session.sql('USE mySchema').execute()

  var result = await session.sql('CALL proc').execute(row => {
    // metadata always comes first, so we can assume the index was already incremented
    rows[i - 1] = rows[i - 1] || [];
    rows[i - 1].push(row);
  }, metadata => {
    columns[i] = columns[i] || [];
    columns[i] = columns[i].concat(metadata);

    i += 1;
  });

  console.log(result.getWarnings()); // []
  console.log(rows); // [[['1', 'foo'], ['2', 'bar']], [['3', 'baz']]]
  console.log(columns.map(row => row.map(column => column.getColumnLabel()))); // [['s1_c1', 's1_c2'], ['s2_c1', 's2_c2']]
})();

Handling single result set queries works pretty much the same as in the relational table CRUD example. Using the Shell, it’s as simple as:

var res = session.sql('SELECT "foo" AS c1, "bar" AS c2').execute();

var rows = res.fetchAll();
var columns = [];

var metadata = res.getColumns();

for (var i = 0; i < metadata.length; ++i) {
  columns.push(metadata[i].getColumnLabel());
}

print(rows); // [['foo', 'bar']]
print(columns); // ['c1', 'c2']

Using Connector/Node.js, you can do the same with:

var mysqlx = require('@mysql/xdevapi');
var config = { /* connection options */ };

var rows = [];
var columns = [];

(async function () {
  var session = await mysqlx.getSession(config);

  var result = await session.sql('SELECT "foo" AS c1, "bar" AS c2')
    .execute(row => rows.push(row), column => columns = columns.concat(column));

  console.log(result.getWarnings()); // []
  console.log(rows); // [['foo', 'bar']]
  console.log(columns.map(column => column.getColumnLabel())); // ['c1', 'c2']
})();

Final Thoughts

In summary, these are probably the only scenarios where the API diverges between Connector/Node.js and the Shell, which speaks volumes about the efficiency of a standard like the X DevAPI.

We understand this way of handling data is not exactly a common Node.js asynchronous pattern, but we believe it is nonetheless effective to prevent unnecessary buffering at the client layer and at giving a lot more control to the application developer, particularly with regards to handling multiple result sets, at least until a proper streaming solution is provided. Whether you agree or not, please give us your feedback, as we are always looking for improvements to the API and the overall developer experience.

If you want to learn more about Connector/Node.js and the X DevAPI, please check the following:

Make sure you also join our community Slack and come hang around at the #connectors channel:

MySQL Community on Slack