Search This Blog
2018/10/08
Javascript : Array - map ,reduce,filter
2018/10/06
REST Based CRUD API for Postgres & Express.js
Lets first create table in database required in article.
create table course(id varchar(50),name varchar(500),primary key(id))
insert into course values(1,'C'),(2,'CPP'),(3,'C#'),(4,'python'),(5,'Ruby')
now create PgUtil.js
const pgp = require('pg-promise')(/* initialization options */);
const cn = {
host: 'localhost', // server name or IP address;
port: 5432,
database: 'sangram',
user: 'xdba',
password: 'sangram'
};
const db = pgp(cn); // database instance;
var PgUtil = {
executeQuery: async function (sqlQuery, callback) {
var result = await db.any(sqlQuery)
console.log(result);
callback(null,result)
},
executeNonQuery: async function (sqlQuery, callback) {
var result = await db.none(sqlQuery)
console.log(result);
callback(null,result)
}
}
module.exports = PgUtil
Inside routes add course.js
var express = require('express');
var router = express.Router();
var PgUtil = require('../PgUtil');
/*getbyid */
router.get('/:id?', function (req, res, next) {
var queryString = ''
if (req.params.id) {
queryString = "select * from course where id= '" + req.params.id + "'"
} else {
queryString = "select * from course "
}
console.log(queryString);
PgUtil.executeQuery(queryString, function (err, data) {
if (err) {
res.json(err);
} else {
res.json(data)
}
})
});
/*update*/
router.put('/:id', function (req, res, next) {
console.log(req.body);
var queryString = "update course set name='" + req.body.Name + "' where id= '" + req.params.id + "'"
PgUtil.executeNonQuery(queryString, function (err, data) {
if (err) {
res.json(err);
} else {
res.json({
"message": "row updated successfully"
})
}
})
});
//insert
router.post('/', function (req, res, next) {
var queryString = "insert into course(id,name) values('" + req.body.Id + "','" + req.body.Name + "')"
console.log(queryString);
PgUtil.executeNonQuery(queryString, function (err, data) {
if (err) {
res.json(err);
} else {
res.json({
"message": "row inserted successfully"
})
}
})
});
/*delete */
router.delete('/:id', function (req, res, next) {
var queryString = "delete from course where id= '" + req.params.id + "'"
console.log(queryString);
PgUtil.executeNonQuery(queryString, function (err, data) {
if (err) {
res.json(err);
} else {
res.json({
"message": "row deleted successfully"
})
}
})
});
module.exports = router;
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 of this article can be viewed at https://github.com/gitsangramdesai/express-pg-rest
REST Based CRUD API for Mysql & Express.js
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