Erick Ruiz de Chavez

Web Developer, Geek "to the bone", Happy husband.

NodeJS and MySQL

Following Ricky’s request from the comments, now I have a little demo showing how to consume data from a MySQL DB. This time I am not using express to avoid the amount of dependencies and focus on the actual test (Even there I can’t keep myself 100% focused on MySQL example cause I am a big enemy of nested callbacks).

The example is using Q to “synchronize” the initialization of MySQL connection and the HTTP server so we have both dependencies correctly initialized when we receive requests from the clients (browser). You will need to change the values to connect to your database.

Dependencies

1
2
3
var Q = require('q'),
  mysql = require('mysql'),
  http = require('http');

Q is used to provide the promises API. It aids on controlling the application flow with asynchronous methods.

MySQL is self explanatory.

HTTP is used to provide the demo an entry point to our clients. Since this is only a basic demo I am not using other frameworks. If you plan to do your production application then you must consider using a proper framework like Express.

Initialization

1
2
3
4
5
6
7
8
9
10
// Define our initial class.
var MySqlDemo = function() {
  this.initialize();
};

// Initialize: Starts all the fun.
MySqlDemo.prototype.initialize = function() {
  console.log('initialize');
  Q.all([this.startDb(), this.startServer()]).spread(this.ready, this.fail);
};

By following JavaScript’s prototyping pattern, I am creating an empty Object that will function as our Class and then providing a initialization (constructor) method. Inside initialize Q will control the flow and as soon as both startDb and startServer are resolved the control will pass to ready. If something fails, the control will go to fail.

DataBase Connection

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// Start DB: Initialize DB Connection.
MySqlDemo.prototype.startDb = function() {
  console.log('startDb');
  var deferred = Q.defer(),
      connection;

  connection = mysql.createConnection({
      host: 'host',
      user: 'user',
      password: 'password',
      database: 'database'
  });

  connection.connect(function(err) {
      if (err) {
          deferred.reject(err);
      } else {
          deferred.resolve(connection);
      }
  });

  return deferred.promise;
};

In startDb the connection to MySQL is triggered. The method will immediately return a deferred promise so any interested will be able to subscribe to either the resolve or reject result by using PromiseJS API methods like then. Promises are not strictly necessary but in my opinion are a good practice to keep the code both clean and readable.

HTTP Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// Start Server: Initialize HTTP server; this is not actually required but
// since this demo is really small I didn't need to use express or connect.
MySqlDemo.prototype.startServer = function() {
  console.log('startServer');
  var deferred = Q.defer(),
      server;

  server = http.createServer().listen(3001).on('error', function(err) {
      console.log('err', err);
      deferred.reject(err);
  }).on('listening', function() {
      console.log('listening');
      deferred.resolve(server);
  });

  return deferred.promise;
};

If you already did at least the first NodeJS code example on its page, the above example will be familiar; I can only take credit for using Promises to notify instead of a module variable.

Application Behavior

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// In case of emergency... RUN IN CIRCLES!
MySqlDemo.prototype.fail = function(err) {
  console.log('fail', err);
};

// Ready: Once bot asynchronous dependencies are ready (MySQL and HTTP)
// do something useful with them.
MySqlDemo.prototype.ready = function(db, server) {
  console.log('ready');
  var query = 'SELECT post_title FROM wp_posts WHERE post_status = "publish" ORDER BY id DESC LIMIT 5;';
  server.on('request', function(req, res) {
      db.query(query, function(err, rows, fields) {
          if (err) {
              res.send('oops!');
              return;
          }

          res.end(JSON.stringify(rows));
      });
  });
};

Now that we have our DB Connection and the HTTP server waiting for connections, let’s do something. On ready method it is defined an event listener that will receive all the requests on the server, on port 3001, it will perform a query, and finally respond to the server with the result of the query.

It’s Alive!

1
var demo = new MySqlDemo();

Last but not least, here is our demo instance that will start everything automatically.

An example of the output of this demo:

1
2
3
4
5
6
7
8
9
10
11
[{
  "post_title": "How to wait for 2+ asynchronous responses on NodeJS (CommonJS Promises)"
}, {
  "post_title": "NodeJS + OAuth + Handlebars + Socket.IO + Twitter"
}, {
  "post_title": "Auto reload NodeJS application after code update"
}, {
  "post_title": "Now on EC2!"
}, {
  "post_title": "Chat application with Node.js and Socket.io"
}]

You can get the full source code from this Git Repository. Once you have it, do not forget to do “npm install” on the project folder to grab all the node dependencies.

Leave your questions and comments and I’ll be more than happy to troll help you :D !

Comments