Agenda

• SQL
  • Definition, basic syntax, querying and more
• ORMs
  • Definition, popular ORMs and examples
• Best Practices and Tips
  • Performance, security and debugging

SQL Database Example

Databases, Tables, Rows and Columns

Imagine we have a PostgreSQL server running:

postgres://localhost:5432

• Databases

• blog

• supermarket

• gym

Databases, Tables, Rows and Columns

Databases, Tables, Rows and Columns

Table Columns

• users
  • id - int - Primary key
  • user_type - int - Foreign key
  • name - varchar
  • ...
  • created_at - datetime

Databases, Tables, Rows and Columns

Table Rows

blog=# select id, user_type_id, name, email, created_at from users;
 id | user_type_id |   name    |           email           |         created_at
----+--------------+-----------+---------------------------+----------------------------
  1 |            1 | André     | andre.luciani@email.com   | 2023-06-25 20:31:46.438607
  2 |            1 | John      | john.doe@email.com        | 2023-06-25 20:31:46.438607
  3 |            1 | Priscilla | priscilla.scott@email.com | 2023-06-25 20:31:46.438607
(3 rows)

Data Manipulation Language (DML)

• Subset of a programming language used explicitly to make changes in the database
  (e.g. CRUD operations).

• In case of SQL:

  • SELECT - Retrieve

  • INSERT - Create

  • UPDATE - Modify existing data

  • DELETE - Exclude entries

The SELECT statement

• Used to retrieve data. Basic syntax:

SELECT
  column1, column2, column3
FROM
  table_name;

• The wildcard * can be used to select "all columns"

• When making SQL statementes, always remember to close the statement with ;

• The SQL words (SELECT, FROM, ...) usually are written with uppercase for better readability.

The SELECT statement

Example using psql:

psql      # starts postgres interactive terminal
\c blog   # connects to 'blog' database

After connecting to the database, execute the query:

blog=# SELECT id, name, last_name, email FROM users;
 id |   name    | last_name |           email
----+-----------+-----------+---------------------------
  1 | André     | Luciani   | andre.luciani@email.com
  2 | John      | Doe       | john.doe@email.com
  3 | Priscilla | Scott     | priscilla.scott@email.com
(3 rows)

The INSERT statement

• Used to add data. Basic syntax:

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

• Multiple rows can be added by providing more values grouped with ()

The INSERT statement

Adding a new row on the posts table:

INSERT INTO posts(title, content, create_at, updated_at)
VALUES ('Another post', 'Another example', NOW(), NOW());

• NOW() is a SQL function that returns the current date and time. These functions may differ depending on the DMS used.

The INSERT statement

id |             title              |         content          |         create_at          |         updated_at
----+--------------------------------+--------------------------+----------------------------+----------------------------
  1 | PostgreSQL 101                 | This is an example post. | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  2 | Bread Recipe                   | This is an example post. | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  3 | Will AI take over the world?   | This is an example post. | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  4 | How to learn a new technology. | This is an example post. | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
+ 5 | Another post                   | Another example          | 2023-06-25 22:23:57.567231 | 2023-06-25 22:23:57.567231
(5 rows)

The UPDATE statement

• Used to update entries.
  Basic syntax:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

The UPDATE statement

Updating a row on the posts table:

UPDATE posts
SET content = 'The post content was updated!',
    updated_at = NOW(),
WHERE id = 2;

The UPDATE statement

 id |             title              |            content            |         create_at          |         updated_at
----+--------------------------------+-------------------------------+----------------------------+----------------------------
  1 | PostgreSQL 101                 | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
- 2 | Bread Recipe                   | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  3 | Will AI take over the world?   | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  4 | How to learn a new technology. | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  5 | Another post                   | Another example               | 2023-06-25 22:23:57.567231 | 2023-06-25 22:23:57.567231
+ 2 | Bread Recipe                   | The post content was updated! | 2023-06-25 20:30:40.617806 | 2023-06-25 22:36:44.371102

The DELETE statement

Basic syntax:

DELETE FROM table_name
WHERE condition;

The DELETE statement

Deleting a row from the posts table:

DELETE FROM posts
WHERE id = 5;

The DELETE statement

 id |             title              |            content            |         create_at          |         updated_at
----+--------------------------------+-------------------------------+----------------------------+----------------------------
  1 | PostgreSQL 101                 | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  3 | Will AI take over the world?   | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
  4 | How to learn a new technology. | This is an example post.      | 2023-06-25 20:30:40.617806 | 2023-06-25 20:30:40.617806
- 5 | Another post                   | Another example               | 2023-06-25 22:23:57.567231 | 2023-06-25 22:23:57.567231
  2 | Bread Recipe                   | The post content was updated! | 2023-06-25 20:30:40.617806 | 2023-06-25 22:36:44.371102

Data Definition Language (DDL)

• Subset of a programming language used to define or describe databases schemas (the "structure" of the DB).

• A few examples from SQL:

  • CREATE TABLE - Create tables

  • ALTER TABLE - Modify existing tables

  • DROP TABLE - Exclude tables

The CREATE TABLE statement

• Used to create tables. Basic syntax:

CREATE TABLE table_name (
   column1 datatype(length) column_constraint,
   column2 datatype(length) column_constraint,
   column3 datatype(length) column_constraint,
   table_constraints
);

• Each column has a datatype and may have constraints.

• The statement CREATE TABLE IF NOT EXISTS ... can be used to prevent an error if the table name is already in use.

The CREATE TABLE statement

Constraints

• Used to ensure data consistency

• Common constraints used in columns:

  • NOT NULL - values cannot be null

  • UNIQUE - all rows must have different values for the column

  • PRIMARY KEY - Column that is unique, not null and used to identify each row of a table.

  • CHECK - Boolean verifier to prevent wrong inputs
    (e.g. CHECK (price > 0))

The CREATE TABLE statement

Constraints

• Common constraints used in columns (a few more):

  • FOREIGN KEY - Link tables by storing the PRIMARY KEYof another table.

  • DEFAULT - Sets a default value for a column if no value is specified

  • CREATE INDEX - Used to create and retrieve data from the database very quickly (more on that later)

The CREATE TABLE statement

CREATE TABLE posts (
    id serial PRIMARY KEY,
    title varchar NOT NULL,
    content varchar NOT NULL,
    create_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

• The title and content could have a minimum lenght and the create_at and updated_at columns could have default values.

• The create_at column has a typo, thankfully not everything is doomed yet...

The ALTER TABLE statement

• Used to modify tables. Usually add, remove, rename columns or its constraints:

ALTER TABLE table_name
RENAME COLUMN column_name
TO new_column_name;

ALTER TABLE table_name
ADD COLUMN column_name datatype column_constraint;

ALTER TABLE table_name
DROP COLUMN column_name;

The ALTER TABLE statement

Fixing the typo on the create_at column:

ALTER TABLE posts
RENAME create_at
TO created_at;

The ALTER TABLE statement

• It is possible to add only constraints as well, for instance:

ALTER TABLE users
ADD CONSTRAINT users_fk0 FOREIGN KEY (user_type_id) REFERENCES user_types(id);
ALTER TABLE user_post
ADD CONSTRAINT user_post_fk0 FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE user_post
ADD CONSTRAINT user_post_fk1 FOREIGN KEY (post_id) REFERENCES posts(id);

The DROP TABLE statement

• As the database evolves, it might be necessary to exclude tables (e.g. a table was deprecated because the data it held is now being stored in another place)

• The DROP TABLE command is used for these cases.

The DROP TABLE statement

Basic syntax:

DROP TABLE table_name;

The DROP TABLE statement

Deleting the posts table:

DROP TABLE posts;

But...

blog=# DROP TABLE posts;
ERROR:  cannot drop table posts because other objects depend on it
DETAIL:  constraint user_post_fk1 on table user_post depends on table posts
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

• Since it has constraints, the operation cannot be done...

The DROP TABLE statement

• Using the CASCADE solves the problem

DROP TABLE posts CASCADE;

blog=# DROP TABLE posts CASCADE;
NOTICE:  drop cascades to constraint user_post_fk1 on table user_post
DROP TABLE

• The operation returns which dependent objects were dropped because of CASCADE.

Database Relationships

There are three main SQL database relationships:

1. One-to-one
2. One-to-many (or Many-to-one)
3. Many-to-many

Let's see when to use each of them!

One-to-One

This relation is used when one row of table_one is linked (or related) to only one row of table_two

Example: A person and their birthplace, an employee and their salary, a user and their role

One-to-One

One-to-One

To achieve a one-to-one relationship between tables:

1. Create the two tables that will be linked
2. On one table, add a column with the FOREIGN KEY constraint.
3. The FOREIGN KEY column must be UNIQUE

One-to-One

CREATE TABLE users (
  id serial PRIMARY KEY,
  first_name VARCHAR(50)
);

CREATE TABLE salaries (
  user_id int UNIQUE NOT NULL,
  amount int
);

ALTER TABLE salaries
ADD CONSTRAINT users_salaries_fk0
FOREIGN KEY (user_id)
REFERENCES users (id);

One-to-Many

The one-to-many relation is the most used relation in SQL and occurs when one row from table_one is related one or many rows in table_two.

Example: A country and its states or cities, students and the class they belong to, employees and their department, among several others.

One-to-Many

One-to-Many

To achieve a one-to-many relationship between tables:

1. Create the two tables that will be linked
2. On one table, add a column with the FOREIGN KEY constraint.

One-to-Many

CREATE TABLE countries (
    id serial PRIMARY KEY,
    name varchar NOT NULL,
);
CREATE TABLE cities (
    id serial PRIMARY KEY,
    country_id integer NOT NULL,
    name varchar NOT NULL,
);
ALTER TABLE cities
ADD CONSTRAINT cities_fk0
FOREIGN KEY (country_id)
REFERENCES countries(id);

Many-to-Many

The many-to-many relation occurs when many rows from table_one are related to many rows in table_two.

Example: Product and suppliers, flights and passengers, etc.

Many-to-Many

• Many-to-many relation between customers and flights throught the passengers table
• The flights table has two one-to-one relationships with airports table.

Many-to-Many

To achieve a many-to-many relationship between tables:

1. Create the two tables that will be linked
2. Create a third table (called linking, bridging or junction table)
3. The third table will store the primary keys of both the tables to be linked

Many-to-Many

Creating the tables:

CREATE TABLE customers (
    id serial PRIMARY KEY,
    email varchar NOT NULL UNIQUE,
    name varchar NOT NULL,
);
CREATE TABLE passengers (
    id serial PRIMARY KEY,
    customer_id integer NOT NULL,
    flight_id integer NOT NULL,
);
CREATE TABLE flights (
    id serial PRIMARY KEY,
    from_airport_id integer NOT NULL,
    to_airport_id integer NOT NULL,
);

Many-to-Many

Adding the constraints:

ALTER TABLE flights
ADD CONSTRAINT flights_fk0
FOREIGN KEY (from_airport_id)
REFERENCES airports(id);

ALTER TABLE flights
ADD CONSTRAINT flights_fk1
FOREIGN KEY (to_airport_id)
REFERENCES airports(id);

Querying with SQL

• So far, we have focused on changing SQL databases, both the data itself (using DML) and the "structure" schema (using DDL), defining tables, columns and relations.

• More often than not, though, databases are read rather than changed

• Because of that, SQL has several features to query the data and that's what we will take a look at now...

Querying with SQL

• The SELECT statement was the first DML that was presented, and it is the command we will use to make more complex queries.

• We already know how to specify which columns and which table we want to get results, but that is a bit limited

Querying with SQL

• What if we want to filter the results based on relations between two (or more) tables?

• What about ordering the results based on some criteria?

• How can we generate analytics from a database?

Querying with SQL

• To answer all those questions, let's use a database as an example.
• There's a start.sh script that can be used in Gitpod to create a PostgreSQL database exactly like the one used in the next slides.
• PSQL was used in the slides, but any other tool works just as fine (PgAdmin, DataGrip, etc)

Querying with SQL

• Here's the database schema diagram used in the examples:
  
• It is a blog database with six tables

The LIMIT clause

• To limit the results returned, we can append the LIMIT keyword in a query, followed by the maximum number of results expected:

SELECT * FROM table_name LIMIT 5;

• When in doubt of the size of a table, it is a good practice to put a limit in the results to prevent slow queries (imagine querying a table with thousands of rows )

The LIMIT clause

• Seeing the query in practice:

blog=# SELECT * FROM users LIMIT 5;
 id |  name   |       email       | city_id | role_id
----+---------+-------------------+---------+---------
  1 | Viviana | viviana@email.com |       5 |       1
  2 | Callan  | callan@email.com  |       2 |       2
  3 | Aila    | aila@email.com    |       5 |       1
  4 | Moses   | moses@email.com   |       2 |       1
  5 | Amelia  | amelia@email.com  |       3 |       2
(5 rows)

The WHERE clause

• Another way we can refine SQL queries is using the WHERE keyword:

SELECT * FROM table_name
WHERE condition;

• The condition can vary a lot. Some examples are:
  • a numeric column is greater than some value
  • a column is not null
  • and so on...

The WHERE clause

• In the LIMIT example, there was a column role_id with different values (1 and 2). Let's filter only the rows where the column role_id is equal to 2:

SELECT * FROM users
WHERE role_id=2;

The WHERE clause

blog=# SELECT * FROM users WHERE role_id=2;
 id |  name  |      email       | city_id | role_id
----+--------+------------------+---------+---------
  2 | Callan | callan@email.com |       2 |       2
  5 | Amelia | amelia@email.com |       3 |       2
(2 rows)

• We can see there are two users with the role_id equal to 2

The ORDER BY clause

• If we want to specify how the results should be organized, we can use the ORDER BY statement:

SELECT * FROM table_name
ORDER BY column ASC;

• The ASC (default) keyword orders the results in ascending order
• The DESC keyword orders the result in descending order

The ORDER BY clause

• Let's order the cities stored in table cities based on the population column, from the most inhabited to the least inhabited:

SELECT * FROM cities
ORDER BY population DESC;

The ORDER BY clause

blog=# SELECT * FROM cities ORDER BY population DESC;
 id |      name      | population
----+----------------+------------
  1 | São Paulo      |   12396372
  2 | Rio de Janeiro |    6775561
  3 | Brasília       |    3094325
  4 | Salvador       |    2900319
  5 | Fortaleza      |    2703391
(5 rows)

The JOIN statemnet

LEFT JOIN

• The LEFT JOIN clause returns all the values from the "left" table and also the values from the "right" table where the join condition is met.

• Let's use a Venn diagram to visualize it better...

The JOIN statemnet

LEFT JOIN

The JOIN statemnet

LEFT JOIN

• The basic syntax is:

SELECT table_one.column_name, table_two.column_name
FROM table_one
LEFT JOIN table_two
ON condition;

The JOIN statemnet

LEFT JOIN

• Cheking what is the actual role of the users:

SELECT users.name, roles.name
FROM users
LEFT JOIN roles
ON users.role_id = roles.id
LIMIT 5;

The JOIN statemnet

LEFT JOIN

• Cheking what is the actual role of the users:

  name   | name
---------+-------
 Viviana | admin
 Callan  | basic
 Aila    | admin
 Moses   | admin
 Amelia  | basic
(5 rows)

The JOIN statemnet

LEFT JOIN

• To improve readability or simplify, we can use the AS clause to rename both the elements of the query and the columns returned:

SELECT u.name, r.name AS role
FROM users AS u
LEFT JOIN roles AS r
ON u.role_id = r.id
LIMIT 5;

The JOIN statemnet

LEFT JOIN

• To improve readability or simplify, we can use the AS clause to rename both the elements of the query and the columns returned:

  name   | role
---------+-------
 Viviana | admin
 Callan  | basic
 Aila    | admin
 Moses   | admin
 Amelia  | basic
(5 rows)

The JOIN statemnet

LEFT JOIN (excluding)

• The excluding LEFT JOIN returns all the values from the left table that do not have a matching row in the right table.

• Let's use a Venn diagram once again...

The JOIN statemnet

LEFT JOIN (excluding)

The JOIN statemnet

LEFT JOIN (excluding)

• The basic syntax is:

SELECT table_one.column_name, table_two.column_name
FROM table_one
LEFT JOIN table_two
ON table_one.column_name = table_two.column_name;
WHERE table_two.column_name IS NULL;

The JOIN statemnet

LEFT JOIN (excluding)

• Fetching all the posts that do not have an image attached:

SELECT p.title, p_i.id
FROM posts as p
LEFT JOIN posts_images as p_i
ON p.id = p_i.post_id
WHERE p_i.id IS NULL;

LEFT JOIN (excluding)

• Fetching all the posts that do not have an image attached:

                             title                              | id
----------------------------------------------------------------+----
 Fallen Angel : Fact versus Fiction                             |
 Can Blue Bottles Dance : An exploration of Memes               |
 How to Make Your Own Admirable Dress for less than £5          |
 Mickey Mouse - 10 Best Moments                                 |
 From Zero to Shape Shifter - Makeover Tips                     |
 7 Unmissable YouTube Channels About Thoughts                   |
 7 Pictures of Rihanna That We Would Rather Forget              |
 How to Attract More Admirable Subscribers                      |
 10 Awesome Ways to Photograph Blue Bottles                     |
 Introducing programmer - Who Am I And Why Should You Follow Me |
 10 Things Mickey Mouse Can Teach Us About Thoughts             |
 Snakes Are the New Black                                       |
 Blue Bottles Are the New Black                                 |
 The Week: Top Stories About Rihanna                            |
 How to Increase Your Income Using Just Your Knees.             |
 10 Awesome Ways to Photograph Snakes                           |
 7 Pictures of Paul McCartney That We Would Rather Forget       |
 How to Increase Your Income Using Just Your Ankles.            |
 20 Dress Reviews in Tweet Form                                 |
(19 rows)

The JOIN statemnet

RIGHT JOIN

• The RIGHT JOIN works (as you may have guessed) just like the LEFT JOIN but in this case the results returned are from the right table.

• Let's use a Venn diagram to visualize it better...

The JOIN statemnet

RIGHT JOIN

The JOIN statemnet

RIGHT JOIN (excluding)

The JOIN statemnet

RIGHT JOIN

SELECT table_one.column_name, table_two.column_name
FROM table_one
RIGHT JOIN table_two
ON table_one.column_name = table_two.column_name;

The JOIN statemnet

RIGHT JOIN (excluding)

SELECT table_one.column_name, table_two.column_name
FROM table_one
RIGHT JOIN table_two
ON table_one.column_name = table_two.column_name;
WHERE table_one.column_name IS NULL;

The JOIN statemnet

INNER JOIN

• Another type of SQL join is the INNER JOIN and in this case, the results returned are the ones that have values defined in both tables, i.e., the intersection between the tables based on a given condition.

The JOIN statemnet

INNER JOIN

The JOIN statemnet

INNER JOIN

SELECT table_one.column_name, table_two.column_name
FROM table_one
INNER JOIN table_two
ON table_one.column_name = table_two.column_name;

The JOIN statemnet

INNER JOIN

• Let's fetch the users and the cities they live:

SELECT users.name, cities.name AS city
FROM users
INNER JOIN cities
ON users.city_id = cities.id
LIMIT 15;

INNER JOIN

Let's fetch the users and the cities they live:

   name   |      city
----------+----------------
 Viviana  | Fortaleza
 Callan   | Rio de Janeiro
 Aila     | Fortaleza
 Moses    | Rio de Janeiro
 Amelia   | Brasília
 Chandler | Fortaleza
 Alicia   | Salvador
 Nehemiah | Fortaleza
 Everly   | Salvador
 Kayson   | Brasília
 Imani    | Brasília
 Jamie    | São Paulo
 Ximena   | São Paulo
 Alexis   | Rio de Janeiro
 Estrella | São Paulo
(15 rows)

The JOIN statemnet

INNER JOIN

• But is there any city without users associated with?

SELECT cities.name AS city, users.name
FROM users
RIGHT JOIN cities
ON users.city_id = cities.id
WHERE city IS NULL;

The JOIN statemnet

blog=# SELECT cities.name AS city, users.name
blog-# FROM users RIGHT JOIN cities
blog-# ON users.city_id = cities.id
blog-# WHERE users.city_id IS NULL;
      city      | name
----------------+------
 Belo Horizonte |
 Manaus         |
 Curitiba       |
 Recife         |
 Goiânia        |
(5 rows)

The JOIN statemnet

• There is also another type of JOIN which fetches all the rows from both the left and right tables

• This is the FULL JOIN and it is basically a LEFT JOINand RIGHT JOIN "combined".

The JOIN statemnet

FULL OUTER JOIN

The JOIN statemnet

FULL OUTER JOIN (excluding)

The JOIN statemnet

FULL OUTER JOIN

SELECT table_one.column_name, table_two.column_name
FROM table_one
FULL OUTER JOIN table_two
ON table_one.column_name = table_two.column_name;

The JOIN statemnet

FULL OUTER JOIN (excluding)

SELECT table_one.column_name, table_two.column_name
FROM table_one
FULL OUTER JOIN table_two
ON table_one.column_name = table_two.column_name;
WHERE table_one.column_name IS NULL OR table_two.column_name IS NULL;

The JOIN statement

• In the blog database being used for the examples we have a many-to-many relationship between the posts and images tables that is achieved through the posts_images junction table.

• What if we want to list the images URL for each of the posts that have at least one image attached?

The JOIN statement

• We can achieve that with the following steps:

  1. Do a INNER JOIN between the tables posts and posts_images
  2. Do another INNER JOIN between the results and the images table.

The JOIN statement

• Results goal:

The JOIN statement

Step 1:

SELECT posts.id AS "Post ID", posts.title AS "Title"
FROM posts
INNER JOIN posts_images
ON posts.id = posts_images.post_id;

The JOIN statement

 Post ID |                         Title
---------+-------------------------------------------------------
      19 | 10 Things Mickey Mouse Can Teach Us About Thoughts
      12 | Mickey Mouse - 10 Best Moments
      24 | 21 Myths About Blue bottles Debunked
      12 | Mickey Mouse - 10 Best Moments
      11 | How to Make Your Own Admirable Dress for less than £5
      21 | 10 Awesome Ways to Photograph Blue Bottles
      27 | Can Blue Bottles Dance : An exploration of Memes
      13 | How to Attract More Admirable Subscribers
      28 | Blue Bottles Are the New Black
      10 | From Zero to Shape Shifter - Makeover Tips
      23 | How to Increase Your Income Using Just Your Knees.
      19 | 10 Things Mickey Mouse Can Teach Us About Thoughts
      27 | Can Blue Bottles Dance : An exploration of Memes
      23 | How to Increase Your Income Using Just Your Knees.
       2 | 7 Pictures of Rihanna That We Would Rather Forget
      13 | How to Attract More Admirable Subscribers
(16 rows)

The JOIN statement

Step 2:

- SELECT posts.id AS "Post ID", posts.title AS "Title"
+ SELECT posts.id AS "Post ID", posts.title AS "Title", images.img_url as "Images"
FROM posts_images
INNER JOIN posts
ON posts_images.post_id = posts.id
+ INNER JOIN images
+ ON posts_images.image_id = images.id
+ ORDER BY posts.id;

The JOIN statement

 Post ID |                             Title                              |          Images
---------+----------------------------------------------------------------+--------------------------
       4 | 21 Myths About Snakes Debunked                                 | https://onlink.site/yQCF
       4 | 21 Myths About Snakes Debunked                                 | https://onlink.site/iVhX
       4 | 21 Myths About Snakes Debunked                                 | https://onlink.site/93iP
       5 | Introducing programmer - Who Am I And Why Should You Follow Me | https://onlink.site/AuUT
       5 | Introducing programmer - Who Am I And Why Should You Follow Me | https://onlink.site/93iP
       7 | Can Snakes Dance : An exploration of Memes                     | https://onlink.site/YMrl
       8 | Snakes Are the New Black                                       | https://onlink.site/AuUT
      12 | Mickey Mouse - 10 Best Moments                                 | https://onlink.site/yQCF
      16 | The Week: Top Stories About Rihanna                            | https://onlink.site/iVhX
      20 | Mistakes That Snakes Make and How to Avoid Them                | https://onlink.site/AuUT
      20 | Mistakes That Snakes Make and How to Avoid Them                | https://onlink.site/93iP
      22 | 7 Pictures of Paul McCartney That We Would Rather Forget       | https://onlink.site/iVhX
      23 | How to Increase Your Income Using Just Your Knees.             | https://onlink.site/93iP
      25 | Introducing database - Who Am I And Why Should You Follow Me   | https://onlink.site/AuUT
      25 | Introducing database - Who Am I And Why Should You Follow Me   | https://onlink.site/YMrl
      26 | Fallen Angel : Fact versus Fiction                             | https://onlink.site/YMrl
(16 rows)

The GROUP BY statement

• We use GROUP BY to group rows using an aggregate function on one or more columns.

• Some of the possible aggregate functions are:

  • COUNT() - returns the number of rows that meet the condition

  • MAX(), MIN() and AVG() - return the maximum, minimum and average of the values

  • SUM() - returns the sum of the values

The GROUP BY statement

• With GROUP BY we can answer:

  • How many users live in the most inhabited city from the cities table?

  • Who is the user with most posts?

The GROUP BY statement

Basic syntax:

SELECT AGG(column_name_one), column_name_two
FROM table_name
GROUP BY column_name_two;

• Where AGG() is an aggregate function

The GROUP BY statement

Let's start with a query using a single table:

SELECT COUNT(image_id), post_id
FROM posts_images
GROUP BY post_id
ORDER BY count DESC;

The GROUP BY statement

• The result shows the posts ordered by the number of images attached:

 count | post_id
-------+---------
     3 |       4
     2 |      25
     2 |      20
     2 |       5
     1 |      12
     1 |      23
     1 |      22
     1 |       8
     1 |      26
     1 |      16
     1 |       7
(11 rows)

The GROUP BY statement

But we can use GROUP BY on joins too:

SELECT COUNT(users.id) AS "Users" , cities.name AS "City"
FROM users
INNER JOIN cities
ON users.city_id = cities.id
GROUP BY cities.name
ORDER BY "Users" DESC;

The GROUP BY statement

And get the number of users per city:

 Users |      City
-------+----------------
     9 | Salvador
     7 | Brasília
     6 | Fortaleza
     5 | São Paulo
     3 | Rio de Janeiro
(5 rows)

Subqueries

Subqueries

• Subqueries (also called inner queries) are used to query data "inside" another query.

• For instance, we may use subqueries to filter out rows before making a JOIN statement, or to define values to be used in thw WHERE clause.

• Let's see an example to better understand the concept!

Subqueries

• The basic syntax of subqueries is quite simple, just wrap a regular query in parenthesis:

SELECT column1, column2
FROM table_name1
WHERE condition
    (SELECT column1, column2
     FROM table_name2);

Subqueries

• Here's an example using the same blog dataset used in the "Querying" section

• In this example the query will use a subquery to get all the posts that have authors with names that start with letter "A"

• This query does not look too useful, but it's just an example

Subqueries

• The subquery will look like this:

SELECT id
FROM users
WHERE name LIKE 'A%';

Subqueries

 id
----
  3
  5
  7
 14
 17
 21
 24
 26
(8 rows)

Subqueries

• Great! Now, we can use the values returned to filter the posts table:

SELECT id,title
FROM posts
WHERE user_id IN
    (SELECT id
    FROM users
    WHERE name LIKE 'A%');

Subqueries

 id |                            title
----+--------------------------------------------------------------
  9 | 20 Dress Reviews in Tweet Form
 11 | How to Make Your Own Admirable Dress for less than £5
 15 | Unboxing My New Shape Shifter Poo
 18 | 7 Unmissable YouTube Channels About Thoughts
 22 | 7 Pictures of Paul McCartney That We Would Rather Forget
 25 | Introducing database - Who Am I And Why Should You Follow Me
 26 | Fallen Angel : Fact versus Fiction
 28 | Blue Bottles Are the New Black
 29 | 20 Hat Reviews in Tweet Form
 30 | From Zero to Fallen Angel - Makeover Tips
(10 rows)

Subqueries

• The previous example was quite simple, but subqueries are very powerful and can be used more than once in a single query

• One important thing to notice is that subqueries are somewhat similar to joins. The same results can be achieved with both features. But a "rule of thumb" is to use subqueries to define conditions.

Views

Views

Let's remember one query we've used before to list blog posts titles along with their images

SELECT posts.id AS "Post ID",
    posts.title AS "Title",
    images.img_url as "Images"
FROM posts_images
    INNER JOIN posts ON posts_images.post_id = posts.id
    INNER JOIN images ON posts_images.image_id = images.id
ORDER BY posts.id;

Views

We can create a view to use the data returned exactly as a table. It has a few benefits:

1. Simplifies complex queries
2. Can limit what data is shared with users
3. Provides more meaningful/readable column names
4. Can be used as a step in a really complex query

Views

• When creating views, we must provide a name to it
• View do not persist data anywhere, so when querying from a view the underlying query will be executed too

Views

Basic syntax:

CREATE VIEW view_name AS
-- query to be saved

Views

Using the example shown before:

CREATE VIEW vw_post_titles_images AS
SELECT posts.id AS post_id,
    posts.title AS post_title,
    images.img_url as image_link
FROM posts_images
    INNER JOIN posts ON posts_images.post_id = posts.id
    INNER JOIN images ON posts_images.image_id = images.id
ORDER BY posts.id;

Views

After creating the view, we can query as usual:

blog=# SELECT * FROM vw_post_titles_images LIMIT 5;
 post_id |                      post_title                       |        image_link
---------+-------------------------------------------------------+--------------------------
       6 | Shape Shifter : Fact versus Fiction                   | https://onlink.site/yQCF
       9 | 20 Dress Reviews in Tweet Form                        | https://onlink.site/93iP
      10 | From Zero to Shape Shifter - Makeover Tips            | https://onlink.site/YMrl
      10 | From Zero to Shape Shifter - Makeover Tips            | https://onlink.site/93iP
      11 | How to Make Your Own Admirable Dress for less than £5 | https://onlink.site/yQCF
(5 rows)

Indexes

Indexes

• In SQL databases indexes are used to query data with better performance.

• They work similarly to an index of a book: instead of scanning through all the pages of the book searching for a keyword,
  we can look at the index and find what we're looking for way faster.

Indexes

• Indexes may be used for columns that are queried a lot.
• Indexes are created automatically for primary keys or unique constraint columns (it makes sense, since primary keys or unique values are often used in SQL queries)

Indexes

• Basic syntax:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

And just like that, you've created an index!

Indexes

• When creating indexes, the index_name must be unique in the database
• A good approach is to prefix indexes names with idx_
• There are different types of indexes, and they vary among SQL DMS, but the default is B-tree

Indexes

• Let's create an index for the blogdatabase:

CREATE INDEX idx_posts_titles
ON posts(title);

In this example, querying for keywords in the title column would be faster because of the new index.

Indexes

"Rules of thumb" for index creation:

• Columns used in WHERE, JOIN or HAVING clauses
• Foreign keys columns
• Columns with several distinct values

Transactions

Transactions

• Transactions allow grouping multiple database operations in a "all-or-nothing" manner

• It is a fundamental concept that enhances integrity and consistency of the database

Transactions

• For instance when a user creates a profile in our blog database, it might be the case that the city where they live is not listed in the cities table and has to be created in the same operation.

• Let's see what the query would look like...

Transactions

INSERT INTO cities(name)
VALUES ('New York');
INSERT INTO users(name, email, city_id, role_id)
VALUES ('John', 'john@email.com', (SELECT city_id FROM cities WHERE name = 'New York'), 1);

Transactions

That's why transactions are so important, if we wrap the operations in a single transaction, the database will persist the data only if both operations are successful!

Transactions

• Here's the basic syntax:

BEGIN;
-- SQL statements
COMMIT;

• Or:

BEGIN;
-- SQL statements
ROLLBACK;

Transactions

• When using COMMIT the operations will be persisted to the database.

• ROLLBACK on the other hand, discards the changes up to the point where the transacion begun (at the BEGIN statement)

• It is possible to ROLLBACK to different steps of the transaction using savepoints.

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

Transactions

• Here's our example using a transaction

BEGIN;
INSERT INTO cities(name)
VALUES ('New York');
INSERT INTO users(name, email, city_id, role_id)
VALUES ('John', 'john@email.com', (SELECT city_id FROM cities WHERE name = 'New York'), 1);
COMMIT;

Multi-tenancy

Multi-tenancy

• When developing a software that will be used by several users, it might be the case that we need to separate concerns, i.e., customer data, based on a context.

• For instance, if the software is a B2B SaaS, we probably want to separate data from each client (company).

• To achieve this, there are a few options that will be discussed next. When this scenario shows up, we call each customer context a tenant

Multi-tenant Architectures

Here are a few options to achieve multi-tenancy:

1. Database per tenant
2. Shared tables
   2.1. Tenant identification column
   2.2. Row-level access
3. Schema per tenant

Database per Tenant

In the database per tenant architecture, each tenant is allocated a dedicated database instance, ensuring strict data isolation and autonomy. This approach offers excellent security and scalability, making it ideal for large-scale applications with diverse tenants. However, it can be resource-intensive and costly to maintain numerous separate databases.

Shared Tables

Shared tables employ a single database with a common schema, where a unique identifier (usually a tenant ID) distinguishes tenant-specific data. Rows are tagged with this identifier, enabling efficient data separation. While this approach reduces infrastructure complexity and costs, it demands careful management to prevent unauthorized data access and can face performance challenges as the dataset grows.

Schema per Tenant

In the schema per tenant strategy, every tenant gets their distinct schema within a shared database. This method combines aspects of both previous approaches, granting tenants autonomy over their data structure while benefiting from resource consolidation. Nonetheless, it requires careful schema management and may still incur additional overhead compared to shared tables.

Agenda

• SQL

• ORMs

  • Definition, popular ORMs and examples

• Best Practices and Tips

  • Performance, security and debugging

Here is a list of ORM libraries for different languages

Language	ORM Libraries
NodeJS	Sequelize Prisma
Python	SQLAlchemy Django SQLModel
Go	GORM REL
Java	Hibernate EclipseLink
C#	Entity NHibernate

An Example With Go

Let's build a simple HTTP server that returns a random motivational quote.

We can start with the implementation of the endpoint /quote with a hard coded value.

Starter code: main.go

package main

import (
  "log"
  "net/http"
)

func quoteHandler(w http.ResponseWriter, r *http.Request) {
  w.Header().Set("Content-Type", "text/plain")
  w.Write([]byte("Nothing is impossible.\n"))
}

func main() {
  http.HandleFunc("/quote", quoteHandler)

  log.Println("Listening on port 8080")
  log.Fatal(http.ListenAndServe(":8080", nil))
}

An Example With Go

• We can spin up the server:

$ go run main.go
2023/06/30 00:53:16 Listening on port 8080

And make a request:

$ curl http://localhost:8080/quote
Nothing is impossible.

An Example With Go

• Awesome! We've got our first quote.

• But we will need more quotes to randomize the reponse.

• To do that, instead of a single hardcoded quote, let's create a list of quotes.

import (
  "log"
  "math/rand"
  "net/http"
)

var quotes = []string{
  "Nothing is impossible.\n",
  "If you're going through hell, keep going.\n",
  "We need much less than we think we need.\n",
  "If things go wrong, don't go with them.\n",
  "Whatever you are, be a good one.\n",
}

func quoteHandler(w http.ResponseWriter, r *http.Request) {
  index := rand.Intn(len(quotes))
  quote := quotes[index]
  w.Header().Set("Content-Type", "text/plain")
  w.Write([]byte(quote))
}

An Example With Go

• Running the server again:

$ go run main.go
2023/06/30 00:53:16 Listening on port 8080

And making a few requests:

$ curl http://localhost:8080/quote
Whatever you are, be a good one.
$ curl http://localhost:8080/quote
If you're going through hell, keep going.
$ curl http://localhost:8080/quote
Nothing is impossible.

An Example With Go

• Beautiful! We already are returning random quotes.
• But... this will not scale well, of course. What if we want to store hundreds or even thousands of quotes?
• That's when a database comes in handy. In the next step, we are going to connect to a PostgreSQL database so we can separate the concerns.

An Example With Go

An Example With Go

• In the diagram showed, the Go server and the PostgreSQL server are different services, but will not necessarely run on different computers.

• Before we connect to the database, the database must be ready to receive connections.

• We're going to see how to do it, it is quite simple!

An Example With Go

1. If not installed already, install PostgreSQL
2. Create a database called quotes_db and connect to it:

psql
DROP DATABASE IF EXISTS quotes_db;
CREATE DATABASE quotes_db;
\c quotes_db

An Example With Go

3. Create a table called quotes and add some values:

CREATE TABLE quotes (id serial PRIMARY KEY, quote varchar NOT NULL);
INSERT INTO quotes(quote)
VALUES ('Nothing is impossible'),
    ('If you`re going through hell, keep going'),
    ('We need much less than we think we need'),
    ('If things go wrong, don`t go with them'),
    ('Whatever you are, be a good one');

An Example With Go

Here is the data stored ready to be fetched:

$ psql quotes_db
quotes_db=# SELECT * FROM quotes;
 id |                  quote
----+------------------------------------------
  1 | Nothing is impossible
  2 | If you`re going through hell, keep going
  3 | We need much less than we think we need
  4 | If things go wrong, don`t go with them
  5 | Whatever you are, be a good one
(5 rows)

An Example With Go

• Instead of using psql we want to query the database from the server.
• To achieve that we need to connect to the database (DB) and then do the query to get a quote and return it in the response.

An Example With Go

• To connect to the DB we are going to use the database/sql native Go package and also a PostgreSQL driver. In this example, lib/pq.

go mod init go-sql-example
go get -u github.com/lib/pq

An Example With Go

• Next, let's update the code to create the DB connection:

import (
  "database/sql"
  "log"
  "math/rand"
  "net/http"

  _ "github.com/lib/pq"
)

var db *sql.DB

func init() {
  connStr := "postgresql://localhost/quotes_db?sslmode=disable"
  var err error
  db, err = sql.Open("postgres", connStr)
  if err != nil {
    log.Fatal("Failed to connect to database:", err)
  }

  err = db.Ping()
  if err != nil {
    log.Fatal("Failed to ping database:", err)
  }
  log.Println("Connected to the database")
}

An Example With Go

• Testing the connection:

$ go run main.go
 2023/07/01 00:20:51 Connected to the database
 2023/07/01 00:20:51 Listening on port 8080

• Awesome, we are connected to the DB. Now we can query the table with quotes!

An Example With Go

• First, we have to define what the query will be. We can either:

  1. Get a list of quotes and randomize the response in the Go server
  2. Get a single random quote from the database

• With option 1 we have a problem: there is a limit on how many quotes we can fetch at once, and it would be really slow and inefficient to ingest several quotes to return just one.

• In option 2 we would have a problem if there was no way to get a random quote from the DB. But as you might have noticed, SQL databases are very flexible and feature-rich. Getting random values is quite easy

An Example With Go

• The query will look like this:

SELECT quote FROM quotes ORDER BY RANDOM() LIMIT 1;

• The ORDER BY RANDOM() makes the result random and the LIMIT 1 clause returns only one result.

• Now we just have to update the Go code and assign this result to one variable to return it in the response...

An Example With Go

• Add the getRandomQuote function to return the result:

func getRandomQuote() (string, error) {
  rows, err := db.Query("SELECT quote FROM quotes ORDER BY RANDOM() LIMIT 1")
  if err != nil {
    return "", err
  }
  defer rows.Close()

  var quote string
  for rows.Next() {
    err := rows.Scan(&quote)
    if err != nil {
      return "", err
    }
  }

  return quote, nil
}

An Example With Go

• Update the quoteHandler function:

func quoteHandler(w http.ResponseWriter, r *http.Request) {
  quote, err := getRandomQuote()
  if err != nil {
    log.Println("Failed to retrieve quote:", err)
    http.Error(w, "Internal Server Error", http.StatusInternalServerError)
    return
  }

  w.Header().Set("Content-Type", "text/plain")
  w.Write([]byte(quote))
}

An Example With Go

• Testing the server again:

go run main.go
2023/07/01 01:28:18 Connected to the database
2023/07/01 01:28:18 Listening on port 8080

$ curl http://localhost:8080/quote
Nothing is impossible
$ curl http://localhost:8080/quote
We need much less than we think we need

An Example With Go

• Once again, we start with a simple HTTP server with the /quote endpoint:

package main

import (
  "log"
  "net/http"
)

func quoteHandler(w http.ResponseWriter, r *http.Request) {
  w.Header().Set("Content-Type", "text/plain")
  w.Write([]byte("Nothing is impossible.\n"))
}

func main() {
  http.HandleFunc("/quote", quoteHandler)

  log.Println("Listening on port 8080")
  log.Fatal(http.ListenAndServe(":8080", nil))
}

An Example With Go

• To connect to the DB we are going to use the GORM library with its PostgreSQL driver, gorm.io/driver/postgres.

go mod init go-orm-example
go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres

An Example With Go

• In the example with Go and vanilla SQL we created the database quotes_db and added some data using psql.

• Here we are going to use GORM to do this step as well, so we can see how the DDL is translated to the ORM.

// package, import ...
type Quotes struct {
  ID    uint `gorm:"primaryKey"`
  Quote string
}

var (
  initialQuotes = []Quotes{
    {Quote: "Nothing is impossible"},
    {Quote: "If you`re going through hell, keep going"},
    {Quote: "We need much less than we think we need"},
    {Quote: "If things go wrong, don`t go with them"},
    {Quote: "Whatever you are, be a good one"},
  }
)

func main() {
  dsn := "host=localhost dbname=quotes_db port=5432 sslmode=disable"
  db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  db.AutoMigrate(&Quotes{})

  for _, quote := range initialQuotes {
    db.Create(&quote)
  }
}

An Example With Go

• First, we will declare a global variable db that holds the database connection, and declare the model used in this example:

var db *gorm.DB

type Quotes struct {
  ID    uint `gorm:"primaryKey"`
  Quote string
}

• In order to make the code more DRY we could have used a different file to declare the model and use it both for the seed script and the server itself

An Example With Go

• Then we create a function to execute the database query:

func getRandomQuote() (string, error) {
  var quote Quotes
  err := db.Order("RANDOM()").Take(&quote).Error
  if err != nil {
    return "", err
  }
  return quote.Quote, nil
}

An Example With Go

• In the previous slide we have used the db variable (which has the gorm.DB type) to make the query.

• Two methods were called: Order() and Take(). The first one is directly related to the SQL statement ORDER BY.

• In the GORM docs we can find how to create models, associations, queries, and so on.

An Example With Go

• Here are a few "equivalent" functions:

An Example With Go

• This does not mean that the ORM queries will look exactly like the SQL queries. Some SQL keywords are added to the query "behind the scenes". Here's another GORM example:

db.First(&user) is equivalent to

SELECT * FROM users ORDER BY id LIMIT 1;

An Example With Go

• Actually, GORM provides a method to get the resulting SQL query:

sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
  return tx.Order("RANDOM()").Take(&quote)
})
log.Println(sql)

Returns:

SELECT * FROM "quotes" ORDER BY RANDOM() LIMIT 1

• Quite similar to the query used in the Go + SQL example!

CRUD with GORM

• It is a really simple schema to store books and authors with a single one-to-many relation (one author can have several books associated with them)

• The first step is to create the models using GORM

CRUD with GORM

• models/authors.go

package model

import (
  "gorm.io/gorm"
)

type Author struct {
  gorm.Model
  FirstName string
  LastName  string
  Books     []Book
}

CRUD with GORM

• models/books.go

package model

import (
  "gorm.io/gorm"
)

type Book struct {
  gorm.Model
  Title             string
  Description       string
  YearOfPublication int
  AuthorID          uint
  Author            Author
}

CRUD with GORM

• In the previous slides, we have used the gorm.Model struct to abstract a lot of things.

• No need to explicitly declare the id, created_at, updated_at and deleted_at fields!

CRUD with GORM

• Back to GORM ...

• Let's create a seed file to add some initial data as well. We could have started with empty tables, too, but adding initial values will make simpler to explain the next steps.

CRUD with GORM

package main

import (
  "log"

  "go-book-server/model"

  "gorm.io/driver/postgres"
  "gorm.io/gorm"
)

var (
  initialAuthors = []model.Author{
    {FirstName: "William", LastName: "Shakespeare"},
    {FirstName: "Harper", LastName: "Lee"},
  }

  initialBooks = []model.Book{
    {
      Title:             "Macbeth",
      Description:       "A Scottish general's ruthless quest for power...",
      YearOfPublication: 1600,
      AuthorID:          1,
    },
    {
      Title:             "Romeo and Juliet",
      Description:       " The forbidden love between two young individuals...",
      YearOfPublication: 1595,
      AuthorID:          1,
    },
    {
      Title:             "To Kill a Mockingbird",
      Description:       "Set in the racially-charged 1930s Deep South...",
      YearOfPublication: 1860,
      AuthorID:          2,
    },
  }
)

CRUD with GORM

func main() {
  dsn := "host=localhost port=5432 sslmode=disable"
  db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }
  // Checking if DB exists
  rs := db.Raw("SELECT * FROM pg_database WHERE datname = 'books_db';")
  if rs.Error != nil {
    log.Fatal("Raw query failed:", err)
  }

  // If not, create it
  var rec = make(map[string]interface{})
  if rs.Find(rec); len(rec) == 0 {
    if rs := db.Exec("CREATE DATABASE books_db;"); rs.Error != nil {
      log.Fatal("Couldn't create database: ", err)
    }

    // Close db connection
    sql, err := db.DB()
    defer func() {
      _ = sql.Close()
    }()
    if err != nil {
      log.Fatal("An error occurred: ", err)
    }
  }

  // Reconnect and add initial data
  dsn = "host=localhost dbname=books_db port=5432 sslmode=disable"
  db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  db.AutoMigrate(&model.Author{}, &model.Book{})

  for _, author := range initialAuthors {
    db.Create(&author)
  }
  for _, book := range initialBooks {
    db.Create(&book)
  }

  log.Println("Successfully added seed data!")
}

CRUD with GORM

• Awesome! Now we have a database called books_db with a few entries to work with.

$ go run seed/main.go
2023/07/05 01:05:05 Successfully added seed data!
$ psql books_db
books_db=# SELECT * FROM authors;
 id |          created_at           |          updated_at           | deleted_at | first_name |  last_name
----+-------------------------------+-------------------------------+------------+------------+-------------
  1 | 2023-07-05 01:05:05.803875+00 | 2023-07-05 01:05:05.803875+00 |            | William    | Shakespeare
  2 | 2023-07-05 01:05:05.805375+00 | 2023-07-05 01:05:05.805375+00 |            | Harper     | Lee
(2 rows)
books_db=# SELECT id,title FROM books;
 id |         title
----+-----------------------
  1 | Macbeth
  2 | Romeo and Juliet
  3 | To Kill a Mockingbird
(3 rows)

CRUD with GORM

• Next step: implement CRUD operations.
  • Create
  • Retrieve
  • Update
  • Delete

CRUD with GORM

• We'll start with the Retrieve operation, implementing the /authors and /books GET endpoints:

CRUD with GORM

• The code will be structured as follows:

.
└── books/
    ├── handler/
    │   ├── authors.go
    │   ├── books.go
    │   └── handler.go
    ├── model/
    │   ├── authors.go
    │   └── books.go
    ├── seed/
    │   └── main.go
    ├── go.mod
    ├── go.sum
    └── main.go

• The model and seed folders are done already

CRUD with GORM

package main

import (
  "go-book-server/handler"
  "log"
  "net/http"

  "gorm.io/driver/postgres"
  "gorm.io/gorm"
)

var db *gorm.DB

func main() {
  dsn := "host=localhost dbname=books_db port=5432 sslmode=disable"
  var err error
  db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
  if err != nil {
    panic("failed to connect to database")
  }

  controller := handler.NewController(db)

  http.HandleFunc("/authors", controller.Authors())
  http.HandleFunc("/authors/", controller.AuthorsByID())

  log.Println("Server started on http://localhost:8080")
  log.Fatal(http.ListenAndServe(":8080", nil))
}

CRUD with GORM

handler/handler.go

package handler

import "gorm.io/gorm"

func NewController(db *gorm.DB) *Controller {
  return &Controller{
    db: *db,
  }
}

type Controller struct {
  db gorm.DB
}

CRUD with GORM

handler/authors.go

package handler

import (
  "encoding/json"
  "go-book-server/model"
  "log"
  "net/http"
)

func (c *Controller) Authors() http.HandlerFunc {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
    if r.Method == http.MethodGet {
      c.ListAuthors(w, r)
      return
    }
    w.WriteHeader(http.StatusMethodNotAllowed)
    w.Write([]byte("Method not allowed"))
  })
}
// ...

CRUD with GORM

handler/authors.go

// ...
func (c *Controller) AuthorsByID() http.HandlerFunc {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
    if r.Method == http.MethodGet {
      c.GetAuthorByID(w, r)
      return
    }
    w.WriteHeader(http.StatusMethodNotAllowed)
    w.Write([]byte("Method not allowed"))
  })
}
// ...

CRUD with GORM

handler/authors.go

// ...
func (c *Controller) ListAuthors(w http.ResponseWriter, r *http.Request) {
  var authors []model.Author
  err := c.db.Preload("Books").Find(&authors).Error
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  result, err := json.Marshal(authors)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusOK)
  w.Write(result)
}
// ...

CRUD with GORM

handler/authors.go

// ...
func (c *Controller) GetAuthorByID(w http.ResponseWriter, r *http.Request) {
  id := r.URL.Path[len("/authors/"):]
  var author model.Author
  err := c.db.Preload("Books").First(&author, id).Error
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  result, err := json.Marshal(author)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusOK)
  w.Write(result)
}

CRUD with GORM

• In the previous slides, the method .Preload("Books") was called.

• This is a feature from GORM that tells the query to return the books associated with the author.

• This technique is called eager loading and will be exaplained in more detail later

CRUD with GORM

• Now let's spin up the server and do some testing!

$ go run main.go
2023/07/05 02:46:57 Server started on http://localhost:8080

CRUD with GORM

• /authors endpoint:

$ curl http://localhost:8080/authors | jq
[
   {
    "ID": 1,
    "CreatedAt": "2023-07-06T01:18:28.220539Z",
    "UpdatedAt": "2023-07-06T01:18:28.220539Z",
    "DeletedAt": null,
    "FirstName": "William",
    "LastName": "Shakespeare",
    "Books": [{...},{...}]
  },
  {
    "ID": 2,
    "CreatedAt": "2023-07-06T01:18:28.221913Z",
    "UpdatedAt": "2023-07-06T01:18:28.221913Z",
    "DeletedAt": null,
    "FirstName": "Harper",
    "LastName": "Lee",
    "Books": [{...}]
  }
]

CRUD with GORM

• /authors/<id> endpoint:

$ curl http://localhost:8080/authors/2 | jq
{
  "ID": 2,
  "CreatedAt": "2023-07-06T01:18:28.221913Z",
  "UpdatedAt": "2023-07-06T01:18:28.221913Z",
  "DeletedAt": null,
  "FirstName": "Harper",
  "LastName": "Lee",
  "Books": [
    {
      "ID": 3,
      "CreatedAt": "2023-07-06T01:18:28.226073Z",
      "UpdatedAt": "2023-07-06T01:18:28.226073Z",
      "DeletedAt": null,
      "Title": "To Kill a Mockingbird",
      "Description": "Set in the racially-charged 1930s ...",
      "YearOfPublication": 1860,
      "AuthorID": 2,
    }
  ]
}

CRUD with GORM

• /authors/<id> endpoint (wrong id):

$ curl http://localhost:8080/authors/123
curl: (52) Empty reply from server

• Meanwhile, on the server:

2023/07/05 02:51:45 /book/handler/authors.go:53 record not found
[3.437ms] [rows:0] SELECT * FROM "authors" WHERE "authors"."id" = '123'
                   AND "authors"."deleted_at" IS NULL
                   ORDER BY "authors"."id" LIMIT 1
2023/07/05 02:51:45 record not found
exit status 1

• We can fix this!

CRUD with GORM

handler/authors.go

var author model.Author
err := c.db.First(&author, id).Error
if err != nil {
+  if errors.Is(err, gorm.ErrRecordNotFound) {
+    w.WriteHeader(http.StatusNotFound)
+    w.Write([]byte("author not found."))
+    return
+  }
  w.WriteHeader(http.StatusInternalServerError)
  log.Fatal(err)
  return
}

CRUD with GORM

• The /books endpoint is implemented in a very similar fashion:

// package, import, var ...

func main() {
  // db connection ...

  controller := handler.NewController(db)

  http.HandleFunc("/authors", controller.Authors())
  http.HandleFunc("/authors/", controller.AuthorsByID())

  http.HandleFunc("/books", controller.Books())
  http.HandleFunc("/books/", controller.BooksByID())

  log.Println("Server started on http://localhost:8080")
  log.Fatal(http.ListenAndServe(":8080", nil))
}

CRUD with GORM

• The /books endpoint is implemented in a very similar fashion:

// package, import, var ...

func main() {
  // db connection ...

  controller := handler.NewController(db)

  http.HandleFunc("/authors", controller.Authors())
  http.HandleFunc("/authors/", controller.AuthorsByID())

  http.HandleFunc("/books", controller.Books())
  http.HandleFunc("/books/", controller.BooksByID())

  log.Println("Server started on http://localhost:8080")
  log.Fatal(http.ListenAndServe(":8080", nil))
}

CRUD with GORM

// ...
func (c *Controller) ListBooks(w http.ResponseWriter, r *http.Request) {
  var Books []model.Book
  err := c.db.Preload("Author").Find(&Books).Error
  if err != nil {
    if errors.Is(err, gorm.ErrRecordNotFound) {
      w.WriteHeader(http.StatusNotFound)
      w.Write([]byte("Book not found."))
      return
    }
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  result, err := json.Marshal(Books)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusOK)
  w.Write(result)
}

CRUD with GORM

// ...
func (c *Controller) GetBookByID(w http.ResponseWriter, r *http.Request) {
  id := r.URL.Path[len("/Books/"):]
  var Book model.Book
  err := c.db.Preload("Author").First(&Book, id).Error
  if err != nil {
    if errors.Is(err, gorm.ErrRecordNotFound) {
      w.WriteHeader(http.StatusNotFound)
      w.Write([]byte("Book not found."))
      return
    }
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  result, err := json.Marshal(Book)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusOK)
  w.Write(result)
}

• Testing the /books endpoint:

$ curl http:/localhost:8080/books | jq
[
  {
    "ID": 1,
    "CreatedAt": "2023-07-05T03:15:43.252328Z",
    "UpdatedAt": "2023-07-05T03:15:43.252328Z",
    "DeletedAt": null,
    "Title": "Macbeth",
    "Description": "A Scottish general's ruthless quest ...",
    "YearOfPublication": 1600,
    "AuthorID": 1,
    "Author": {
      "ID": 1,
      "CreatedAt": "2023-07-05T03:15:43.248912Z",
      "UpdatedAt": "2023-07-05T03:15:43.248912Z",
      "DeletedAt": null,
      "FirstName": "William",
      "LastName": "Shakespeare"
    }
  },
  {
    "ID": 2,
    "CreatedAt": "2023-07-05T03:15:43.254232Z",
    "UpdatedAt": "2023-07-05T03:15:43.254232Z",
    "DeletedAt": null,
    "Title": "Romeo and Juliet",
    "Description": " The forbidden love between two young individuals ...",
    "YearOfPublication": 1595,
    "AuthorID": 1,
    "Author": {
      "ID": 1,
      "CreatedAt": "2023-07-05T03:15:43.248912Z",
      "UpdatedAt": "2023-07-05T03:15:43.248912Z",
      "DeletedAt": null,
      "FirstName": "William",
      "LastName": "Shakespeare"
    }
  },
  ...

...
  {
    "ID": 3,
    "CreatedAt": "2023-07-05T03:15:43.255502Z",
    "UpdatedAt": "2023-07-05T03:15:43.255502Z",
    "DeletedAt": null,
    "Title": "To Kill a Mockingbird",
    "Description": "Set in the racially-charged 1930s Deep South...",
    "YearOfPublication": 1860,
    "AuthorID": 2,
    "Author": {
      "ID": 2,
      "CreatedAt": "2023-07-05T03:15:43.250817Z",
      "UpdatedAt": "2023-07-05T03:15:43.250817Z",
      "DeletedAt": null,
      "FirstName": "Harper",
      "LastName": "Lee"
    }
  }
]

CRUD with GORM

• Testing the /books/<id> endpoint:

$ curl http:/localhost:8080/books/1 | jq
{
  "ID": 1,
  "CreatedAt": "2023-07-05T03:15:43.252328Z",
  "UpdatedAt": "2023-07-05T03:15:43.252328Z",
  "DeletedAt": null,
  "Title": "Macbeth",
  "Description": "A Scottish general's ruthless quest...",
  "YearOfPublication": 1600,
  "AuthorID": 1,
  "Author": {
    "ID": 1,
    "CreatedAt": "2023-07-05T03:15:43.248912Z",
    "UpdatedAt": "2023-07-05T03:15:43.248912Z",
    "DeletedAt": null,
    "FirstName": "William",
    "LastName": "Shakespeare"
  }
}

CRUD with GORM

• That concludes the Retrieve operation. Next: Delete
  • Create
  • Retrieve
  • Update
  • Delete

CRUD with GORM

• For the Delete operation, we will implement a soft delete for the endpoints the /authors/<id> and /books/<id>:

CRUD with GORM

func (c *Controller) AuthorsByID() http.HandlerFunc {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
    if r.Method == http.MethodGet {
      c.GetAuthorByID(w, r)
      return
    }
+    if r.Method == http.MethodDelete {
+      c.DeleteAuthor(w, r)
+      return
+    }
    w.WriteHeader(http.StatusMethodNotAllowed)
    w.Write([]byte("Method not allowed"))
  })
}

CRUD with GORM

func (c *Controller) DeleteAuthor(w http.ResponseWriter, r *http.Request) {
  id := r.URL.Path[len("/authors/"):]
  var author = model.Author{}
  err := c.db.Where("id = ?", id).Delete(&author).Error
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusNoContent)
}

CRUD with GORM

• Testing the new endpoint:

$ curl -i -X DELETE http://localhost:8080/authors/1
HTTP/1.1 204 No Content

• Awesome! For the /books/<id> endpoint, the same logic can be used, changing only the model used in the GORM query.

CRUD with GORM

• All right, one more operation done! Next: Create
  • Create
  • Retrieve
  • Update
  • Delete

CRUD with GORM

• For the Create operation, we will use the POST HTTP method in the endpoints /authors and /books:

• Also, when requesting these endpoints, we'll need a payload with the data to be added.

CRUD with GORM

func (c *Controller) Authors() http.HandlerFunc {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+    if r.Method == http.MethodPost {
+      c.CreateAuthor(w, r)
+      return
+    }
    if r.Method == http.MethodGet {
      c.ListAuthors(w, r)
      return
    }
    w.WriteHeader(http.StatusMethodNotAllowed)
    w.Write([]byte("Method not allowed"))
  })
}

CRUD with GORM

func (c *Controller) CreateAuthor(w http.ResponseWriter, r *http.Request) {
  defer r.Body.Close()
  var payload createAuthorPayload
  if err := json.NewDecoder(r.Body).Decode(&payload); err != nil {
    w.WriteHeader(http.StatusBadRequest)
    w.Write([]byte(err.Error()))
    return
  }
  author := &model.Author{
    FirstName: payload.FirstName,
    LastName:  payload.LastName,
  }
  if err := c.db.Create(&author).Error; err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
  }
  result, err := json.Marshal(author)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusCreated)
  w.Write(result)
}

CRUD with GORM

• The createAuthorPayload type is declared with the fields and types used in the payload:

type createAuthorPayload struct {
  FirstName string
  LastName  string
}

CRUD with GORM

• Let's test the route:

$ curl -X POST 'http://localhost:8080/authors' \
  -H 'Content-Type: application/json' \
  -d '{"firstName":"Jane","lastName":"Austen"}' | jq
{
  "ID": 9,
  "CreatedAt": "2023-07-06T23:44:41.681442697Z",
  "UpdatedAt": "2023-07-06T23:44:41.681442697Z",
  "DeletedAt": null,
  "FirstName": "Jane",
  "LastName": "Austen",
  "Books": null
}

CRUD with GORM

• Let's test the route:

$ curl -X POST 'http://localhost:8080/authors' \
  -H 'Content-Type: application/json' \
  -d '{"firstName":123,"lastName":"Austen"}'
json: cannot unmarshal number into Go struct field createAuthorPayload.FirstName of type string

CRUD with GORM

• The implementation for /books is quite similar. Here's the createBookPayload type declaration:

type createBookPayload struct {
  Title             string
  Description       string
  YearOfPublication int
  AuthorID          int
}

• The only difference is checking if the author_id it exists.

CRUD with GORM

// ...
if payload.AuthorID != 0 {
    var author model.Author
    err := c.db.First(&author, payload.AuthorID).Error
    if err != nil {
      if errors.Is(err, gorm.ErrRecordNotFound) {
        w.WriteHeader(http.StatusNotFound)
        w.Write([]byte("author not found."))
        return
      }
      w.WriteHeader(http.StatusInternalServerError)
      log.Fatal(err)
      return
    }
    book.AuthorID = payload.AuthorID
  }
// ...

CRUD with GORM

• One more operation to go! Next: Update
  • Create
  • Retrieve
  • Update
  • Delete

CRUD with GORM

• For the Update operation, we will use the PATCH HTTP method in the endpoints /authors and /books:

CRUD with GORM

func (c *Controller) AuthorsByID() http.HandlerFunc {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
    if r.Method == http.MethodGet {
      c.GetAuthorByID(w, r)
      return
    }
+    if r.Method == http.MethodPatch {
+      c.UpdateAuthor(w, r)
+      return
+    }
    if r.Method == http.MethodDelete {
      c.DeleteAuthor(w, r)
      return
    }
    w.WriteHeader(http.StatusMethodNotAllowed)
    w.Write([]byte("Method not allowed"))
  })
}

CRUD with GORM

• First we get the author being updated and check if it exists:

func (c *Controller) UpdateAuthor(w http.ResponseWriter, r *http.Request) {
  id := r.URL.Path[len("/authors/"):]
  var author model.Author
  err := c.db.Preload("Books").First(&author, id).Error
  if err != nil {
    if errors.Is(err, gorm.ErrRecordNotFound) {
      w.WriteHeader(http.StatusNotFound)
      w.Write([]byte("author not found."))
      return
    }
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
// ...

CRUD with GORM

• Then we load the payload and update the fields:

// ...
  defer r.Body.Close()
  var payload updateAuthorPayload
  if err := json.NewDecoder(r.Body).Decode(&payload); err != nil {
    w.WriteHeader(http.StatusBadRequest)
    w.Write([]byte(err.Error()))
    return
  }

  if payload.FirstName != "" {
    author.FirstName = payload.FirstName
  }

  if payload.LastName != "" {
    author.LastName = payload.LastName
  }

  if err := c.db.Save(&author).Error; err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
  }
// ...

CRUD with GORM

• Lastly we return the author with updated fields:

// ...
  result, err := json.Marshal(author)
  if err != nil {
    w.WriteHeader(http.StatusInternalServerError)
    log.Fatal(err)
    return
  }
  w.WriteHeader(http.StatusCreated)
  w.Write(result)
}

CRUD with GORM

• The updateAuthorPayload type is identical with the createAuthorPayload type:

type updateAuthorPayload struct {
  FirstName string
  LastName  string
}

CRUD with GORM

• Testing the route:

$ curl -X PATCH http://localhost:8080/authors/1 \
  -H 'Content-Type: application/json' \
  -d '{"firstName":"Gulielmus","lastName":"Shakspere"}' | jq
{
  "ID": 1,
  "CreatedAt": "2023-07-06T01:18:28.220539Z",
  "UpdatedAt": "2023-07-07T00:55:05.965733711Z",
  "DeletedAt": null,
  "FirstName": "Gulielmus",
  "LastName": "Shakspere",
  "Books": [...]
}

CRUD with GORM

• Testing the route:

$ curl -X PATCH http://localhost:8080/authors/1 \
  -H 'Content-Type: application/json' \
  -d '{"firstName":"William"}' | jq
{
  "ID": 1,
  "CreatedAt": "2023-07-06T01:18:28.220539Z",
  "UpdatedAt": "2023-07-07T00:55:05.965733711Z",
  "DeletedAt": null,
  "FirstName": "William",
  "LastName": "Shakspere",
  "Books": [...]
}

CRUD with GORM

• Testing the route:

$ curl -X PATCH http://localhost:8080/authors/1 \
  -H 'Content-Type: application/json' \
  -d '{"lastName":"Shakespeare"}' | jq
{
  "ID": 1,
  "CreatedAt": "2023-07-06T01:18:28.220539Z",
  "UpdatedAt": "2023-07-07T00:55:05.965733711Z",
  "DeletedAt": null,
  "FirstName": "William",
  "LastName": "Shakespeare",
  "Books": [...]
}

CRUD with GORM

• Let's test the route:

$ curl -X PATCH http://localhost:8080/authors/1 \
  -H 'Content-Type: application/json' \
  -d '{"firstName":123}'
json: cannot unmarshal number into Go struct field updateAuthorPayload.FirstName of type string

CRUD with GORM

• The implementation for /books is (again) quite similar.

• The only difference is, again, if there is an author_id in the payload, checking if it exists.

CRUD with GORM

• That completes the CRUD operations
  • Create
  • Retrieve
  • Update
  • Delete

Lazy Loading

x

Eager Loading

Caching and Performance Optimization

Object-Relational impedance mismatch

Agenda

• SQL

• ORMs

• Best Practices and Tips

  • Performance, security and debugging