Part 1: Setting up postgres for a simple C.R.U.D. using Nodejs (express)
Tags: node, express, postgressql, crud
Published: 2023-05-16 01:55:13

I have an interview coming up and needed to quick refresher on [nodeJS](https://nodejs.org/e), 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](https://nodejs.org/en)
    
* [ExpressJS](https://expressjs.com/)
    
* [Postman](https://www.postman.com/) (for testing)
    

The goal for my refresher was to make a simple api that could use a sql database. I decided to use [**PostgreSQL**](https://www.postgresql.org/) as my database.

## Getting Started

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

```javascript
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`](https://www.npmjs.com/package/dotenv)." 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.](https://expressjs.com/)
        
* 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.](https://www.npmjs.com/package/nodemon)
        
* pg/pg-pool - `npm i pg-pool pg`
    
    * pg/pg-pool - [A connection pool for node-postgres](https://github.com/brianc/node-postgres/tree/master/packages/pg-pool)
        

My package.json looked like this:

```javascript
{
  "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.

```markdown
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)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1682989457342/3b4253d4-5208-4aa3-ab70-6bd30b5e2860.png align="center")

Create a database:

```sql
postgres=# CREATE DATABASE animals;
```

Create a table:

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

Enter/cd into the animals table:

```markdown
\c animals
```

Insert rows into the table:

```sql
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:

```sql
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

```javascript

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](https://dev.to/sincerelybrittany/day-11-100daysofcode-restful-routes-1ab1) request to update the database through each route.

As always, I leave you with a song:

{% embed https://soundcloud.com/femmeitforward/ambre-what-you-deserve %}