Build a Website Series Part 2: NodeJS CRUD with Postgres

<< Part 1: React Routes || Next tutorial >>

The previous tutorial showed how to set up React with routes and a navbar. Next, we are going to focus on the backend setup.

If needed, here is my repo to refer to. Now on to making the backend!

What Part 2 Covers:

1. Set up a nodeJS API
2. Set up a PostgresSQL Database
3. Build the CRUD (Create Read Update Delete) and confirm it is working via Postman

Before the instructions begin, Here's a little background for the things mentioned above in case you are new to this. Otherwise feel free to skip below to the steps.

Baseline knowledge:

What is an API or “Application Programming Interface”?
When I was brand new to this, knowing the abbreviation didn't really clarify things for me. Instead I tried to understand it via metaphors.

An API is the 'middle person' between the front end (web page that user sees and interacts with) and the backend (where the database is). The API allows the front and back end to communicate back and forth.

A common metaphor you can use to understand the use of an API is to think of a waiter at a restaurant. They take your request or order for food and deliver that request to the cook. Then the waiter brings you back the specified food. Similarly, an API takes a request from a client (user) and gives it to the backend for processing (the cook). The backend returns the request to the user.

What is CRUD?
Continuing with the waiter metaphor, a waiter has multiple tasks they can perform. Those tasks can include getting a menu, updating the cook if you want to alter your order, bring your meal out, or even telling the cook you no longer want something.

With this in mind, there are certain core tasks that an API should do and it is abbreviated as CRUD. As long as an API can do these four things, it is considered a RESTful API which is the standard for a good functional website:

Create: create a new item in the database
Read: display or return existing item/s from the database
Update: change an existing item in the database
Delete: remove an existing item from the database

What is NodeJS?
It is a Javascript based software that can be used to create applications. In the case of this tutorial, it is used to create the REST API server.

What is PostgresSQL?
This is a relational database system. It will hold all the tables and schemas for the website. For example, it will hold the both the template and the actual data for a user’s username, email, and password.

Now that the basic idea has been explained, we can now set up our NodeJS API.

Step 1 -Set up NodeJS-

A. Download and install NodeJS

B. Confirm successful installation by typing node -v in the terminal

C. In the folder containing your project, create a new folder called bird_banders_api

D. To create the NodeJS API, cd into this new folder and write npm init -y in the terminal. The -y is so that it doesn’t ask a bunch of questions and just creates the default settings for us.

E. Now there's several NPM packages to install, they can be written all on one line: npm i express pg cors

The server is through Express, PG connects NodeJS to the database, and CORS allows for domains to interact with each other.

F. To allow the server to automatically display code changes (rather than having to start and stop the server all the time), in your terminal write npm i -D nodemon. The -D is because we only need this installation applied to our development environment, not for the deployed site.

Here is an example of how the terminal should look thus far:

G. Once this is done, if you have Zsh installed, you can type code . in the terminal and it will open the api project in vscode. Otherwise, open the project however you want. The side bar should look like this:

H. To create a file that will handle all the functions of the API. In the terminal write, touch index.js

I. We also need some convenient terminal commands for starting the server. Go to package.json and edit the scripts:

"start": "node server.js",
"dev": "nodemon server.js"

Here is what the package.json file should look like now:

J. In order to start using express (our server) add these lines of code to index.js:

// pulls in the express library
const express = require('express')

// allows us to write app and the crud action we want ex. app.get | app.post | app.delete etc...
const app = express()
const cors = require('cors')

// middleware
app.use(express.json()) // =>  allows us to read the request or req body
app.use(cors())

// Routes (to be filled out later in tutorial)


// define what localhost port we want our server to run on
app.listen(3000, ()=> {
    console.log(`Server running on port: 3000`)
})

K. From here on out, you can now type: npm run dev to start the backend server. In your console, you should see:

We have now completed the NodeJS set up! Now we’ll create the database.

Step 2 -Set up the Postgres Database-

A. There are multiple ways to install Postgres, for me I did it through Mac’s Brew software. If you don’t have brew, you can install it by pasting this in the terminal:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

B. To install Postgres, in the terminal type:
brew install postgresql

C. Connect to postgres with:
psql -U postgres

Explanation:
psql tells the terminal to start the postgres command line.
-U means we are about to specify which user we want to use.
Postgres is the default superuser name, and a superuser means you don’t have any limitations on manipulating databases.

So the above essentially means: start the psql command line through the superuser that is named “postgres”.

D. If you do not have 'postgres' as a superuser, you can create the superuser 'postgres' by pasting this into your terminal. Note the ;, it won't work if you forget to include it in the second command: psql
then: CREATE USER postgres SUPERUSER;

E. Once you are in the psql command line, add:
CREATE DATABASE bird_banders;

We now have our database!

Some commands to familiarize with for Postgres:

• \l will show you the list of databases you’ve created so far including the one you just created:

• \c bird_banders connects you to that database, so we can add tables to it.

• \dt will show us that we don’t have anything set up yet, but in the future this command will show us the tables we have.

F. Connect to the database now by pasting this into the terminal: \c bird_banders

G. Create a table in the database...

Click on summary if you want a beginner's explanation on databases. If not, just keep reading ahead to see the steps.

H. Enter the following code into the terminal to create the table (I have shortened “organization” to “org”):

CREATE TABLE org(
    org_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

Explanation:
SERIAL PRIMARY KEY tells Postgres to make a unique ID number for each organization. Name VARCHAR(100) tells Postgres that each org will have a name attribute and that it shouldn’t exceed 100 characters in length.

I. When \dt is typed into the terminal, we can see our database now has a table. The below image show what the terminal should look like after following steps F through I:

J. This newly created database now needs to be connected to the nodeJS server. Press ctrl + d to get out of Postgres in the terminal and go back to the nodeJS project in VScode. Write touch db.js to create a file to hold the database settings.

K. Inside of db.js we’ll add:

const Pool = require('pg').Pool

const pool = new Pool({
    user: 'postgres',
    password: '',
    database: 'bird_banders',
    host: 'localhost',
    port: 5432
})

module.exports = pool;

Explanation
Earlier in the tutorial we installed pg which is a library that allows Postgres and NodeJS to connect. Pool contains all the information that nodeJS needs to communicate with the database. The 5432 port is the default for Postgres. Lastly, we export this as a module so that we can actively use it in our main file, index.js

L. In index.js, add under the other requires:
const pool = require('./db')

Here is what it should look like so far:

Now we can finally get to the CRUD!

Step 3 -Build the CRUD-

Each of the CRUD operations follow a similar formula:

app.action(‘route path’, async (req, res) => {
    try {
        const data_we_want = await pool.query(“postgres commands”)
        res.json(data_we_want)
    } catch (err) {
        console.error(err.message)
    }
})

Explanation:

app.action could be app. get, post, put, or delete. We’ll be doing one of each.

There are two arguments that go into the app.action. The first is the route path, in the current case, it’ll be /orgs since that’s the table we’ve created.

The second argument is a function that will process data. Think inception… because this function also takes in two arguments. They are the request and the response for the data (think back to the waiter analogy as well). It is like this function is taking in the order for your meal and will output that meal once the chef has prepared it.

try and catch is a handy way to execute a block of code, but also have a backup action if that block of code fails. In order to use this though, we have to make the function asynchronous or put the async label in front of it and the await in front of the query.

try is where you put the block of code you want to attempt. catch is what will execute if that block code fails.

With repetition this will start to make more sense.

A. Create action
The create action allows the front end user to create an organization with a name. This would usually be submitted by a front-end user. This new incoming information is contained in a request or req (think about the waiter metaphor.. requesting a food order to a waiter).

Paste this code in your routes section in index.js

// create an org
app.post('/orgs', async (req, res) => {
    try {
        // await
        console.log(req.body)
        const { name } = req.body
        const newOrg = await pool.query(
            "INSERT INTO org (name) VALUES ($1) RETURNING *", // returning * lets us see the data in the json response
            [name]
        ) 
        res.json(newOrg.rows[0])
    } catch (err) {
        console.error(err.message)
    }
})

The index.js page should look like this:

Explanation:
The route is /orgs because that’s the name of the table we want to interact with.

If we had a user table (which we will go over in a future tutorial), then the route to create a new user would be /users.

In the try block, we only want to grab the actual name of the organization from req, which is inside the req’s body. Destructuring the name out of the body makes the code look cleaner.

Once the organization’s name is pulled out of the request, that data must be sent to the org table in the database and a new organization must be created:
“INSERT INTO org (name) VALUES ($1) RETURNING *", [name])

The $1 sign is just a variable placeholder in the first argument and the second argument [name] is the value that will be inserted into where $1 resides in the Postgres command.

RETURNING * lets us see the data in the json response (for example if you are using Postman).

In Postman:

• Set the link to http://localhost:3000/orgs
• Select POST (dropdown menu to the left of the link)
• Select 'Body'
• Make sure 'raw' is selected as well as 'JSON' (next to where it says 'GraphQL')
• Type some incoming data:

{
    "name": "Bird Group" 
}

• Press the blue send button to see the results. If successful it should say in the lower half of the screen:

{
    "org_id": 1,
    "name": "UTD Birding"
}

B. Read action
The read action for seeing all organizations in the database is one of the easiest actions. And now that we can add some names with the create action, we can actually see some these new orgs by using the read action. Below the create action, paste in this:

// get all orgs
app.get('/orgs', async (req, res) => {
    try {
        const allOrgs = await pool.query("SELECT * FROM org")
        res.json(allOrgs.rows)
    } catch (err) {
        console.error(err.message)
    }
})

Explanation
The * means all, so the query is saying select all items from the org table.

res.json(newOrg.rows[0]) sends back (can see on postman) the new information that was generated.

In Postman:

• Make sure the link is still http://localhost:3000/orgs
• Select GET to the left of the link
• Press the blue send button to see the results. If successful you should now see a list of organizations in your database.

C. Read action 2
The second most common read action is when you want to only get one organization from the table instead of the whole list.

// get only one organization
app.get('/orgs/:id', async (req, res) => {
    console.log(req.params)
    const { id } = req.params
    try {
        const org = await pool.query("SELECT * FROM org WHERE org_id = $1", [id]) 
        // $1 is a placeholder, then the 2nd argument is what that variable is 
        //going to be
        res.json(org.rows[0])
    } catch (err) {
        console.error(err.message)
    }
})

Explanation
It is pretty much the same thing as the previous read action, only this time we need one extra thing for it to work. Remember how each organization in the table has a unique ID attached to it? Well, we can grab that from the req.params. And we add the id to the route path /orgs + /:id.

In Postman:

• Add an ID number to the end of the url: http://localhost:3000/orgs/2
• Select GET to the left of the link
• Press the blue send button to see the results. If successful you should now see the organization that is associated with with that ID number.

D. Update action
The update action is one of the more complex actions. It is very similar to our previous action of getting only one organization. We need the ID number in order to know which organization in the table to update. We then need to grab the updated information from the req.body.

// update an organization
app.put('/orgs/:id', async (req, res) => {
    try {
        const { id } = req.params // where
        const { name } = req.body // grab the new info
        const updateOrg = await pool.query(
            "UPDATE org SET name = $1 WHERE org_id = $2", [name, id]
        )
        res.json('The organization name was updated')
    } catch (err) {
        console.error(err.message)
    }
})

Explanation
"UPDATE org SET name = $1 WHERE org_id = $2", [name, id]
The query is saying:

• UPDATE at the org table.
• SET the name of the organization WHERE org_id matches the variable $2
• There are two variables needed for this, the name hence the $1 and the id hence the $2.

The res.json line is to return to the user a message that the update was successful.

In Postman:

• Add the ID number of the organization you want to update. If you are unsure which to choose, try performing a GET in Postman first. http://localhost:3000/orgs/1
• Select PUT to the left of the link
• Press the blue send button to see the results. If successful you should now see a message saying "The organization name was updated".

Delete action
We’ve finally reached the last action! This one is pretty straightforward. Once again, we need to grab the ID so we know which organization to delete.

// delete an org
app.delete('/orgs/:id', async (req, res) => {
    try {
        const { id } = req.params
        const deleteOrg = await pool.query(
            "DELETE FROM org WHERE org_id = $1", [id]
        )
        res.json('The organization was deleted')
    } catch (err) {
        console.error(err.message)
    }
})

Explanation
"DELETE FROM org WHERE org_id = $1", [id])
For the query it means DELETE FROM the org table WHERE the org_id matches the $1 variable, which is set to [id].

In Postman:

• Add the ID number of the organization you want to update. If you are unsure which to choose, try performing a GET in Postman first. http://localhost:3000/orgs/1
• Select DELETE to the left of the link
• Press the blue send button to see the results. If successful you should now see a message saying "The organization was deleted".

So with that, you should now know how to set up your backend!