Ahmed Abdulrahman's Desk

Prerequisites

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.

1CREATE ROLE resful_user WITH LOGIN PASSWORD '1234';
2ALTER ROLE resful_user CREATEDB;

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
2\q
3
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.

1CREATE TABLE todos (
2  ID SERIAL PRIMARY KEY,
3  title VARCHAR(255) NOT NULL
4);

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)
7
8restfull-api-db=>

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 |
7
8  Indexes:
9      "todos_pkey" PRIMARY KEY, btree (id)
10
11restfull-api-db=>

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)
8
9restfull-api-db=>

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