Connecting Nodejs Application to MySQL

Pre-requisites: To proceed with this blog, we are going to use one of my previous articles, only for the sake of the structure,
Getting started: We are required to connect to MySQL. Hence, I have a sample database already created in it with one table: users in it. The structure and the data of the table is given below:

Table Structure
 
Structure

Sample Data

Data

Installing Package

Let’s now install MySQL Package in our Application. Open cmd in an administration mode and reach the folder directory of your project, enter command npm, install MySQL to install the MySQL package in your Application.

command
 
command
 
Connect DB

Now, let’s create a folder “config” in the project, add a file database.js file in our project and add the configuration to our db to connect it. 
  1. var mysql = require('mysql');  
  2. //  Read more about mysql connection here : https://www.npmjs.com/package/mysql  
  3.   
  4. var connection = mysql.createConnection({  
  5.           
  6.         host:'localhost',  
  7.         user:'root',  
  8.     password:'123456',  
  9.     database:'test'  
  10.     });  
  11.   
  12.      exports.getConnected = function(){  
  13.         return connection;  
  14.           
  15.     };  
This code will connect to our local DB.The exports keyword here makes the function getConnected to be called in the other file. 

Including database.js

Include this database.js file in app.js. This is nothing more than including this file in the project. Please note that app.js is the first file to be executed in the project, including any file into it will include it in the whole Application.

  1. var dbConnect  = require('./config/database');  
  2. var con = dbConnect.getConnected();   
Query We will now go ahead and create a DB query to call the data from the database.   
  1. con.query('SELECT * FROM USERS',function(err,rows){  
  2.      if(err)  
  3.      console.log("Error has occured: "+ err);  
  4.      else  
  5.      console.log("Data: "+ JSON.stringify(rows));  
  6.    })  
The resultant output is displayed in the debug console of the Application, 
 application
 
I hope this article provides good information regarding connecting Nodejs to MySQL db.