A Simple RESTful API using Nodejs & PostgreSQL.
Hey geeks! Hope you are doing well. From the title of this article, you will understand what we are talking about in this article. So in this article, we will be learning how easy can be to build a RESTFUL API, integrated with a PostgreSQL server. I learned it when one of my friends needs a secure interface for sending the sensor data from the hardware to the SQL server. He asked me, is there was any way I can send the data directly to the server? So, the first time I worked with SQL & NodeJs. Because in most of the cases Nodejs is combined with NoSQL servers like Mongo DB.
So let’s begin !!
Before going Nodejs must be installed in your system so that you can access the node package manager (npm). For checking if Nodejs is installed in your system or not, paste this command in your command prompt:
node --versionIf NodeJs is installed and added to your path, then this command will return the NodeJs version installed in your system. If it is not installed, don’t worry, you can follow this video.
Once done, you are good to go!
Create an empty folder, where all your source files will be placed. Then start a Nodejs project with the help of this command.
npm initThis command will take time and ask you some questions, answer them and enter yes at the end, as mentioned in the image below. Then a new file is created package.json in your project directory.
The content of the package.json file will look something like this!
Then what you have to do is add this code to your package.json file.
"dependencies": {
"body-parser": "~1.0.1",
"cors": "2.8.1",
"express": "~4.0.0",
"pg": "^8.7.1"
},After this, your package.json file will look like this,
Then you just need to write a short command in the command prompt.
npm installThen all the packages written in the dependencies will be installed in your current directory and a folder named node_modules will be created and a file name package-lock.json will also be created, this is used to keep track of the exact version of every package that is installed. Now we understand what all these packages have to do in our project.
- Body-parser: Body-parser is a Nodejs body parsing Middleware for parsing incoming request bodies before we handle it.
- Cors: First things first, CORS stands for Cross-Origin Resource Sharing, which is an HTTP-header-based mechanism that allows a server to indicate any origins other than its own and for making cross-origin calls.
- Express: We all are familiar with the express, as it is the backbone of the MERN stack applications. Used in creating server-side web applications faster and smarter.
- Pg: It is a non-blocking PostgreSQL client for Node.js. It is a kind of an adaptor for accessing the PostgreSQL server.
NOTE: To install any other package or something, you need to write npm install <package name>, this will fetch the package from the node package manager.
Now the project structure will look like this:
We can write all the code in one file, but it is not a good practice, and it becomes difficult to debug the code. That is why we have created three JavaScript files.
- dbconfig.js: This file contains all the configuration parts of the PostgreSQL server, where we have mentioned the HOSTNAME, PORT, USER, PASSWORD, and DATABASE NAME.
NOTE: For Installing and setup PostgreSQL in your system, follow this video.
const Pool = require('pg').Pool;const pool = new Pool({
host : 'localhost',
user : 'abc',
password : 'abc123',
database : 'api_test',
port: '5432'
});module.exports = pool;
This code will be written in your dbconfig.js file. The default port number of the PostgreSQL server is 5432. For user, password, and database, you need to add your credentials. In this, we have created a Pool object, with the help of which we can connect and query the PostgreSQL database. Then we have exported our object so that we can import it into other files.
Now what we have to do is defining our operations. In the operations.js file, we will be writing the actual SQL queries for querying the database.
// importing the connector
const config = require('./dbconfig');// function for getting all the instances from the table "test"
async function getInstances(){
try {
let instance = await config
.query("SELECT * from test");
return instance.rows;
}catch (error){
console.log(error);
}
}// function to get any one instance with a InstanceId
async function getInstance(InstanceId){
try {
let instance = await config
.query("SELECT * from test where id = $1", [InstanceId]);
return instance.rows[0];
}catch (error){
console.log(error);
}
}// function to insert a new instance into the database
async function addInstance(data){
try {
// In the SQL query “RETURNING *” is used to return the instance after adding into the table
let instance = await config
.query("INSERT into test (id, sensor1, sensor2) VALUES ($1, $2, $3) RETURNING *",
[data.id, data.sensor1, data.sensor2]);
return instance.rows[0];
}catch (error){
console.log(error);
}
}// function to delete an instance from the table with Id
async function deleteInstance(id){
try {
let instance = await config
.query("DELETE from test where id = $1", [id]);
return;
}catch (error){
console.log(error);
}
}// exporting all the functions for importing and using in other files
module.exports = {
getInstances: getInstances,
getInstance: getInstance,
addInstance: addInstance,
deleteInstance: deleteInstance
};
Paste this code in your operations.js file and follow the comments to understand what all the utility functions are doing. In pg, if we want to use one variable in our SQL queries, we have to write “$1” at the place where we want to use that variable and after the query in the square brackets mention the variable name.
Now our main server.js begins. In this file, we define our routes, ports for listening to the server.
// importing all the necessary libraries
const database = require('./operations');
const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');// creating an instance of express class and a router for routing.
const app = express();
const router = express.Router();// defining how we parse the request body using BodyParser
app.use(bodyParser.urlencoded({ extended: true}));
app.use(bodyParser.json());
app.use(cors());// This is the our base route, all the routes are added after "/api/"
app.use('/api', router);// Route "/api/instances" for getting all the instances from the database.
router.route('/instances').get((req, res) => {
database.getInstances().then((result) => {
if (result){
return res.json(result);
}else{
return res.json({
message: "Empty Database"
});
}
})
});// Route "/api/get-instance/:id" for getting a particular instance from the database.
// using the instance id, which is passed through the route.
router.route("/get-instance/:id").get((req, res) => {
// getting the instance id from the request parameters.
const instanceid = req.params.id;
database.getInstance(instanceid).then((result) => {
return res.json(result);
})
});// Route "/api/add-instance" for adding a new instance to the database.
router.route("/add-instance").post((req, res) => {
let instance = {...req.body};
database.addInstance(instance).then((result) => {
return res.status(201).json(result);
})
});// Route "/api/delete-instance/:id" for deleting a particular instance from the database.
// using the instance id, which is passed through the route.
router.route("/delete-instance/:id").get((req, res) => {
// getting the instance id from the request parameters.
const instanceid = req.params.id;
database.deleteInstance(instanceid).then(() => {
return res.status(200).json({message: `Instance id ${instanceid} deleted successfully.`});
})
});// defining the port number
const port = process.env.PORT || 8000;
app.listen(port, () => {
console.log(`Server is up and running on ${port}`);
});
Now, save this file and we are almost done. Open the command prompt and write this command:
node server.jsIf there is no error, the prompt will look like this.
Guys, It’s time to test our APIs. Here I’m using Postman to test that our API is working perfectly or not.
This is my dummy Table which I have used for my APIs.
Now, let’s test the first API by adding something to the database using the endpoint http://127.0.0.1:8000/api/add-instance.
Let’s get all the instances from the database using http://127.0.0.1:8000/api/instances.
Let’s use this endpoint http://127.0.0.1:8000/api/get-instance/4 for getting a single instance of a particular id.
Thank you for sparing time for this article. Hope you find this article helpful. All the Source files will be uploaded on GitHub, you can get them from there. I’ll share the link to the GitHub repository.
https://github.com/Mohit-15/A-Simple-RESTful-API-using-Nodejs-PostgreSQL.
