PERN Stack and its deployment

In this article we will start from scratch about PERN Stack, what is it, why is it, how is it and then we will build a simple PERN App and deploy it on heroku.

Pern stack: A web stack consisting of 4 awesome open-source libraries and frameworks, PostgreSQL, Express, ReactJS, NodeJS. You can build any full stack website with the help of these technologies together like a one we are going to build here today. Also it is worth mentioning here that PERN stack sounds similar to a more popular tech stack, the MERN stack, as they both share 3 technologies, i.e express, node and react but they both use different technology for databases.

MERN Stack uses MongoDB, a non-relational database and PERN uses a PostgreSQL, a relational and more robust database. No Doubt both, MongoDB and PostgreSQL share great features but they are very different in the basic structure of a database. As I mentioned earlier, Mongo is a non-relational database, i.e it uses JSON to store its data and Postgres uses Table or Relations to do the same.

That’s enough talk about our nemesis MongoDB, now lets start the main part and get ourselves into coding

Setting Up Postgres Table

I’m going to assume that you have Postgres installed in your system already, however if that's not the case follow along this video on YouTube and it will get things done for you to follow along.

Ok, so make sure you are connected on to your database and lets start,

Create a database on your localhost

CREATE DATABASE todo;

Switch to the created database

\c todo postgres localhost 5432;

Create a relation or a table on created database

CREATE TABLE task(work VARCHAR(150), completed BOOL);

At this point, you are all set. You have created a database and a table inside it on your local machine. Now you can deal with the data as you are ready to store it now in your relation.

Insert a value in the database

INSERT INTO task(work, completed) VALUES('Learn postgres', false);

Read all the values stored in your database

SELECT * FROM task;

Update a value in database

UPDATE task SET completed = true WHERE work = ‘Learn Postgres’;

Delete a value from the database

DELETE FROM task where work = 'Learn Postgres';

So you just Created, Read, Updated and Deleted data from your database. It means that you are all set to write up your own CRUD API using node and express with PostgreSQL

Now lets start with node and express and build an API. You obviously need node installed in your system, you can check it by running

node -v

If you get v14.xx.x something, you are good to go, otherwise you can download node here. Just follow the installation wizard

Now create a folder on your PC and open it in VS Code.

Open up the terminal and lets’ begin with command:

npm init -y

This one small command has now just set upped your node app. Now lets go ahead and set up express and other important packages, then our local server

npm install express cors pg

Now make a ‘server.js’ file in root directory and paste the following code

const express = require('express')
const server = express()
server.get("/", (req, res) => {
    res.send("Hello World")
})
const PORT = process.env.PORT || 5000
server.listen(PORT)

Now run following command on the terminal

node server.js

Now Open you browser and go to ‘http://localhost:5000’ you’ll see Hello World on the browser. Now you are all done with the boilerplate stuff, lets begin with the crud API and write 4 endpoints to create, read, update and delete task from our database.

However, before writing API, you need it to connect it with your local database. Make a file ‘connectDB.js’ and paste following code

const pg = require('pg')
const Pool = pg.Pool;
const pool = new Pool({
    user: "postgres",
    host: "localhost",
    port: 5432,
    password: "your_password",
    database: "todo"
})
module.exports = pool

This code is enough for you to connect your code with database, now change your ‘server.js’ as following to make a complete rest API. Keep in mind to change the password with the password you created while setting up postgreSQL on your machine.

const express = require('express')
const pool = require('./connectDB')
const server = express()
server.use(express.json())
server.post('/create', async (req, res) => {
    const { work } = req.body
    const response = await pool.query("INSERT INTO task(work,     completed) VALUES($1, $2) returning *", [work, false])
    res.json(response.rows[0])
})
server.get('/read', async (req, res) => {
    const response = await pool.query("SELECT * FROM task")
    res.json(response.rows)
})
server.patch('/update', async (req, res) => {
    const { work } = req.body
    await pool.query("UPDATE task SET completed = $1 WHERE work = $2", [true, work])
    res.json({ msg: "Task Updated" })
})
server.delete('/delete', async (req, res) => {
    const { work } = req.body
    await pool.query("DELETE FROM task where work = $1", [work])
    res.json({msg: "Task Deleted"})
})
const PORT = process.env.PORT || 5000
server.listen(PORT)

This is the code for a simple Express API with Postgres. It is always so simple, easy and fast to make APIs with the help of Express framework. This code can serve as a complete backend API for your todo PERN stack application. Ofcourse, you can make it more better by adding UUID, auth and more such functionalities but this works just fine for now.

So I guess this is time, to host our backend Postgres, Node and Express API on heroku to serve our upcoming frontend project. Lets begin

To get started with hosting your API on heroku, we first need to install heroku cli to access your heroku dashboard right from your terminal. Run following command on your terminal to do so

npm i heroku-cli

You may want to use npm or sudo according to your local environement and settings. Now to access your dashboard, login with heroku,

heroku login

Follow on-screen commands, you will get a login success message. Now you can close the browser tab and come back to your terminal.

heroku create example-pern-app

You can use your API name according to you by running this command above. You can also visit your heroku dashboard and see your app actually listed there after success message on your terminal.

Now after setting up a node application on heroku, comes setting up postgreSQL on heroku. Heroku provides us with a addon service called heroku-postgresql. To add this addon service, go to your dashboard on browser and follow following actions

1. Go to your Dashboard
2. Select your created app
3. Go to resources tabs
4. Search for heroku-postgres on addons sections
5. Choose Hobby-Dev-Free Plan
6. Submit your order form
7. Done.

Now you can go to your terminal and write following command to see your addon linked with your app

heroku addons

Note for windows user: Make sure before proceeding that your PostgreSQL path to bin is registered in the environment variable section of your machine. You can check the documentation here.

Now you are ready to access your postgres database hosted on heroku. Ho ahead and use following command on the terminal.

Replace the ‘postgresql-something-xxxxx’ part with the name you got under add-on section when you ran the command ‘heroku addons’ and replace ‘example-pern-app’ with the name you selected for your app.

heroku pg:psql postgresql-something-xxxxx --app example-pern-app

After succesful terminal command execution, you’ll see that you are not exited, but you are in the database. Your terminal must be showing your cursor ahead of

example-pern-app:DATABASE=>

it means, you are now inside of your database. You can now run the postgres command exactly like you used it in your SQL Shell. Go ahead and copy paste your commands to create the tables inside the database (Do not use create database and ‘\c’ commands as heroku has settuped your database automatically) of your heroku app. and you’re done.

Your complete app is now all set on heroku. You have your node application, linked with your own database and now we are going to deploy it.

Before deploying however, we’ll have to change the connection string on our postgresDB.js file to make it connect to the one database on heroku and not the one on our localhost. Copy and paste the following in your connectDB.js file.

const pg = require('pg')
const Pool = pg.Pool;
const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    ssl: { rejectUnauthorized: false }
})
module.exports = pool

If you are interseted in knowing what is that ‘process.env.DATABASE_URL’, you’ll have to go to your dashboard again and then

1. Go to Settings
2. Go to Config vars sections
3. Click on Reveal Config Wars

There you’ll find a long sting in front of DATABASE_URI. This is your connection string including your database username, password, host, port for the database associated with your hosted node app. Heroku provides you simply with this string to connect your app.

Note: Before proceeding further, you need git to be installed in your system. Also make sure to make a ‘.gitignore’ file in the root of your folder and add ‘node_modules’ in it

Okk so now here comes the big moment, uploading your app and deploying it on heroku. Use following commands in your terminal.

git init
heroku git:remote -a example-pern-app
git add .
git commit -m "My API upload"
git push heroku master

So now, you have your app ready, uploaded and well connected. Now you need to do npm install and then start your server. Run following commands after one another

heroku run npm install
heroku run node server.js

Thats it… You have now successfully uploaded and deployed your API to serve your basic todo application made with PostgreSQL, NodeJS and Express.

You can go on https://example-pern-app.herokuapp.com/read and you’ll get an empty array which is a sign of your successful deployment as your database has no task in it to fetch. You can either test your API via Postman or directly via your frontend react app.