Creating React/Node apps that connect to PostgreSQL and HarperDB

I’m sure that most of you are already more than familiar with the MERN stack. Having a React front end with a Node/Express back end that connects to a MongoDB database. Well, I will show you just how easy it is to connect to a Node back end which uses a PostgreSQL database to persist the data. And as a bonus I will even show you how to connect to https://harperdb.io/ which is a SQL/NoSQL data management platform. It is fully indexed, doesn’t duplicate data, and runs on any device from the edge to the cloud.

I will assume that you already have an understanding of JavaScript, Node and SQL as this guide is meant to be a quick introduction.

You will be creating an app that looks like the image below.

Prerequisites

  • Insomnia or Postman API App installed
  • NPM/Node Installed on your computer
  • PostgreSQL installed and setup

Create a PostgreSQL Database

For this guide I will be using Valentina Studio as a GUI to manage the local PostgreSQL database which you can find here https://www.valentina-db.com/en/valentina-studio-overview However feel free to use whatever tool you like you can even use the command line to interact with your database if you prefer.

Firstly create a database called metacritic and then use the SQL below the images to create a table called movies.

Then use the SQL below the image to add some data to the table movies.

Run the SQL below to see all of the data in the table movies.

Create a Node/Express back end Server

First navigate to a location like your desktop or a folder and then use the code below to set up your project by using your terminal application.

Open the project in your code editor and then create a Node server in the index.js file

Add this run script to your package.json file.

Add this code to your .gitignore file in the root folder.

Run the application from the back end folder and go to your browser window to see the homepage.

Connect to the PostgreSQL database

Add your database name, username and password like in the example below to your .env file. I believe that the username is always postgres when working with postgres databases locally.

Now update the index.js file in the root folder with the code below.

Restart your server and go to your browser window and reload the page. You should see the data in your database for the table movies returned as json and the data is also logged to your terminal window.

You can look at the documentation for the Knex.js package to learn more about the code http://knexjs.org/

Implementing some CRUD functionality

Replace the code in your index.js file with the code below. It is now possible to create, read update and delete data from the database. Restart your Node server to see the changes.

Using an API tool to test the different endpoints

In this guide I will be using the Insomnia API app to perform different CRUD requests. Use the screenshots as an example to see it work on your computer.

GET: Fetch all movies from the database

Just go to http://127.0.0.1:5000/ and hit send to see all of the database data returned as json

GET: Fetch movie by movieId from the database

Just go to http://127.0.0.1:5000/1 and hit send to see the movie that is matched with that ID returned as json. It will work with any ID number as long as it is in the database.

POST: Create movies and add them to the database

Send a POST request to http://127.0.0.1:5000/add-movie with key value pair data as displayed in the example screenshot. Then go to the Fetch all movies route to see the new entry. Alternatively you can just use your database GUI or the CLI to see the new database entry.

DELETE: Delete movie by movieId from the database

Send a DELETE request to the route http://127.0.0.1:5000/delete-movie using the name movieId. And as the value use any ID that is in the database to delete that entry.

PUT: Update movie by movieId from the database

Use your API tool and send a PUT request to http://127.0.0.1:5000/update-movie to update an entry in the database. Go to the bottom of the index.js file to see the code for the UPDATE route. You can change the SQL query to update any of the fields in the table and then all you have to do is select the movie_id to update its entry. You can see the Javascript code and SQL query below.

Python

SQL

Well done you just created a Node app that connects to a PostgreSQL database. The next section will be about HarperDB.

Create a HarperDB Database

First you need to create a HarperDB account and then create a database. I called my database “movies”. Creating and setting up a HarperDB Database is very easy. Just follow this video HarperDB Cloud Launch Tour and you can also take a look at the documentation for HarperDB with Node here https://docs.harperdb.io/.

Login Credentials

You will need an authorisation code to connect to HarperDB. First use your API tool to send a GET request to your HarperDB URL with your username and password. You need to use Basic Auth. Then use the generate code button and select Node.js and HTTP you will find your authorisation code in the headers code. The images below show you how it’s done.

Connecting to HarperDB

Once you are set up make sure that you update your .env file with your HarperDB credentials like below.

Now update your index.js file with the code below. We imported HarperDB, the database credentials for it and also created routes which you can find at the bottom with full CRUD requests. Axios is used for fetching data from the HarperDB API.

Use your API tool or check out the routes in the browser to see the data returned as json from the HarperDB Database instance. For the update route just use your API tool with a key value pair like below.

HarperDB stores ID’s as strings so please be aware that you won’t be able to fetch, update and delete a movie by movieId if its ID is a number unless you make some adjustments to your code. We have been storing our ID’s as numbers however it’s easy to switch between the two just make the movieId a string instead of a number.

You will need to restart your node server to see the changes.

Building the front end

It’s time to create a front end that will get data back from the API. cd into the root folder for the meta-movies-app and then run the command below to set up a project in React.

Now start the react app server using either npm start or yarn start

Navigate inside of your react project and then delete all of the css inside of the index.css file. Next replace the code inside of the App.css and App.js files with the code below.

App.css

App.js

Restart your Node server if you need to and make sure that it is also running. You should see the app working inside of your browser. It also has a form that allows you to add new database entries which automatically get displayed on the page. Meta Scores are even colour coded depending on their number which is done using an if statement which you can see in the code.

The app is connected to your local PostgreSQL database however it is easy enough to change the endpoint for the API to HarperDB. All of the other routes are in the back end so you can play around with them and connect them to the front end which i’m sure you are already capable of doing.

When you add a new movie it does not redirect back to the react homepage. If you want to add this functionality then update your post route function in the backend index.js file in the PostgreSQL section with the code below. Restart your back end server to see the changes.

👨🏿‍💻 Software Developer @CGI_Global 🖼 Content Creator. Sharing the mindset and content so you work hard to grow stronger than your past self ☯️

👨🏿‍💻 Software Developer @CGI_Global 🖼 Content Creator. Sharing the mindset and content so you work hard to grow stronger than your past self ☯️