It’s very easy to use MySQL with Node.js. This tutorial will explain how to connect the MySQL server database using Node.js. MySQL is the most popular database system used with other programming languages like PHP,Java etc. MySQL is ideal for both small and large applications. Which is developed distributed, and supported by Oracle Corporation. MySQL is very fast, reliable, free to download and use.

connect-mysql-database-using-node.js

Connect MySQL Database using Node.js

First, you need to install mysql driver with the help of node package manager. Use the following command on the terminal or node shell.

$ mkdir node-mysql-test
$ cd node-mysql-test
$ npm install mysql

Database Configuration

For this tutorial we will be using a database called ‘mysql-demo’ and a table called todos. Here is a dump of the database, so that you can get up and running quickly,

CREATE TABLE  `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO tasks (id, title, description) VALUES
(1, 'vetbossel', 'Website name'),
(2, 'vetri', 'This is vetripandi'),
(3, 'boss', 'This is boss')

Get strated with Code

Let’s create a file called app.js and import MySQL driver into that page. You can establish connection to mysql in two different methods, first on is basic connection method which is suitable for development and testing, second one is suitable for production which uses connection pooling.

var mysql      = require('mysql');
var connection = mysql.createConnection({
   host     : 'localhost',
   user     : 'root',
   password : '',
   database : 'your-database-name'
});
 
connection.connect(function(err){
    if(err) {
        console.log("Error connecting database ... \n\n");
     } else {
       console.log("Database is connected ... \n\n");      
     }
});

Pooled connection

Connection pooling is a technique to maintain cache of database connection so that connection can be reused after releasing it. Basic version may not work for the concurrent users that is why pooling connection is preferred which can handle thousands of concurrent users.

var mysql = require('mysql');
 
var pool  = mysql.createPool({
   host     : 'localhost',
   user     : 'root',
   password : '',
   database : 'your-database-name'
});
 
pool.getConnection(function(err, connection) {
    // Use the connection
    connection.query( 'SELECT * FROM todos', function(err, rows) {
       if(err) throw err;
       console.log('Data received from Db:\n');
       console.log(rows);
       connection.release(); // return the connection to the pool.
    });
});
 
pool.end(function (err) {
    // all connections in the pool have ended
});

Run the following command in shell

node app.js

Execute the Queries

As shown above, once the connection is established we will use the connection variable to execute a query against the database table.

//For reading data from table
connection.query('SELECT * FROM todos',function(err,rows){
  if(err) throw err;
  console.log('Data received from Db:\n');
  console.log(rows);
});
 
//For creating a new row in table
var todo = { title: 'Delete Comments', description: 'Delete all the spam comments from websites' };
connection.query('INSERT INTO todos SET ?', todo, function(err,res){
  if(err) throw err;
  console.log('Last insert ID:', res.insertId);
});
 
//For updating data in the table
connection.query('UPDATE todos SET title = ? Where ID = ?',["Delete spam comments", 1],function (err, result) {
    if (err) throw err;
    console.log('Changed ' + result.changedRows + ' rows');
  }
);
 
//For deleting a row
connection.query('DELETE FROM todos WHERE id = ?',[1], function (err, result) {
    if (err) throw err;
    console.log('Deleted ' + result.affectedRows + ' rows');
  }
);

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods. If you build your likes using wildcards like we did above you don’t have to use escape methods MySQL driver module will take of it.