Appcelerator Blog

The Leading Resource for All Things Mobile

Creating a MySQL-powered Web Service using Node.ACS

5 Flares 5 Flares ×

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: http://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.

For this example, I used “nodesqldashboard” for the service name. The Node.ACS Service will be created in an “acs” folder.

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.

For this example, I used “getData” for the method name. This will create a method, named “getData” in a new services.js controller. It will also update the routes section in config.json.

Services.js

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.

For this example, a bindings file called ti.cloud.nodesqldashboard.js is created in the resources folder. It will also add the following to the index.js file (if you select index.js in the dialogue box):

index.js

// @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">http://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.

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 providing 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.

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

The following screen shot demonstrates a sample flash sales report mobile application that gets its data from a MySQL server using the method described in this post.

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 totals calculation.

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 to 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.

5 Flares Twitter 0 Facebook 0 Google+ 5 LinkedIn 0 Email -- 5 Flares ×

6 Comments

  1. Bert Grantges

    Great post Leor! This will be very helpful to get our users connected to backend datasources

  2. Roy

    Good post, there is an option to use this service with pure js ?

  3. Leor Brenman

    Thanks Roy. Node.ACS services are exposed as Restful API and hence can be used with pure js.

    Node.ACS Getting Started Guide can be found here:

    http://docs.appcelerator.com/cloud/latest/#!/guide/node

    Some other useful Node.ACS posts can be found here:

    http://developer.appcelerator.com/blog/2013/05/appcelerator-node-acs-financial-stock-watch-list.html

    http://developer.appcelerator.com/blog/2013/05/building-a-custom-front-end-to-acs-using-node-acs-part-1.html

  4. Felix Tjernberg

    Hey there I started following the steps for your guide, tho I’m on an windows machine. And I can’t seem to get mysql npm installed and the folder setup.
    Do you know how to do this on windows as $ npm install felixge/node-mysql does not work. all I get back is command not found in the terminal.

  5. Great post and thanks. It helped us greatly in our adoption of node.js on Appcelerator.

    Just one question, on the mobile project, where it says

    nodesqldashboard.services_getData({}, function(r,e)….

    should it be “function(e, r)” instead?

    Also, we saw from InvokeService, it always returns “cb(null, r)”, making e=null and e.error=undefined all the time. Is that a correct observation?

  6. Erik

    This post is sadly out of date now, not lining up with anything in the current studio menus. Makes it frustrating to get started with. Please someone, update the examples!

Comments are closed.

Sign up for updates!

Become a mobile leader. Take the first step to scale mobile innovation throughout your enterprise.
Get in touch
computer and tablet showing Appcelerator software
Start free, grow from there.
5 Flares Twitter 0 Facebook 0 Google+ 5 LinkedIn 0 Email -- 5 Flares ×