Repository files navigation

Tutorial for setting up PostgreSQL using Heroku.

• This is forbrand new SQL programmers, includingcoding classstudents who want a simple and to-the-point guide on connecting toa Heroku provisioned Postgres database.

• This guide assumes you already have fundamental Bash and Heroku knowledge. Ifyou are new to Heroku, read ourHeroku Getting Startedguide.

• This guidedoes not support Windows. It assumes you use eithermacOS orUbuntu GNU/Linux.

This was original created for Kickstart Coding, the affordable,inclusive, and intensive coding course teaching cutting-edge Python /Django and JavaScript / React web development in Oakland, CA.Learn more and enroll here.

Databases are how computers and web applications store thousands or millions ofitems of data, and look them up quickly. SQL is the most popular programminglanguage for talking to databases.

Postgres is one of the most popular and powerful databases systems outthere, and it uses a dialect of SQL.

Heroku is a hosting service that also provides a "free-tier" version ofPostgres that they will run for us.

• database - How web apps store lots and lots of data. In some ways,databases resemble Excel spreadsheets, because they store grids ("tables") ofdata.

• SQL - The programming language for talking to databases.

• Postgres - A popular and powerful database management system ("DBMS")that uses SQL.

Note: See bottom of this guide for an spreadsheet analogy.

• table - A named collection of data. Consists of series of columns. Adatabase can have multiple tables.

• column - A named column of data. A table must have 1+ columns.

• row - A single item of data. Found within tables, and has values (e.g.table data cells) for each column in that table. A table can have 0+ rows,such as one for each user.

• Sqlite - A simple, limited version of SQL, that's great for learning andsimple applications, but does not work for web apps with multiple users

• MySQL - Another popular database and SQL dialect (bought by Oracle).MariaDB is a truly free software spin-off of this.

• MS SQL - Another popular database and SQL dialect (by Microsoft).

• schema - A common and flexible term that more or less means the shape orlayout of your database. In Postgres, also it means a way for a database tofurther "group together" it's tables (more or less).

• Heroku (the business) - a company that will run your HTTP serverapplication for you, and expose it to the web. They also offer free Postgresservers, which is what we are using from them.

• heroku (the CLI command) - A tool that Heroku the company has developedwhich we will use for interacting with your Heroku account, includingcreating new Postgres databases.

Now that you know a little about Postgres, it's time to install the client onyour system and get practice connecting to a Heroku hosted version of it.

Ubuntu Linux users, run the following command:

sudo apt-get install postgresql-client

macOS users can't install the client separately, so run the following commandto install everything:

brew install postgres

First you will need to have a Heroku app already created. This can be either aproject you are working on, or just a brand new thing. If you don't have anapp, you can useheroku create to create one.

1. Provision for that app a new Postgres Database (free "hobby" tier):

heroku addons:create heroku-postgresql:hobby-dev --app pure-crag-68

2. Connect to your database with the command-line client:

heroku pg:psql --app pure-crag-68

NOTE: For the these last commands, you will need to change "pure-crag-68" tothe similarly random name your app got when you created it.

Connect to your database with the commandline client:

heroku pg:psql --app pure-crag-68

For the second challenge, create the tables necessary to house the followingdata. Add the data, and practice querying it.

Example code to run with Postgres:

• Create a new table

CREATETABLEpeople (    idnumint,    first_namevarchar(255),    last_namevarchar(255));

• Check which tables we have

\d\d people

• Add data to a table

INSERT INTO people (idnum, first_name, last_name)VALUES (1,'Jane','Hacker');

• Check the data in a table

SELECT*FROM people;

• Challenge: Add data for a person with first name"Dolan" and last name"Duck", and ID number2. Ensure it is added withSELECT.

Included in this repo is a SQL file that contains information on the 50 statesin the USA, including Puerto Rico and District of Columbia.

1. Copy & paste that SQL file into your window (or be clever and redirect itinto the Postgres prompt using Bash).

2. View the data:

SELECT*FROM states;

3. Try usingWHERE in conjunction toSELECT to look for states withdifferent characteristics (will have to Google for more information onWHERE). (e.g. Only mid-sized states, with population between 5 and 10million).

4. Try usingORDER BY in conjunction toSELECT to sort the output bydifferent orderings. (e.g. Sorted by population, least populated first)

5. Try usingLIMIT to only select the top state that matches a certainordering and criteria. (e.g. only the most populous state that starts with theletterM).

• Look up remaining SQL commands. Get practice with all the different optionsforSELECT,INSERT,DELETE,UPDATE.

• If you have time, look up online tutorials on installing and running Postgreslocally (as opposed to Heroku). This is something you may need to do at somepoint in the future, so doesn't hurt playing around with that now. It isn'ttoo hard, though sometimes can have hang-ups.

• Don't forget to end all your SQL statements with ";"! Otherwise it will thinkyou are still adding on to the same statement, and typically result in asyntax error.

• If you use an ORM, such as Django ORM, it is actually writing and reading theSQL code for you under the hood. Even using an ORM, you will often need toknow how to use SQL for certain operations, or at least understand what ishappening.

• All versions (dialects) of SQL are about ~90% the same, so if you learn one,you'll be able to use others.

• If you are having a hard time understanding DBs, it might help to think ofthem as kind of like a spreadsheet software, such as Microsoft Excel. Thisis a very rough analogy, but maybe this will help: