Postgres

PostgreSQL

The Scout app persists its data in a PostgreSQL database. If you want to run the app locally on your laptop or remotely on your https://data.heroku.com/ you will need to install Postgres, create a database, add initial objects (tables) to the database.

Local / Development

Install

The Setup instructions tell you how to install Postgres (PG): https://www.postgresql.org/download/ - you can confirm it is running from a command prompt:

which psql
psql --version

Create Role and Database

First log in as the postgres user, then create the role:

cd FRCScout2020

psql postgress
=> create role robotiger with login password 'robo1796';
=> alter role robotiger createdb;
=> \q

Next login as the robotiger user and create the database:

psql -d postgres -U robotiger
=> create database scout2020;
=> \c scout2020;
=> \q

It is important for all developers to use the same username/password/etc so that we can share the .env file in the repository which is where local database configuration information is stored. It would be better for us to put this .env file in .gitignore so it does not live in our repository and let each developer use any configuration on their local, but for learning purposes we will keep it visible.

Create the tables

The schema for the tables is stored in the repository in schema.sql. You can use psql to read this file and create the required database objects (tables, etc).

psql -d scout2020 -U robotiger -a -f schema.sql

Okay now you are ready to run the app locally. You will see this in the console log which tells you that your local Postgres database is being used:

postgresql://robotiger:robo1796@localhost:5432/scout2020

Database Configuration

You will need the following env vars set to the database:

• DB_USER
• DB_PASSWORD
• DB_HOST
• DB_PORT
• DB_DATABASE

Please reach out to the app admin for these details. You can create a .env file in your repo (.gitignore it) or set the env vars outside the repo.

Heroku

If you want to test in your Heroku then you'll need to provision a Postgres database in your heroku.

Postgres Add-on

You can add a Postgres database to your Heroku by going to your Heroku dashboard: https://dashboard.heroku.com/apps

• Click on Resource
• In the "Add-ons" section click on 'Add new Postgres Database'

Note that Heroku does not offer credentials for its free hobby plan so we will just use the default username/password (which they rotate). So you don't have to repeat the steps that you had to do in your local (role and database) - just take the default.

After you have provisioned a database for your Heroku, you can confirm it via command line interface on your local.

heroku addons // Shows the database, for example: postgresql-trapezoidal-95582

You could also see your Heroku database information at https://data.heroku.com/

Add Tables and Data

You need to create the tables in your Heroku database and add some initial data (at least the reference data such as the rows in the competition table). You can use the schema.sql file to do this using the -f option for psql. But if you have data already in your local database you can just push it to your Heroku. (There is also a pull which works it the opposite direction but in this step we are just trying to get our database set up in Heroku.)

heroku pg:reset DATABASE_URL --app <your heroku app name> // You will need to do this if your Heroku PG is not empty
heroku pg:push scout2020 DATABASE_URL --app <your heroku app name>

You should actually type in DATABASE_URL; don't try to replace it with some value. This is the name of the environment variable heroku will use to connect to your heroku database.

Testing

When you run your local app you can examine the database to see the rows being added:

psql -d scout2020 -U robotiger
=> \dt (list the tables)
=> select * from match;

You can also connect to your heroku database:

heroku pg:psql postgresql-trapezoidal-95582
=> \dt (list the tables)
=> select * from match;

Don't be alarmed that the database name is DATABASE (and not scout2020). It's okay; we are using PG defaults for our free hobby-dev Heroku plan :)

Team Heroku PG

Heroku PG username: robotiger Heroku PG passwd: (see admin) Heroku PG database: scout2020