Categories
JavaScript Templating NodeJS

CRUD app with Node, Express , and MySQL

I would like to share my CRUD (Create, Read, Update and Delete) with Node, Express framework, MySQL and I’m using EJS view engine to manage HTML code .

Prerequisites

to get started follow this article for installation https://medium.com/@bhanushali.mahesh3/creating-a-simple-website-with-node-js-express-and-ejs-view-engine-856382a4578f

Creating the Database Table

Execute SQL Dump file

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 17, 2020 at 08:40 AM
-- Server version: 10.1.38-MariaDB
-- PHP Version: 7.1.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_vue_product`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `namex` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `stock` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `namex`, `category`, `qty`, `stock`) VALUES
(150, '1st Product', 'category 1', '4', 'In Stock'),
(151, '2nd Product', 'category 12', '0', 'Out of stock'),
(186, '3rd Product', 'category 3', '22', 'In Stock'),
(187, '4th Products', 'wwd', '2', 'In Stock');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=188;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Server.

const createError = require('http-errors');
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const path = require('path');


const { doProduct, getHomePage } = require('./routes/api');
const port = 2000;

const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'db_angular_product'
});

// connect to database
db.connect((err) => {
  if (err) {
    throw err;
  }
  console.log('Connected to database');
});
global.db = db;

const app = express();

// configure middleware
app.set('port', process.env.port || port); // set express to use this port
app.set('views', __dirname + '/views'); // set express to look in this folder to render our view
app.set('view engine', 'ejs'); // configure template engine
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json()); // parse form data client
app.use(express.static(path.join(__dirname, 'public'))); // configure express to use public folder


// routes for the app
app.get('/', getHomePage);
app.get('/api/:id/:action', doProduct);
app.post('/api/:action', doProduct);


app.get('/search', function (req, res) {
  db.query('SELECT * from products where namex like "%' + req.query.key + '%"',
    function (err, result) {
      if (err) throw err;
      res.render('index.ejs', {
        page: 'proghubsterzeak.com', menuId: 'home', products: result, title: "Add New Product"
      });
    });
});


// catch 404 and forward to error handler
app.use(function (req, res, next) {
  next(createError(404));
});

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

// set the app to listen on the port
app.listen(port, () => {
  console.log(`Server running on port: ${port}`);
});

module.exports = app;

Web API Controller

The Web API Controller consists of the following three Action methods.
– Add and Update Product
– Delete Product

module.exports = {
  getHomePage: (req, res) => {
    let query = "SELECT * FROM `products` ORDER BY id ASC";
    db.query(query, (err, result) => {
      if (err) {
        res.redirect('/');
      } else {
        message = '';
        res.render('index', {
          page: 'proghubsterzeak.com', menuId: 'home', products: result, message: message, title: "Add New Product"
        });
        // console.log(result[0].id);
      }
    });
  },

  doProduct: (request, response) => {
    let productId = request.params.id;
    let message = '';
    let update_id = request.body.update_id;
    let namex = request.body.namex;
    let category = request.body.category;
    let qty = request.body.qty;

    let action = request.params.action;

    switch (action) {
      case "add":

        let nameQuery = "SELECT * FROM `products` WHERE namex = '" + namex + "'";

        db.query(nameQuery, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          if (result.length > 0) {
      
            message = 'Product already exists';
            response.render('index.ejs', {
              page: 'proghubsterzeak.com', menuId: 'home', products: result, message: message, title: "Add New Product"
            });
          } else {

            let query = "INSERT INTO `products` (namex, category, qty) VALUES ('" +
              namex + "', '" + category + "', '" + qty + "')";
            db.query(query, (err, result) => {
              if (err) {
                return response.status(500).send(err);
              }
              response.redirect('/');
            });

          }
        });

        break;

      case "edit":

        let query = "UPDATE `products` SET `namex` = '" + namex + "', `category` = '" + category + "', `qty` = '" + qty + "' WHERE `products`.`id` = '" + update_id + "'";
        db.query(query, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          response.redirect('/');
        });

        break;

      case "delete":

        let deleteUserQuery = 'DELETE FROM products WHERE id = "' + productId + '"';

        db.query(deleteUserQuery, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          response.redirect('/');
        });

        break;

    }
  },
};

Creating Product Page

This is the main page of the app which contains a table to display a list of all the products, modal form to add and Update product record to the database.

<!DOCTYPE html>
<html lang="en">

<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Welcome | <%= page %></title>
<meta name="active-menu" content="<%= menuId %>">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="./assets/bootstrap/css/bootstrap.min.css">
</head>

<body>
  
  <header>
  <nav class="navbar navbar-light bg-light">
    <span class="navbar-text">
      <a href="https://proghubsterzeak.com/">https://<%= page %></a>
    </span>
  </nav>
</header>

  <section id="my-app">
    <div class="container">
      <div class="row my-5">
        <button type="button" class="btn btn-info btn-sm" data-toggle="modal" data-target="#Productform">Add
          Product</button>
        <div class="ml-auto">

          <input class="form-control typeahead" spellcheck="false" autocomplete="off" name="typeahead" type="text" />
        </div>
      </div>
      <div class="row">
        <div class="table-responsive">
          <% include table %>
        </div>
      </div>
    </div>

  </section>
  <% include modal %>
  
      <script src="./assets/jquery/jquery.min.js"></script>
<script src="./assets/bootstrap/js/bootstrap.bundle.js"></script>
<script src="./assets/ejs/ejs.min.js"></script>
<script src="./assets/js/typeahead.js"></script>
<script>
  $(document).ready(function () {
    $('input.typeahead').typeahead({
      name: 'typeahead',
      remote: 'search?key=%QUERY',
      limit: 10
    });

    $('#myProductTable').on('click', '.edit', function () {
      var product_id = $(this).attr('data-id');
      var product_name = $(this).attr('data-name');
      var product_qty = $(this).attr('data-qty');
      var product_category = $(this).attr('data-category');
      $('#Productform form').attr('action', 'api/edit');
      $('#Productform').modal('show');
      $('#my-modal-title').html('Update Product');
      $('#Productform form .btn').html('Update Product');
      $('.product_name').val(product_name);
      $('.product_qty').val(product_qty);
      $('.product_category').val(product_category);
      $('.product_id').val(product_id);
    })
  });
</script>
</body>


</html>
<% if (products.length > 0) {%>
<table id="myProductTable" class="table-product table table-bordered table-striped">
  <thead class="thead-product">
    <tr>
      <th style="width:5%">No.</th>
      <th>Name</th>
      <th style="width:15%">Category</th>
      <th style="width:5%">Quantity</th>
      <th style="width:15%">Status</th>
      <th style="width:18%">Action</th>
    </tr>
  </thead>
  <tbody>
    <% products.forEach((product, index) => { %>
    <tr>
      <td><%= product.id %></td>
      <td><%= product.namex %></td>
      <td><%= product.category %></td>
      <td><%= product.qty %></td>
      <% if (product.qty > 10) {%>
      <td><span class="badge badge-success">In Stock</span></td>
      <% } else if (product.qty <= 10 && product.qty > 0) { %>
      <td><span class="badge badge-primary">ALmost Sold Out</span></td>
      <% } else { %>
      <td><span class="badge badge-warning">Out Of Stock</span></td>
      <% } %>
      <td>
        <div class=" buttons text-center">
          <a href="javascript:void(0);" data-id="<%= product.id %>" data-name="<%= product.namex %>"
            data-category="<%= product.category %>" data-qty="<%= product.qty %>"
            class="btn btn-sm btn-info edit">Edit</a>
          <a href="/api/<%= product.id %>/delete" class="btn btn-sm btn-danger delete">Delete</a>
        </div>
      </td>
    </tr>
    <% }) %>
  </tbody>
</table>
<% } else { %>
<p class="text-center">No product found...</p>
<% } %>

If you want to get complete source code, please click Download Code button on below link. For more information about the system. You can contact me @ Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!

Share this
  •  
  •  
  •  
  •  
  •  
  •  
  • 42
    Shares

Leave a Reply

Your email address will not be published. Required fields are marked *