Node.js and PostgreSQL

In this post we will create a REST service using node.js and PostgreSQL database. First thing to do is to install PostgreSQL module using npm (Node Package Manager) if we don't have it yet as shown below.

Then we have to create an Eclipse project as shown in this article. Create two new folder controllers and routes. Inside controllers folder create a js file CustomerController.js, and CustomerRoutes.js inside routes folder. The project structure is shown below.

Now we need to modify server.js as shown below. Here we import express and body-parser package. We also import our own CustomerRoutes.js. Line 8-11 means we parse body part of a request as json and register our route CustomerRoutes.js. Line 13-15 means we return a string 'url not found' if a url doesn't exist. We will see an example later.

Now we will see the CustomerRoutes.js as shown below. Here we import our controller CustomerController.js. Here we have two url /customer and /customer/:id. The first url is bound to http get and post. The second url is bound to http get by id, put, and delete. We will see the logic in CustomerController.js.

We will now see the controller. Here we import pg module and create a connection pool as shown below in CustomerController.js

Next we will see the method to get all data from database and return them as json. Here we connect to database using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a query to get all customer data. We release the connection soon after query is run. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The next method we will see is the method to get customer by its id. Here we connect to database using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a query to get a customer data by specifying its id from request parameter. We release the connection soon after the query is run. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The following method is a method to insert data to PostgreSQL. First we get customer's first and last name from request body. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, INSERT statement, and lastly COMMIT statement. Actually, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single insert statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The method to update a customer data is shown below. First we get customer id from request parameter and customer data from request body. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, UPDATE statement, and lastly COMMIT statement. Same logic applies, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single update statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

And lastly we will see a method to delete a customer data. First we get the customer id from request parameter. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, DELETE statement, and lastly COMMIT statement. As mentioned, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single delete statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

Let's now run the project and see the result in Postman.

A request to get all customers is shown below.

Next we will create a new customer and then update it.

Now we will get the new customer and delete it.

Lastly, what if we request a wrong url? Below is the result.