MySQL • 1 year ago

How To Use MySQL with NodeJS?

It's very easy to use MySQL with Node.js.The following post is a quick look at the MySQL module for Node. There are currently two modules in node.js for connecting with MySQL : db-mysql and node-mysql. In this post we will be using node-mysql. This post guides you through the process.

I’m assuming you have Node.js installed on your system.If not, then this post explains you How to Install node.js on windows system.

 

Installation of node-mysql

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

npm install mysql

In order to use the mysql client in your application javascript file app.js, add the following code to import the module to your script.

var mysql = require('mysql');

 

Creating MySQL Connection

Now the next step is to create a MySQL connection in which we'll define the host,username,password and database. To know more about the connection options to read this.

var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'root',
      password : '123456',
      database : 'nodedemo'
    }
);

the below code will open a new connection.

 connection.connect();

 

MySQL Query

Below is an example of how to perform query in mysql with node.js. To protect the query string from the sql injection use connection.escape().

var queryString = 'SELECT * FROM users';
connection.query(queryString, function(err, rows, fields) {
    if(err){
       throw err;
    }
   else{
      console.log( rows );
    }
});

you can print all the fields also via loop.

connection.query(queryString, function(err, rows, fields) {
    if (err) throw err;
    for (var i in rows) {
     console.log('Username: ', rows[i].username);
    }
});

 

Closing connection

In order to close the connection use the below method.

connection.end();

You can use a callback function also.

connection.end(function(err){
// Actions need to be performed after the connection is terminated.
});

 

Connection Pooling in MySQL with Node.js

In case of websites with high traffic running on Node.js,it is recommended that instead of creating a new connection for each nodejs instance, you use persistent connections to the database. By using connection pooling, efficient management of multiple connections can be achieved by limiting them and reusing them with different schedules. Below is an example of how to create a connection pool.

var mysql = require('mysql');
var pool  = mysql.createPool({
   host     : 'localhost',
   user     : 'root',
   password : '123456'
 });

Now you can get the connection from the created pool as below,

pool.getConnection(function(err, connection){
});

In order to query the database, just use the parameter connection in the callback function of getConnection.

pool.getConnection(function(err, connection){
  connection.query("select * from table",  function(err, rows){
   if(err) {
    throw err;
   }else{
    console.log( rows );
   }
  });
  
  connection.release();
});

At last use connection.release() to release the connection and the connection will return to the pool, ready to be used again.
 

Complete Example:

var mysql = require('mysql');
var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'root',
      password : '123456',
      database : 'nodedemo'
    }
);

connection.connect();
var queryString = 'SELECT * FROM users';
connection.query(queryString, function(err, rows, fields) {
    if(err){
        throw err;
    }
    else{
       console.log( rows );
    }
});

When running the above code, following output will be displayed.
 

[
   {
      "id":1,
      "username":"kevin",
      "email":"kevin@example.com",
      "password":"123456"
   },
   {
      "id":2,
      "username":"john",
      "email":"john@example.com",
      "password":"456789"
   }
]

That's it.!! This is the basic example of how to use MySQL with Node.js.

Categories

Tags