Setting up postgres

database_setup_guidance.md

Setting up a database

We're going to set up a postgres database and play with it a bit to understand its structure!

Part 1

What is a database?

A structured set of data held, usually in files, in a computer that is accessible in various ways.

Is Postgres a database?

A common database system for modern web development is called PostgreSQL.

PostgreSQL (the name is interchangeable with postgres) is actually a server that runs a database. Therefore, it can be started, stopped, and interacted with as you might expect - through an interface.

In this challenge, you will install the PostgreSQL database system, and interact with it to create a database.

Learning Objectives covered

• Install PostgreSQL.
• Create a database.

To complete this challenge, you will need to

• Install the postgresql command-line package via Homebrew (use the command brew).
• Start postgres and set it to run automatically when your computer starts. (See the instructions in the output that brew shows when it's installing postgres.)
• Check your installation by running psql in the terminal. Use the resources below to understand any errors that you see.
• Use the CREATE DATABASE command in psql to set up a PostgreSQL database with the same name as your computer username, e.g. timmy507. PostgreSQL will connect to this database on startup.

Resources

• Homebrew Package Manager
• PostgreSQL Command Line Cheat Sheet

Walkthrough - Mac OS | Linux

Part 2

In this part, you will use Postgres' built-in command-line interface to interact with the database using a database-specific language, SQL. You will create and drop a table with some columns and record your changes to the database schema.

You rarely have to interact with databases from the command-line like this (why?), but play around to create and destroy a few tables so you know what's going on at the data storage level.

Learning Objectives covered

• Use the psql command to interact with Postgres
• Create and drop tables using SQL.

To complete this challenge, you will need to

• Create a new PostgreSQL database for - give it a name
• Use psql to connect to this new database.
• Use psql to create a table (give it a name) with at least two columns: id, a SERIAL PRIMARY KEY, and any others - explore different data types
• Now drop the table. And repeat.
• Record the database setup instructions, so you can do it again and again,

Hints

 

CLICK ME

• All of the commands you will need for this step are listed in the two documents listed below. You may need to check both of them.
• Don't forget that you can list databases using \l and tables using \dt. You can use these commands to check that your set up has been successful.  

Resources

• PostgreSQL Command Line Cheat Sheet
• SQL in One Page
• PostgreSQL Cheat Sheet

Walkthrough

Part 3

Manipulating Table Data

You will use SQL to create, read, update and delete (CRUD) data in the table you created, inside your PostgreSQL database. The vast majority of queries will be SELECT statements.

Learning Objectives covered

• Use SQL terms like SELECT, FROM, WHERE and * to query a database table
• Use SQL terms like INSERT, UPDATE and DELETE to create, update and delete database entries

To complete this challenge, using the psql command line interface

• Display the current state of your table
• INSERT at least 2 more records in your table, using an INSERT statement.
• SELECT all the records using a SELECT statement.
• DELETE only one of the records using a DELETE statement.
• Update one of the records using an UPDATE statement.
• How do you know if each of the statements was successful - how is it verified?

Hints

 

CLICK ME

• Again, use the documents linked below to look up the commands you need.
• Sanity check each step using a SELECT statement.  

Resources

• PostgreSQL Command Line Cheat Sheet
• Alternative PostgreSQL Cheat Sheet
• SQL in One Page
• Relational Database: Terminology

Walkthrough

Part 4

Connecting to postgres from a ruby application

Learning Objectives covered

• Configure an application to connect to a database using an ORM

To complete this challenge

• Choose one of the ORMs below and follow the setup
• Configure an application to connect to a database using an ORM
• Successfully persist data in your database from your application - only by interacting with your application using your browser.

Resources

• Sinatra - DataMapper | DataMapper docs
• Sinatra - ActiveRecord