MySQL :: Develop By Example – Document Store Connections using Node.js

In this post we are going to explain how to connect a Node.js application to a MySQL server using the new MySQL Connector/Node.js; needless to say that we will be using the MySQL server as a document store.

There are two types of session that a connection can provide: XSession and NodeSession.
An XSession encapsulates access to a single MySQL server running the X Plugin or
multiple MySQL Cluster nodes; and the NodeSession serves as an abstraction for a physical connection to exactly one MySQL server running the X Plugin. To enable the XPlugin in the MySQL server using the MySQL Client command line you need to use the root account or an account with INSERT privilege to mysql.plugin table:

Invoke the MySQL command-line client: mysql -u user –p
Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Click here for more information about how to setting up MySQL as document store.

Creating a connection to a MySQL server as a document store is quite similar to create a connection to a traditional MySQL server; we require the following connection parameters: host, database user, user password, and port.

The following example demonstrates how to connect to a single MySQL Server using XSession:

var mysqlx = require('mysqlx');
mysqlx.getSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (session) {
        console.log('We are connected!');
        session.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

In the previous code example, we created and closed a connection to a server using an XSession; as you can see the code is very simple and easy to read.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession method. This method implements a promise. If the connection to the MySQL server is successful the promise is fulfilled by returning an XSession (session) object. We then call the session object’s close method to close the connection.

In the previous code there are two important things to note. The first one is that we do not specify a schema because the XSession works similar to a traditional session: You do not need to specify a schema because, at the time you connect, your working schema might not exist yet. The second one is the port. By default the X DevAPI uses the port 33060; we are assuming that the running server is using the default port for TCP/IP connections. The port can be configured when the server starts and is stored in a server variable.

The following example demonstrates how to connect to a single MySQL Server using NodeSession:

var mysqlx = require('mysqlx');
mysqlx.getNodeSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (nodeSession) {
        console.log('We are connected!');
        nodeSession.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

The NodeSession example code is almost the same code used to get an XSession object, the difference is the method that is called to get the session object and the port; the code does exactly the same.

You might need to use a NodeSession in certain scenarios where you require access to SQL features that are not supported by an XSession. In a subsequent post we are going to cover some examples about how to use the NodeSession.

To work with schemas and collections we need to add some extra lines of code. The following code demonstrates how to do it.

var mysqlx = require('mysqlx');
mysqlx.getSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (session) {
     var schema = session.getSchema('test');
     var coll = schema.getCollection('myColl');

     coll.find("$._id == '1'").execute(function (myDoc) {
           return myDoc;
     }).catch(function (err) {
           console.log(err.message);
           console.log(err.stack);
     });
     session.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

In the last code example; from the session object, we call the getSchema method to get an object (schema) that represents the schema in which we want to work. Once we have the schema object we execute the method getCollection to get an object (coll) that represents the collection we want to work with. In this example, we want to retrieve the document with an id value of ‘1’ from the collection. First we call the find method passing the JSON path and value we are searching for. Then we call the execute method to perform the query. The execute method returns a promise which supplies the requested document when the method completes.

See you in the next blog post where we are going to explain more about the operations that can be performed using collections.