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 IF NOT EXISTS `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.