There are various advantages of using MySQL with Nodejs App instead of using MongoDB or NoSQL, The first advantages is that MySQL is widely used Database management system and easy to use commands as well. I am sure there is many other advantages of NoSQL over MySQL but still MySQL is great choice to develop software application.

NodeJs with MySQL Tutorials (2).jpg

This Blog post is part of the Youtube Nodejs Tutorial Series where i post videos on Nodejs with MYSQL and Express where pug is used as template engine.

This post Contain exact code that i have written in the fourth part of the Nodejs with MySQL tutorial series.

In This part of the series we are going to connect our nodejs app with mysql, if don't know how to setup nodejs or Database server in your system , you can check part 1 and part 2 of this series.

If you want to follow along with me on the video , you have to create a node app and a simple node server to create our app using express. I have done this in at Part-3 of tutorial series. Please check out those videos before reading further in the

** Start MySQL Server Using XAMPP **

1. Setup MySQL Connection in NodeJs App

To be able to create a mysql connection first thing we need to is import mysql package in our node app. We can do this
by using require function at the top of our application

var mysql = require('mysql');

We are going to use createConnection() method to initialized mysql database information using which we can connect to the database server.

createConnection() method takes array of arguments mainly Host, User, Password, Port and Database these fields name describe themselves. The Port is the port number of mysql server.


var  db = mysql.createConnection({
     host:'localhost', //host name of database
     user:'root', // username of database
     password: '', // database password
     port:3306, // mysql port number
     // database:''; // database name >>  we are going to create database later in the post

});

Now lets Connect to mysql database server using above given information. Using CONNECT() methods provided my MYSQL node module.

db.connect(function(err){
   if(err) throw err;
   console.log('Database Connected');
});

2 . Create New Database in MySQL Using Nodejs routes

Routes are the url using which we can navigates in our node app. For example localhost:5000/home is a routes given to home page, just like that we are going to create a database when we visit the routes localhost:5000/createDB in browser.

The Query() method is used to performs queries to the database using nodejs app. The following given code create a database named as testNode when the get request is made to the given url.

app.get('/createdb',function(req,res){

    let sql = 'CREATE DATABASE testNode';

    db.query(sql,function(err,results,fields){
            if(err) throw err;

            res.send('New Database is Created.');
    });

});

Now we have created new Database, We need to specify database name in our createConnection() method. The Final createConnection method looks like this:

var  db = mysql.createConnection({
     host:'localhost', //host name of database
     user:'root', // username of database
     password: '', // database password
     port:3306, // mysql port number
     database:'testNode'; // database name 

});

3. Create Table inside the Database Using Express Routes

Similarly, We can Create Database Table using routes from node app, in given code we are going to create a database table named as sample which contain attributes id name phone.

app.get('/createtable',function(req,res){

    let sql = 'CREATE TABLE sample(id int primary key, name varchar(20), phone varchar(10))';


    db.query(sql,function(err){
            if(err) throw err;
            res.send('New Table is created.')
    });
});