REST Based CRUD API for Mysql & Express.js

First we will create a table in mysql for purpose of mysql crud demonstration.

CREATE TABLE IF NOT EXISTS `course` (  `Id` varchar(50) NOT NULL,  `Name` varchar(500) DEFAULT NULL,  PRIMARY KEY (`Id`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Add Some Entries into course table

    insert into course values(1,'C');
    insert into course values(2,'CPP');
    insert into course values(3,'C#');
    insert into course values(4,'Python');
    insert into course values(5,'Ruby');
    insert into course values(6,'Java');


create express project by  running
     express --view=ejs mysqlcrud

install packages with
    npm install

Now
      dbconnection.js as follows

    var mysql = require('mysql');
    var connection = mysql.createPool({
        host: 'localhost',
        user: 'xdba',
        password: 'Sangram@123',
        database: 'play'
    });
    module.exports = connection;

Create a folder called models and add file "Course.js" with following content

var db = require('../dbconnection'); //reference of dbconnection.js

    var Course = {
        getAllCourses: function (callback) {
        return db.query("Select * from course", callback);
        },
        getCourseById: function (id, callback) {
        return db.query("select * from course where Id=?", [id], callback);
        },
        addCourse: function (Course, callback) {
        return db.query("Insert into course values(?,?)", [Course.Id, Course.Name], callback);
        },
        deleteCourse: function (id, callback) {
        return db.query("delete from course where Id=?", [id], callback);
        },
        updateCourse: function (id, Course, callback) {
        return db.query("update course set Name=? where Id=?", [Course.Name, id], callback);
        }
    };
    module.exports = Course;


add file course.js in route folder as
    var express = require('express');
    var router = express.Router();
    var Course = require('../models/Course');


    /*getbyid */
    router.get('/:id?', function (req, res, next) {
      if (req.params.id) {
        Course.getCourseById(req.params.id, function (err, rows) {
          if (err) {
        res.json(err);
          } else {
        res.json(rows);
          }
        });
      }else {
        Course.getAllCourses(function (err, rows) {
          if (err) {
        console.log("error");
        res.json(err);
          } else {
        res.json(rows);
          }
        });
      }
    });

    /*save new */
    router.post('/', function (req, res, next) {
      console.log(req.body);
      Course.addCourse(req.body, function (err, count) {
        if (err) {
          res.json(err);
        } else {
          res.json(req.body);//or return count for 1 & 0
        }
      });
    });

    /*delete */
    router.delete('/:id', function (req, res, next) {
      Course.deleteCourse(req.params.id, function (err, count) {
        if (err) {
          res.json(err);
        } else {
          res.json(count);
        }
      });
    });

    /*update*/
    router.put('/:id', function (req, res, next) {
      Course.updateCourse(req.params.id, req.body, function (err, rows) {
        if (err) {
          res.json(err);
        } else {
          res.json(rows);
        }
      });
    });

    module.exports = router;

in app.js below "var users = require('./routes/users');" add

    var course = require('./routes/course');
and in app.js below "app.use('/users', users);" add
    app.use('/course', course);

Now we are done ,run "npm start"


On Postman

1) GET : http://localhost:3000/course
    will list all course
2) GET :http://localhost:3000/course/1
    will list course with id 1
3) POST:http://localhost:3000/Course
   Header:Content-Type:application/json
   Body:
    {
    "Id":"7",
    "Name":"Java"
    }
   will add new course
4)PUT:http://localhost:3000/Course/4
   Header:Content-Type:application/json
   Body:
    {
    "Name":"Java"
    }
   will update course with id 4 its name to java from old value

5) Delete: http://localhost:3000/Course/5
    will delete course record with id as 5

code sample can be viewed at  https://github.com/gitsangramdesai/mysql-crud-express