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.
To get started with this tutorial, knowledge of the following will be beneficial.
- 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.
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.
Create an empty directory and run the following set of commands to initialize an Express server.
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
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
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.
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
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.
createdb command is made available to us when we install the PostgreSQL database. Now, let us make some modifications to the
config.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 to
null. Also, you will have to change the database dialect to
You can change the aforementioned values for all three:
production but do make a notice, for now, we are going to use
development. This is done in
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 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
This is how our second model inside
todoitem.js looks like. It has two attributes,
content of datatype
string and complete of datatype
Creating the Database Relationship
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
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
as attribute next to
foriegnKey means that on each query for a todo, it will include the todo items for the above id.
First, you are going to edit both attributes
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
Similar changes are made inside
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
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
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
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
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.
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
routes/index.js and create the new route for this logic to run.
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
Next step is to add the route for it inside
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
10. If everything is going right for you, this may differ. You will have
IDs starting from
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
Add the corresponding route inside
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.
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 👇
How To Build a REST API With Nodejs and Postgresql Demo - amandeepmittal/rest-api-node-postgresqlgithub.com
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.