Node js

CRUD app in node js using MySQL & bootstrap 5 with Source code

Hey Everyone today i am gone tell you How you can Create CRUD app in node js using MySQL & bootstrap 5

What i Use in my Crud Project..

  • express.js    — this is for node framework to reduce node.js code
  • nodemon     — This is for auto Refresh
  • mysql           — to use MySQL database
  • ejs                — ejs use for templating

You need to download all this packages in your project

  • npm i express
  • npm i nodemon
  • npm i mysql
  • npm i ejs

Our CRUD App Look like  Snapshots

CRUD app in node js using MySQL & bootstrap 5

 

CRUD app in node js using MySQL & bootstrap 5 Read page



 

Folder Structure…

 
 

Database Name: nodecrud  and table name student

  
    CREATE TABLE `student` (
    `id` int(11NOT NULL,
    `name` varchar(55NOT NULL,
    `email` varchar(55NOT NULL,
    `message` varchar(55NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

 

————————————————————————————————————————-

 

Step 1) Create file config.js

  var mysql = require(“mysql”);
  var connection = mysql.createConnection({
    host: “localhost”// Replace with your host name
    user: “root”// Replace with your database username
    password: “”// Replace with your database password
    database: “nodecurd”// // Replace with your database Name
  });
  connection.connect(function (err) {
    if (errthrow err;
    console.log(“Database is connected successfully !”);
  });
  module.exports = connection;

 

 

————————————————————————————————————————-


 
Step 2) We Need to Create views Folder & 4 ejs pages..
 
 
1st) Create.ejs
<!DOCTYPE html>
<html lang=“en”>
  <head>
    <!– Required meta tags –>
    <meta charset=“utf-8” />
    <meta name=“viewport” content=“width=device-width, initial-scale=1” />

 

    <!– Bootstrap CSS –>
    <link
      href=“https://cdn.jsdelivr.net/npm/bootstrap@5.0.0/dist/css/bootstrap.min.css”
      rel=“stylesheet”
      integrity=“sha384-wEmeIV1mKuiNpC+IOBjI7aAzPcEZeedi5yW5f2yOq55WWLwNGmvvx4Um1vskeMj0”
      crossorigin=“anonymous”
    />

 

    <link rel=“preconnect” href=“https://fonts.googleapis.com” />
    <link rel=“preconnect” href=“https://fonts.gstatic.com” crossorigin />
    <link
      href=“https://fonts.googleapis.com/css2?family=Raleway&display=swap”
      rel=“stylesheet”
    />
    <style>
      body {
        background-colorwhitesmoke;
        font-family“Raleway”sans-serif;
      }
    </style>
    <title>Create</title>
  </head>
  <body>
    <ul class=“nav justify-content-center p-4”>
      <li class=“nav-item”>
        <a class=“nav-link btn-primary text-dark rounded” href=“/”>Create</a>
      </li>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <li class=“nav-item”>
        <a class=“nav-link btn-warning text-dark rounded” href=“/Read”>Read</a>
      </li>
    </ul>
    <div class=“container pt-3” style=width: 40%>
      <h1 class=“text-center”>Insert Data using node js</h1>
      <hr />
      <form action=“” method=“POST”>
        <div class=“mb-3”>
          <label for=“exampleInputEmail1” class=“form-label”>Name</label>
          <input type=“text” class=“form-control” name=“name” />
        </div>
        <div class=“mb-3”>
          <label for=“exampleInputEmail1” class=“form-label”>Email</label>
          <input type=“text” class=“form-control” name=“email” />
        </div>
        <div class=“mb-3”>
          <label for=“exampleInputEmail1” class=“form-label”>Message</label>
          <input type=“text” class=“form-control” name=“message” />
        </div>
        <button type=“submit” class=“btn btn-primary”>Submit</button>
      </form>
      <br /><br />
      <p></p>
    </div>
  </body>
</html>

 

 
 
 
 
2nd) Read.ejs
 
<!DOCTYPE html>
<html lang=“en”>
  <head>
    <meta charset=“UTF-8” />
    <meta http-equiv=“X-UA-Compatible” content=“IE=edge” />
    <meta name=“viewport” content=“width=device-width, initial-scale=1.0” />
    <!– Bootstrap CSS –>
    <link
      href=“https://cdn.jsdelivr.net/npm/bootstrap@5.0.0/dist/css/bootstrap.min.css”
      rel=“stylesheet”
      integrity=“sha384-wEmeIV1mKuiNpC+IOBjI7aAzPcEZeedi5yW5f2yOq55WWLwNGmvvx4Um1vskeMj0”
      crossorigin=“anonymous”
    />
    <link rel=“preconnect” href=“https://fonts.googleapis.com”>
    <link rel=“preconnect” href=“https://fonts.gstatic.com” crossorigin>
    <link href=“https://fonts.googleapis.com/css2?family=Raleway&display=swap” rel=“stylesheet”>
    <style>
      body{
        background-colorwhitesmoke;
        font-family‘Raleway’sans-serif;
      }
      </style>
    <title>Read Data</title>
  </head>
  <body>
    <ul class=“nav justify-content-center p-4”>
        <li class=“nav-item”>
          <a class=“nav-link btn-primary text-dark rounded” href=“/”>Create</a>
        </li>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <li class=“nav-item “>
          <a class=“nav-link btn-warning text-dark rounded” href=“/Read”>Read</a>
        </li>
      </ul>
    <div class=“container”>
      <h1 class=“text-center p-2 mt-4”>Display Data using Node.js & MySQL</h1>
      <hr/>
      <div class=“table-data mt-4”>
        <table class=“table table-hover”>
          <tr>
            <th>S.N</th>
            <th>Name</th>
            <th>Email</th>
            <th>Message</th>
            <th>Edit</th>
            <th>Delete</th>
          </tr>

 

          <% if(userData.length!=0){ var i=1; userData.forEach(function(data){
          %>
          <tr>
            <td><%=data.id %></td>
            <td><%=data.name %></td>
            <td><%=data.email %></td>
            <td><%=data.message %></td>

 

            <td><a href=“/Update/<%=data.id%>”>Edit</a></td>
            <td><a href=“/Delete/<%=data.id%>” >Delete</a></td>
          </tr>
          <% i++; }) %> <% } else{ %>
          <tr>
            <td colspan=“7”>No Data Found</td>
          </tr>
          <% } %>
        </table>
      </div>
    </div>
  </body>
</html>

 

 
 
 
 
3rd) Delete.ejs
 
<!DOCTYPE html>
<html lang=“en”>
<head>
    <meta charset=“UTF-8”>
    <meta http-equiv=“X-UA-Compatible” content=“IE=edge”>
    <meta name=“viewport” content=“width=device-width, initial-scale=1.0”>
    <title>Delete data</title>
</head>
<body>
    <h1>Delete me….</h1>
</body>
</html>
 
 

 
4th) Update.ejs
 
<!DOCTYPE html>
<html lang=“en”>

 

<head>
  <meta charset=“UTF-8” />
  <meta http-equiv=“X-UA-Compatible” content=“IE=edge” />
  <meta name=“viewport” content=“width=device-width, initial-scale=1.0” />
  <!– Bootstrap CSS –>
  <link href=“https://cdn.jsdelivr.net/npm/bootstrap@5.0.0/dist/css/bootstrap.min.css” rel=“stylesheet”
    integrity=“sha384-wEmeIV1mKuiNpC+IOBjI7aAzPcEZeedi5yW5f2yOq55WWLwNGmvvx4Um1vskeMj0” crossorigin=“anonymous” />
  <link rel=“preconnect” href=“https://fonts.googleapis.com” />
  <link rel=“preconnect” href=“https://fonts.gstatic.com” crossorigin />
  <link href=“https://fonts.googleapis.com/css2?family=Raleway&display=swap” rel=“stylesheet” />
  <style>
    body {
      background-colorwhitesmoke;
      font-family“Raleway”sans-serif;
    }
  </style>
  <title>Update Data</title>
</head>

 

<body>
  <div class=“container mt-2” style=width: 40%>
    <h1 class=“p-2 mt-4 text-center”>Update Data using Node js</h1>
    <hr />
    <form action=“” method=“POST”>
      <div class=“mb-3”>
        <label for=“exampleInputEmail1” class=“form-label”>Name</label>
        <input type=“text” class=“form-control” name=“id” value=<%=(typeof editData!=’undefined’)? editData.id:”%>”
          disabled />
      </div>
      <div class=“mb-3”>
        <label for=“exampleInputEmail1” class=“form-label”>Name</label>
        <input type=“text” class=“form-control” name=“name”
          value=<%=(typeof editData!=’undefined’)? editData.name:”%>” />
      </div>
      <div class=“mb-3”>
        <label for=“exampleInputEmail1” class=“form-label”>Email</label>
        <input type=“text” class=“form-control” name=“email”
          value=<%=(typeof editData!=’undefined’)? editData.email:”%>” />
      </div>
      <div class=“mb-3”>
        <label for=“exampleInputEmail1” class=“form-label”>Message</label>
        <input type=“text” class=“form-control” name=“message”
          value=<%=(typeof editData!=’undefined’)? editData.message:”%>” />
      </div>
      <button type=“submit” class=“btn btn-primary”>Update</button>
    </form>
    <br /><br />
  </div>

 

  <td></td>
</body>

 

</html>
 

————————————————————————————————————————-


Step 3) Now Create Main file to run app

Create app.js

var express = require(‘express’);
var app = express();

 

//ejs templete
app.set(‘view engine’‘ejs’);

 

//Database connection file config.js..
var connection = require(‘./config’);

 

// this is for read POST data
app.use(express.json());
app.use(express.urlencoded({
  extended: true
}));

 

//All routing start here.. 

 

// index page get data by database…
app.get(‘/’function(reqres){
res.render(“Create”);
});
app.get(‘/Read’function(reqres){
    connection.query(“SELECT * FROM student”function (errresult) {
        if (errthrow err;
        //console.log(result);
        res.render(‘Read’, { title: ‘pizza’userData: result});
      });

 

 });

 

 // index page insert data in databse…
app.post(‘/’function(reqres){
   var a = req.body.name;
   var b = req.body.email;
   var c = req.body.message;
            
            var sql = “INSERT INTO `student`(`name`,`email`,message) VALUES (‘”+a+“‘,'”+b+“‘,'”+c+“‘)”;
            connection.query(sqlfunction (errresult) {
              if (errthrow err;
              console.log(“1 record inserted”);
            });
            return res.render(‘Create’, { errormessage: ‘insert data successfully’ });
 });

 

 

// Delete page…. delete data by id
app.get(‘/delete/:id’function(reqres) {
    var idreq.params.id;
      var sql = ‘DELETE FROM student WHERE id = ?’;
      connection.query(sql, [id], function (errdata) {
      if (errthrow err;
      console.log(id);
      console.log(” record deleted”+id);
    });
    return res.redirect(‘/Read’);

 

  });

 

 

 // Update page get data by id… 
  app.get(‘/Update/:id’function(reqres) {
       var idreq.params.id;
       
       var sql = ‘SELECT * FROM student WHERE id = ?’;
      connection.query(sql, [id], function (errdata) {
      if (errthrow err;
     
      console.log(data);
      res.render(‘Update’, { title: ‘User List’editData: data[0]});
      
    });
    
  });

 

 

  //update page sned post request to update data into database…
  app.post(‘/Update/:id’function(reqres) {
    var idreq.params.id;
      var updateData=req.body;
      var sql = `UPDATE student SET ? WHERE id= ?`;
      connection.query(sql, [updateDataid], function (errdata) {
      if (errthrow err;
      console.log(data.affectedRows + ” record(s) updated”);
      return res.redirect(‘/Read’);
    });
  
  });

 

 

app.listen(3000);

 

Open Vs Code terminal and Run Command:

    nodemon app

 

Open browser type

    http://localhost:3000/

    http://localhost:3000/Read

 

Download Source code 

https://github.com/starkverma111/node-crud.git

 
 
I hope this Post help you….
if you get any errror comment or message me.. i will help you..