Saruni
DocsGuidesGitHub
  • Database
    • PostgreSQL
  • Deployment

PostgreSQL

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.

Creating a database

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.

bash
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 psql too.

After entering the interactive client we can create a new PostgreSQL database with the CREATE DATABASE database_name statement and confirm creation with the \list command.

bash
# 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     |

Writing SQL

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.

bash
# Connect to database.
\c learn_postgres
sql
# 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.

sql
INSERT INTO dogs (name, breed) VALUES ('Bernie', 'Dachshund');
INSERT INTO dogs (name, breed) VALUES ('Stephanie', 'Weimaraner');
INSERT INTO dogs (breed) VALUES ('French Bulldog');

Basic SQL queries

Using a relational database means we can write SQL queries to understand our data.

sql
SELECT * FROM dogs;
#  id |   name    |     breed
# ----+-----------+----------------
#   1 | Bernie    | Dachshund
#   2 | Stephanie | Weimaraner
#   3 |           | French Bulldog
SELECT name FROM dogs WHERE breed = 'Dachshund';
#  name
# --------
#  Bernie

SQL aggregate functions

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.

sql
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.

Relations

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.

  • One-to-one
  • One-to-many
  • Many-to-many

One-to-one relationship

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” (ssn) table.

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.

sql
# 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.

sql
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".

One-to-many relationship

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 (M:1).

sql
# 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

Many to many relationship

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.

sql
# 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

Joins

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:

  • INNER
  • LEFT
  • RIGHT
  • FULL OUTER
  • CROSS
  • NATURAL
  • SELF

Join syntax

The different kinds of joins return different data, but the general syntax is similar across the board.

sql
SELECT [table_name.column_name1, table_name.column_name2, ...]
FROM table_name1
join_type JOIN table_name2
ON (join_condition);

Let’s take a look at a couple of the more common joins in practice.

Example: inner join

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.

sql
SELECT owners.*, dogs.*
FROM owners
INNER JOIN dogs
ON (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

Example: left join

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.

sql
SELECT owners.*, dogs.*
FROM owners
LEFT JOIN dogs
ON (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 dogs table.

Conclusion

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.