How to Install and Set-up MySQL with NodeJS in Ubuntu

In this Post we are going to see How to Install and Set-up MySQL with NodeJS in Ubuntu and creating connection of NodeJS with MySQL database using express framework. As we know NodeJS has done dramatic increase in adoption in the back-end side since it allows JavaScript uses in back-end as well as in front-end and MySQL in NodeJS is used to Fetch and post data from database.

Let’s get started now.

Installation:

>>sudo apt-get update
>>sudo apt-get install mysql-server

Run these commands in your terminal, now during installation You will be asked to set up root password. Make sure to remember the password or make note of it because you will be using every time you enter the database.

now You can check MySQL version

>>mysql --version
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Now the Installation part is done. now Let’s Start the MySQL Services

>>service mysql status

This command will show us the status of the MySQL and it will also Starts the services of the database.
If in any case this gives unknown error use this:

>>sudo service mysql start

Login into MySQL

Now login into MySQL cli with the following command:

mysql -u root -p

this will ask for the root password which we set during the time of installation.
enter the correct password and now you are logged in into the MySQL database.

Creating a Database

Creating a database is so easy as working in MySQL just run this command to create a Database in MySQL.

mysql> create database DbName;

Here “DbName” is the name of the database you can give your database any name you want.
If you want to confirm the existence of your database, you can just run the following command:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| DbName             | 
| performance_schema | 
| recorder           | 
| sys                | 
+--------------------+ 
6 rows in set (0.00 sec) 

Now for using a particular MySQL database

mysql> use DbName;

Now exit MySQL cli using “exit” command.

Now we are gonna work in NodeJs for adding MySQL in it. To learn more about NodeJS go to our previous post Integrating NodeJS server into an Ionic Application.

Installing Dependencies

Will going to use following node modules for handling session
Express
Body parser
MySQL

>>npm install express body-parser mysql

Now we have created a package.json file that will install all required dependencies. have a look,

package.json

{
 "name": "Adding-Mysql ",
 "version": "0.0.1",
 "main": "server.js",
 "dependencies": {
 "express": "^4.16.2",
 "mysql": "^2.15.0",
 "body-parser": "^1.18.2",
 }
 }

By typing following command you can install dependencies,

>>npm install

Initialization & Require

First will include require node modules and http server.
Also we have done connection of MySQL.

server.js

var app = require('express')(); // Express App include
var http = require('http').Server(app); // http server
var mysql = require('mysql'); // MySQL include
var bodyParser = require("body-parser"); // Body parser for fetch posted data
var connection = mysql.createConnection({ // MySQL Connection
 host : 'localhost',
 user : 'root',
 password : '',
 database : 'DbName',
 });
var app = express();
 
 connection.connect(function(err){
 if(!err) {
     console.log("Database is connected ... \n\n");  
 } else {
     console.log("Error connecting database ... \n\n");  
 }
 });
app.listen(3000);

Replace password value with MySQL root user password and DbName with database name you created earlier.

Now run the server with the following command

>>node server.js

If you get the log as Database is connected in terminal logs then your NodeJS setup with MySQL is complete.

Let’s see some GET and POST server requests with MySQL connection, so we take a book database as an example here.

GET

app.get('/book',function(req,res){
    var data = {
        "error":1,
        "Books":""
    };
    
    connection.query("SELECT * from book",function(err, rows, fields){
        if(rows.length != 0){
            data["error"] = 0;
            data["Books"] = rows;
            res.json(data);
        }else{
            data["Books"] = 'No books Found..';
            res.json(data);
        }
    });
});

here connection.query(“Query”,function)” will used for writing query and in callback will get required data as you can see above code.

POST

app.post('/book',function(req,res){
 var Bookname = req.body.bookname;
 var Authorname = req.body.authorname;
 var Price = req.body.price;
 var data = {
 "error":1,
 "Books":""
 };
 if(!!Bookname && !!Authorname && !!Price){
 connection.query("INSERT INTO book VALUES('',?,?,?)",[Bookname,Authorname,Price],function(err, rows, fields){
 if(!!err){
 data["Books"] = "Error Adding data";
 }else{
 data["error"] = 0;
 data["Books"] = "Book Added Successfully";
 }
 res.json(data);
 });
 }else{
 data["Books"] = "Please provide all required data (i.e : Bookname, Authorname, Price)";
 res.json(data);
 }
 });

And First will get posted data by using “req.body” to add book data to database using connection.query.
That will add book data and based on that return response as json.

 

Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *