In this video we are going to learn about nodejs mysql crud operation.
We will create crud application in express js framework with mysql database.
So Let's start.
First of all create new project.
So go to the project location where you want to create and then here create a new folder.
Lets say folder name is myproject.
Now open command prompt here.
So in explorer url type cmd and press enter.
Now in command prompt run the command.

npm init -y

Now install express so run the command.

npm install express

Also install the body parser and mysql.

npm install body-parser
npm install mysql

Alright now this project to the visual studio code.
Inside the project just create a file.
Lets say file name is index.js.
Inside the index.js file.
Just require the express, mysql and body-parser.

const express = require('express'); 
const mysql = require('mysql'); 
const bodyparser = require('body-parser'); 

Now create express app and configure express server.

var app = express(); 
app.use(bodyParser.json()); 
app.use(bodyParser.urlencoded({extended:true})); 

Now create a database inside the mysql.
So go to the phpmyadmin and here just create new database.
Lets say database name is studentdb Just open the studentdb databse.
Now create new table.
So click on sql link and run the following sql query.

CREATE TABLE `students` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `phone` varchar(255) NOT NULL,  
    PRIMARY KEY (`id`)
);

Now switch to the project and inside the index js file add the mysql connection.

var mysqlConnection = mysql.createConnection({ 
    host:'localhost', 
    user:'root', 
    password:'', 
    database:'studentdb', 
    multipleStatements:true. 
}); 

Now use the connect() function to connect establish the connection with the database using provided credentials.

mysqlConnection.connect((err)=> { 
    if(!err) 
        console.log('Connection Established Successfully'); 
    else. 
        console.log('Connection Failed!'+ JSON.stringify(err,undefined,2)); 
}); 
app.listen(3000,()=>{ 
    console.log(\"Express is running on localhost:3000\"); 
}); 

Now lets install the nodemon.

npm install nodemon

Now run the index.js file so type.

nodemon index.js

You can see here connection established successfully.
Now lets see the create operation.
So go to index.js write the code for insert record.

app.post('/student',(req,res)=>{ 
    mysqlConnection.query(\"INSERT INTO students(name,email,phone) values(?,?,?)\",[req.body.name,req.body.email,req.body.phone],(err,response)=>{ 
        if(!err) 
        { 
            res.send(\"Record has been inserted successfully!\"); 
        }
        else{ 
            throw err; 
        } 
    }) 
}); 

Now lets run this.
So goto the postman and here send post request.
So select Post in method and in url set the url localhost:3000/student and inside the body just pass name,email and phone.
Now click on send and you can see the response record created.
Now lets fetch all records from database.
In index.js file lets write the the following code for fetch records from database.

app.get('/students',(req,res)=>{ 
    mysqlConnection.query('SELECT * FROM students',(err,rows,fields)=>{ 
        if(!err) 
        { 
            res.send(rows); 
        }
        else
        { 
            throw err; 
        } 
    }); 
}); 

Lets run this.
So goto the postman and here send get request.
So choose get request and url is localhost:3000/students.
Click on send.
You can see here all the students.

Now create a route to GET specific student from the MySQL database.

app.get('/students/:id',(req,res)=>{ 
    mysqlConnection.query('SELECT * FROM students WHERE id=?',[req.params.id],(err,row,fields)=>{ 
        if(!err) 
        { 
            res.send(row); 
        }
        else{ 
            throw err; 
        } 
    }); 
}); 

Lets run this.
So goto the postman and here send get request.
So choose get request and url is localhost:3000/students/1.
Click on send.
You can see students whose id is 1.
If I change the id with 2.
You can see here the id 2 students.

Now lets create the routes for update.

app.put('/students/:id',(req,res)=>{ 
    mysqlConnection.query('UPDATE students SET phone=? WHERE id=?',[req.body.phone,req.params.id],(err,rows,fields)=>{ 
        if(!err) 
        { 
            res.send(\"Record has been updated\"); 
        } 
        else{ 
            throw err; 
        }
    }) 
}); 

Lets run this.
So goto the postman and here send put request.
So choose put request and url is localhost:3000/students/1 and the body pass the updated data like phone = 122323122 now click on send.
You can see here phone is updated.

app.delete('/students/:id',(req,res)=>{ 
    mysqlConnection.query('DELETE FROM students WHERE id=?',[req.params.id],(err,rows,fields)=>{ 
        if(!err){ 
            res.send('Record has been deleted successfully!'); 
        }
        else{ 
            throw err; 
        } 
    });
}); 

Lets run this.
So goto the postman and here send delete request.
So choose delete request and url is localhost:3000/students/1.
Click on send.
You can see id 1 student has been deleted.
So in this way you can perform the crud operation in nodejs.