Navigate back to the homepage

Create Secure Node.js and PostgreSQL RESTful API

In this tutorial, you’ll learn how to create your own RESTful API in a Node.js environment running on an Express server and utilizing a PostgreSQL database.

Ahmed Abdulrahman
Dec 12th, 2019 • ☕️ 2 min read

Photo by Taylor Vick on Unsplash


This guide uses installation instructions for macOS and assumes a prior knowledge of:

Set Up PostgreSQL Database

We are going to use Homebrew to install PostgreSQL on macOS. So, make sure you install it if you haven’t done so already. Next update the it’s dependencies by running:

1» brew update

Installing PostgreSQL

Next, install PostgreSQL on the command line:

1» brew install postgresql

It might take a little while to compile and install so be patient. Next, check your if PostgreSQL installed successfully:

1» postgres --version
2# postgres (PostgreSQL) 12.1

And then you we need to start Postgres database server as a background servic with the following command:

1brew services start postgresql

Create Our Database

We are going to create our restfull-api-db database that can be used for our application with the following command:

1» createdb restfull-api-db

In order to execute SQL statements, we need to login to our database using the psql command:

1psql restfull-api-db

Next, we create a user and password and give it create database access.


We need to log out of from the root user by exiting from psql shell and then log in again but this time with to the newly created user:

1# Logout from postgress shell by typing
4# Then login again
5# -d flag is for db name, -U for postgress user
6» psql -d restfull-api-db -U resful_user

Create a todos table with ID, and title.

3 title VARCHAR(255) NOT NULL

To get a list of all the tables in our database, we can use \dt command:

1restfull-api-db=> \dt
2 List of relations
3 Schema | Name | Type | Owner
4 --------+-------+-------+-------------
5 public | todos | table | resful_user
6 (1 row)

As you see The todos table is the only table in our database as we just created it. In case if you want to get a closer look at the structure of todos table like displaying Column Names and Data Types, you can use the following command \d table_name,:

1restfull-api-db=> \d todos
2 Table "public.todos"
3 Column | Type | Collation | Nullable | Default
4 --------+------------------------+-----------+----------+---------------------------------
5 id | integer | | not null | nextval('todos_id_seq'::regclass)
6 title | character varying(255) | | not null |
8 Indexes:
9 "todos_pkey" PRIMARY KEY, btree (id)

Next, we populate our todos table with data using the INSERT statement as follows:

1INSERT INTO todos (title) VALUES ('Design a prototype');
2INSERT INTO todos (title) VALUES ('Organize photo shot');
3INSERT INTO todos (title) VALUES ('Bring an umbrella');

To view the data we just inserted, we use a SELECT statement as follows:

1restfull-api-db=> SELECT * from todos;
2 id | title
3 ----+---------------------
4 1 | Design a prototype
5 2 | Organize photo shot
6 3 | Bring an umbrella
7 (3 rows)

Creating new Project

create a new folder there named todos-restful-api in root folder.

1» mkdir todos-restful-api

Initialize a new app.

We can initialize our app is using NPM or YARN*. To do so, first we need to cd into our project folder and type:

1» npm init
2# or
3» yarn init

Then it asks some questions to build package.json file for us:

1» yarn init
2 yarn init v1.19.2
3 question name (todos-restful-api):
4 question version (1.0.0):
5 question description: Node.js and PostgreSQL RESTful API
6 question entry point (index.js):
7 question repository url:
8 question author: Ahmed Abdulrahman
9 question license (MIT):
10 question private: true
11 success Saved package.json
12 ✨ Done in 50.58s.

Install Dependencies

To start with creating our express api, we are going to install the following dependencies:

  • Express: a Web Application Framework

  • PG - a PostgreSQL client for Node

  • Dotenv: a zero-dependency module that loads environment variables from a .env file into process.env

  • CORS: enable CORS

  • Nodemon - automatically restarts our server every time we make any change in the code.

  • express-rate-limit - limit repeated requests to endpoints

  • express-validator- string validators and santizers

  • compression - compression middleware

  • helmet - secure HTTP headers in an Express app

1yarn add express pg cors dotenv

Discuss on TwitterFollow @_ahmed_ab

Other things I've written

ADD vs COPY in Docker

In this post, we'll learn what are ADD and COPY commands in docker, How they differ, and why COPY is more secure than ADD.

Mar 17th, 2020 · 1 min read

Understand the concept of Images and Containers

A step by step guide to understanding the concept of Images and Containers in Docker

Feb 13th, 2020 · 2 min read

© 2018–2020
Ahmed Abdulrahman


Link to $ to $ to $