Building RESTful Api With Node.js, Express.Js And PostgreSQL the Right way

Blessing krofegha
ITNEXT
Published in
18 min readJan 28, 2020

After my article about “Generating a Pdf with Nodejs and Cloudinary” i got few feedbacks from folks requesting that i write an article on Building RESTful Api’s with NodeJs. So i decided to put this piece with the hope that it will help somebody.

What we will build?

You’ll learn how to build a public bus transportation booking API — Such that Users book tickets for their trips, can see all their bookings and have the right to cancel any booking.

The only impossible journey is the one you never begin. 🙌— Tony Robbins

Table of Content

  • Getting Started
  • Prerequisite
  • Project Setup
  • Project Structure
  • Setting up Database(PostgrelSQL)
  • Server Helpers
  • Authentication & Authorization with JWT
  • Setup Controllers
  • Setup Routes
  • Server Setup
  • Testing with POSTMAN
  • Conclusion

Getting Started

In this post, I’ll explain how to connect and use PostgreSQL Database to store data. We will also learn how to write some basic [SQL](Structure Query Language) queries. SQL is a standard language for storing, manipulating and retrieving data in databases. To make this simple and so that we can have a better understanding of how SQL works, I’m not going to make use ORM(Object Relational Mapping) in this post. Check out this answer on stackoverflow to get a better understanding of ORM. ORM is basically a technique that allows us to query and manipulate data from the database with little knowledge of SQL. ORM packages expose some methods needed to query and manipulate the data in the database. We have several ORM packages that could be used with our database e.g Sequelize. Instead of using an ORM, we will use PG NodeJS package directly — PG is a NodeJs package for interfacing with the PostgreSQL database. Using PG alone will also give us the opportunity to understand some basic SQL queries as we will be querying and manipulating data in the DB using raw SQL queries.

Prerequisite

Although I’ll try to keep things simple. However, having some basic Knowledge about Nodejs before diving into this Tutorial is important, this post assumes that you have Nodejs and Npm installed in your PC, if you don’t kindly follow this guide.

Project Setup

  • Create a new project directory on your system, you can call ittransportApi
  • Change working directly to the project and run npm init on your terminal or command prompt if you're using a window system - Running npm init will prompt you with some questions to help set up your project

When that is done, you should see package.json file in your project and it contains basic information about your project.

{"name": "transportapi","version": "1.0.0","description": "","main": "server.js","scripts": {"test": "echo \"Error: no test specified\" && exit 1"},"author": "Beveloper","license": "ISC"}

Installation of all dependencies for the project:

We are going to install the required dependencies needed for this project no fancy external packages e.g Morgan e.t.c.

  • expressjs — Expressjs is a nodejs web application framework that gives us the ability to create quick and easy APIs.
  • PG— PG is a NodeJs package for interfacing with the PostgreSQL database.
  • JSON Web Token (JWT)— is an open standard for securely transmitting information between parties as a JSON object. It’s pronounced jot, or as our Dutch friends would say, yaywaytay. JWT is commonly used for authorization. JWTs can be signed using a secret or a public/private key pair. Once a user is logged in, each subsequent request will require the JWT, allowing the user to access routes, services, and resources that are permitted with that token.
  • Body Parser — Parse incoming request bodies in a middleware before your handlers, available under the req.body property.
  • Moment — Is a lightweight JavaScript date library for parsing, validating, manipulating, and formatting dates.
  • Dotenv — Is a zero-dependency module that loads environment variables from a .env file into process.env
  • CORS (Cross Origin Resource Sharing) — Is a node.js package that allows requests to skip the Same-origin policy and access resources from remote hosts.
  • Bcryptjs — is a secured way to help you hash passwords.
  • @babel/polyfill — is a toolchain that is mainly used to convert ECMAScript 2015+ code into a backwards compatible version of JavaScript in current and older browsers or environments.
  • babel watch — This is needed for development. One thing that babel watch package does is to compile our code and reload the server each time we make changes to our code.

Run the following command to install all the above packages

$ npm install --save express pg moment body-parser dotenv jsonwebtoken cors make-runnable bcryptjs @babel/polyfill npm-run-all $ npm install --save-dev babel-core babel-cli babel-preset-env babel-watch babel-preset-es2015 babel-register

If all went well, you should see something similar to this

You’ll notice that express and moment etc is under dependencies, that is because those are needed by the time we deploy our code to production. babel-cli, babel-preset-env and babel-watch etc are only needed during development.

Project Structure

Install PostgreSQL on your system. If you don’t have it installed on your PC walk through this guide

Set up your project structure using the following format;

TransportApi |-app
|-controllers
|-db
|-helpers
|-middlewares
|-routes
|-node_modules
|-.babelrc
|-.env
|-env.js
|-package-lock.json
|-package.json
|-server.js

Set Up Database

Lets create our DB and name it transportApi. To create the DB, we can make use of any PostgreSQL client such as POSTICO for Mac Users or PgAdmin4 for window users.
If you need a guide on out to setup PostgrelSQL on your PC checkout this easy to read guide

Create TransportApi Table

First, let save our Database URL in the system environment. Your Database URL should be in the following format
postgres://{db_username}:{db_password}@{host}:{port}/{db_name}
e.g postgres://transportUser:develop@localhost:5235/transport

If you were unable to set your DB locally, the easy way out is to make use of any PostgreSQL cloud DB such as ElephantSQL. If you use cloud PostgreSQL DB, copy the URL and use it instead.
My port may differ from yours, so ensure you use the correct p.

ENVIRONMENT VARIABLE SETUP

Open the .env file located in the root directory, copy and paste the codes into it.

#.envDATABASE_URL=postgres://transportUser:develop@localhost:5235/transportPORT=5253
  • We defined a few environment variables (DATABASE_URL AND PORT) so we can use them later across our project.

Next, we need to create transportApi table in our DB. This is where we will start making use of pg package with basic SQL query to create the table.

Inside our db folder create a folder called dev and create three files named dbConnection.js, dbQuery.js and pool.js respectively.

The files should look like this.

Code Steps:

  • Import Pool object from pg. Check pg documentation to read more on pooling. We use this to connect to our PostgreSQL Db. Pool is the easiest and common way of using pg. You can also make use of their Client API to connect to the DB.
  • Import dotenv from dotenv and load it using dotenv.config() - what this does is to search for .env file in our project and load its content into the system environment so we can use node process.env to access those variables.
  • We create a new instance of Pool and pass in connectionString to its constructor. We use process.env.DATABASE_URL to get DATABASE_URL variable from the system environment.
  • We listened to pool connect event and console log connected to the db

Code Steps:

  • We created a new method query that takes in two arguments text - query text and params - values required by text. These two arguments are what is needed to query the DB. The method returns a promise and we will call it in our controller. Click here to read more about JavaScript Promise.

Code Steps:

  • Import Pool object from pg. Check pg documentation to read more on pooling. We use this to connect to our PostgreSQL Db. Pool is the easiest and common way of using pg. You can also make use of their Client API to connect to the DB.
  • We listened to pool connect event and console log connected to the db
  • We set up a createTables() function, inside the function is a query that creates userTbale, busTable, tripTable and bookingTable respectively with unique fields.
const createUserTable = () => {   const userCreateQuery = `CREATE TABLE IF NOT EXISTS users   (id SERIAL PRIMARY KEY,   email VARCHAR(100) UNIQUE NOT NULL,   first_name VARCHAR(100),   last_name VARCHAR(100),   password VARCHAR(100) NOT NULL,   created_on DATE NOT NULL)`;

What the above does is to tell PostgreSQL DB to create users table if users table does not exist with fields listed and the same goes for the rest of the tables respectively.

Using the first Table(usersTable) as Case study, we observe the following;

  • We call pool query method with userCreateQuery as an argument and it returns a promise.
    We called pool.end() to close pool connection to the db.
  • We created another function called dropTables() - What this does it to delete users table.
    We set up a new query DROP TABLE IF EXISTS users, that drops users table if it exists in the DB.
  • We use pool.on('remove') to listened to pool remove event and use process.exit(0) to exit the node process.
  • Lastly, we require make-runnable package - We need this to be able to call and any of our two functions from the terminal.
    Note: You have to require make-runnable at the end. Remember we’d earlier installed make-runnable as a project dev-dependency.
    You'll also notice that we used require instead of import, this is because we only want to run db.js file from the terminal alone and it is not directly part of our project so there is no point in compiling it.
    Finally, lets run createTables function to create our tables. To do this we need to add a command in our Package.json file.

In your start script remove the test command and add this

"scripts": {"create-dev-tables": "babel-node ./app/db/dev/dbConnection createAllTables","start": "nodemon --watch . --exec babel-node -- server","setup": "npm-run-all -p start create-dev-tables"},

Your package.json should know look like this

Next, we run the command $ npm run setup

After running the above, you should see something similar below;

NOTE: Instead of going through the above process, you can also create tables directly either using any PostgreSQL client e.g pgAdmin, POSTICO or using PostgreSQL commands on the terminal.

SETUP HELPERS

Before diving to the core project, there’s need to create a few helper methods, that would foster resuability across our project.

Copy the following and paste it inside app/helpers/validations.js

Code Steps:

  • isValidEmail() method uses regex(Regular Expression) in validating user's provided email address.
    Check this guide to learn more about Regex
  • validatePassword — This method checks if the length of the password provided by a user at the time of signup or signin is less than or equal to 5 characters.
  • isEmpty method checks if any input provided by a user is undefined or empty.
  • We exports this methods so we can use them across our projects.

Copy the following and paste it inside app/helpers/status.js

Code Steps:

  • Created an Object status and defined a few properties with values.
  • Success — This returns a status code of 200 when a user performs a post request
  • Error — This returns a status code of 500 when a user encounters a glitch
  • Notfound — As the name implies when a user tries to perform an undefined request a status code of 404 is returned, another name for forbidden😜.
  • Unauthorized — Such status occurs when a user accesses a path/page without been authorized, in this case a status code of 401 is returned.
  • Created — When data is created, a status code of 201 is returned.
  • Bad — A good use case is when an invalid email is provided, a status code of 400 is returned.
  • Nocontent — When a user submits an empty form, then a status code of 400 is returned.
  • Conflict — A good use case is when a user inputs an email that already exists. A 409 status code should be returned.
  • We exports this methods so we can use them across our projects.

AUTHORIZATION AND AUTHENTICATION(JWT)

The joy of JWT

As we proceed in thus tutorial our project would have a few routes that need protecting and some user’s that need authorizing. Much like myself at one point, you’re probably wondering how this can be achieved. Thankfully, we have JSON Web Tokens (JWT) (among other things) for that.

JSON Web Token (JWT) is an open standard that defines a compact and self-contained way of securely transmitting information between parties as a JSON object. This information can be verified and trusted because it is digitally signed.

Authorization is the most common scenario for using JWT. Once the user is logged in, each subsequent request will include the JWT, allowing the user to access routes, services, and resources that are permitted with that token. Single sign-on is a feature that widely uses JWT nowadays, because of its small overhead and its ability to be easily used across different domains.

There are some advantages of using JWT for authorization:

  1. Purely stateless. No additional server or infra required to store session information.
  2. It can be easily shared among services.

In its compact form, JSON Web Tokens consist of three parts separated by dots (.), which are:

  • Header
  • Payload
  • Signature

Therefore, a JWT typically looks like the following.

xxxxx.yyyyy.zzzzz

Now, time for some code.

AUTHENTICATION
jwt.sign()

jwt.sign(payload, secretkey, [options, callback])

The first function jwt.sign() will generate a JWT token, assign it to a user object, and then return that JWT token so we can pass it where ever we may need. It can be either asynchronous or synchronous depending if a callback is supplied. The payload parameter will be the user object in our case, the secretkey is made up by you, and it can be anything. The callback parameter is where we handle sending our token, and the options parameter will be where can set an expiration time among other things.

Copy the following codes and paste it app/helpers/validations.js

Code Steps:

Inside validation.js we have a function generateUserToken

  • We imported jsonwebtoken(JWT) and dotenv from their respective modules.
  • We created a function generateUserToken , we used jwt.sign(..) in signing user's token, by sending email, user_id, is_admin(will be discussed later ), first_name and last_name as the payload, we also passed our secret and setting token to expire in 3 days. .

AUTHORIZATION
jwt.verify()

jwt.verify(token, secretkey, [options, callback])

jwt.verify() will verify the users token when a protected route is accessed. It takes in the token as one parameter, the secret key that you defined in the jwt.sign() function, and then you have the options and callback parameters. The callback will be where we can access and send protected data.

Copy the following and paste it app/middleware/verifyAuth.js

Code Steps:

Here, we create a new Auth object with verifyToken() method. What verifyToken() method does is basically to validate and decode user request token using the same secret key we used in signing the token. We used const{token} = req.headers to get the token from the request header and send it to jwt.verify(..) along with the secret we used in signing the token. If the token is valid, we retrieve email, user_id, is_admin,first_name, last_namefrom the token payload and query the DB to make sure the user exists in the DB. If the user exists in the DB, we created a new object property in the req object. We will use this to process other requests handler. Finally, since this method is a middleware, we used next() in moving to the next request handler. If any error occurred in here, we return an error message back to the user without having to move to the next request handler.

SETUP CONTROLLERS

Next, we need to setup our controllers for our projects and starting with is our admin and User controller.

adminController

Copy the following and paste it inside app/controllers/adminController.js

Code Steps:

Note: There are a few routes that needs an admin privilege to access

  • Here, we created two methods — createAdminand updateUserToAdmin. We also made use of async/await.
  • we Import moment, and the reason for that is because we’re going to be using it for date creation.
  • We imported our dbQuery from app/db/dev/dbQuery.js.
  • we also imported our status helper methods empty and errorMessage,successMessage, statusfrom app/helpers/status.js.
  • we also imported our validations helper methods hashPassword and comparePassword, isValidEmail, validPassword, isEmpty, generateUserTokenfrom app/helpers/validations.js.
  • We created a function createAdmin , in it we deconstructed our values email, first_name, last_name, is_admin, password from the request body.
  • Next, we created a variable named created_on, which holds the date of our created user table.
  • Next we check if our email is a valid, using our helper method isValidEmail.
  • Also, we check if our password characters is more than 5.
  • Using our helper method hashPassword , we hash our password using the bcryptjs module.

usersController

Copy the following and paste it inside app/controllers/usersController.js

Code Steps:

  • Here, we created two methods — createUserand signUser. We also made use of async/await.
  • we Import moment, and the reason for that is because we’re going to be using it for date creation.
  • We imported our dbQuery from app/db/dev/dbQuery.js.
  • we also imported our status helper methods empty and errorMessage,successMessage, statusfrom app/helpers/status.js.
  • we also imported our validations helper methods hashPassword and comparePassword, isValidEmail, validPassword, isEmpty, generateUserTokenfrom app/helpers/validations.js.
  • We created a function createUser , in it we deconstructed our values email, first_name, last_name, password from the request body.
  • Next, we created a variable named created_on, which holds the date of our created user table.
  • Next we check if our email is a valid, using our helper method isValidEmail.
  • Also, we check if our password characters is more than 5.
  • Using our helper method hashPassword , we hash our password using the bcryptjs module.
  • Next, we created a variable named created_on, which holds the date of our created bus table.
  • For our SQL query, we used INSERT INTO users(list_columns_here..) VALUES($1, $2, $3, $4, $5...) - what this does is create a new row in users table and insert the supplied values into its fields. values is an array of values that contains what we want to insert into the table. The elements inside values array must be in the same order as $1, $2, $3, $4, $5. We used returning * to return the created row. Remembered we created query method that takes in two arguments quertText and params inside app/db/dev/dbQuery.js, this is where we will use it. We called the method and send in createUserQuery and values as parameters.
  • we made use of try and catch, i 💕 it.
  • Using generateUserToken helper method we generated our user token.
    JWT will be discussed later in this post.
  • _bt_check_unique — Checks for duplicate values and returns an error.

Since dbQuery.query returns a promise we make use of async/await to make our code looks sexy😘.

  • signUser - We set up SELECT * FROM users WHERE email = $1to get a user with a specific email.
  • Similar to createUser function, it checks for if email and password is incorrect, it also compare the passwords and ensures that both password and email inputed by the user are correct before signing them.
  • Also, a user gets a Token, when he signs in. Thats made possible by our generateUserToken.
  • And finally we exported our methods createUser and signUserrespectively, so we can use them across our project.

busController

Copy the following and paste it inside app/controllers/busController.js

Code Steps:

  • Here, we created two methods - addBusDetails()and getAllBuses(). We also made use of async/await.
  • we Import moment, and the reason for that is because we’re going to be using it for creating our date.
  • addbusDetails() - We imported our dbQuery from app/db/dev/dbQuery.js.
  • we also imported our helper methods empty and errorMessage,successMessage, statusfrom app/helpers/validations.js. and app/helpers/status.js respectively.
  • We created a function addBusDetails() , in it we deconstructed our values number_plate, manufacturer, model, year, capacity from the request body.
  • Next, we created a variable named created_on, which holds the date of our created user table.
  • We also check to see if the input fields are empty using our empty method, if its empty it sends an error “all fields are required” and returns a status code of 400.
  • For our SQL query, we used INSERT INTO bus(list_columns_here..) VALUES($1, $2, $3, $4, $5, $6 ...) - what this does is create a new row in bus table and insert the supplied values into its fields. values is an array of values that contains what we want to insert into the table. The elements inside values array must be in the same order as $1, $2, $3, $4, $5, $6. We used returning * to return the created row. Remembered we created query method that takes in two arguments quertText and params inside app/db/dev/dbQuery.js, this is where we will use it. We called the method and send in createBusQuery and values as parameters. Since dbQuery.query returns a promise we make use of async/await to make our code looks sexy😘.

tripController

Next, we’d be creating our tripController, similar to the bus Controller.

Copy the following and paste it inside app/controllers/tripController.js

Code Steps:

The tripController is almost similar to that of the BusController earlier created, but with few additions

  • Here, we created two methods — createTrip(), getAllTrips()cancelTrips, filterTripByOrigin, filterTripByDestination.
  • CancelTrips — We set up UPDATE trip SET status=$1 WHERE id=$2 returning*to cancel trips according to trip ID.
  • filterTripByOrigin — We set up SELECT * FROM trip WHERE origin=$1 ORDER BY id DESC to filter the origin of a users trip from the descending order .
  • filterTripByDestination — We set up SELECT * FROM trip WHERE destination=$1 ORDER BY id DESC to filter the destination of a user trip from the descending order .

bookingController

Next, we’d consider the bookingController which seems to be an important part of our transportApi.

Copy the following and paste it inside app/controllers/bookingController.js

Code steps:

The bookingController is almost similar to that of the BusController earlier created, but with few additions

  • Here, we created four methods — createBooking(), getAllBooking, deleteBooking, and updateBookingSeat.
    The first two methods createBooking and getAllBooking are same with the previous explanations in our busController.
  • DeleteBooking — We set up UPDATE trip SET status=$1 WHERE id=$2 returning*to cancel trips according to trip ID.
  • UpdateBookingSeat— We set up UPDATE booking SET seat_number=$1 WHERE user_id=$2 returning*to update bookings according to the users ID and the seat number of a user, during booking.

If you have gotten this far, you deserve a thumbs up!

SETUP ROUTES

Next, we’d consider setting up our routes for every individual controller created.

BusRoutes

Copy the following and paste it in app/routes/busRoute.js.

Code Steps:

  • We imported express.
  • Next, we imported all methods created in our busController in app/controllers/busController.js.
  • We declared a variable(router), that holds our express router.
  • We defined our routes using our router variable and passing request methods(POST, PUT,PATCH,GET) with matching routes e.g
    router.post('/buses', addBusDetails), router.get('/buses', getAllBuses)etc.

NOTE:

  • Although the routes are same but the request methods differs, therefore handles different request.
  • The same pattern applies to all routes in this tutorial
  • Each request to any of the endpoint will pass through verifyAuth middleware before processing the actual request. With this new setup, a user without a valid token will get an error.

BookingRoutes

TripRoutes

SETTING UP OUR SERVER

Now that we’ve our Controller and Route setup, lets see how to put everything together.

Copy the following codes and paste it in app/server.js.

Code Steps:

.We installed babel-polyfill npm package and imported it — We need this here so that node runtime will recognize async/await and Promise.

.We imported the necessary packages from their respective modules

.Next, we imported all the routes from our routes folder

.Adding cors middleware for parsing URL encoded bodies (which are usually sent by browser)

.Express.urlencoded is added as a middleware for parsing JSON and urlencoded data and populating `req.body`.

.Next, we versioned our Api '/api/v1', userRouteand appended the respective routes.

.Lastly we listened to our port so we can start the server.

TESTING WITH POSTMAN

Create User — POST api/v1/signup

Create Admin— POST api/v1/admin/signup

Copy Token and put it in the Header

Create Buses— POST api/v1/buses

Get Buses— GET api/v1/buses/

Create Trip— POST api/v1/trips

Get Trips— GET api/v1/trips

Cancel Trips — PATCH api/v1/trips/:tripId

Create Booking— POST api/v1/bookings

Get Bookings— GET api/v1/bookings

CONCLUSION

We just successfully built our Transport Backend App.
If you came thus far congrats, ✌.
Surely, you’ve got questions or issues as you went through this tutorial, kindly drop your comments and you’d be responded to ASAP.
Once again thanks for reading, Clap, and share!👌

Follow me on Twitter let’s discuss.

Check out the complete code here

Resources

  1. Why You as a Developer Should be Using a CMS
  2. Headless Commerce Explained: Definitions, Use Cases, and Roadblocks | Agility CMS
  3. APIs vs. SDKs: What’s the Difference? | Agility CMS
  4. TOP Static Site Generators to Watch in 2021
  5. https://agilitycms.com/resources/posts/what-s-the-difference-between-headless-cms-and-static-site-generator/
  6. What is Netlify and Why Should You Care as an Editor? | Agility CMS
  7. What is a Headless CMS? | Agility CMS
  8. Benefits of Headless CMS vs a Traditional CMS | Agility CMS
  9. Content Architecture: The Key to Organizing and Managing Your Content and Team
  10. WordPress And Its Alternatives: Headless CMS | Agility CMS
  11. Open Source CMS vs. Proprietary CMS: Can You Have the Best of Both? | Agility CMS
  12. Choosing a React CMS: What to Look For? | Agility CMS
  13. Take control of your Content Architecture: Content Modelling
  14. DXP vs Headless CMS: Modern DXP Architecture
  15. Jamstack Development: 10 JAMstack Pioneers You Need To Know About in 2021

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Published in ITNEXT

ITNEXT is a platform for IT developers & software engineers to share knowledge, connect, collaborate, learn and experience next-gen technologies.

Written by Blessing krofegha

I write codes and articles for a living, you want my attention? Send me food 😉

Responses (11)

What are your thoughts?