How to Create a CRUD app in Nodejs with MySQL data?

In this project, I can show you how to create add, edit, delete, and list implements in Nodejs with SQL data. At first, go to your http://localhost/phpmyadmin/sql.php  server and create a database. Create a table:

CREATE TABLE nodetest (

name varchar(255),

roll_no varchar(255),

ph_no varchar(255)

);

Now create a folder in a directory and open the terminal here, in my case I create a folder called “blog”, now in a terminal type “npm init”

After setup, you can see package.json file is created. Now install required npm packages like ‘body-parser’, ‘ejs’, ‘express’, ‘mysql’, ‘path’, ‘nodemon’(you can check these packages from https://www.npmjs.com/). After installing those npm packages create app.js and db.js file. And also create some folder in your directory models, view, and routes. After that, your folder looks like this:

app.js

var express = require('express');
var path = require('path');
var bodyParser = require('body-parser');

var app = express();

var nilmoni = require('./routes/nilmoni');

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.engine('html', require('ejs').renderFile);
app.set('view engine', 'html');

// app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

app.use('/, nilmoni);

app.use(function(req, res, next) {
    var err = new Error('Not Found');
    err.status = 404;
    next(err);
  });
  
  // error handler
  app.use(function(err, req, res, next) {
    // set locals, only providing error in development
    res.locals.message = err.message;
    res.locals.error = req.app.get('env') === 'development' ? err : {};
  
    // render the error page
    res.status(err.status || 500);
    res.render('error');
  });
  
  app.listen(3200, function(){
    console.log('Server running at port 3000: http://127.0.0.1:3200')
  })
  
  module.exports = app;

var mysql=require('mysql');
var connection=mysql.createPool({
    host:'localhost',
    user:'root',
    password:'',
    database:'nodetest'   //that is the database name// 
});
module.exports=connection;

var db=require('../db');
var Nilmoni={  
  getAllNilmoni:function(callback){  
return db.query("SELECT * FROM `nodetest`",callback); //nodetest is      the name of the table
  },

  addNilmoni:function(Nilmoni,callback){    
    return db.query("Insert into nodetest (name,roll_no,ph_no) values(?,?,?)",[Nilmoni.name,Nilmoni.roll_no,Nilmoni.ph_no],callback);  
  },

  deleteNilmoni:function(id,callback){    
    return db.query("delete from `nodetest` where id=?",[id],callback);
  },

  getNilmoniById:function(id,callback){      
    return db.query("SELECT * FROM `nodetest` where id=?",[id],callback);
  },

  updateNilmoni:function(id,Nilmoni,callback){
    return  db.query("update nodetest set name=?,roll_no=?,ph_no=?  where id=?",[Nilmoni.name,Nilmoni.roll_no,Nilmoni.ph_no,id],callback);
  }, 
};
module.exports=Nilmoni;

Manage Information
Add



<%
var i=1;
nilmoni.forEach(function (nilmon) {
%>

<%
i++})
%>



Sl No.
Name
Roll
Phone
Action



<%= i %>
<%= nilmon.name %>
<%= nilmon.roll_no %>
<%= nilmon.ph_no %>
Edit
Delet

Routes/nilmoni.js

var express = require('express');
var router = express.Router();
var Nilmoni=require('../models/Nilmoni'); //required models here


router.get('/', function(req, res, next) {
    
        Nilmoni.getAllNilmoni(function(err,results){
       //using getAllNilmoni  I showing the result of all data on the tables//    
                    if(err){                        
                        res.json(err);
                    }
                    else{                       
                        res.render('list',{nilmoni:results});
                    }
        });     
});

module.exports = router;

Now I start the project. Open the terminal on this directory and type nodemon. Here I am using ndemon, for that, I install the nodemon package but you can also use npm start.

Now go to the browser and run localhost:3200, the result is like this:

Here we can see the list page is working properly. Now we can create the add and edit page to set the routes.

So create add_nilmoni.html file in the views folder.

Add_nilmoni.html:

Name 

Roll No. 

Phone No. 




Submit

router.get('/add_nilmoni', function(req, res, next) {
 
    Nilmoni.getAllNilmoni(function(err,results){
            if(err)
            {
                res.json(err);
            }else{                 
                res.render('add_nilmoni',{ nilmoni: results});                  
            }
            })
});//that can help load the add_nilmoni.html file//

router.post('/add', function(req, res, next) {
    Nilmoni.addNilmoni(req.body,function(err,results){
        if(err){                
            res.json(err);
        }else{                 
        
            return res.redirect('/nilmoni');         
            }          
    }); 
});


// that function can help for add//

Now you can see the Add page is working properly. For the Edit, we can create an edit_nilmoni.html in the views folder.

Views/edit_nilmoni.html

Name











Roll No.











Phone No:















Submit