How To Build a REST API With Nodejs and PostgreSQL

Create a REST API using Node.js as the back-end server with help from ExpressJS framework and Sequelize, and connect it with a PostgreSQL database.

One of the most important aspects of being a Web Developer is to know how to work with APIs.

Whether building your own application as a front-end or back-end engineer, you are going to come across them. APIs are the core communication system behind the different software systems including the server, the database and the client-side of an application.

In this tutorial, we are going to create a REST API using Node.js as the back-end server with help from ExpressJS framework, and connect it with PostgreSQL database.

Prerequisites

To get started with this tutorial, knowledge of the following will be beneficial.

  • Knowledge of some of the ES6 JavaScript features
  • Node.js and npm installed on your local dev machine
  • PostgreSQL installed

If you do not have PostgreSQL installed on your local development machine, you can continue reading the next step where I will walk you through step by step installing it. If you already have it, please skip the next step.

Building with Node? Try out the Crowdbotics application builder to instantly scaffold and deploy a Node applications.

Installing PostgreSQL

PostgreSQL is a powerful and an open source object-relational database. If you are on a Windows machine, PostgreSQL offers an installer.

On macOS, I am going to use Homebrew to install it. Open a terminal window and type the following command.

Once the installation is complete, to start the PostgreSQL database you will need to run the below command.

After starting this service, you will get a success message like below.

Note: Later on, when you need to stop the postresql service, you can run the command brew services stop postgresql.

Getting Started

Create an empty directory and run the following set of commands to initialize an Express server.

Create a server.js file which is going to be the entry point for our server. At the top, we are going to require the express module and add some configuration middleware functions to handle req.body data. To run the server, type the command node index.js and go to URL http://localhost:4000/ in a browser window and you will get the following result.

With that working, we need a way to restart the server every time we change something in our code. I am going to use nodemon which will automatically watch for changes in any .js file we make in our demo app. To install run npm i -D nodemon and add the following to your package.json file.

To now run the project we need to use npm run start command from now on.

Setting up Sequelize

Since you have installed PostgreSQL database, and the service is up and running, we can move on to the next step that is to make a connection between the ExpressJS server and PostgreSQL. In order to do that, you will need an ORM (Object Relational Mapper) to provide us an interface for both the connection and the API. For our demo, we are going to use Sequelize. Let us begin by installing it.

The next step is to create a .sequelizerc file in the root of our project. This is going to be the configuration file that contains the specific paths required by Sequelize module. It will help us generate folders and files necessary for sequelize to work.

Notice that we are making use of path module from Node's core API in this process. The path.resolve() method resolves a sequence of paths or path segments into an absolute path. If no path segments are passed, path.resolve() will return the absolute path of the current working directory and in our case, the current working directory is going to be the root of our project.

Now, let’s run the initializing command in order to generate the boilerplate code and necessary folders.

When you run the above command successfully, you will get the following result.

You will also find changes made to your project directory like below. New files and folders created from the .sequelizerc file.

Making Database Connection

The next step is to install the required dependencies in order to create a database connection with a PostgreSQL database and have access to the database for CRUD operations. Fire up your terminal with the following command.

The package pg is responsible for creating the database connection with our Express server and pg-hstore is for serializing and deserializing JSON data into the PostgreSQL's hstore format. hstore data format stores information in key/value pairs within a single PostgreSQL value. To read more about what exactly hstore is or how it works, you can pause here and give the official documentation a glimpse.

The two files that are being used in order to create the database connection in our project are config/config.js and models/index.js. The first thing you need to do here is to create a local database instance. From the command line run the below command.

The above createdb command is made available to us when we install the PostgreSQL database. Now, let us make some modifications to theconfig.js file. We have to manually define the database instance we create. You can even set up the password to protect the database on your local machine. For our demo, I am going to leave this configuration field tonull. Also, you will have to change the database dialect to postgres.

You can change the aforementioned values for all three: development, test, and production but do make a notice, for now, we are going to use development. This is done in models/index.js.

When you deploy your application and use an online hosted database instance, you will need to change const env = process.env.NODE_ENV || 'development'; with the database URL you get. With this, our setup is complete.

Creating the Database Model

In this section, you are going to create two models: Todo and TodoItem. The Todo is going to be the list of one or many TodoItem. In terms of traditional SQL database, you can say that Todo will have a relationship with TodoItem of one-to-many. We are going to sequelize command line interface to generate the boilerplate code for our models.

The above command will generate a new file inside models/todo.js. You can verify that the above command runs successfully with below image.

As you can see, another file is created in migrations/ directory. Our concern at this time is the model file itself which looks like this.

This file is exporting a model called Todo which has a single attribute title as type string. Let us now create our next model for TodoItem.

This is how our second model inside todoitem.js looks like. It has two attributes, content of datatype string and complete of datatype boolean.

Creating the Database Relationship

Todo and TodoItem are going to have one-to-many-relationship. This done by associating both the models and define a custom class method. Let us modify both the model files to add this and some modification in each attribute for things to work our way. Open todo.js file.

The first modification you will make is to add type to make the attribute easier to read. Next, inside Todo.associate class method we are defining a relationship between both our models using hasMany method. Notice how it intakes models parameter. The foriegnKey, in this case, the id of each TodoItem. The as attribute next to foriegnKey means that on each query for a todo, it will include the todo items for the above id.

Now, open todoitem.js.

First, you are going to edit both attributes content and complete. With complete a default value is now has been added. Having a default value will allow the database to provide a value for the particular field. The onDelete tells the database to delete the whole todo item when from the associated todo it belongs too.

Running the Migrations

Migrations provide a clearer picture of what our database models going to look like inside. Since you are making changes in the generated attributes and adding fields like in our case, defaultValue, you have to edit the migration files for each specific model wherever necessary.

Running migrations take care of creating the database table and associated column inside the table for us. This is the functionality of each up function in both migrations files. There is also a down function that is only to run when you need to undo the changes inside the database table for some reason. Open inside migrations/<date-time>-create-todo.js file.

Similar changes are made inside migrations/<date-time>-crrate-todo-item.js.

In the above file, you are also defining the relationship between both the models. The todoId field is not going to be generated by Sequelize automatically until we define it above inside migrations/<date-time>-crrate-todo-item.js. Let us run the migration command and generate these models in our database. Open your terminal and execute the below command.

You will get a result like below indicating the success of the above command.

Please Note if the above command throws an error such as ERROR: role "root" does not exist. This means you do not have a username created already for postgres. You will have to run the following command and can follow the official documentation here for information.

Building the API

Creating models might seem overwhelming if you are doing it the first time but if you have followed closely so far, you will have no problem going through the whole process next time. In this section, we are going to start building our API. You are going to create your first controller for todos inside a new directory and file controllers/todos.js. We start by requiring the todos model.

Then, we are exporting the controller function inside which create function exists. This function will handle the business logic behind the route handler that we are going to define soon for creating a new todo in our database. It accepts two parameters, req for incoming requests and res to send the response back to an individual incoming request. On success, .then() function will trigger and send back the todo item with an HTTP status of 201. If an error is encountered, .catch will return the error with an HTTP status of 400.

You can think of each todo we create here as the name of a list of items. Right now we only are defining the business logic of creating the name for each list. Let’s complete this process and test with a REST client to see if everything works. Now we are going to connect this controller to the desired route. Create a new folder called routes and inside it a file called index.js.

In the above file, there are two routes being defined. One is /api which displays the welcome message and using app.post('/api/todos) where the request for creating a new to-do list can be send. Last step before the testing of this two new routes begin is to hook the routes inside server.js file.

To see if everything is working, let’s run the server by running npm run start and open your favorite REST API client like Postman Abhinav Asthana or Insomnia REST Client to test the new routes. When you run the URL http://localhost:4000/api you will get the success message like below.

Next step is to create a new todo item. Run the URL http://localhost:4000/api/todos.

So far so good. Let us create a new controller and a route that will list all the to-do lists in our database. Open controllers/todos.js. Add the below after create() function.

Now open routes/index.js and create the new route for this logic to run.

Open the REST client and visit the URL http://localhost:4000/api/todos.

API for Todo Items

Since the API is responding to the logic we have written behind it, you can continue to build it. In this section, you are going to create an individual item and add it to a specific list. Start by creating a file todoitem.js inside controllers/.

Next step is to add the route for it inside routes/index.js.

The last step is to test this API endpoint. Run the URL http://localhost:4000/api/todos/2/items. Do note that the 2 in this URL is the id of the list that will associate this todo item to a todo list. From earlier images, you can note that I had three lists with IDs: 2, 9 and 10. If everything is going right for you, this may differ. You will have IDs starting from 1.

Now let us modify the list function in controllers/todos.js such that it returns the todo item along with the list name.

To test this, run the URL http://localhost:4000/api/todos and you will get similar result like below.

Notice how the below snippet is added. This is done through the association methods we defined earlier when creating both of our models.

Deleting Todo Lists

The last API endpoint we require is to delete a todo list which will further delete all items inside it as todoItems. Open controllers/todos.js.

Add the corresponding route inside routes/index.js.

When you run the URL http://localhost:4000/api/todos/2 with HTTP DELETE request it will respond back like below.

This can also be verified by running http://localhost:4000/api/todos. In response, you will not see the same todo list we just deleted.

Conclusion

That’s it! In this article, you learned about using PostgreSQL as a database and using ORM tools like Sequelize, and how to migrate data models into database properly. We learned how to connect PostgreSQL database and what the correct configuration to do so. Also, we created a REST API using Express as the back-end server framework. Our REST API can be improved with better error handling and form validation. The possibilities from here are endless. The popularity of the PostgreSQL database among developers is at its peak.


You can find the complete code for this tutorial in the Github Repository below 👇

Starting a new Node.js project, or looking for a Node developer?

Crowdbotics helps business build cool things with Node (among other things). If you have a Node project where you need additional developer resources, Crowbotics can help you estimate build time for given product and feature specs, and provide specialized Node developers as you need them.

Also, try out the Crowdbotics application builder to instantly scaffold and deploy a node app.