One of the most important aspect 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 part of communication 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 ExpressJS framework and connect it with PostgreSQL database.
To get started with this tutorial, knowledge of the following will be beneficial.
- Knoweledge 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.
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 temrinal window and type the following command.
https://gist.github.com/b21aa52575cf3fcfb3201f62880aa8d1
Once the installation is complete, to start the the postgresql database you will need to run the below command.
https://gist.github.com/08ce8df4238b29550b93e56c84245aca
After starting this service, you will get a success message like below.
(ss1)
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 and run the following set of commands to initialize an Express server.
https://gist.github.com/f18d53400eedd8d370cc1cfc0fef41d9
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.
(ss2)
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.
https://gist.github.com/4570aedc4f4a49d0076a29a4813eae3d
To now run the project we need to use npm run start command from now on.
Since you have installed PostgreSQL database, and its 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 Mappper) 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.
https://gist.github.com/b9f8783667a13f7ff6db583b5b38de73
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.
https://gist.github.com/27ebc55f8c8b725e80101b48fe11ecda
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 us run the initializing command in order to generate boilerplate code and necessary folders.
https://gist.github.com/9e2d23bc2ca1cd882cd7c29a42cfaf57
On running the above command successfully, you will get the following result.
(ss3)
You will also find changes made to your project directory like below. New files and folders created from the .sequelizerc file.
(ss4)
Next step is to install required dependencies in order to create a database connection with a PostgreSQL database. Fire up your terminal with the following command.
https://gist.github.com/d081a51aa9d79f5042977208a8232e51
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. First thing you need to do here is to create a local database instance. From the command line run the below command.
https://gist.github.com/ee8a3e22a116d79fc7b02f58dfd8f3e5
The above createdb command is made available to us when we install PostgreSQL database. Now, let us make some modifications to config.js file. We have to manually define the database instance we created. You can even setup the password to protect the database on your local machine. For this configuration I am going to leave it null. Also, you will have to change the database dialect to postgres.
https://gist.github.com/e708bcf1f5f43175c44c173328719f91
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.
https://gist.github.com/c3df9b6a9b506f9a04b98995eb2e4e5b
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.
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.
https://gist.github.com/988aa73e855fe3065f228946f5d17400
The above command will generate a new file inside models/todo.js. You can verify that the above command runs successfully with below image.
(ss5)
As you can notice, another file is created in migrations/ directory. Our concern at this time is the model file itself which looks like this.
https://gist.github.com/d5adc0f7ec6b958082313b07ab0647cd
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.
https://gist.github.com/d4ccbf099d96643a6e04e8e00c1d720a
This is how our second model inside todoitem.js looks like. It has two attributes, content of datatype string and complete of datatype boolean.
https://gist.github.com/7a97a877399d2913cab0ad0e781f8ef5
Remember few moments ago we discussed that both our models, 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.
https://gist.github.com/77a0cfd353a2e85847d0a64b8c8e2233
The first modification you will make is to add type to make attribute more verbose 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.
https://gist.github.com/b18c68e95f50e812baed0a62e68747c4
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.
Migrations in the database are 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.
https://gist.github.com/9fbc899ebb29f0d245d67ff0257e8eaf
Similar changes are made inside migrations/<date-time>-crrate-todo-item.js.
https://gist.github.com/97b48e439fcc66436d8d74972e50fa6e
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.
https://gist.github.com/dcc68008516875e3cda9a3ae22c0ed16
You will get a result like below indicating the success of the above command.
(ss6)
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.
https://gist.github.com/74f5f714e7143485847ef9c87a53e354
Creating models might seem overwhelming if you are doing it 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.
https://gist.github.com/b7dd5fb4e1371a1199e3f33b485ffabb
Then we are exporting the controller function inside which create function exists. This function will handle the business logic behind 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 the 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 us 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.
https://gist.github.com/6fb1e5de718572b91d63d72853d2a780
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 todo list can be send. Last step before the testing of this two new routes begin is to hook the routes inside server.js file.
https://gist.github.com/b71f46051ccfa732ed047c116f1c55af
To see if everything is working let us run the server by running npm run start and open your favorite REST API client like Postman or Insomnia to test the new routes. When you run the URL http://localhost:4000/api you will get the success message like below.
(ss7)
Next step is to create a new todo item. Run the URL http://localhost:4000/api/todos.
(ss8)
So far so good. Let us create a new controller and a route that will list all the todo lists in our database. Open controllers/todos.js. Add the below after create() function.
https://gist.github.com/d61d8c38641fb74264d05b721a113068
Now open routes/index.js and create the new route for this logic to run.
https://gist.github.com/3670969622fe602b42bc05eae33b8284
Open the REST client and visit the URL http://localhost:4000/api/todos.
(ss9)
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/.
https://gist.github.com/41ba35f9fccb07bdccca7026ee533242
Next step is to add the route for it inside routes/index.js.
https://gist.github.com/636d13462469d6d9f858f62bfbceba20
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 screnshots 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.
(ss10)
Now let us modify the list function in controllers/todos.js such that it returns the todo item along with the list name.
https://gist.github.com/04a8fb075728d9b886dd2ee373c89a29
To test this, run the URL http://localhost:4000/api/todos and you will get similar result like below.
(ss11)
Notice how the below snippet is added. This is done through the association methods we defined earlier when creating both of our models.
https://gist.github.com/cf17fbe698eba1501c9a4ef2995cea02
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.
https://gist.github.com/f5bd0631ef73ae7520a04440fe54f70c
Add the corresponding route inside routes/index.js.
https://gist.github.com/544fe3ea1440eebf3eb995e974e24b27
When you run the URL http://localhost:4000/api/todos/2 with HTTP DELETE request it will respond back like below.
(ss12)
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.
(ss13)
That's it! In this article you have 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 is right configuration to do that. 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 possibilites to go from here are endless. The popularity of PostgreSQL database among developers is at its peak.