Querying MySQL with Node.js

June 30, 2012 sameer nodejs

Node.js is slowly making inroads in normal web development routines. There are a number of modules available for Node to work with almost any requirement you imagine, although the stability of many of them is open to question. Although I’ll surely not switch my development practices to Node from PHP any time soon; for many tasks Node would be a perfect match. For example for one site I use a CRON job to regularly sync remote data to MySQL. Node with its support for asynchronous requests may possibly help me make the data syncing faster.

The following post is a quick look at the MySQL module for Node. There are currently two modules for connecting with MySQL – db-mysql and node-mysql. In this post we will be using node-mysql. Unlike db-mysql, you don’t need to install specialized MySQL client software to work with node-mysql.

I’m assuming you have Node.js installed on your system. If not you can quickly get started at the Node.js site.

Installing node-mysql

The latest version is a alpha release, however despite the fact that it is tagged as ‘alpha’ this is the recommended version to use for new developments as there are many changes to the core API as and these will be carried on to the future versions. Using npm is the best way to install the module.

npm install mysql@2.0.0-alpha3

Once the alpha version becomes stable you will need to download using the normal module name, but till then use the above command.

npm install mysql

Running your first MySQL query

We will be using a WordPress database as an example to grab post titles from the ‘wp_posts’ table. The complete code is shown below.

var mysql = require('mysql');
 
var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'your-username',
      password : 'your-password',
      database : 'wordpress',
    }
);
 
connection.connect();
 
var queryString = 'SELECT * FROM wp_posts';
 
connection.query(queryString, function(err, rows, fields) {
    if (err) throw err;
 
    for (var i in rows) {
        console.log('Post Titles: ', rows[i].post_title);
    }
});
 
connection.end();

You could also pass the connection options as one single string rather than as an object.

var connection = mysql.createConnection('mysql://user:pass@host/wordpress');

The query results are returned as an array of objects with each row representing a single object in the array. So we need to iterate through the array to print all post titles. The object for a single row is shown below.

{ ID: '21',
  post_author: '1',
  post_date: Fri Feb 27 2009 03:44:07 GMT+0530 (India Standard Time),
  post_date_gmt: Fri Feb 27 2009 03:44:07 GMT+0530 (India Standard Time),
  post_content: 'sample content',
  post_title: 'web scrape',
  post_category: 0,
  post_excerpt: '',
  post_status: 'publish',
  comment_status: 'open',
  ping_status: 'open',
  post_password: '',
  post_name: 'web-scrape',
  to_ping: '',
  pinged: '',
  post_modified: Fri Feb 27 2009 03:44:07 GMT+0530 (India Standard Time),
  post_modified_gmt: Fri Feb 27 2009 03:44:07 GMT+0530 (India Standard Time),
  post_content_filtered: '',
  post_parent: '0',
  guid: 'http://localhost/wp/wordpress/?p=21',
  menu_order: 0,
  post_type: 'post',
  post_mime_type: '',
  comment_count: '0' },

Where we can access each field (post_title shown here) as below.

rows[i].post_title

The ‘query’ method of the connection object requires a callback function which will be executed whenever either one of the three events fires – error, fields, results, here denoted by the parameters err, fields and rows respectively. Here the callback is registered as a anonymous function.

The above code with the callback defined as an anonymous function will return the results as a single data stream. However if there are a large number of rows in the table and you want to process each row as it arrives rather then wating to collect all the rows you can change the code to the following.

var mysql = require('mysql');
 
var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'your-username',
      password : 'your-password',
      database : 'wordpress',
    }
);
 
connection.connect();
 
var query = connection.query('SELECT * FROM wp_posts');
 
query.on('error', function(err) {
    throw err;
});
 
query.on('fields', function(fields) {
    console.log(fields);
});
 
query.on('result', function(row) {
    console.log(row.post_title);
});
 
connection.end();

Note that each row is written to the console as it arrives. If you need to process the row for some purpose before getting the next row, you will have to pause the query and resume after some processing is done. But beware it is showing inconsistent results with some errors thrown in on my side.

query.on('result', function(row) {
    connection.pause();
    // Do some more processing on the row
    console.log(row);
    connection.resume();
});

Escaping query values

To avoid SQL injection you can escape user data before running the query. There are two methods. One using the ? operator is shown below.

.
.
connection.connect();
 
var key = '_edit_lock'; 
var queryString = 'SELECT * FROM wp_postmeta WHERE meta_key = ?';
 
connection.query(queryString, [key], function(err, rows, fields) {
    if (err) throw err;
 
    for (var i in rows) {
        console.log(rows[i]);
    }
});
 
connection.end();

The other using the connection.escape() method is given below.

.
.
connection.connect();
 
var key = '_edit_lock'; 
var queryString = 'SELECT * FROM wp_postmeta WHERE meta_key = ' + 
                   connection.escape(key);
 
connection.query(queryString, function(err, rows, fields) {
    if (err) throw err;
 
    for (var i in rows) {
        console.log(rows[i]);
    }
});

Closing of connections

Your connection to the MySQL server may close unexpectedly due to an error or you may close it explicitly. If it closes due to some error then you will need to handle that and reopen it if required. The 'close' event is fired when a connection is closed, so we need to handle that.

connection.on('close', function(err) {
  if (err) {
    // Oops! Unexpected closing of connection, lets reconnect back.
    connection = mysql.createConnection(connection.config);
  } else {
    console.log('Connection closed normally.');
  }
});

The connection.config object holds the current connections details which you can use to reconnect back to the MySQL server. Printing the object to the console returns the following.

{ host: 'localhost',
  port: 3306,
  socketPath: undefined,
  user: 'sam',
  password: 'some-pass',
  database: 'wordpress',
  insecureAuth: false,
  debug: undefined,
  typeCast: true,
  maxPacketSize: 0,
  charsetNumber: 33,
  clientFlags: 193487 }

Production usage

So will I be using Node.js for my next project? Absolutely not! The whole Node ecosytem is still standing on shifting sands – Module API’s are changing faster than I can complete a single project. So until the entire thing stabilizes and we have good support tools to make Node development easier, I’d stick to my old and trusted languages. Even then, I’d be using Node.js for specific tasks along with PHP rather than building entire sites using Node alone. But as it usually happens in tech, my last sentence may sound reserved and cautious 3 years from now when probably we will be building entire sites using Node.js.

CREDIT: http://www.codediesel.com/nodejs/querying-mysql-with-node-js/

Vision of the Machine

2/04/2016