I am Hack Sparrow
Captain of the Internets.

Using MySQL with Node.js

Posted on October 17th, 2011 under Database, Node.js
Tags: , ,

How to use MySQL with Node.js


New to Node.js and wondering if you can use your old friend MySQL with it? Good news - it is surprisingly very easy to get MySQL working with Node.js. This article guides you through the process.

First of all, you'll need to install an aptly named Node.js module called mysql.

$ npm install mysql

The mysql module is entirely written in Node.js, and implements an async MySQL client. Mad respect to the module author(s). The GitHub page of the project is located here, refer to it for other details about the API.

Once you have installed the module, you can include it in your Node.js files and configure it this way.

var _mysql = require('mysql');

var HOST = 'localhost';
var PORT = 3306;
var MYSQL_USER = 'nodehacker';
var MYSQL_PASS = 'lulwut';
var DATABASE = 'nodedb';
var TABLE = 'gadgets';

var mysql = _mysql.createClient({
    host: HOST,
    port: PORT,
    user: MYSQL_USER,
    password: MYSQL_PASS,
});

mysql.query('use ' + DATABASE);

Now you are ready to execute MySQL commands on the database "nodedb". Let's try out an example.

mysql.query('select id, name, price from ' + TABLE + ' where price < 100',
function(err, result, fields) {
    if (err) throw err;
    else {
        console.log('Gadgets which costs less than $100');
        console.log('----------------------------------');
        for (var i in result) {
            var gadget = result[i];
            console.log(gadget.name +': '+ gadget.price);
        }
    }
});

You pass two parameters to the mysql.query method - i. the MySQL query, ii. the callback function. Notice how the result is an array of objects. Super simple isn't it?'

In the next example, you add a new item to the "gadgets" table, the data for which comes from the POST form values. Once insertion is completed you send back the result "success". This looks like something you might do from an AJAX request.

mysql.query('insert into '+ TABLE +' (name, price) values ("' + req.body.name + '", "' + req.body.name.price + '")',
function selectCb(err, results, fields) {
    if (err) throw err;
    else res.send('success');
});

Following? It's that easy!

To get the last insert ID, you use the insertId property of the insert result object. 

client.query('INSERT INTO my_table SET title = "Node and MySQL"', function(err, info) {
    console.log(info.insertId);
});

So that's all you need to know to use MySQL with Node.js. There are other MySQL modules for Node.js, but mysql Node module has been the best from my experience for my requirements, which is not likely to be very different from yours.

7 Responses to “Using MySQL with Node.js”

  1. Thanks on January 14th, 2012 at 3:57 pm

    Thanks for this! Google is surprising unfruitful responding to “nodejs mysql.”

    Can you post an example on how to retrieve the user’s IP?

  2. Captain on January 14th, 2012 at 11:58 pm

    The users’s IP address is available at the variable req.connection.remoteAddress, where req is the request object.

  3. tyler on January 24th, 2012 at 11:50 am

    how do you set up mysql with the user name and password before attempting to connect with it?

  4. naqib on March 21st, 2012 at 5:48 am

    Very good, Can you please do something on post/get form and how to include css in node.js for begginers.

    thanks

  5. Captain on March 21st, 2012 at 9:01 am

    @naqib, if you browse through my website you will find a lot of Node.js stuff for beginners, including GET and POST – http://www.hacksparrow.com/post-get-request-handling-in-node-js-express.html.

  6. Baylock on April 18th, 2012 at 5:15 pm

    Hi there,

    I use Php/MySQL along with PHPMyAdmin for quite a long time but that node.js thing is very new for me.

    As an absolute beginner, I follow with a lot of interest your articles regarding node.js and I must say that your blog helps me a lot.

    This said, I have to admit that this post is, in my opinion, poorly written.

    You develop things as if we only have to follow step by step but it just doesn’t work.

    Ok, Mysql installed and then what?
    How do you configure it?
    How do you start and stop it?

    How do you set the name and price columns?

    How come you begin with a SELECT, saying “now you are ready to execute MySQL commands on the database nodedb”, when this database is still empty?

    Chances are that the mysql port and the node.js server don’t share the same port by default (which brings an”Error: connect ECONNREFUSED”). How do we manage that?

    Maybe it is obvious to you and maybe it is obvious to anyone but me (except Tyler maybe) but as far as I’m concerned, this is not the best article I read from you.

    node-mysql is poorly documented over the internet and my questions are still unanswered.
    I did “npm install mysql” alright. Now I’m stuck with an “econnrefused” message and no idea how to configure this damn mysql before connecting to it.

    Maybe I’m way to far behind to get your article but, as far as I know, I got the other ones quite easily.

    Thank you for your effort though.

  7. Captain on April 18th, 2012 at 9:48 pm

    Thanks a million for the feedback, Baylock. I always try to approach my tutorials with the beginner’s mind. I agree, I missed on this one. I have updated the post to include HOST and PORT information. Hope it works out for all of you now, else ping me!

Make a Comment