Build and Deploy a League sponsorship service with Node.js, Sequelize, PostgreSQL , PostGis (PART 1)

Today we’re going to be building a league sponsorship service that is built with Node.js, Express, Sequelize, Postgres, PostGis , and Deployed to Heroku. We will also be building the frontend to our application, in another article, and it will be built with Angular, Bootstrap then be deployed to S3.

Overview

The purpose of this application is to enable brands to find leagues to sponsor, and leagues to join our platform, to potentially receive sponsorship .Our application will initially only have two endpoints, in part one. One for adding a league to the system, and other one that lets users find leagues to sponsor.

Data Model

The data model for the league table will consist of a name, pair of longitude/latitude, and a single price to purchase their sponsorship opportunity.

Adding a League

Add a league using name, longitude, latitude, and price.

Finding a League

Our endpoint for finding a league should consist of three arguments, a latitude/longitude pair around which to focus the search, a radius in miles in which to search around that central point, and a total budget is willing to spend, to sponsor that league.

We will be using Postgis a spatial database extender for Postgres.It adds support for geographic objects allowing location based queries to be run in Postgres. Functions such as distance, area, union, intersection, and geometry data types, which we will use to store the coordinates in a point in Postgres.

TDLR:

Postgis will allow us to store locations of leagues as a series of points

Prerequisites

This tutorial assumes that you have:

• NodeJS and npm installed
• PostgreSQL installed
• How to make requests with Postman

Setting Up

Clone this repository to get the starter folders and files needed for this application.

git clone https://github.com/willshepp28/League-Spx-Starter

Install all dependencies

Open your terminal and run this to install the dependencies our our project needs.

npm install

Install sequelize, postgres, and the sequelize-cli

npm i --save sequelize pg 
npm i --save-dev sequelize-cli

Next we will run this following command, which will initialize our project with a config, migrations, models, and seeders subdirectory

npx sequelize-cli init

Before continuing take a look at our db/config/config.json file, this is how we tell Sequelize how to connect to our database.

We now need to create the database. Run this command in the terminal

npx sequelize db:create

When you initialize sequelize its generates a config.json file with a corresponding json object, but when looking for this file in the db/models/index.js file its looks for a config.js file(WEIRD). To fix this replace the config variable with in index.js with this, so that its looks up the appropriate config.json file.

Next we’ll run this command in the terminal which will generator our league model and migration

npx sequelize-cli model:generate --name League --attributes name:string,email:string,location:geometry,price:decimal

We now have to run our migration which will League table with corresponding columns in our database.

npx sequelize-cli db:migrate

Running the migrate command will cause us to generate an error. Which essential tells us that the geometry type doesn't exist. To store spatial datatypes in Postgres we will needs an extension call PostGis

To remedy this issue, in your command line, go the the league-spx database in Postgres, issue the command below to enable PostGIS in your database. And we can now use spatial functions and store spatial object types in your db.

CREATE EXTENSION postgis;

Now you can run the migration command again.

npx sequelize-cli db:migrate

Seed The Database With Leagues

Inside of the db directory create a folder and name it seed-data, then inside the seed-data folder, create a file and name it league.seed.data.js. This file will have the data we need to populate the database with the inital Leagues. The leagues in this file has leagues that start in Santa Monica, CA all the way out to San Bernardino,CA which is approximately 80 miles apart.

Running the command below will tell sequelize to generate a seed file.

npx sequelize-cli seed:generate --name 01_Leagues

Then require our getLeagues file with our leagues data, inside of db/seeders/20201201031127–01_Leagues.js file, and use it in the queryInterface.bulkInsert method so that seqeulize can populate our database with leagues

Now to run our seeds, run the command below and voilà!!! Leagues baby

npx sequelize db:seed:all

Validating /POST Leagues user inputs

Create an api folder in the root directory, then inside the api folder, create a file and name it leagues.api.js. This file will have our endpoints to /POST and /GET leagues.

Afterwards lets define our leagues route in app.js

This will be the initial setup of leagues.api.js. Complete with our get and post routes.

Starting with our post route, which will add leagues to the database, we know a league at minimum must have a name, longitude, latitude, and a price. A user shouldn't be able to pass empty values, so lets validate the schema of our post request with joi

npm i --save joi

Create a helper folder in the root directory, and inside that create a validation folder. Then inside the validation folder create a file, and name it league.validation.js. This file will hold our validation logic, and make sure that the user passes the appropriate credentials to our /POST leagues endpoint.

Then back in our leagues.api.js, require addLeagueValidator, and use it as a middleware in /POST leagues route

Test our route in postman and if a user is missing any credentials then we get a error message

Adding Leagues

Now we need to require our sequelize models in our leagues.api.js to query our database. Once we’ve done that create a data object that will hold the name, location, price to create our league. Then afterward we’ll pass that data in the create method that sequelize provides us for each model to create the league.

Now pass valid values in postman, and we have successfully created a league WOOHOO!!!

Then open pgAdmin(or postgres via terminal), and check to see that our league was actually stored. ✅👌🏾

Validating /GET Leagues user inputs

Users should be able to find leagues within a certain radius, of any specified longitude, and latitude. And they should also only be returned leagues from that query up to their max sponsorship budget.

The first order of business will be validating user inputs. So being that a user shouldn't be able to pass missing or invalid credentials, let’s create a findLeagueValidator to validate user inputs, in our league.validation.js file.

Require findLeagueValidator file in our leagues.api.js, and use it as middleware in our /GET leagues endpoint. Now if a user passes missing or invalid values, an error message will be returned.

Finding Leagues

First thing we need to do is create a services folder to handle our business logic. So in the root directory create a folder and name it services, then inside that folder create a folder for leagues, and inside that a file named league.service.js

FYI:

order: we ordering price by lowest to highest so that once we filter the leagues based on the users budget, they are now returning the most amount of leagues that their budget can sponsor.

Now inside of our leagues.api.js we use the getLeaguesByRadiusRaw function in our league.services.js to get the leagues in that specific radius.

This is nice but we are returning all the leagues in the radius that the user specified, without taking into account their budget😦

To get all the leagues within the budget specified by the user, we’ll create a file in helper directory and name it getLeaguesInBudget.helper.js, and its job will be to take the leagues returned to us by sequelize, then return as many leagues as that a user can sponsor based on their budget.

Require getLeaguesInBudget in app.js, modify our /GET leagues endpoint so that we are not only leagues with a specific radius, but filtering even further to only return the most amount of leagues in that specific area, up to their sponsorship budget.

PostGis Functions :

ST_DWithin : Returns true if the geometries are within the specified distance of one another.

ST_SetSRID: Sets the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries

ST_MakePoint: make points with XYM coordinates.

And we have completed part 1 of our application, and we can now add leagues, and find leagues. Hope you enjoyed, and stay tuned for part two.