NodeJS PostgreSQL API

Entertainment Packages API Exercise

Author: Nico Rithner

• Github: github.com/nicorithner
• LinkedIn: linkedin.com/in/nicorithner/
• E-mail: [email protected]

Summary

Create a REST API using Node.js and PostgreSQL. Seed the database from the data provided as JSON and expose REST APIs with some specific filtering requirements.

Table of Contents

• Requirements
• Stack
• Schema
• Installation
• Endpoints
  • Postman
  • List of Endpoints
  • Swagger UI

Requirements

• REST API using Node / Express API with PostgreSQL Database

• README with clear instructions on how to run and use the application

• Seed script that seeds the PostgreSQL Database with the provided data

• CRUD APIs for each entity

• GET Shows and filter by Network (ex: /shows?network_id=1)

• GET Shows and filter by Package (ex: /shows?package_id=1)

• GET Package by ID should also provide all Networks included in that Package (ex: /packages/:id)

Stack

• NodeJS
• ExpressJS
• PostgreSQL
• Sequelize

Schema

The project includes three main tables and a through-table.

Based on the data provided as json:

• A 'package' has many networks.
• A 'network' belongs to many packages.
• A 'network' has mnay shows.
• A 'show' belongs to a network.

To avoid nesting packages and network are associated through a join table named 'packageNetworks'. A 'packageNetworks' table has both a package and a network foreign key. A 'show' belongs to a newtork only so there is a reference to network in 'show' with a foreing key.

Schema Diagram

Installation

1. Make sure you have yarn installed
2. Clone this repo git clone [email protected]:Coding-Gymnasium/node-express-api-2023.git
3. Cd to project and run yarn install
4. Set psql user:

       $ psql
       <default-user>=# CREATE USER postgres WITH PASSWORD "postgres";
       <default-user>=# GRANT ALL PRIVILEGES ON DATABASE node_express_api_dev TO postgres;

5. Create database npx sequelize-cli db:create
6. Migrate tables yarn migrate:up
7. Seed tables. Note: because of the associations the tables need to be seed in order. Please use the following command: yarn seed-in-order
8. Run project yarn start

Endpoints

Postman

Using Postman try the following endpoints

Sample Endpoints

View All Packages

View Shows by Package

View Shows by Package

View Networks by Package

List of Endpoints

---

Packages

Create a Package

POST ➡️ http://localhost:8000/api/v1/packages/

{
  "name": "Gold"
}

Get All Packages

GET ➡️ http://localhost:8000/api/v1/packages/

Get Package by ID

GET ➡️ http://localhost:8000/api/v1/packages/1

• Displays Networks associated with package

Update Package by ID

PUT ➡️ http://localhost:8000/api/v1/packages/1

Change price

{
  "price": 19.9
}

Delete Package by ID

DELETE ➡️ http://localhost:8000/api/v1/packages/1

Delete ALL Packages

DELETE ➡️ http://localhost:8000/api/v1/packages

---

Networks

Create a Network

POST ➡️ http://localhost:8000/api/v1/networks/

{
  "name": "ABC"
}

Get All Networks

GET ➡️ http://localhost:8000/api/v1/networks/

Get Network by ID

GET ➡️ http://localhost:8000/api/v1/networks/1

Update Network by ID

PUT ➡️ http://localhost:8000/api/v1/networks/1

Change name

{
  "name": "CBS"
}

Delete Network by ID

DELETE ➡️ http://localhost:8000/api/v1/networks/1

Delete ALL Networks

DELETE ➡️ http://localhost:8000/api/v1/networks

---

Shows

Create a Show

POST ➡️ http://localhost:8000/api/v1/shows/

{
  "title": "American Gods",
  "imdb_rating": 7.7
}

Get All Shows

GET ➡️ http://localhost:8000/api/v1/shows/

Get Associated Shows by Package Id

GET ➡️ http://localhost:8000/api/v1/shows?package_id=1

Get Associated Shows by Networks Id

GET ➡️ http://localhost:8000/api/v1/shows?network_id=2

Get Show by ID

GET ➡️ http://localhost:8000/api/v1/shows/1

Update Show by ID

PUT ➡️ http://localhost:8000/api/v1/shows/1

Change title

{
  "title": "Forge in Fire"
}

Change network

{
  "NetworkId": 2
}

Delete Show by ID

DELETE ➡️ http://localhost:8000/api/v1/shows/1

Delete ALL Shows

DELETE ➡️ http://localhost:8000/api/v1/shows

---

Connect Network to Package

Create a PackageNetwork

POST ➡️ http://localhost:8000/api/v1/packageNetworks/

{
  "NetworkId": 1,
  "PackageId": 2
}

Get All PackageNetworks

GET ➡️ http://localhost:8000/api/v1/packageNetworks/

Get PackageNetwork by ID

GET ➡️ http://localhost:8000/api/v1/packageNetworks/1

Update PackageNetwork by ID

PUT ➡️ http://localhost:8000/api/v1/packageNetworks/1

{
  "id": 4
}

Delete PackageNetwork by ID

DELETE ➡️ http://localhost:8000/api/v1/packageNetworks/1

Delete ALL PackageNetworks

DELETE ➡️ http://localhost:8000/api/v1/packageNetworks

---

Swagger UI

To use the swagger ui please navigate to:

http://localhost:8000/api-docs