MySQL Blog Archive
For the latest blogs go to
MySQL 5.7.12 - Part 5: Connector/Node.js

In part 2 of this series of blog posts about MySQL 5.7.12 you could read about our new X Protocol for MySQL. Part 3 gave a glance on our new common basic API, the DevAPI. What’s a protocol and an API without clients using it? So we also worked on implementations of the protocol and API in our connectors. But we not only extended existing Connectors, like Connector/J or Connector/.Net, but also decided to create new languages and environments. One of those environments is Node.JS. Node.JS is an event-driven server-side environment which allows backend developers to use the same language, JavaScript, they use for their front-end development in browsers. Node.JS’s asynchronous event-based model also allows handling multiple requests in parallel from a single process.

One thing you’ll notice when you start working with our new Node.JS connector is that many of our API methods will return a Promise object.  Promise objects were introduced in Node.JS 4 as a way of solving the well known problem of “callback hell”.  Instead of control flow bouncing from callback to callback, control flows naturally from statement to statement and the developer uses the promise objects to retrieve the results of the method.  This has become a standard pattern in the Node.JS community.

In the early days of asynchronous JavaScript people frequently observed an anti-pattern – the “callback hell” which makes it hard to follow the flow of an program. In form of the Promise object ECMAScript 2015, also known as ECMAScript 6 or ES6 for short, the standardized version of JavaScript, a solution for this was standardized and became available with Node.JS 4. We decided this was a good approach for our Connector so that in Connector/Node.JS all network operations will return a Promise which will resolve on success or return an error.

Assuming you’ve read on the API and collected first experience with the X Shell we can take a look and connect to the server:

Lets’ go over this example in detail.

First thing we have to do is to load the Connector library into our code. The mysqlx object is our main entry point. We then create an X session by providing the hostname of MySQL server as well as our credentials. The getSession method will try to contact the server. As we don’t block and wait for the connection this will return a Promise. On the Promise we assign two handlers. One will fire on success, when we connected to the server. The other will be our error handler.  This example, like the ones below, uses an Arrow Function. In this case

is equal to

Usage of arrow functions not only saves typing, but also improves the handling of the this pointer.  Using arrow functions typically avoids mistakes and removes the need for constructs like var self = this one often sees in older JavaScript code.

When our connection succeeds we receive an object representing our current session.  If the connection fails (if we provided a wrong hostname or wrong credentials, for example), we’ll receive an exception in the form of an Error object in the catch block.

Now let’s make something a bit more interesting such as writing data into the database.

We connect to the database just like before and setup our error handlers just like we did in the previous example.  Once we have our connection we use a fluent design pattern and call session.getSchema() to retrieve a Schema object and then call getCollection() on that Schema object to get a Collection object.   A Collection is a special form of table for storing unstructured data. This is an example of methods in our API that do not return Promise objects.  Instead of Promise objects, the  getSchema() and getCollection()  methods (and others) return placeholder objects. We will check the existence of those objects on the server only later when they are actually part of an operation. Placeholder objects will normally have an existsInDatabase() member function which will verify the existence of the physical object.

Now we’re adding three documents of different structure into our store. The purpose of the structure chosen here is to discuss the error handling in an asynchronous, Promise-based world. So let’s discuss the program flow.

Once the session is established, our example app sends two documents grouped into a single operation to the server. Without waiting for any type of response from the server, our example sends the third document.  Again, without waiting for a response, we request the connection to be closed.  If all goes well the three objects will be stored in the database, the connection cleanly closed, and our program will finish. But what happens in case of a failure? The first important part here is the Promise.all() call which groups the Promise objects returned from the execute() and close() calls.  By returning the resulting Promise we make sure our error block is being called. Instead of this global error handler we, of course, might also attach error handlers to the individual operations.

The next aspect in regards to error handling was indicated above – we’re grouping some operations and pipeline multiple operations for the network. So the first two objects form an atomic unit. Either InnoDB will succeed to store both, or none.  It is unlikely but let’s assume we trigger an error while storing the first document. Then the second won’t be stored either. But the third document is waiting in a different operation in the pipeline.  This third document operation might succeed or also fail. Once the server handled it, the server processes the close connection request and closes the connection.  Now mind that the grouping with Promise.all() will only return the first error or report success if all operations succeeded. For more fine grained error handling one might handle the cases individually.

Of course that example was a bit complicated and we might simply group all documents into one call like this:

Then all form a unit and either all will be added or none.

Now that we’ve written documents to the database let’s load them again using a find() operation.

In this find() we search for all documents containing an answer of 42 and also apply a limit clause. As mentioned we make heavy use of Promises so when executing an find() we  also receive a Promise object.  This promise only resolves after the last result has been returned from the database. To avoid buffering this is a place where we additionally use a callback. This callback is triggered for each document returned from the server as soon as it available, so we can directly process it. Of course the error handler has access o the Error object and could provide more guidance.

Let’s hope this helps to get you started with our new Connecor/Node.js!