Building a simple API with Nodejs, Expressjs and PostgreSQL DB - 2

"There is always one more bug to fix." Ellen Ullman

This tutorial is a continuation from where we stopped from the last post. If you have not read my previous post, please do. Click here to read my post on Building a simple API with Nodejs, Expressjs, and JS Data structure to persist data.

In this post, I'll explain how to connect and use PostgreSQL Database to store input data as opposed to JavaScript Object datatype we used in part 1. 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. SQL is basically the only language you can use in querying data in the database.

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 or zero 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.

Getting Started

Install PostgreSQL on your system. To check if progress was installed successfully, run which psql from the terminal

Now, let's make some tweak to our project structure. Create two new folders in src - usingJSObject and usingDB. Move the previous folders in src to usingJSObject folder.

Next, create two new folders inside usingDB - controllers and db. Create Reflection.js js file in controller folder and index.js in db folder.

Next, create db.js and .env in the project root directory.

Your new project structure should look like the following

project
 |-src
   |-usingDB
     |-controllers
       |-Reflection.js
     |-db
       |-index.js
   |-usingJSObject
     |-controllers
       |-Reflection.js
     |-models
       |-Reflection.js
 |-.babelrc
 |-.env
 |-db.js
 |-package.json
 |-server.js

Lastly, we need to install PG and dotenv(we need this to load our environment variables from .env) packages.

Run the following command to install the two packages

$ npm install --save pg dotenv

Set Up Database

Create Database
Let create our DB and name it reflection_db. To create the DB, we can make use of any PostgreSQL client such as POSTICO and we can also create the DB directly from the terminal using createdb command.

  $ createdb reflection_db

use \l to check the list of available DB from the terminal

  $ psql
  -# \l

you should see something similar to this

Create Reflections 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://olawalequest@127.0.0.1:5432/reflection_db.

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.

Save the DB URL in .env file located in the root directory.

#.env
DATABASE_URL=postgres://olawalequest@127.0.0.1:5432/reflect_db

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

Copy and paste the following into db.js in the project directory

alt

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
• We set up a createTables() function, inside the function is a query that creates reflections table with the following fields; id - UUID datatype, success - TEXT datatype, low_point - TEXT datatype, take_away - TEXT datatype, created_date - TIMESTAMP datatype and modified_date - datatype.

  const queryText =
    `CREATE TABLE IF NOT EXISTS
      reflections(
        id UUID PRIMARY KEY,
        success TEXT NOT NULL,
        low_point TEXT NOT NULL,
        take_away TEXT NOT NULL,
        created_date TIMESTAMP,
        modified_date TIMESTAMP
      )`;

What the above does is to tell PostgreSQL DB to create reflections table if reflections table does not exist with fields listed.

• We call pool query method with queryText as an argument and it returns a promised. We called pool.end() to close pool connection to the db.
• We created another function called dropTables() - What this does it to delete reflection table. We set up a new query DROP TABLE IF EXISTS reflections that drop reflections 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. Also, don't forget to install make-runnable as 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 reflections table. Use the following command

$ node node db createTables

After running the above, you should something similar to below

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

Set Up Controller

Copy the following and paste it inside src/usingDB/db/index.js

alt

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.

To make our controller simple, it will contain the same five methods created in the previous tutorial but we will tweak it contents to get values from the DB.

Copy and paste the following inside src/usingDB/controllers/reflections.js

alt

Code Steps:

• Here, we create Reflection object with five methods - create(), getAll(), getOne(), update() and delete(). We also make use of async/await.
• create() - We imported our db from src/usingDB/db/index.js. For our SQL query, we used INSERT INTO reflections(list_columns_here..) VALUES($1, $2, $3, $4, $5, $6 ...) - what this does is create a new row in reflections 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 text and params inside src/usingDB/db/index.js, this is where we will use it. We called the method and send in createQuery and values as parameters. Since db.query returns a promise we make use of async/await to make our code looks sweet .
• getAll() - We set up SELECT * FROM reflections to get all rows in reflections table
• getOne() - We use SELECT * FROM reflections WHERE id = $1 to retrieve a single row from reflections table where the id is the specified id.
• update() - To update a reflection, we queried the DB twice - the first one is to get the specific row the user wants to update and the second DB query update the row based on new input from the user.
  We use UPDATE reflections SET success=$1,low_point=$2,take_away=$3,modified_date=$4 WHERE id=$5 returning * to update a specific row in the table.
• delete() - We use DELETE FROM reflections WHERE id=$1 returning * to delete a row in reflection table using the row id

Update Server.js

We need to figure out a way to switch between using what we did in the previous post or using DB. The best way I could think of is to add TYPE in the environment variable. We will use this to check what to use for the app. If TYPE===db we will make use of what we did in this post and if TYPE===jsobject we will use what we did in the previous post. Now put TYPE=db inside your .env - your .env should contain two variables now DATABASE_URL and TYPE.

Update your server.js with the following

// server.js
import express from 'express';
import dotenv from 'dotenv';
import 'babel-polyfill';
import ReflectionWithJsObject from './src/usingJSObject/controllers/Reflection';
import ReflectionWithDB from './src/usingDB/controller/Reflection';

dotenv.config();
const Reflection = process.env.TYPE === 'db' ? ReflectionWithDB : ReflectionWithJsObject;
const app = express()

app.use(express.json())

app.get('/', (req, res) => {
  return res.status(200).send({'message': 'YAY! Congratulations! Your first endpoint is working'});
});

#####################
# Existing Code     #
#####################

app.listen(3000)
console.log('app running on port ', 3000);

Code Steps:

• We installed babel-polyfill npm package and imported it - We need this here so that node runtime will recognise async/await and Promise.
• Since we need to load/get TYPE to/from system enviroment, we make use of dotenv to do just that and process.env.TYPE is used to retrieve its value.
• We make use of JavaScript ternary operator to set the value of Reflection. What this process.env.TYPE === 'db' ? ReflectionWithDB : ReflectionWithJsObject; basically does is to assign ReflectionWithDB to Reflection if TYPE === db or assign ReflectionWithJsObject otherwise.

YAY!!!! Run and Test all your endpoints.

Conclusion

In this tutorial, we learned how to connect our node app to PostgreSQL and we also learned how to query and manipulate data in the DB.
Please check out part 1 of this post here if you haven't done so.

Check out the complete code here

As always, drop your questions and comments. Don't forget to like and share this post if you learned one or two things.