Node JS Tutorial - Nodejs + Mysql CRUD

In this tutorial, we will learn about performing CRUD (Create, Read, Update, Delete) operations in Node.js using the Express.js framework and a MySQL database.

Creating a New Project

First, create a new project by navigating to the desired location and creating a new folder. Let's call the folder "myproject". Open the command prompt in the folder by typing "cmd" in the explorer URL and pressing enter.

Run the following command to initialize the project:

npm init -y

Next, install Express.js by running the following command:

npm install express

Also, install the body-parser and mysql packages:

npm install body-parser
npm install mysql

Setting up the Project in Visual Studio Code

Open the project in Visual Studio Code and create a new file called "index.js". Inside the index.js file, require the Express.js, mysql, and body-parser packages:

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

Create an Express.js app and configure the Express.js server:

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

Creating a MySQL Database

Create a new database in MySQL by navigating to phpMyAdmin and creating a new database called "studentdb". Open the studentdb database and create a new table by running 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`)
);

Connecting to the MySQL Database

Switch back to the project and add the MySQL connection to the index.js file:

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

Use the connect() function to establish a connection to the database using the 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"); 
}); 

Installing Nodemon

Install Nodemon by running the following command:

npm install nodemon

Running the Application

Run the index.js file by typing the following command:

nodemon index.js

You should see a message indicating that the connection was established successfully.

Performing CRUD Operations

Create Operation

Write the code to insert a record into the database:

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; 
        } 
    }) 
}); 

Run the application and send a POST request to the /student endpoint using Postman:

Select the POST method and set the URL to localhost:3000/student. Pass the name, email, and phone number in the request body and click send. You should see a response indicating that the record was created.

Read Operation

Write the code to fetch all records from the database:

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

Run the application and send a GET request to the /students endpoint using Postman:

Select the GET method and set the URL to localhost:3000/students. Click send and you should see a list of all students.

Get Specific Student

Create a route to get a 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; 
        } 
    }); 
}); 

Run the application and send a GET request to the /students/1 endpoint using Postman:

Select the GET method and set the URL to localhost:3000/students/1. Click send and you should see the student with ID 1.

Update Operation

Create a route to update a student:

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; 
        }
    }) 
}); 

Run the application and send a PUT request to the /students/1 endpoint using Postman:

Select the PUT method and set the URL to localhost:3000/students/1. Pass the updated data in the request body and click send. You should see a response indicating that the student was updated.

Delete Operation

Create a route to delete a student:

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; 
        } 
    });
}); 

Run the application and send a DELETE request to the /students/1 endpoint using Postman:

Select the DELETE method and set the URL to localhost:3000/students/1. Click send and you should see a response indicating that the student was deleted.

This concludes the tutorial on performing CRUD operations in Node.js using Express.js and a MySQL database.