menu

Node.js MySQL Tutorial

Node.js and MySQL is one of the necessary binding needed for any web application. MySQL is one of the most popular open source database in world and efficient as well. Almost every popular programming language like Java or PHP provides driver to access and perform operations with MySQL.

In this tutorial i am trying to cover code for learning and code for production. So if you know this already and looking for ready made code for production. Click here to jump there directly.

Introduction:

Node.js is rich with number of popular packages registered at package registry called NPM. Most of them are not so reliable to use for production but there are some on which we can rely upon. For MySQL there is one popular driver called node-mysql.

In this tutorial, I am going to cover the following points related to Node.js and MySQL.

  • Sample code to get started.
  • Code for Production.
  • Testing concurrent users.

If you are new to Node and Express then you won’t regret taking our Node course. Its FREE!

Sample code to get started.

Project directory:

---node_modules
-----+ mysql
-----+ express
---index.js
---package.json
package.json
{
  "name": "node-mysql",
  "version": "0.0.1",
  "dependencies": {
    "express": "^4.10.6",
    "mysql": "^2.5.4"
  }
}

Install dependencies using

npm install

Here is sample code which connects to Database and perform SQL query.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : '< MySQL username >',
  password : '< MySQL password >',
  database : '<your database name>'
});

connection.connect();

connection.query('SELECT * from < table name >', function(err, rows, fields) {
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
});

connection.end();

Make sure you have started MySQL on default port and changed the parameter in above code then run this code using

node file_name.js

Code for production :

Above code is just for learning purpose and not for production payload. In production scenario is different, there may be thousands of concurrent users which turns into tons of MySQL queries. Above code won’t run for concurrent users and here is a proof. Let’s modify our code little bit and add Express routes in that, here it is.

test.js ( Change database settings in code )
var express    = require("express");
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'address_book'
});
var app = express();

connection.connect(function(err){
if(!err) {
    console.log("Database is connected ... nn");    
} else {
    console.log("Error connecting database ... nn");    
}
});

app.get("/",function(req,res){
connection.query('SELECT * from user LIMIT 2', function(err, rows, fields) {
connection.end();
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
  });
});

app.listen(3000);

Install siege in your system. I use this command to install it in Ubuntu.

apt-get install siege

then run our node and server and following command.

node test.js
siege -c10 -t1M http://localhost:3000

Assuming you are running Node server on Port 3000.
Here is the output.
Node and mysql
In above code, we are allowing it to run for standalone connection i.e one connection at a time but reality is bit different. You may get 100 or 1000 connection at one particular time and if your server is not powerful enough to serve those request then at least it should put them in queue.

Pool connection in MySQL :

Connection Pooling is mechanism to maintain cache of database connection so that connection can be reused after releasing it. In Node mysql, we can use pooling directly to handle multiple connection and reuse the connection. Let’s write same code with pooling and check whether it can handle multiple connection or not.

test.js
var express   =    require("express");
var mysql     =    require('mysql');
var app       =    express();

var pool      =    mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'address_book',
    debug    :  false
});

function handle_database(req,res) {
   
    pool.getConnection(function(err,connection){
        if (err) {
          res.json({"code" : 100, "status" : "Error in connection database"});
          return;
        }  

        console.log('connected as id ' + connection.threadId);
       
        connection.query("select * from user",function(err,rows){
            connection.release();
            if(!err) {
                res.json(rows);
            }          
        });

        connection.on('error', function(err) {      
              res.json({"code" : 100, "status" : "Error in connection database"});
              return;    
        });
  });
}

app.get("/",function(req,res){-
        handle_database(req,res);
});

app.listen(3000);

Run the app using

node test.js

and fire 10 concurrent users for 1 minute using siege by using this command.

siege -c10 -t1M http://localhost:3000

Here is output.
Code is stable !

**UPDATE**

You can directly use pool.query() which internally will acquire connection and release it when query is executed. In my personal code review experience, majority of the developers often forget to release the acquired connection which in turns creates bottleneck and database load.

Refer the code snippet below:

test.js
var express   =    require("express");
var mysql     =    require('mysql');
var app       =    express();

var pool      =    mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'address_book',
    debug    :  false
});

function handle_database(req,res) {
       // connection will be acquired automatically
       pool.query("select * from user",function(err,rows){
        if(err) {
            return res.json({'error': true, 'message': 'Error occurred'+err});
        }
                //connection will be released as well.
                res.json(rows);
       });
}

app.get("/",function(req,res){-
        handle_database(req,res);
});

app.listen(3000);

I have used this function in a production environment with heavy payload and it works like charm.

Executing Queries

Let’s learn how to execute queries using Node.js.

Inserting Rows into Table

Here is the code to add new rows in the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// add rows in the table

function addRow(data) {
    let insertQuery = 'INSERT INTO ?? (??,??) VALUES (?,?)';
    let query = mysql.format(insertQuery,["todo","user","notes",data.user,data.value]);
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows added
        console.log(response.insertId);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    addRow({
        "user": "Shahid",
        "value": "Just adding a note"
    });
},5000);

The mysql.format function will perform the query escape.

Querying data in Table

Here is the code to query rows in the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// query rows in the table

function queryRow(userName) {
    let selectQuery = 'SELECT * FROM ?? WHERE ?? = ?';    
    let query = mysql.format(selectQuery,["todo","user", userName]);
    // query = SELECT * FROM `todo` where `user` = 'shahid'
    pool.query(query,(err, data) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows fetch
        console.log(data);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // select rows
    queryRow('shahid');
},5000);

If you would like to add multiple rows in the single query, you can pass an array in the values. Like this.

    let insertQuery = 'INSERT INTO ?? (??,??) VALUES (?,?)';
    let values = [["shahid","hello"],["Rohit","Hi"]]; // each array is one row
    let query = mysql.format(insertQuery,["todo","user","notes",values]);

Updating data in Table

Here is the code to update data in the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// update rows

function updateRow(data) {
    let updateQuery = "UPDATE ?? SET ?? = ? WHERE ?? = ?";
    let query = mysql.format(updateQuery,["todo","notes",data.value,"user",data.user]);
    // query = UPDATE `todo` SET `notes`='Hello' WHERE `name`='shahid'
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows updated
        console.log(response.affectedRows);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // update row
    updateRow({
        "user": "Shahid",
        "value": "Just updating a note"
    });
},5000);

Deleting Rows in the table

Here is the code to delete a row from the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

function deleteRow(userName) {
    let deleteQuery = "DELETE from ?? where ?? = ?";
    let query = mysql.format(deleteQuery, ["todo", "user", userName]);
    // query = DELETE from `todo` where `user`='shahid';
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows deleted
        console.log(response.affectedRows);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // delete row
    deleteRow('shahid');
},5000);

Calling MySQL Stored Procedure Using Nodejs

You can also call a stored procedure using Node.js. If you don’t have stored procedure created in MySQL, you can refer to the code below to do the same.

DELIMITER $$
 
CREATE PROCEDURE `getAllTodo`()
BEGIN
    SELECT * FROM todo;
END$$
 
DELIMITER ;

Here is the code to call the stored procedure from the code.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

function callSP(spName) {
    let spQuery = 'CALL ??';
    let query = mysql.format(spQuery,[spName]);
    // CALL `getAllTodo`
    pool.query(query,(err, result) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows from SP
        console.log(result);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // call sp
    callSP('getAllTodo')
},5000);

Final comments :

Siege is a really powerful tool for the testing server under pressure. We have created 100 connection limit in code, so you might be wondering that after 100 concurrent connection code will break. Well, let me answer it via code. Fire 1000 concurrent user for 1 minute and let’s see how our code reacts.

siege -c1000 -t1M http://localhost:3000

If your MySQL server is configured to handle such traffic at one socket then it will run and our code will manage the scheduling of concurrent connection. It will serve 100 connection time but rest 900 will be in the queue. So the code will not break.

Conclusion :

MySQL is one of a widely used database engine in the world and with Node it really works very well. Node-MySQL pooling and event-based debugging are really powerful and easy to code.

Further reading: