Bootcamp
Search…
4.POCE.1: Sequelize CLI App - Travel Itinerary

Introduction

Create an app that tracks the places you want to go during your holiday.

Getting started

Begin by forking this starter repo.​

Base

Set Up Database Schema

Create a new database and the following tables inside that database.

Trip Table (trips)

column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the trip
Christmas PH

Attraction Table (attractions)

column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the attraction
Universal Studios
trip_id
Foreign Key
ID of the trip that this attraction belongs to
1

Commands

Create a new trip:
node index.mjs create "Christmas PH"
Create an attraction for your trip:
node index.mjs add-attrac "Christmas PH" "Universal Studios"
Get your itinerary for a trip:
node index.mjs trip "Christmas PH"

Comfortable

Categories

Add categories to our travel itinerary app to help organise attractions. Each category can have multiple attractions, and each attraction belongs to a single category.
  1. 1.
    Create a new table categories
  2. 2.
    Add a foreign key category_id to the attractions table to associate categories with attractions.

Create Category

Sample Command:
node index.mjs add-category museum
Command Template:
node index.mjs add-category <CATEGORY>

Create Attraction with Category

Sample Command:
node index.mjs add-attrac "Christmas PH" "Art Science Museum" museum
Command Template:
node index.mjs add-attrac <TRIP> <ATTRACTION> <CATEGORY>

Get All Attractions in Trip with Given Category

Sample Command:
node index.mjs category-trip "Christmas PH" museum
Command Template:
node index.mjs category-trip <TRIP> <CATEGORY>

Get Attractions for All Trips that Belong to Specific Category

Sample Command:
node index.mjs category-attractions museum
Command Template:
node index.mjs category-attractions <CATEGORY>

More Comfortable

Locations

Add locations to your attractions.
Using a migration, add two columns to the table that sets the latitude and longitude of an attraction.
If you want to get the real latitude and longitude for a search, those numbers appear in the URL of a google maps search.
Note that the calculations described below don't have to be done inside of SQL. They can be done with JavaScript logic.
node index.mjs add-attrac "Christmas PH" "Universal Studios" 1.2540421 103.8238084
Command Template
node index.mjs add-attrac <TRIP> <ATTRACTION> <LATITUDE> <LONGITUDE>

Directions

Write a command that shows all attractions in a given direction from a location.
node index.mjs get-attractions-from "Christmas PH" 1.2540421 103.8238084 south
Command Template
node index.mjs get-attractions-from <TRIP> <LATITUDE> <LONGITUDE> <CARDINAL_DIRECTION>

Within

Write a command that gets all attractions within 300 kms of a location.
Use this formula to convert from coordinate degrees to kms.
node index.mjs get-attractions-within "Christmas PH" 1.2540421 103.8238084
node index.mjs get-attractions-within <TRIP> <LATITUDE> <LONGITUDE>

Reference solution

Click here to see the reference solution for this post class.