Bootcamp
Search…
3.4.1: PostgreSQL, psql

Intro to Database Servers

Database servers run applications that allow programs to manipulate the data inside them. In this module we'll learn how to create, manage, and deploy database servers. Note that this is separate from managing the data inside the database, which is done by the SQL language. How SQL interacts with the database server hard drive depends on the particular SQL implementation.
Part of setting up a database is setting up database schema, i.e. what tables and columns are in the database. We'll learn more about how to design database schema in 3.4.6: SQL Schema Design, but for now Rocket will provide database schema that we will implement. Web applications typically do not manipulate database schema in response to user actions; application code depends on database schema, but the schema is not defined by the application.
The data in the database is stored separately from the application code.
Unlike with the data.json file we used prior to SQL, the data inside our database will not be part of the application repo. Database servers are often on separate machines from web application servers, such that they can be accessed by multiple applications. We will see this in Module 4 when we use Heroku, but for now, we will keep our database server on the same machine as our Express app.
The PostgreSQL server is typically on a separate machine from the web applications that access it.
Managing the database server application, e.g. ports, backups, version upgrades, is typically considered developer operations or DevOps and can be a separate role from application development.

Intro to PostgreSQL

PostgreSQL (or Postgres for short) is the database application server that we will be using. Other popular SQL implementations are MySQL and SQLite, but PostgreSQL is a good generalist implementation with a good balance of lightweight and feature-rich.
Postgres implements the SQL language in order to store and retrieve data from a set of files on the hard drive.
Postgres is a server application that uses its own TCP/IP protocol, usually on port 5432 (although this is configurable). Requests are sent to the server that contain the SQL language queries for the server to process. The result of the queries is sent back to the client.
Postgres is a software implementation of a database system. So far we've only dealt with a database at a conceptual level, and using that database though SQL.
However, a database system is the actual implementation of something that runs SQL and keeps the data. Each system implements the SQL language slightly differently, keeps the data on the disk in a slightly different way, and gets the data back out differently.
The database system ensures fundamental database properties like ACID (atomicity, consistency, isolation, durability). A database system needs to account for circumstances like when two opposing queries happen at the same time, or like when a long running INSERT query fails while executing, due to something like power failure. The system doesn't solve these problems, but it is guaranteed to behave in a consistent manner each time.
A database system also includes functionality like automatic data backup and database indexes for increasing query speed.

psql and psql Setup

Psql is the Postgres command-line client to access our databases. To help us learn SQL we will use psql to manipulate our databases with SQL commands. In later modules we will use a SQL client that allows us to manipulate our databases using a GUI.

Mac

Install postgres.app. Open the application and follow the setup instructions on the website. Do the optional step to configure $PATH to use included command line tools.

Ubuntu (for Windows users in WSL and EC2 Installation)

Install Postgres
sudo apt update
sudo apt upgrade
sudo apt install postgresql
sudo apt install postgresql-client
Set the Postgres server to start in the background:
sudo service postgresql start
Set password-less login by opening pg_hba.conf and copying the below contents into it.
sudo chmod 777 /etc/postgresql/12/main/pg_hba.conf
# "sudo" runs the command as the root user
# "$(which code)" gets the location of the VSCode application locally
​
sudo "$(which code)" /etc/postgresql/12/main/pg_hba.conf
If the above command doesn't work, try running VSCode without sudo.
# Open pg_hba.conf in VSCode
​
code /etc/postgresql/12/main/pg_hba.conf
The above will open a new VSCode window.
pg_hba.conf contents to copy: (replace the whole file contents with the lines below)
# TYPE DATABASE USER ADDRESS METHOD
​
# IPv4 local connections:
local all all trust
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
Restart Postgres to get the new configs:
sudo service postgresql restart
Login as the user postgres - the default root user for the Postgres database. This system user was created when you installed Postgres.
sudo su - postgres
Create a user and database named after your Ubuntu user. We can retrieve our Ubuntu username via the whoami command on the command line.
Use the Postgres createuser command to create a new Postgres database user that is named the same as your current user.
createuser -s <MY_UNIX_USERNAME>
Create a default Postgres database named after your current user. We will use DBs named after our usernames to test SQL syntax. Once we start building applications with SQL, we will name our DBs after our applications.
createdb <MY_UNIX_USERNAME>
Exit out of the postgres user
exit
Test it:
psql
To connect to a specific database, enter the database name as a parameter after psql, e.g. psql <DATABASE_NAME>.
On Ubuntu, when you restart your computer you may have to start the Postgres server with the following command.
sudo service postgresql start
On Mac you should be able to start Postgres by opening the Postgres app.
To quit out of psql, use the command \q. For more psql commands, see the psql cheatsheet here.

Exercises

Create a table and make SQL commands.
The CREATE TABLE is different from the SQL query in 3.4.1. This is because some SQL syntax is different in Postgres from SQLite.

CREATE DATABASE

Run this on the command line (outside of psql). A rule of thumb is to create a new database instance per app. This is to ensure we do not mix data between apps and corrupt our databases. For now we will use DBs named after our Unix usernames to test SQL syntax.
createdb <MY_UNIX_USERNAME>

CREATE TABLE

This and the following SQL query exercises need to be run from within psql. Notice we are specifying the data type of the columns. See a full list of Postgres data types here.
CREATE TABLE cats (
id SERIAL PRIMARY KEY,
name TEXT,
type TEXT,
weight INTEGER
);

INSERT

Note that we are not specifying the id column in the INSERT statement.
INSERT INTO cats (name, type, weight) VALUES ('Mr. Snuggles', 'Calico', 327);
INSERT INTO cats (name, type, weight) VALUES ('Jake AlPurrrrtsen', 'Bambino', 424);
INSERT INTO cats (name, type, weight) VALUES ('Furry Mc Furmeister', 'Persian', 512);
INSERT INTO cats (name, type, weight) VALUES ('Kai', 'LaPerm', 387);

Primary Key

In this course, for every table, we'll be naming a column called id. A feature of SQL systems is that we can have an auto-incrementing column that give an automatic and guaranteed unique number for every row created.

SELECT

Get the name column from all rows.
SELECT name FROM cats;
Get the id and name column from all rows.
SELECT id, name FROM cats;
Get the same columns in a different order.
SELECT name, id FROM cats;
Get all rows from all columns of this table.
SELECT * FROM cats;

SELECT ... WHERE ...

SELECT * FROM cats WHERE id=1;

UPDATE

UPDATE cats SET name = 'Susan Chan' WHERE id=2;

DELETE

DELETE FROM cats WHERE id=2;
Note that a property of the unique id column is that if there is another insert query, the ids do not rearrange. Try this out by making a new INSERT query.
INSERT INTO cats (name, type, weight) VALUES ('Chubby Pants', 'Calico', 433);

SELECT ... ORDER BY ...

SELECT * FROM cats ORDER BY name ASC;

SELECT COUNT

SELECT COUNT (*) FROM cats;

Further Reading

  1. 1.
    Past students have found this document helpful in describing differences between SQL dialects: https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres​