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
No comments:
Post a Comment