Relational databases like SQLite, MySQL and PostgreSQL store data in structures known as tables, which are similar to spreadsheets in that they’re made up of columns and rows. Each column has a name, and each row stores a set of information.
Over the course of this guide we’ll learn how to create tables using Structured Query Language (SQL), how to write SQL queries and demonstrate why traditional relational databases are a great choice when writing web applications.
After following the suitable installation instructions for our operating system we should be able to check our Postgres version in the command line as a sign of a successful install.
psql --version# => psql (PostgreSQL) 12.2
psql is a terminal-based front-end for interacting with PostgreSQL. In Saruni, all database interactions will be codified, but for convenience when learning we’ll utilise
After entering the interactive client we can create a new PostgreSQL database with the
CREATE DATABASE database_name statement and confirm creation with the
# Enter the Postgres terminal client.psql# Create a new Postres database.CREATE DATABASE learn_postgres;# List defined databases.\list# List of databases# Name | Owner | Encoding |# ----------------+----------+----------+# learn_postgres | saruni | UTF8 |
Structured Query Language (SQL) is used to communicate with relational databases.
We wrote our first SQL statement when we created our database. By convention, SQL keywords are stylized in uppercase, with user-selected parameters like our database name in lowercase. We mark the end of a SQL statement with a semicolon.
SELECT name FROM dogs WHERE breed = 'Dachshund';
After connecting to our database, we create a table with the
CREATE TABLE table_name statement, optionally providing the name and types of the columns on that table.
# Connect to database.\c learn_postgres
# Create a table with columns `id`, `name`, and `breed`.CREATE TABLE dogs (id SERIAL PRIMARY KEY,name TEXT,breed TEXT);# Display tables in database.\dt# List of relations# Schema | Name | Type | Owner# --------+------+-------+--------# public | dogs | table | saruni
Every table we create should include an
id SERIAL PRIMARY KEY column and data type. The value will auto-increment as new rows are added, which makes our data easier to access and update since PostgreSQL can identify a given row by this unique value.
We can use
INSERT INTO statements to add data to our new table.
INSERT INTO dogs (name, breed) VALUES ('Bernie', 'Dachshund');INSERT INTO dogs (name, breed) VALUES ('Stephanie', 'Weimaraner');INSERT INTO dogs (breed) VALUES ('French Bulldog');
Using a relational database means we can write SQL queries to understand our data.
SELECT * FROM dogs;# id | name | breed# ----+-----------+----------------# 1 | Bernie | Dachshund# 2 | Stephanie | Weimaraner# 3 | | French BulldogSELECT name FROM dogs WHERE breed = 'Dachshund';# name# --------# Bernie
SQL aggregate functions are more complex SQL queries that can retrieve minimum and maximum values from a column, sum values in a column, get the average of column values, or count the number of records in a column that meet user-defined conditions.
SELECT breed, COUNT(breed) FROM dogs GROUP BY breed;# breed | count# ----------------+-------# French Bulldog | 1# Weimaraner | 1# Dachshund | 1
The result of SQL queries could be served to users of our web applications to provide summary statistics on the data we hold.
So far we’ve seen how to add data to tables and how to query that data. That’s great, but the true magic of relational databases comes when we introduce foreign keys. These keys allow us to form relationships between tables.
There are three kinds of relationships in relational database design.
In a one-to-one (
1:1) relationship a row in our table has only one matching row in a separate table, and vice versa.
For example, a veterinary practice may store a unique identifier for each dog they have registered—like a social security number. In that case, an entry in our
dogs table would have a relationship with an entry in our “social security number” (
In practice, this kind of relationship is not common since the
ssn value could just as easily be stored directly on the
dogs table but there are valid reasons for maintaining this type of relationship like securing a table with access privileges.
# Create new social security number (`ssn`) table with 1:1 relation.CREATE TABLE ssn (dog_id SERIAL,unique_identifier UUID,PRIMARY KEY (dog_id),FOREIGN KEY (dog_id) REFERENCES dogs (id) ON DELETE CASCADE);# Add data to `ssn` table.INSERT INTO ssn (dog_id, unique_identifier) VALUES(1, '8dca558b-4b67-4814-ba66-f50981f3f340'),(2, 'a565bb2b-3267-42bc-b9d2-e390981f27a4'),(3, '43dafab7-f224-4dde-ae87-48e168425099');# => INSERT 0 3
If we try to add
INSERT values to our
ssn table with an invalid
dog_id (i.e. no dog with the
id exists) the statement will fail to execute.
INSERT INTO ssn (dog_id, unique_identifier) VALUES(4, '75b98ac7-0277-48c3-9893-6c634e92a0fe');# => ERROR: insert or update on table "ssn" violates foreign key constraint "ssn_dog_id_fkey"# => DETAIL: Key (dog_id)=(4) is not present in table "dogs".
In a one-to-many (
1:M) relationship a row in our table can have many matching rows in a separate table, but not the other way around.
For example, an owner may have many dogs but a given dog has one owner. Depending on the perspective, this type of relationship may also be known as many-to-one (
# Create new `owners` table.CREATE TABLE owners (id SERIAL PRIMARY KEY,name TEXT);# Alter `dogs` table to support M:1 relation.ALTER TABLE dogs ADD COLUMN owner_id INTEGER;ALTER TABLE dogs ADD CONSTRAINT fk_owner_id FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE;# Add data to `owners` table and update `dogs` table with owners.INSERT INTO owners (name) VALUES ('Alexa'), ('Malik'), ('Jada');UPDATE dogs SET owner_id = 1 WHERE name = 'Bernie';UPDATE dogs SET owner_id = 2 WHERE name <> 'Bernie' OR name IS NULL;# View updated `dogs` table with M:1 relation.SELECT id, name, owner_id FROM dogs;# id | name | owner_id# ----+-----------+----------# 1 | Bernie | 1# 3 | | 2# 2 | Stephanie | 2
In a many-to-many (
M:M) relationship a row in our table can have many matching rows in a separate table, and vice versa.
For example, a dog may attend many types of training classes and those training classes may have many dogs as participants (yes, we really managed to devise dog-related scenarios for all three kinds of relations).
In practice, many-to-many relationships can be thought of as two one-to-many relationships linked by an intermediary table, known as a junction- or cross-reference table. This table links the other two by way of two fields that reference the
PRIMARY KEY of the other two tables.
# Create a new `classes` table.CREATE TABLE classes (id SERIAL PRIMARY KEY,name TEXT);# Add data to `classes` table.INSERT INTO classes (name) VALUES ('Agility'), ('Obedience'), ('Trials');# Create a new `participants` table to support M:M relation.CREATE TABLE participants (id SERIAL PRIMARY KEY,dog_id int NOT NULL,class_id int NOT NULL,FOREIGN KEY (dog_id) REFERENCES dogs(id) ON DELETE CASCADE,FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE);# Add data to `participants` table.INSERT INTO participants (dog_id, class_id) VALUES (1, 1), (1, 2), (2, 2);# id | dog_id | class_id# ----+--------+----------# 1 | 1 | 1# 2 | 1 | 2# 3 | 2 | 2
We use SQL joins to handle queries across more than one table.
JOIN statements link tables together, usually through the keys that define the relationship between the tables.
There are several kinds of joins supported by PostgreSQL:
- FULL OUTER
The different kinds of joins return different data, but the general syntax is similar across the board.
SELECT [table_name.column_name1, table_name.column_name2, ...]FROM table_name1join_type JOIN table_name2ON (join_condition);
Let’s take a look at a couple of the more common joins in practice.
An inner join return all rows when there is at least one match in both tables. Since inner joins are the most common, if we don’t specify a
join_type in our SQL statement PostgreSQL will fallback to an inner join.
SELECT owners.*, dogs.*FROM ownersINNER JOIN dogsON (owners.id = dogs.owner_id);# id | name | id | name | breed | owner_id# ----+-------+----+-----------+----------------+----------# 1 | Alexa | 1 | Bernie | Dachshund | 1# 2 | Malik | 3 | | French Bulldog | 2# 2 | Malik | 2 | Stephanie | Weimaraner | 2
A left join—also known as a left outer join—returns all rows from the left table, and the matched rows from the right table.
SELECT owners.*, dogs.*FROM ownersLEFT JOIN dogsON (owners.id = dogs.owner_id);# id | name | id | name | breed | owner_id# ----+-------+----+-----------+----------------+----------# 1 | Alexa | 1 | Bernie | Dachshund | 1# 2 | Malik | 3 | | French Bulldog | 2# 2 | Malik | 2 | Stephanie | Weimaraner | 2# 3 | Jada | | | |
With the left join, the owner with name
Jada is included in the join table despite lacking an
owner_id relation on the
This guide introduced relations in the context of a PostgreSQL database as well as SQL, which enables us to query the data stored within it. An understanding of this content provides us with the mental models needed to build sophisticated web applications.