Querying MySQL with Node.js


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.

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

4 Responses

1

Adding colors to your Node.js console

July 10th, 2012 at 12:59 am

[...] easily differentiate the output of one log from another. Take for example the last post about MySQL and Node.js. We logged all the post titles to the console with the following code. This printed the output [...]

2

oprs

April 21st, 2014 at 7:05 am

This is a nice round-up article. Too bad it’s undated though, we’ll never know when “3 years from now” is.

3

henock bongi

May 27th, 2014 at 7:36 am

hi, please i want your help connection.query() doesn’t work please help me

4

Node js connect mysql | Tech Geeks

May 28th, 2014 at 11:24 pm

Your thoughts