Creating a CRUD Application using NodeJS and MySQL

Here i have documented how we can create simple CRUD Application using Node JS and MySQL.
I have started the project with npm init command.
npm init
Then i have installed below dependencies.
  • express
  • nodemon
  • mysql
  • joi
  • @joi/date
Express
Express is a node js framework that provides robust set of features for web application development. Click here for learn more about express js
nodemon
As Nodejs application runs in server side, server restart would require for every single code changes. to overcome that we can use nodemon. nodemon will restart automatically for the code changes. we need to start the application nodemon index.js instead of node index.js.
mysql
This is a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed. Click here to more information.
joi
Validation is very important for every input received from user. We are using joi package for the validation purpose. Click here to know more about that. @joi/date is the date validation extention of joi.
Folder structure of our project
  • >node_modules
  • index.js
  • package-lock.json
  • package.json
I have used node js version v13.14.0
Package.xml 

	{
	  "name": "Infoskarp",
	  "version": "1.0.0",
	  "description": "",
	  "main": "index.js",
	  "scripts": {
	    "test": "echo \"Error: no test specified\" && exit 1"
	  },
	  "keywords": [],
	  "author": "",
	  "license": "ISC",
	  "dependencies": {
	    "@joi/date": "^2.1.0",
	    "express": "^4.17.1",
	    "joi": "^17.4.0",
	    "mysql": "^2.18.1",
	    "nodemon": "^2.0.7"
	  }
	}
index.js 

	{
	const Joi = require("joi").extend(require("@joi/date"));
	const express = require("express");
	const app = express();

	const mysql = require("mysql");
	const connection = mysql.createConnection({
	    host:"localhost",
	    user:"root",
	    password:"",
	    database:"test"
	});

	connection.connect((err)=>{
	    if(!err)
	    {
	        console.log("MySql Connection Established successfully");
	    }
	    else{
	        console.log("Connection error");
	    }
	})

	app.use(express.json());


	//insert user

	app.post("/users",(req,res) => {
	    console.log(req.body);
	    let { error } = validation(req.body)
	    if(error) return res.status(400).send(error.details[0].message);
	    let {name, email, phone, dob} = req.body;

	    let querySt = `insert into users(name,email,phone,dob) values ('§{name}','§{email}','§{phone}','§{dob}')`;
	    connection.query(querySt,(err,row,field)=>{
	        if(!err)
	        {
	            res.send("User added Successfully");
	        }
	        else
	        {
	            res.send(err)
	        }
	    })
	});

	//getting user by id
	app.get("/users/:id",(req,res)=>{
	    let id = parseInt(req.params.id);
	    let queryStr = `select * from users where id=§{id}`;
	    connection.query(queryStr,(err,row,field) => {
	      if(!err)
	      {   
	          if(row.length)
	          {
	               res.send(row)
	          }
	          else
	          {
	              res.status(404).send("The user not found");
	          }
	      }
	      else
	      {
	          res.send(err)
	      }
	    })
	});


	//getting all users
	app.get("/users",(req,res)=>{
	    let id = parseInt(req.params.id);
	    let queryStr = `select * from users`;
	    connection.query(queryStr,(err,row,field) => {
	      if(!err)
	      {   
	          if(row.length)
	          {
	               res.send(row)
	          }
	          else
	          {
	               res.status(404).send("The user not found");
	          }
	      }
	      else
	      {
	          res.send(err)
	      }
	    })
	});

	//updating the user by id
	app.patch("/users/:id",(req,res)=>{
	    let { error } = validation(req.body)
	    if(error) return res.status(400).send(error.details[0].message);
	    
	    let {name, email, phone, dob} = req.body;
	    let id = parseInt(req.params.id);

	    let queryStr = `update users set name = '§{name}', email = '§{email}', phone = '§{phone}', dob = '§{dob}' where id = §{id}`;
	    console.log(queryStr);
	    connection.query(queryStr, (err,row,field) => {
	        if(!err)
	        {
	            res.send("Updated sucessfully");
	        }
	        else
	        {
	            res.send(err)
	        }
	    });
	    
	});


	// deleting the user by id
	app.delete("/users/:id",(req,res)=>{

	    let id = parseInt(req.params.id);
	    let queryStr = `delete from users where id = §{id}`;
	    console.log(queryStr);
	    
	    connection.query(queryStr, (err,row,field) => {
	        if(!err)
	        {
	            res.send("deleted sucessfully");
	        }
	        else
	        {
	            res.send(err);
	        }
	    });
	});


	function validation(request)
	{
	    const schema = Joi.object({
	        name: Joi.string().min(5).max(20).required(),
	        email: Joi.string().email( { minDomainSegments :2, tlds: { allow:false } }),
	        phone: Joi.string().min(10).required(),
	        dob:Joi.date().format("YYYY-MM-DD").max("now")
	    });

	    return schema.validate({name:request.name,email:request.email,phone:request.phone,dob:request.dob});
	 
	}

	const port = process.env.PORT || 3000;
	app.listen(port,()=>{console.log(`server listening to §{port}`)});
	}
The below comment to start the application. 
nodemon index.js
The database structure
We can use postman to validate the application flow.