Part 1: Setting up postgres for a simple C.R.U.D. using Nodejs (express)

I have an interview coming up and needed to quick refresher on nodeJS, and what better way is there to refresh on something, then building with something with it. I decided to create a simple database for an animal shelter, to help the owners keep track of the animals in the shelter. I was told that I would need to use the following frameworks/libraries for the interview so I decided to use it for this practice project:

• NodeJS

• ExpressJS

• Postman (for testing)

The goal for my refresher was to make a simple api that could use a sql database. I decided to use PostgreSQL as my database.

Getting Started

First I created a folder and initialized it with npm so that I could install my dependencies:

mkdir nodejs-api
cd nodejs-api
npm init -y

This created my package.json file. Then, I added the dependencies that I felt I needed for this project.

• dotenv - npm i dotenv

  • Dotenv - "Dotenv is a zero-dependency module that loads environment variables from a .env file into process.env." Pretty much, it helps you hide variables, things like private api keys, passwords, or emails or whatever you do not want the world to see.

• express - npm install express --save

  • Express - Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications. With a myriad of HTTP utility methods and middleware at your disposal, creating a robust API is quick and easy.

• nodemon - npm i nodemon

  • nodemon - nodemon is a tool that helps develop Node.js based applications by automatically restarting the node application when file changes in the directory are detected.

• pg/pg-pool - npm i pg-pool pg

  • pg/pg-pool - A connection pool for node-postgres

My package.json looked like this:

{
  "name": "nodejs-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.0.3",
    "express": "^4.18.2",
    "nodemon": "^2.0.22",
    "pg": "^8.10.0",
    "pg-pool": "^3.6.0"
  }
}

After installing my dependencies, I added the folders that I would need.

project
│   README.md
│   package.json
│   package-lock.json
|   .gitignore
|   .env
|   db.js
|   server.js
└───src
│   │
│   └───animals
│       │   controller.js
│       │   queries.js
│       │   routes.js

PostgresSQL SQL Shell (psql)

The next step was to open the SQL Shell (psql) and create a database and table to use. Sign into psql (if you use a password, remember it)

Create a database:

postgres=# CREATE DATABASE animals;

Create a table:

CREATE table animals (ID SERIAL PRIMARY KEY,name VARCHAR(255),type VARCHAR(255),age INT,dob DATE);

Enter/cd into the animals table:

\c animals

Insert rows into the table:

INSERT INTO animals (name, email, type, dob) VALUES ('Dory', 'fish', 1, '2022-08-04'), ('Sally', 'dog', 32, '1990-01-01'), ('Perry', 'cat', 32, '1990-01-01');

Make sure everything is working:

animals=# SELECT * FROM animals;

It should return the table.

Now let's get express set up!

Express will allow us to see the database in interfaces such as chrome/postman.

In server.js

const express = require("express");
const app = express();
const port = 3000;

app.listen(port, () => console.log(`App Listening on port ${port}`));

Now you should be able to visit https://localhost:3000 and see hello world.

In the next post, we will create the routes to the animals database, so that we can make get, post, put, and delete request to update the database through each route.

As always, I leave you with a song: