I am Hack Sparrow
Captain of the Internets.

Using MySQL with Node.js

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.

22 Responses to “Using MySQL with Node.js”

  1. Sandeep says:

    Unable to connect Node with mysql
    Code are folllowing
    var express =require(‘express’);
    var mysql =require(‘mysql’);
    var app =express();
    var connection =mysql.createConnection({
    host:’localhost’,
    user:’root’,
    password:”,
    database:’piwikdemo’

    });
    connection.connect(function(error){
    if(error){
    console.log(error);
    }else{
    console.log(‘Connected’);
    }
    });
    For other than ‘test’ and ‘piwikdemo’ script is showing following error
    { [Error: ER_BAD_DB_ERROR: Unknown database ‘books’]
    code: ‘ER_BAD_DB_ERROR’,
    errno: 1049,
    sqlState: ‘42000’,
    fatal: true }

    Please help

  2. Paul says:

    Hi, Sandeep!

    Based on the error output (Unknown database ‘books’) it can’t find the database named “books” and therefore the connection is failed. So make sure that the database name is correct: database:’books’ and not database:’piwikdemo’ as you listed above.

    Hope that helps!

Make a Comment