Creating a MySQL-powered Web Service using Node.ACS

Often Enterprises need to create mobile applications, such as a Sales Flash Report or Executive Dashboard application that integrate with data that resides in a SQL database. There are several means of achieving this integration, including client side libraries for accessing the SQL database or implementing a PHP server to expose web services to access the SQL database through http post commands.
Appcelerator Node.ACS provides an optimal cross device solution without the need for IT to implement and manage server infrastructure or to find and leverage multiple client side libraries. By leveraging Node.ACS and a node packaged module (npm) a mobile optimized middle tier can be implemented easily. In this post, we’ll walk through an example of setting up a Node.ACS service for integrating with a MySQL database using the mysql npm at https://npmjs.org/package/mysql.
Implementing the integration in the middle tier has the advantage of providing an integration facade so that the back end can change without the need to modify the mobile client application. For example, if the back end database or database schema changes, you only need to modify the Node.ACS service. The mobile client application does not need to be modified or re-deployed.
This post will walk you through the steps of using Studio to add a Node.ACS service to your Titanium project, install an npm, build an integration service to connect to MySQL and publish the Node.ACS service. The steps outlined are based on the “Integrated Projects” Node.ACS Guide at: https://docs.appcelerator.com/cloud/latest/#!/guide/node_studio_integrated

Create a Node.ACS Service

1. Select your project in the App Explorer or Project Explorer view.
2. Right-click on the project and select New > Node.ACS Service.
3. Give the service a name.

Add a Node.ACS Method

1. Right-click on the Node.ACS Service (e.g. acs/nodesqldashboard) and select New > Node.ACS Method.
2. Give the method a name.

function getData2(req, res) {
    res.send('Hello, world!');
};

config.json

{
    "routes": [ {
        "path": "/",
        "callback": "application#index"
    }, {
        "path": "/getData",
        "method": "get",
        "callback": "services#getData"
    } ],
    "filters": [ {
        "path": "/",
        "callback": ""
    } ],
    "websockets": [ {
        "event": "",
        "callback": ""
    } ]
}

Generate a Bindings File for your Service

1. Select your project in the App Explorer or Project Explorer view.
2. Right-click on the project and select Node.ACS > Import Node.ACS Bindings
3. Select a project from the list of available Node.ACS projects (e.g. nodesqldashboard).
4. Select the mobile client controller file to add service reference to (e.g. index.js).
5. Click OK.

// @autogenerated:start:ti.cloud.nodesqldashboard
var nodesqldashboard = require("ti.cloud.nodesqldashboard");
// @autogenerated:end:ti.cloud.nodesqldashboard

Studio also injects a base URL key in the tiapp.xml file as follows:

<property name="acs-service-baseurl-nodesqldashboard">https://localhost:61157</property>

Install the mysql npm

Install the mysql npm locally in the Node.ACS project on your computer as follows:

1. Right-click on the Node.ACS service (e.g. acs/ nodesqldashboard)
2. Select Show In -> Terminal
3. Use the following command: $ npm install felixge/node-mysql

Note that you may need to precede the command with sudo depending on your privilege.
This will install the mysql npm (https://npmjs.org/package/mysql) in a sub folder “node_modules/mysql” in the Node.ACS service on your machine. Refer to the npm link above for documentation on how to use this module.
This will create a subfolder node_modules/mysql with the mysql module.

Modify the getData Method to Integrate with MySQL

At this point, we are ready to add the necessary code to the getData method in the service.js controller file created above:

1. Require mysql
2. Instantiate a mysql connection
3. Execute a query
4. Return the data in the res object

In the example shown below a SELECT all query is being made to the table “table1” which will return all rows and all columns of table1 of the MySQL database sql311299 at sql3.freemysqlhosting.net.

function getData(req, res) {
    var mysql      = require('mysql');
    var connection = mysql.createConnection({
      host     : 'sql3.freemysqlhosting.net',
      user     : 'sql311299',
      password : '',
      database : 'sql311299',
    });
    connection.connect();
    connection.query('SELECT * FROM table1', function(err, results) {
      if (err) {
        console.log("getData - MySQL SELECT error = "+err);
      } else {
        res.set('Content-Type', 'application/json');
        res.send(JSON.stringify(results));
        res.end();
      };
    });
    connection.end();
}

Connect Your Client Application to Your Node.ACS Service

1. Require in the bindings (e.g. var nodesqldashboard = require(“ti.cloud.nodesqldashboard”);)
2. Call the exported function (e.g. getData())

The following code sample illustrates calling the getData() function:

nodesqldashboard.services_getData({}, function(r,e){
    if(!e.success){
        Ti.API.info("Error: "+e.error);
        return;
        }
    Ti.API.info("Success, r = "+JSON.stringify(r));
    // populateData(r);
    });

Run the Node.ACS Service Locally

1. Click the Run button and select Local Node.ACS Server to start your service.In the console you will see the following. Note the port number as the port number may be different for you:

   [INFO]  No dependencies detected
   [INFO]  socket.io started
   [INFO]  ACS started on port 52295
   

2. Open your tiapp.xml file.
3. Locate the acs-service-baseurl- property tag.
4. Change port number to the port number recorded above.
5. Save and close the file.
6.  

Launch your mobile client in a simulator and test the method call.

Publish Your Node.ACS Service

1. Click the Publish button and select Publish Node.ACS Service.
2. When your service is published, a dialog appears to provide you with the URL to access the service.
3. Before deploying your app, modify your tiapp.xml file to point to the published URL as described above.

Mobile Optimization

One main advantage of implementing the integration in the middle tier Node.ACS server is that you can implement processing and mobile optimization of data on a powerful server instead of performing this on the mobile device and sacrificing user experience. For example, if you need to display totals for your data, and this data is not contained in your database, you will need to loop through your row set to perform the calculation. It is most efficient to perform this calculation on a high-speed server than on a mobile device. The following code sample shows the modified Node.ACS service with the calculation of the total.

function getData(req, res) {
    var mysql      = require('mysql');
    var connection = mysql.createConnection({
      host     : 'sql3.freemysqlhosting.net',
      user     : 'sql311299',
      password : '',
      database : 'sql311299',
    });
    connection.connect();
    connection.query('SELECT * FROM table1', function(err, results) {
      if (err) {
        console.log("getData - MySQL SELECT error = "+err);
      } else {
        var reply = {};
        var rLen = results.length;
        var MTDTotalVal = 0;
        var YTDTotalVal = 0;
        if(rLen>0){
            for(var i=0;i<rLen;i++){
                MTDTotalVal += results[i].mtd;
                YTDTotalVal += results[i].ytd;
            }
        }
        reply = {data: results, MTDTotal: MTDTotalVal, YTDTotal: YTDTotalVal};
        res.set('Content-Type', 'application/json');
        res.send(JSON.stringify(reply));
        res.end();    };
    });
    connection.end();
}

In this post, we saw how Node.ACS can be leveraged to implement a middle-tier service that integrates into a SQL Database and performs mobile optimization, which results in improved user experience. Furthermore, by implementing the integration in a middle tier, the back end data source can change without the need to modify and re-deploy the mobile client application.