_Note:_ Make sure to save your seed file on your machine so that you can pipe it
into your database in the next phase!
## Phase 3: Pipe your seed file into your new database
After you've saved your seed file, use the caret and pipe methods to seed
your `farm` database with the data from the "edible_seeds" table. (_Note: Make
sure you've quit `psql` first with `\q`._)
There are two ways to seed your database:
### Method 1. Seed your database via caret method
```sql
psql -d [database] -U [username] < [path_to_file/file.sql]
```
### Method 2. Seed your database via pipe method
```sql
cat [path_to_file/file.sql] | psql -d [database] -U [username]
```
Try both of these methods.
### If you want to seed using the file again, you need to first drop the tables.
Access the database by:
```sql
psql -d [database] -U [username]
```
Then drop the tables:
```sql
DROP TABLE [table];
```
Then you can run the seed file again using any of the two above methods.
### Check to make sure your seed file has actually updated the `farm` database
* Log into `psql` again.
* Connect to the `farm` database (syntax: `\c [database]`).
 We also learned how to use
a `JOIN` operation to get information from multiple tables.
In this project, put your SQL knowledge to the test and show off your querying
skills.
We've put together a collection (let's call it a _menagerie_) of SQL problems
for you to solve below.
## Getting started
Clone the starter repository from
https://github.com/appacademy-starters/sql-select-exercises-starter.
## Project overview
1. In Phase 1, pipe the seed file into a new database.
2. In Phase 2, query the seed tables with basic `SELECT` statements.
3. In Phase 3, query the seed tables using `WHERE` clauses to get more specific
rows back.
4. In Phase 4, use a `JOIN` operation to get data from both seed tables.
5. Bonuses! Go beyond what we learned in the readings to deepen your SQL query
knowledge.
## Phase 1: Pipe in a seed file to create new database tables
We've set up a seed file for you to use in this project called
_**cities_and_airports.sql_** that will create two tables: a "cities" table and
an "airports" table. These tables show the top 25 most populous U.S. cities and
their airports, respectively. Pipe this file into your database, and use these
tables for the rest of the project phases.
Go through the following steps:
1. Log into `psql`.
2. Create a new database called "travel".
3. Pipe the _cities_and_airports.sql_ seed file into the "travel" database.
4. Check that there's data in both the "cities" and "airports" tables.
## Phase 2: Write basic SELECT statements
Retrieve rows from a table using `SELECT` FROM SQL statements.
1. Write a SQL query that returns the city, state, and estimated population in
2018 from the "cities" table.
2. Write a SQL query that returns all of the airport names contained in the
"airports" table.
## Phase 3: Add WHERE clauses
Select specific rows from a table using WHERE and common operators.
1. Write a SQL query that uses a `WHERE` clause to get the estimated population
in 2018 of the city of San Diego.
2. Write a SQL query that uses a `WHERE` clause to get the city, state, and
estimated population in 2018 of cities in this list: Phoenix, Jacksonville,
Charlotte, Nashville.
3. Write a SQL query that uses a `WHERE` clause to get the cities with an
estimated 2018 population between 800,000 and 900,000 people. Show the city,
state, and estimated population in 2018 columns.
4. Write a SQL query that uses a `WHERE` clause to get the names of the cities
that had an estimated population in 2018 of at least 1 million people (or
1,000,000 people).
5. Write a SQL query to get the city and estimated population in 2018 in number
of millions (_i.e. without zeroes at the end: 1 million_), and that uses a
`WHERE` clause to return only the cities in Texas.
6. Write a SQL query that uses a `WHERE` clause to get the city, state, and
estimated population in 2018 of cities that are NOT in the following states:
New York, California, Texas.
7. Write a SQL query that uses a `WHERE` clause with the `LIKE` operator to get
the city, state, and estimated population in 2018 of cities that start with
the letter "S". (_Note: See the PostgreSQL doc on [Pattern Matching][1] for
more information._)
8. Write a SQL query that uses a `WHERE` clause to find the cities with either
a land area of over 400 square miles OR a population over 2 million people
(or 2,000,000 people). Show the city name, the land area, and the estimated
population in 2018.
9. Write a SQL query that uses a `WHERE` clause to find the cities with either
a land area of over 400 square miles OR a population over 2 million people
(or 2,000,000 people) -- but not the cities that have both. Show the city
name, the land area, and the estimated population in 2018.
10. Write a SQL query that uses a `WHERE` clause to find the cities where the
population has increased by over 200,000 people from 2010 to 2018. Show
the city name, the estimated population in 2018, and the census population
in 2010.
## Phase 4: Use a JOIN operation
Retrieve rows from multiple tables joining on a foreign key.
The "airports" table has a foreign key called `city_id` that references
the `id` column in the "cities" table.
1. Write a SQL query using an INNER JOIN to join data from the "cities" table
with data from the "airports" table using the `city_id` foreign key.
Show the airport names and city names only.
2. Write a SQL query using an INNER JOIN to join data from the "cities" table
with data from the "airports" table to find out how many airports are in New
York City using the city name. (_Note: Use the [aggregate function][2]
COUNT() to count the number of matching rows._)
## Bonuses
1. **Apostrophe:** Write a SQL query to get all three ID codes (_the Federal
Aviation Administration (FAA) ID, the International Air Transport Association
(IATA) ID, and the International Civil Aviation Organization (ICAO) ID_) from
the "airports" table for Chicago O'Hare International Airport. (_Note:
You'll need to escape the quotation mark in O'Hare.
See [How to include a single quote in a SQL query][3]._)
2. **Formatting Commas:** Refactor Phase 2, Query #1 to turn the INT for
estimated population in 2018 into a character string with commas.
(_Note: See [Data Type Formatting Functions][4]_)
* Phase 2, Query #1: Write a SQL query that returns the city, state, and
estimated population in 2018 from the "cities" table.
3. **Decimals and Rounding:** Refactor Phase 3, Query #5 to turn number of
millions from an integer into a decimal rounded to a precision of two decimal
places. (_Note: See [Numeric Types][5] and the [ROUND function][6]._)
* Phase 3, Query #5: Write a SQL query to get the city and estimated
population in 2018 in number of millions (_i.e. without zeroes at the end:
1 million_), and that uses a `WHERE` clause to return only the cities in Texas.
4. **ORDER BY and LIMIT Clauses:** Refactor Phase 3, Query #10 to return only
one city with the biggest population increase from 2010 to 2018. Show the
city name, the estimated population in 2018, and the census population in
2010 for that city. (_Note: You'll do the same calculation as before, but
instead of comparing it to 200,000, use the [ORDER BY Clause][7] with the
[LIMIT][8] Clause to sort the results and grab only the top result.)
* Phase 3, Query #10: Write a SQL query that uses a `WHERE` clause to find
the cities where the population has increased by over 200,000 people from
2010 to 2018. Show the city name, the estimated population in 2018, and the
census population in 2010.
[1]: https://www.postgresql.org/docs/9.6/functions-matching.html
[2]: https://www.postgresql.org/docs/9.6/functions-aggregate.html
[3]: https://www.essentialsql.com/include-single-quote-sql-query/
[4]: https://www.postgresql.org/docs/9.6/functions-formatting.html
[5]: https://www.postgresql.org/docs/9.6/datatype-numeric.html
[6]: https://www.postgresqltutorial.com/postgresql-round/
[7]: https://www.postgresql.org/docs/9.6/sql-select.html#SQL-ORDERBY
[8]: https://www.postgresql.org/docs/9.6/sql-select.html#SQL-LIMIT
________________________________________________________________________________
# WEEK-10 DAY-3
*Deeper Into Data* {ignore=true}
________________________________________________________________________________
________________________________________________________________________________
# Creating A Schema For Relational Database Design
Schemas allow use to easily visualize database tables and their relationships to
one another, so that we can identify areas that need clarity, refinement, or
redesign.
In this reading, we’re going to cover the stages of relational database design
and how to create schema that depicts database table relationships.
## What is Relational Database Design?
According to Technopedia, [Relational Database Design][1] (or RDD) differs from
other databases in terms of data organization and transactions: “In an RDD, the
data are organized into tables and all types of data access are carried out via
controlled transactions."
In previous readings, we created relational database tables and accessed data
from these tables through PostgreSQL queries. These tables (a.k.a. _entities_)
contain rows (a.k.a. _records_) and columns (a.k.a. _fields_). We also learned
how to uniquely identify table records by adding a `PRIMARY KEY` and how to
create a table association by adding a `FOREIGN KEY`.
A relational database usually contains multiple tables. It’s useful to create
schema to help us visualize these tables, keep track of primary keys and foreign
keys, and create relationships among tables. This is a key part of the RDD
process defined below.
## Stages of Relational Database Design
There are four generally-agreed-upon stages of Relational Database Design:
1. Define the purpose/entities of the relational DB.
2. Identify primary keys.
3. Establish table relationships.
4. Apply normalization rules.
### 1. Define database purpose and entities
The first stage is identifying the purpose of the database (_Why is the database
being created? What problem is it solving? What is the data used for?_), as well
as identifying the main entities, or _tables_, that need to be created. It also
typically involves identifying the table’s attributes (i.e. _columns_ and
_rows_).
For example, if we were creating a database for order processing on an
e-commerce application, we would need a database with at least three tables: a
`products` table, an `orders` tables, and a `users` (i.e. customers) table. We
know that a product will probably have an ID, name, and price, and an order will
contain one or more product IDs. We also know that users can create multiple
orders.
![Orders ERD entities]
### 2. Identify primary keys
The second stage is to identify the primary key (_PK_) of each table. As we
previously learned, a table’s primary key contains a unique value, or values,
that identify each distinct record. For our above example of online orders, we
would probably create IDs to serve as the primary key for each table: a product
ID, an order ID, and a user ID.
![orders-erd-primary-keys.svg]
### 3. Establish table relationships
The third stage is to establish the relationships among the tables in the
database. There are three types of relational database table relationships:
- One-to-one
- One-to-many
- Many-to-many
**One-to-one relationship**
In a one-to-one relationship, one record in a table is associated with only one
record in another table. We could say that only one record in Table B belongs to
only one record in Table A.
A one-to-one relationship is the least common type of table relationship. While
the two tables above could be combined into a single table, we may want to keep
some less-used data separate from the main `products` table.
![products-erd-one-to-one.svg]
The above schema depicts two tables: a “products" table and a “product_details"
table. A `product_details` record belongs to only one product record. We've used
an arrow to indicate the one-to-one relationship between the tables. Both tables
have the same primary key -- `product_id` -- which we can use in a [`JOIN`][2]
operation to get data from both tables.
This table relationship would produce the following example data (note that not
all columns are shown below):
**Products**
| id | name |
| ---- | ------------------- |
| 1597 | Glass Coffee Mug |
| 1598 | Metallic Coffee Mug |
| 1599 | Smart Coffee Mug |
**Product Details**
| id | product_id | full_description |
| --- | ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 | 1597 | Sturdy tempered glass coffee mug with natural cork band and silicone lid. Barista standard - fits under commercial coffee machine heads and most cup-holders. |
| 2 | 1598 | Fun coffee mug that comes in various metallic colors. Sleek, stylish, and easy to wash. Makes a great addition to your kitchen. Take it on the go by attaching the secure lid. |
| 3 | 1599 | This smart mug goes beyond being a simple coffee receptacle. Its smart features let you set and maintain an exact drinking temperature for up to 1.5 hours, so your coffee is never too hot or too cold. |
Take a moment to analyze the data above. Using the foreign keys, you should be
able to reason out that the "Metallic Coffee Mug" is a "Fun coffee mug that
comes in various metallic colors."
**One-to-many relationship**
In a one-to-many relationship, each record in Table A is associated with
multiple records in Table B. Each record in Table B is associated with only one
record in Table A.
![orders-erd-one-to-many.svg]
The above schema depicts a one-to-many relationship between the “users" table
and the `orders` table: One user can create multiple orders. The primary key of
the “orders" table (`id`) is a foreign key in the “users" table (`order_id`). We
can use this foreign key in a `JOIN` operation to get data from both tables.
This table relationship would produce the following example data (note that not
all columns are shown below):
**Users**
| id | name |
| --- | ----- |
| 1 | Alice |
| 2 | Bob |
**Orders**
| id | purchaser_id |
| --- | ------------ |
| 10 | 1 |
| 11 | 1 |
| 12 | 2 |
Take a moment to analyze the data above. Using the foreign keys, you should be
able to reason out that "Alice" has made two orders and "Bob" has made one
order.
**Many-to-many relationship**
In a many-to-many relationship, multiple records in Table A are associated with
multiple records in Table B. You would normally create a third table for this
relationship called a _**join table**_, which contains the primary keys from
both tables.
![orders-erd-many-to-many.svg]
The above schema depicts a many-to-many relationship between the `products`
table and the `orders` table. A single order can have multiple products, and a
single product can belong to multiple orders. We created a third join table
called `order_details`, which contains both the`order_id` and `product_id`
fields as foreign keys.
This table relationship would produce the following example data(note that not
all columns are shown below):
**Products**
| id | name |
| ---- | ------------------- |
| 1597 | Glass Coffee Mug |
| 1598 | Metallic Coffee Mug |
| 1599 | Smart Coffee Mug |
**Users**
| id | name |
| --- | ----- |
| 1 | Alice |
| 2 | Bob |
**Orders**
| id | purchaser_id |
| --- | ------------ |
| 10 | 1 |
| 11 | 1 |
| 12 | 2 |
**Order Details**
| id | order_id | product_id |
| --- | -------- | ---------- |
| 1 | 10 | 1599 |
| 2 | 11 | 1597 |
| 3 | 11 | 1598 |
| 4 | 12 | 1597 |
| 5 | 12 | 1598 |
| 6 | 12 | 1599 |
Take a moment to analyze the data above. Using the foreign keys, you should be
able to reason out that "Alice" has two orders.
### 4. Apply normalization rules
The fourth stage in RDD is _**normalization**_. Normalization is the process of
optimizing the database structure so that redundancy and confusion are
eliminated.
The rules of normalization are called “normal forms" and are as follows:
1. First normal form
2. Second normal form
3. Third normal form
4. Boyce-Codd normal form
5. Fifth normal form
The first three forms are widely used in practice, while the fourth and fifth
are less often used.
**First normal form rules:**
- Eliminate repeating groups in individual tables.
- Create a separate table for each set of related data.
- Identify each set of related data with a primary key.
**Second normal form rules:**
- Create separate tables for sets of values that apply to multiple records.
- Relate these tables with a foreign key.
**Third normal form rules:**
- Eliminate fields that do not depend on the table’s key.
_Note: For examples of how to apply these forms, read [“Description of the
database normalization basics"][3] from Microsoft._
## Schema design tools
Many people draw their relational database design schema with good ol’ pen and
paper, or on a whiteboard. However, there are also lots of online tools created
for this purpose if you’d like to use something easily exportable/shareable.
Feel free to check out the ERD (short for “Entity Relationship Diagram") tools
below.
Free Database Diagram (ERD) Design Tools:
- [Lucidchart][4]
- [draw.io][5]
- [dbdiagram.io][6]
- [QuickDBD][7]
## What we learned:
- Stages of Relational Database Design (RDD)
- Examples of schema depicting table relationships
- Normalization rules
- Schema drawing tools
[1]: https://www.techopedia.com/definition/25113/relational-database-design-rdd
[2]: https://www.postgresql.org/docs/8.3/tutorial-join.html
[3]:
https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics
[4]: https://www.lucidchart.com/
[5]: https://www.draw.io/
[6]: https://dbdiagram.io/home?utm_source=holistics&utm_medium=top_5_tools_blog
[7]: https://www.quickdatabasediagrams.com/
[Orders ERD entities]: images/orders-erd-entities.svg
[orders-erd-primary-keys.svg]: images/orders-erd-primary-keys.svg
[products-erd-one-to-one.svg]: images/products-erd-one-to-one.svg
[orders-erd-one-to-many.svg]: images/orders-erd-one-to-many.svg
[orders-erd-many-to-many.svg]: images/orders-erd-many-to-many.svg
________________________________________________________________________________
# Using SQL Transactions
Transactions allow us to make changes to a SQL database in a consistent and
durable way, and it’s a best practice to use them regularly.
In this reading, we’ll cover what a transaction is and why we want to use it, as
well as how to write explicit transactions.
## What is a transaction?
A transaction is a single unit of work, which can contain multiple operations,
performed on a database. According to the [PostgreSQL docs][1], the important
thing to note about a transaction is that “it bundles multiple steps into a
single, all-or-nothing operation". If any operation within the transaction
fails, then the entire transaction fails. If all the operations succeed, then
the entire transaction succeeds.
## Implicit vs. explicit transactions
Every SQL statement is effectively a transaction. When you insert a new table
row into a database table, for example, you are creating a transaction. The
following `INSERT` statement is a transaction:
```sql
INSERT INTO hobbits(name,purpose)
VALUES('Frodo','Destroy the One Ring of power.');
```
The above code is known as an _implicit_ transaction. With an implicit
transaction, changes to the database happen immediately, and we have no way to
undo or roll back these changes. We can only make subsequent changes/
transactions.
An _explicit_ transaction, however, allows us to create save points and roll
back to whatever point in time we choose. An explicit transaction begins with
the command `BEGIN`, followed by the SQL statement, and then ends with either a
`COMMIT` or `ROLLBACK`.
### PostgreSQL transactional commands
**[BEGIN][3]**
-- Initiates a transaction block. All statements after a BEGIN command will be
executed in a single transaction until an explicit COMMIT or ROLLBACK is given.
Starting a transaction:
```sql
BEGIN;
INSERT INTO hobbits(name,purpose)
VALUES('Frodo','Destroy the One Ring of power.');
```
**[COMMIT][4]**
-- Commits the current transaction. All changes made by the transaction become
visible to others and are guaranteed to be durable if a crash occurs.
Committing a transaction:
```sql
BEGIN;
INSERT INTO hobbits(name,purpose)
VALUES('Frodo','Destroy the One Ring of power.');
COMMIT;
```
**[ROLLBACK][5]**
-- Rolls back the current transaction and causes all the updates made by the
transaction to be discarded. Can only undo transactions since the last COMMIT or
ROLLBACK command was issued.
Rolling back a transaction (i.e. abort all changes):
```sql
BEGIN;
INSERT INTO hobbits(name,purpose)
VALUES('Frodo','Destroy the One Ring of power.');
ROLLBACK;
```
**[SAVEPOINT][6]**
-- Establishes a new save point within the current transaction. Allows all
commands executed after the save point to be rolled back, restoring the
transaction state to what it was at the time of the save point.
Syntax to create save point: `SAVEPOINT savepoint_name;`
Syntax to delete a save point: `RELEASE SAVEPOINT savepoint_name;`
Let’s say we had the following table called `fellowship`:
| name | age |
|---|---|
| Frodo | 50 |
| Samwise | 38 |
| Merry | 36 |
| Pippin | 28 |
| Aragorn | 87 |
| Boromir | 40 |
| Legolas | 2000 |
| Gandalf | 2000 |
We'll create a transaction on this table containing a few operations. Inside
the transaction, we’ll establish a save point that we’ll roll back to before
committing.
```sql
BEGIN;
DELETE FROM fellowship
WHERE age > 100;
SAVEPOINT first_savepoint;
DELETE FROM fellowship
WHERE age > 80;
DELETE FROM fellowship
WHERE age >= 40;
ROLLBACK TO first_savepoint;
COMMIT;
```
Once our transaction is committed, our table would look like this:
| name | age |
|---|---|
| Frodo | 50 |
| Samwise | 38 |
| Merry | 36 |
| Pippin | 28 |
| Aragorn | 87 |
| Boromir | 40 |
We can see that the deletion that happened just prior to the savepoint creation
was preserved.
**[SET TRANSACTION][7]**
-- Sets the characteristics of the current transaction. (_Note: To set
characteristics for subsequent transactions in a session, use `SET SESSION
CHARACTERISTICS`.) The available transaction characteristics are the
transaction isolation level, the transaction access mode (read/write or
read-only), and the deferrable mode. (_Read more about these characteristics
in the [PostgreSQL docs][7]._)
Example of setting transaction characteristics:
```sql
BEGIN;
SET TRANSACTION READ ONLY;
...
COMMIT;
```
## When to use transactions and why
It is generally a good idea to use explicit SQL transactions when making any
updates, insertions, or deletions, to a database. However, you generally
wouldn’t write an explicit transaction for a simple `SELECT` query.
Transactions help you deal with crashes, failures, data consistency, and error
handling. The ability to create savepoints and roll back to earlier points is
tremendously helpful when doing multiple updates and helps maintain data
integrity.
Another benefit of transactions is the _**atomic**_, or “all-or-nothing", nature
of their operations. Because all of the operations in a transaction must succeed
or else be aborted, partial or incomplete updates to the database will not be
made. End-users will see only the final result of the transaction.
## Transaction properties: ACID
A SQL transaction has four properties known collectively as “ACID" -- which is an acronym for _Atomic, Consistent, Isolated, and Durable_. The following descriptions come from the IBM doc “[ACID properties of transactions][2]":
**Atomicity**
-- All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.
For example, in an application that transfers funds from one account to another,
the atomicity property ensures that, if a debit is made successfully from one
account, the corresponding credit is made to the other account.
**Consistency**
-- Data is in a consistent state when a transaction starts and when it ends.
For example, in an application that transfers funds from one account to another,
the consistency property ensures that the total value of funds in both the
accounts is the same at the start and end of each transaction.
**Isolation**
-- The intermediate state of a transaction is invisible to other transactions.
As a result, transactions that run concurrently appear to be serialized.
For example, in an application that transfers funds from one account to another,
the isolation property ensures that another transaction sees the transferred
funds in one account or the other, but not in both, nor in neither.
**Durability**
-- After a transaction successfully completes, changes to data persist and are
not undone, even in the event of a system failure.
For example, in an application that transfers funds from one account to another,
the durability property ensures that the changes made to each account will not
be reversed.
### Banking transaction example
Let’s look at an example from the [PostgreSQL Transactions doc][1] that
demonstrates the ACID properties of a transaction. We have a bank database that
contains customer account balances, as well as total deposit balances for
branches. We want to record a payment of $100.00 from Alice's account to Bob's
account, as well as update the total branch balances. The transaction would
look like the code below.
```sql
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
COMMIT;
```
There are several updates happening above. The bank wants to make sure that all
of the updates happen or none happen, in order to ensure that funds are
transferred from the proper account (i.e. Alice’s account) to the proper
recipient’s account (i.e. Bob’s account). If any of the updates fails, none of
them will take effect. That is, if something goes wrong either with withdrawing
funds from Alice’s account or transferring the funds into Bob’s account, then
the entire transaction will be aborted and no changes will occur. This prevents
Alice or Bob from seeing a transaction in their account summaries that isn’t
supposed to be there.
There are many other scenarios where we would want to use an atomic operation to
ensure a successful end result. Transactions are ideal for such scenarios, and
we should use them whenever they’re applicable.
## Helpful links:
* [PostgreSQL: Transactions][1]
* [PostgreSQL Tutorial: PostgreSQL Transaction][8]
* [PostgreSQL: BEGIN][3]
* [PostgreSQL: COMMIT][4]
* [PostgreSQL: ROLLBACK][5]
* [PostgreSQL: SAVEPOINT][6]
* [PostgreSQL: SET TRANSACTION][6]
[1]: https://www.postgresql.org/docs/8.3/tutorial-transactions.html
[2]: https://www.ibm.com/support/knowledgecenter/en/SSGMCP_5.4.0/product-overview/acid.html
[3]: https://www.postgresql.org/docs/9.1/sql-begin.html
[4]: https://www.postgresql.org/docs/9.1/sql-commit.html
[5]: https://www.postgresql.org/docs/9.1/sql-rollback.html
[6]: https://www.postgresql.org/docs/9.1/sql-savepoint.html
[7]: https://www.postgresql.org/docs/9.1/sql-set-transaction.html
[8]: http://www.postgresqltutorial.com/postgresql-transaction/
________________________________________________________________________________
# Joins vs. Subqueries
To select, or not to select? That is the query. We’ve barely scratched the
surface of SQL queries. Previously, we went over how to write simple SQL
queries using the `SELECT` statement, and we learned how to incorporate a `WHERE` clause into our queries.
There’s a lot more we could add to our queries to get more refined results. In
this reading, we’ll go over joins and subqueries and talk about when we would
use one over the other.
## What is a JOIN?
We briefly looked at the `JOIN` operation after we created foreign keys in a
previous reading. The [JOIN operation][1] allows us to retrieve rows from
multiple tables.
To review, we had two tables: a "breeds" table and a "puppies" table. The two
tables shared information through a foreign key. The foreign key `breed_id`
lives on the "puppies" table and is related to the primary key `id` of the
"breeds" table.
We wrote the following `INNER JOIN` operation to get only the rows from the
“puppies" table with a matching `breed_id` in the “friends" table:
```sql
SELECT * FROM puppies
INNER JOIN breeds ON (puppies.breed_id = breeds.id);
```
[INNER JOIN][2] can be represented as a Venn Diagram, which produces rows from
Table A that match some information in Table B.
![inner-join-venn-diagram]
We got the following table rows back from our `INNER JOIN` on the "puppies"
table. These rows represent the center overlapping area of the two circles. We
can see that the data from "puppies" appears first, followed by the joined data
from the "friends" table.
```sql
id | name | age_yrs | breed_id | weight_lbs | microchipped | id | name
----+----------+---------+----------+------------+--------------+----+---------------------
1 | Cooper | 1.0 | 8 | 18 | t | 8 | Miniature Schnauzer
2 | Indie | 0.5 | 9 | 13 | t | 9 | Yorkshire Terrier
3 | Kota | 0.7 | 1 | 26 | f | 1 | Australian Shepherd
4 | Zoe | 0.8 | 6 | 32 | t | 6 | Korean Jindo
5 | Charley | 1.5 | 2 | 25 | f | 2 | Basset Hound
6 | Ladybird | 0.6 | 7 | 20 | t | 7 | Labradoodle
7 | Callie | 0.9 | 4 | 16 | f | 4 | Corgi
8 | Jaxson | 0.4 | 3 | 19 | t | 3 | Beagle
9 | Leinni | 1.0 | 8 | 25 | t | 8 | Miniature Schnauzer
10 | Max | 1.6 | 5 | 65 | f | 5 | German Shepherd
```
There are different types of `JOIN` operations. The ones you'll use most often are:
1. **Inner Join** -- Returns a result set containing rows in the left table that
match rows in the right table.
2. **Left Join** -- Returns a result set containing all rows from the left
table with the matching rows from the right table. If there is no match, the
right side will have null values.
3. **Right Join** -- Returns a result set containing all rows from the right
table with matching rows from the left table. If there is no match, the left
side will have null values.
4. **Full Outer Join** -- Returns a result set containing all rows from both the
left and right tables, with the matching rows from both sides where
available. If there is no match, the missing side contains null values.
5. **Self-Join** -- A self-join is a query in which a table is joined to itself.
Self-joins are useful for comparing values in a column of rows within the
same table.
(_See this tutorial doc on [PostgreSQL Joins][3] for more information on the
different `JOIN` operations._)
## What is a subquery?
A subquery is essentially a `SELECT` statement nested inside another `SELECT`
statement. A subquery can return a single (“scalar") value or multiple rows.
### Single-value subquery
Let’s see an example of how to use a subquery to return a single value. Take
the "puppies" table from before. We had a column called `age_yrs` in that table
(see below).
```sql
postgres=# SELECT * FROM puppies;
id | name | age_yrs | breed_id | weight_lbs | microchipped
----+----------+---------+----------+------------+--------------
1 | Cooper | 1.0 | 8 | 18 | t
2 | Indie | 0.5 | 9 | 13 | t
3 | Kota | 0.7 | 1 | 26 | f
4 | Zoe | 0.8 | 6 | 32 | t
5 | Charley | 1.5 | 2 | 25 | f
6 | Ladybird | 0.6 | 7 | 20 | t
7 | Callie | 0.9 | 4 | 16 | f
8 | Jaxson | 0.4 | 3 | 19 | t
9 | Leinni | 1.0 | 8 | 25 | t
10 | Max | 1.6 | 5 | 65 | f
(10 rows)
```
We’ll use the [PostgreSQL aggregate function][4] `AVG` to get an average puppy
age.
```sql
SELECT
AVG (age_yrs)
FROM
puppies;
```
Assuming our previous “puppies" table still exists in our database, if we
entered the above statement into psql we’d get an _**average age of 0.9**_.
(_Note: Try it out yourself in psql! Refer to the reading "Retrieving Rows From
A Table Using SELECT" if you need help remembering how we set up the “puppies"
table._)
Let's say that we wanted to find all of the puppies that are older than the
average age of 0.9. We could write the following query:
```sql
SELECT
name,
age_yrs,
breed
FROM
puppies
WHERE
age_yrs > 0.9;
```
In the above query, we compared `age_yrs` to an actual number (0.9). However, as
more puppies get added to our table, the average age could change at any time.
To make our statement more dynamic, we can plug in the query we wrote to find
the average age into another statement as a _subquery_ (surrounded by
parentheses).
```sql
SELECT
puppies.name,
age_yrs,
breeds.name
FROM
puppies
INNER JOIN
breeds ON (breeds.id = puppies.breed_id)
WHERE
age_yrs > (
SELECT
AVG (age_yrs)
FROM
puppies
);
```
We should get the following table rows, which include only the puppies older
than 9 months:
```shell
name | age_yrs | breed
---------+---------+---------------------
Cooper | 1.0 | Miniature Schnauzer
Charley | 1.5 | Basset Hound
Leinni | 1.0 | Miniature Schnauzer
Max | 1.6 | German Shepherd
```
### Multiple-row subquery
We could also write a subquery that returns multiple rows.
In the reading "Creating A Table In An Existing PostgreSQL Database", we
created a “friends" table. In "Foreign Keys And The JOIN Operation", we set up a
primary key in the “puppies" table that is a foreign key in the “friends" table
-- `puppy_id`. We’ll use this ID in our subquery and outer query.
**"friends" table**
```shell
id | first_name | last_name | puppy_id
----+------------+-----------+----------
1 | Amy | Pond | 4
2 | Rose | Tyler | 5
3 | Martha | Jones | 6
4 | Donna | Noble | 7
5 | River | Song | 8
```
Let’s say we wanted to find all the puppies that are younger than 6 months old.
```sql
SELECT puppy_id
FROM puppies
WHERE
age_yrs < 0.6;
```
This would return two rows:
```shell
puppy_id
----------
2
8
(2 rows)
```
Now we want to use the above statement as a subquery (inside parentheses) in
another query. You’ll notice we’re using a `WHERE` clause with the [IN][5]
operator to check if the `puppy_id` from the “friends" table meets the
conditions in the subquery.
```sql
SELECT *
FROM friends
WHERE
puppy_id IN (
SELECT puppy_id
FROM puppies
WHERE
age_yrs < 0.6
);
```
We should get just one row back. River Song has a puppy younger than 6 months
old.
```shell
id | first_name | last_name | puppy_id
----+------------+-----------+----------
5 | River | Song | 8
(1 row)
```
## Using joins vs. subqueries
We can use either a `JOIN` operation or a subquery to filter for the same
information. Both methods can be used to get info from multiple tables. Take the query/subquery from above:
```sql
SELECT *
FROM friends
WHERE
puppy_id IN (
SELECT puppy_id
FROM puppies
WHERE
age_yrs < 0.6
);
```
Which produced the following result:
```shell
id | first_name | last_name | puppy_id
----+------------+-----------+----------
5 | River | Song | 8
(1 row)
```
Instead of using a `WHERE` clause with a subquery, we could use a `JOIN`
operation:
```sql
SELECT *
FROM friends
INNER JOIN puppies ON (puppies.puppy_id = friends.puppy_id)
WHERE
puppies.age_yrs < 0.6;
```
Again, we’d get back one result, but because we used an `INNER JOIN` we have
information from both the “puppies" and “friends" tables.
```shell
id | first_name | last_name | puppy_id | name | age_yrs | breed | weight_lbs | microchipped | puppy_id
----+------------+-----------+----------+--------+---------+--------+------------+--------------+----------
5 | River | Song | 8 | Jaxson | 0.4 | Beagle | 19 | t | 8
(1 row)
```
### Should I use a JOIN or a subquery?
As stated earlier, we could use either a JOIN operation or a subquery to filter
for table rows. However, you might want to think about whether using a JOIN or a
subquery is more appropriate for retrieving data.
A JOIN operation is ideal when you want to combine rows from one or more tables
based on a match condition. Subqueries work great when you’re returning a single
value. When you’re returning multiple rows, you could opt for a subquery or a
JOIN.
Executing a query using a JOIN could potentially be faster than executing a
subquery that would return the same data. (A subquery will execute once for each
row returned in the outer query, whereas the `INNER JOIN` only has to make one
pass through the data.) However, this isn’t always the case. Performance
depends on the size of your data, what you’re filtering for, and how the server
optimizes the query. With smaller datasets, the difference in performance of a
JOIN and subquery is imperceptible. However, there are use cases where a
subquery would improve performance.
(_See this article for more info: [When is a SQL Subquery 260x Faster than a
Left Join?][6]_)
We can use the the [EXPLAIN][11] statement to see runtime statistics of our
queries that help with debugging slow queries. (We'll get into this more later!)
## Helpful links:
* PostgreSQL Tutorial: [PostgreSQL Joins][3]
* PostgreSQL Tutorial: [PostgreSQL Subquery][7]
* PostgreSQL Docs: [Subquery Expressions][8]
* Essential SQL: [Subqueries versus Joins][9]
* Essential SQL: [Using Subqueries with the Select Statement][10]
[1]: https://www.postgresql.org/docs/8.3/tutorial-join.html
[2]: http://www.postgresqltutorial.com/postgresql-inner-join/
[3]: http://www.postgresqltutorial.com/postgresql-joins/
[4]: https://www.postgresql.org/docs/8.2/functions-aggregate.html
[5]: http://www.postgresqltutorial.com/postgresql-in/
[6]: https://crate.io/a/sql-subquery-vs-left-join/
[7]: http://www.postgresqltutorial.com/postgresql-subquery/
[8]: https://www.postgresql.org/docs/8.3/functions-subquery.html
[9]: https://www.essentialsql.com/what-is-the-difference-between-a-join-and-subquery/
[10]: https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/
[11]: http://www.postgresqltutorial.com/postgresql-explain/
[inner-join-venn-diagram]: images/inner-join-venn-diagram.png
________________________________________________________________________________
# PostgreSQL Indexes
PostgreSQL indexes can help us optimize our queries for faster performance. In
this reading, we'll learn how to create an index, when to use an index, and when
to avoid using them.
## What is a PostgreSQL index?
A PostgreSQL index works like an index in the back of a book. It points to
information contained elsewhere and can be a faster method of looking up the
information we want.
A book index contains a list of references with page numbers. Instead of having
to scan all the pages of the book to find the places where specific information
appears, a reader can simply check the index. In similar fashion, PostgreSQL
indexes, which are special lookup tables, let us make faster database queries.
Let’s say we had the following table:
| addresses |
|--------------|
| address_id |
| address |
| address2 |
| city_id |
| postal_code |
| phone_number |
And we made a query to the database like the following:
```sql
SELECT * FROM addresses WHERE phone_number = '5556667777';
```
The above query would scan every row of the "addresses" table to find matching
rows based on the given phone number. If "addresses" is a large table (let's say
with millions of entries), and we only expect to get a small number of results
back (one row, or a few rows), then such a query would be an inefficient way to
retrieve data. Instead of scanning every row, we could create an index for the
phone column for faster data retrieval.
## How to create an index
To create a [PostgreSQL index][1], use the following syntax:
```sql
CREATE INDEX index_name ON table_name (column_name);
```
We can create a phone number index for the above "addresses" table with the
following:
```sql
CREATE INDEX addresses_phone_index ON addresses (phone_number);
```
You can delete an index using the `DROP INDEX` command:
```sql
DROP INDEX addresses_phone_index;
```
After an index has been created, the system will take care of the rest -- it
will update an index when the table is modified and use the index in queries
when it improves performance over a full table scan.
### Types of indexes
PostgreSQL provides several index types: B-tree, Hash, GiST and GIN. The CREATE
INDEX command creates B-tree indexes by default, which fit the most common
situations. While it's good to know other index types exist, you'll probably
find yourself using the default B-tree most often.
**Single-Column Indexes**
Uses only one table column.
Syntax:
```sql
CREATE INDEX index_name ON table_name (column_name);
```
Addresses Example:
```sql
CREATE INDEX addresses_phone_index ON addresses (phone_number);
```
**Multiple-Column Indexes**
Uses more than one table column.
Syntax:
```sql
CREATE INDEX index_name ON table_name (col1_name, col2_name);
```
Addresses Example:
```sql
CREATE INDEX idx_addresses_city_post_code ON table_name (city_id, postal_code);
```
**Partial Indexes**
Uses subset of a table defined by a conditional expression.
Syntax:
```sql
CREATE INDEX index_name ON table_name WHERE (conditional_expression);
```
Addresses Example:
```sql
CREATE INDEX addresses_phone_index ON addresses (phone_number) WHERE (city_id = 2);
```
**Note**: Check out [Chapter 11 on Indexes][3] in the PostgreSQL docs for more
about types of indexes.
## When to use an index
Indexes are intended to enhance database performance and are generally thought
to be a faster data retrieval method than a sequential (or row-by-row) table
scan. However, there are instances where using an index would not improve
efficiency, such as the following:
* When working with a relatively small table (i.e. not a lot of rows)
* When a table has frequent, large-batch update or insert operations
* When working with columns that contain many NULL values
* When working with columns that are frequently manipulated
An important thing to note about indexes is that, while they can optimize READ
(i.e. table query) speed, they can also hamper WRITE (i.e. table
updates/insertions) speed. The latter's performance is affected due to the
system having to spend time updating indexes whenever a change or insertion is
made to the table.
The system optimizes database performance and decides whether to use an
index in a query or to perform a sequential table scan, but we can analyze query
performance ourselves to debug slow queries using `EXPLAIN` and `EXPLAIN ANALYZE`.
Here is an example of using `EXPLAIN` from the [PostgreSQL docs][2]:
```sql
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
```
In the QUERY PLAN above, we can see that a sequential table scan (`Seq Scan`)
was performed on the table called "tenk1". In parentheses, we see performance
information:
* Estimated start-up cost (or time expended before the scan can start): 0.00
* Estimated total cost: 458.00
* Estimated number of rows output: 10000
* Estimated average width (in bytes) of rows: 244
It's important to note that, although we might mistake the number next to `cost`
for milliseconds, `cost` is not measured in any particular unit and is
an arbitrary measurement relatively based on other query costs.
If we use the `ANALYZE` keyword after `EXPLAIN` on a `SELECT` statement, we can
get more information about query performance:
```sql
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Index Cond: (unique2 = t1.unique2)
Total runtime: 14.452 ms
```
We can see in the QUERY PLAN above that there are other types of scans
happening: Bitmap Heap Scan, Bitmap Index Scan, and Index Scan. We know that an
index has been created, and the system is using it to scan for results instead
of performing a sequential scan. At the bottom, we also have a total runtime of
14.42 ms, which is pretty fast.
## Helpful links:
* [PostgreSQL docs: Indexes][3]
* [PostgreSQL docs: Performance Tips][4]
* [Heroku DevCenter: Efficient Use of PostgreSQL Indexes][5]
## What we learned:
* How to create a PostgreSQL index
* Types of indexes
* Use cases for indexes and when to avoid them
* How to use `EXPLAIN` to analyze query performance
[1]: https://www.postgresql.org/docs/9.1/sql-createindex.html
[2]: https://www.postgresql.org/docs/9.1/using-explain.html
[3]: https://www.postgresql.org/docs/9.1/indexes.html
[4]: https://www.postgresql.org/docs/8.1/performance-tips.html
[5]: https://devcenter.heroku.com/articles/postgresql-indexes
________________________________________________________________________________
# Node-Postgres And Prepared Statements
The library [node-postgres] is, not too surprisingly, the library that Node.js
applications use to connect to a database managed by a PostgreSQL RDBMS. The
applications that you deal with will use this library to make connections to the
database and get rows returned from your SQL `SELECT` statements.
## Connecting
The **node-postgres** library provides two ways to connect to it. You can use a
single `Client` object, or you can use a `Pool` of `Client` objects. Normally,
you want to use a `Pool` because creating and opening single connections to any
database server is a "costly" operation in terms of CPU and network resources.
A `Pool` creates a group of those `Client` connections and lets your code use
them whenever it needs to.
To use a `Pool`, you specify any specific portions of the following connection
parameters that you need. The default values for each parameter is listed with
each parameter.
| Connection parameter | What it indicates | Default value |
|----------------------|---------------------------------------------|-----------------------------------------|
| user | The name of the user you want to connect as | The user name that runs the application |
| password | The password to use | The password set in your configuration |
| database | The name of the database to connect to | The user's database |
| port | The port over which to connect | 5432 |
| host | The name of the server to connect to | localhost |
Normally, you will only override the user and password fields, and sometimes the
database if it doesn't match the user's name. You do that by instantiating a
new `Pool` object and passing in an object with those key/value pairs.
```js
const { Pool } = require('pg');
const pool = new Pool({
user: 'application_user',
password: 'iy7qTEcZ',
});
```
Once you have an instance of the `Pool` class, you can use the `query` method on
it to run queries. (The `query` method returns a Promise, so it's nice to just
use `await` for it to finish.)
```js
const results = await pool.query(`
SELECT id, name, age_yrs
FROM puppies;
`);
console.log(results);
```
When this runs, you will get an object that contains a property named "rows".
The value of "rows" will be an array of the rows that match the statement.
Here's an example output from the code above.
```
{
rows:
[ { id: 1, name: 'Cooper', age_yrs: '1.0' },
{ id: 2, name: 'Indie', age_yrs: '0.5' },
{ id: 3, name: 'Kota', age_yrs: '0.7' },
{ id: 4, name: 'Zoe', age_yrs: '0.8' },
{ id: 5, name: 'Charley', age_yrs: '1.5' },
{ id: 6, name: 'Ladybird', age_yrs: '0.6' },
{ id: 7, name: 'Callie', age_yrs: '0.9' },
{ id: 8, name: 'Jaxson', age_yrs: '0.4' },
{ id: 9, name: 'Leinni', age_yrs: '1.0' },
{ id: 10, name: 'Max', age_yrs: '1.6' } ],
}
```
You can see that the "rows" property contains an array of objects. Each object
represents a row in the "puppies" table that matches the query. Each object has
a property named after the column selected in the `SELECT` statement. The query
read `SELECT id, name, age_yrs` and each object has an "id", "name", and an
"age_yrs" property.
You can then use that array to loop over and do things with it. For example,
you could print them to the console like this:
```js
for (let row of results.rows) {
console.log(`${row.id}: ${row.name} is ${row.age_yrs} old`);
}
```
Which would show
```
1. Cooper is 1.0 years old
2. Indie is 0.5 years old
3. Kots is 0.7 years old
...
```
## Prepared Statement
Prepared statements are SQL statements that have parameters in them that you can
use to substitute values. The parameters are normally part of the `WHERE` clause
in all statements. You will also use then in the `SET` portion of `UPDATE`
statements and the `VALUES` portion of `INSERT` statements.
For example, say your application collected the information to create a new row
in the puppy table by asking for the puppy's name, age, breed, weight, and if
it was microchipped. You'd have those values stored in variables somewhere.
You'd want to create an `INSERT` statement that inserts the data from those
variables into a SQL statement that the RDBMS could then execute to insert a
new row.
Think about what a generic `INSERT` statement would look like. It would have to
have the
```sql
INSERT INTO puppies (name, age_yrs, breed, weight_lbs, microchipped)
```
portion of the statement. The part that would change with each time you inserted
would be the specific values that would go into the `VALUES` section of the
`INSERT` statement. With prepared statements, you use _positional parameters_
to act as placeholders for the actual values that you will provide the query.
For example, the generic `INSERT` statement from above would look like this.
```sql
INSERT INTO puppies (name, age_yrs, breed, weight_lbs, microchipped)
VALUES ($1, $2, $3, $4, $5);
```
Each of the "$" placeholders is a positional argument for the parameters that
you pass in. That means, the value that pass in for the first parameter will be
put where the "$1" placeholder is, which is the value for the "name" of the
puppy. The "$2" corresponds to the "age_yrs" column, so it should contain the
age of the puppy. This continues for the third, fourth, and fifth parameters,
as well.
Assume that in your code, you have the variables `name`, `age`, `breedName`,
`weight`, and `isMicrochipped` containing the values that the user provided for
the new puppy. Then, your use of the `query` method will now include another
argument, the values that you want to pass in inside an array.
```js
await pool.query(`
INSERT INTO puppies (name, age_yrs, breed, weight_lbs, microchipped)
VALUES ($1, $2, $3, $4, $5);
`, [name, age, breedName, weight, isMicrochipped]);
```
You can see that the variable `name` is in the first position of the array, so
it will be substituted into the placeholder "$1". The `age` variable is in the
second position of the array, so it will be substituted into the "$2"
placeholder.
The full documentation for how to use queries with **node-postgres** can be
found on the [Queries] documentation page on their Web site.
## Try it out for yourself
Make sure you have a database with a table that has data in it. If you don't,
create a new database and run the following SQL.
```sql
CREATE TABLE puppies (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age_yrs NUMERIC(3,1) NOT NULL,
breed VARCHAR(100) NOT NULL,
weight_lbs INTEGER NOT NULL,
microchipped BOOLEAN NOT NULL DEFAULT FALSE
);
insert into puppies(name, age_yrs, breed, weight_lbs, microchipped)
values
('Cooper', 1, 'Miniature Schnauzer', 18, 'yes'),
('Indie', 0.5, 'Yorkshire Terrier', 13, 'yes'),
('Kota', 0.7, 'Australian Shepherd', 26, 'no'),
('Zoe', 0.8, 'Korean Jindo', 32, 'yes'),
('Charley', 1.5, 'Basset Hound', 25, 'no'),
('Ladybird', 0.6, 'Labradoodle', 20, 'yes'),
('Callie', 0.9, 'Corgi', 16, 'no'),
('Jaxson', 0.4, 'Beagle', 19, 'yes'),
('Leinni', 1, 'Miniature Schnauzer', 25, 'yes' ),
('Max', 1.6, 'German Shepherd', 65, 'no');
```
Now that you have ten rows in the "puppies" table of a database, you can create
a simple Node.js project to access it.
Create a new directory somewhere that's not part of an existing project. Run
`npm init -y` to initialize the **package.json** file. Then, run `npm install
pg` to install the library from this section. (Why is the name of the library
"node-postgres" but you install "pg"? Dunno.) Finally, open Visual Studio Code
for the current directory with `code .`.
Create a new file named **sql-test.js**.
The first thing you need to do is import the `Pool` class from the
**node-postgres** library. The name of the library in the **node_modules**
directory is "pg". That line of code looks like this and can be found all over the
[node-postgres] documentation.
```js
const { Pool } = require('pg');
```
Now, write some SQL that will select all of the records from the "puppies"
table. (This is assuming you want to select puppies. If you're using a different
table with different data, write the appropriate SQL here.)
```js
const { Pool } = require('pg');
const allPuppies = `
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies;
`;
```
You will now use that with a new `Pool` object. You will need to know the name
of the database that the "puppies" table is in (or whatever database you want
to connect to).
```js
const { Pool } = require('pg');
const allPuppies = `
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies;
`;
const pool = new Pool({
database: '«database name»'
});
```
Of course, replace "«database name»" with the name of your database. Otherwise,
when you run it, you will see this error message.
```
UnhandledPromiseRejectionWarning: error: database "«database name»" does not exist
```
This will, by default, connect to "localhost" on port "5432" with your user
credentials because you did not specify any other parameters.
Once you have the pool, you can execute the query that you have in `allPuppies`.
Remember that the `query` method returns a Promise. This code wraps the call to
`query` in an `async function` so that it can use the `await` keyword for
simplicity's sake. Then, it prints out the rows that it fetched to the console.
Finally, it calls `end` on the connection pool object to tell **node-postgres**
to close all the connections and quit. Otherwise, your application will just
hang and you'll have to close it with Control+C.
```js
const { Pool } = require('pg');
const allPuppiesSql = `
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies;
`;
const pool = new Pool({
database: '«database name»'
});
async function selectAllPuppies() {
const results = await pool.query(allPuppiesSql);
console.log(results.rows);
pool.end();
}
selectAllPuppies();
```
When you run this with `node sql-test.js`, you should see some output like this
although likely in a nicer format.
```
[ { id: 1, name: 'Cooper', age_yrs: '1.0', breed: 'Miniature Schnauzer', weight_lbs: 18, microchipped: true },
{ id: 2, name: 'Indie', age_yrs: '0.5', breed: 'Yorkshire Terrier', weight_lbs: 13, microchipped: true },
{ id: 3, name: 'Kota', age_yrs: '0.7', breed: 'Australian Shepherd', weight_lbs: 26, microchipped: false },
{ id: 4, name: 'Zoe', age_yrs: '0.8', breed: 'Korean Jindo', weight_lbs: 32, microchipped: true },
{ id: 5, name: 'Charley', age_yrs: '1.5', breed: 'Basset Hound', weight_lbs: 25, microchipped: false },
{ id: 6, name: 'Ladybird', age_yrs: '0.6', breed: 'Labradoodle', weight_lbs: 20, microchipped: true },
{ id: 7, name: 'Callie', age_yrs: '0.9', breed: 'Corgi', weight_lbs: 16, microchipped: false },
{ id: 8, name: 'Jaxson', age_yrs: '0.4', breed: 'Beagle', weight_lbs: 19, microchipped: true },
{ id: 9, name: 'Leinni', age_yrs: '1.0', breed: 'Miniature Schnauzer', weight_lbs: 25, microchipped: true },
{ id: 10, name: 'Max', age_yrs: '1.6', breed: 'German Shepherd', weight_lbs: 65, microchipped: false } ]
```
Now, try one of those parameterized queries. Comment out the `selectAllPuppies`
function and invocation.
```js
// async function selectAllPuppies() {
// const results = await pool.query(allPuppiesSql);
// console.log(results.rows);
// pool.end();
// }
// selectAllPuppies();
```
Add the following content to the bottom of the file.
```js
// Define the parameterized query where it will select a puppy
// based on an id
const singlePuppySql = `
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies
WHERE ID = $1;
`;
// Run the parameterized SQL by passing in an array that contains
// the puppyId to the query method. Then, print the results and
// end the pool.
async function selectOnePuppy(puppyId) {
const results = await pool.query(singlePuppySql, [puppyId]);
console.log(results.rows);
pool.end();
}
// Get the id from the command line and store it
// in the variable "id". Pass that value to the
// selectOnePuppy function.
const id = Number.parseInt(process.argv[2]);
selectOnePuppy(id);
```
Now, when you run the program, include a number after the command. For example,
if you run `node sql-test.js 1`, it will print out
```
[ { id: 1,
name: 'Cooper',
age_yrs: '1.0',
breed: 'Miniature Schnauzer',
weight_lbs: 18,
microchipped: true } ]
```
If you run `node sql-test.js 4`, it will print out
```
[ { id: 4,
name: 'Zoe',
age_yrs: '0.8',
breed: 'Korean Jindo',
weight_lbs: 32,
microchipped: true } ]
```
That's because the number that you type on the command line is being substituted
in for the "$1" in the parameterized query. That means, when you pass in "4",
It's like the RDMBS takes the parameterized query
```sql
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies
WHERE ID = $1;
```
and your value "4"
and mushes them together to make
```sql
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies
WHERE ID = 4; -- Value substituted here by PostgreSQL.
```
That happens because when you run the query, you call the `query` method like
this.
```js
await pool.query(singlePuppySql, [puppyId]);
```
which passes along the sql stored in `singlePuppySql` and the value stored in
`puppyId` (as the first parameter) to PostgreSQL.
What do you think will happen if you change `singlePuppySql` to have _two_
parameters instead of one, but only pass in one parameter through the `query`
method?
```js
const singlePuppySql = `
SELECT id, name, age_yrs, breed, weight_lbs, microchipped
FROM puppies
WHERE ID = $1
AND age_yrs > $2;
`;
```
PostgreSQL is smart enough to see that you've only provided one parameter value
but it needs _two_ positional parameters. It gives you the error message
```
error: bind message supplies 1 parameters, but prepared statement "" requires 2
```
In this error message, the term "bind message" is the kind of message that the
`query` method sends to PostgreSQL when you provide parameters to it.
Change the query back to how it was. Now, add an extra parameter to the
invocation of the `query` method. What do you think will
```js
await pool.query(singlePuppySql, [puppyId, 'extra parameter']);
```
Again, PostgreSQL gives you an error message about a mismatch in the number of
placeholders in the SQL and the number of values you passed to it.
```
error: bind message supplies 2 parameters, but prepared statement "" requires 1
```
## What you've learned
Here, you've seen how to connect to a PostgreSQL database using the
**node-postgres** library named "pg". You saw how to run simple SQL statements
against it and handle the results. You also saw how to create parameterized
queries so that you can pass in values to be substituted.
If you are using the **node-postgres** library and running your own handcrafted
SQL, you will most often use parameterized queries. It's good to get familiar
with them.
[node-postgres]: https://www.node-postgres.com
[Queries]: https://node-postgres.com/features/queries
________________________________________________________________________________
# Recipe Box Project
In this project, you will build the Data Access Layer to power a Web
application. This means that you will provide all of the SQL that it takes to
get data into and from the database. You will do this in SQL files that the
application will load and read.
**Note**: This is not a good way to create a database-backed application. This
project is structured this way so that it isolates the activity of SQL writing.
Do not use this project as a template for your future projects.
## The data model analysis
What goes into a recipe box? Why, recipes, of course! Here's an example recipe
card.
![Recipe card]
You can see that a recipe is made up of three basic parts:
* A title,
* A list of ingredients, and
* A list of instructions.
You're going to add a little more to that, too. It will also have
* The date/time that it was entered into the recipe box, and
* The date/time it was last updated in the recipe box.
These are good pieces of data to have so that you can show them "most recent"
for example.
Ingredients themselves are complex data types and need their own structure. They
"belong" to a recipe. That means they'll need to reference that recipe. That
means an ingredient is made up of:
* An amount (optional),
* A unit of measure (optional),
* The actual food stuff, and
* The id of the recipe that it belongs to.
That unit of measure is a good candidate for normalization, don't you think?
It's a predefined list of options that should not change and that you don't want
people just typing whatever they want in there, not if you want to maintain
data integrity. Otherwise, you'll end up with "C", "c", "cup", "CUP", "Cup", and
all the other permutations, each of which is a distinct value but means the same
thing.
Instructions are also complex objects, but not by looking at them. Initially,
one might only see text that comprises an instruction. But, very importantly,
instructions have _order_. They also _belong_ to the recipe. With that in mind,
an instruction is made up of:
* The text of the instruction,
* The order that it appears in the recipe, and
* The id of the recipe that it belongs to.
That is enough to make a good model for the recipe box.
![recipe box data model]
## Getting started
* Download the starter project from
https://github.com/appacademy-starters/sql-recipe-box
* Run `npm install` to install the packages
* Run `npm start` to start the server on port 3000
You'll do all of your work in the **data-access-layer** directory. In there, you
will find a series of SQL files. In each, you will find instructions of what to
do to make the user interface to work. They are numbered in an implied order for
you to complete them. The only real requirement is that you finish the SQL for
the **00a-schema.sql** and **00b-seed.sql** files first. That way, as you make
your way through the rest of the SQL files, the tables and some of the data will
already exist for you. You can run the command `npm run seed` to run both of
those files or pipe each it into `psql` as you've been doing.
 The seed won't correctly run while the application maintains a
connection to the application database. You may also need to exit all of the
active `psql` instances that you have running to close out all of the active
connections. When you run the seed, if it reports that it can't do something
because of active connections, look for a running instance of the server,
Postbird, or `psql` with that connection.
**Warning**: running the seed files will destroy all data that you have in the
database.
## Your SQL
When you write the SQL, they will mostly be parameterized queries. The first
couple of files will show you how it needs to be done, where you will place the
parameter placeholders "$1", "$2", and so on. If you need to, refer to the
[Parameterized query] section of the documentation for **node-postgres**.
In each of the following files in the **data-access-layer**, you will find one
or more lines with the content `-- YOUR CODE HERE`. It is your job to write the
SQL statement described in the block above that code. Each file is named with
the intent of the SQL that it should contain.
## The application
The application is a standard [express.js] application using the [pug] library
to generate the HTML and the [node-postgres] library to connect to the database.
It reads your SQL files whenever it wants to make a database call. If your file
throws an error, then the UI handles it by telling you what needs to be fixed
so that the UI will work. The application will also output error messages for
missing functionality.
The SQL files contain a description of what the content is and where it's used
in the application. Tying those together, you'll know you're done when you have
all of the SQL files containing queries and there are no errors in the UI or
console.
## Directions
Fill out the **00a-schema.sql** and **00b-seed.sql** files first. Then seed
the database with command, `npm run seed`.
### Home Page
Start the server by running `npm run dev`. Then go to `localhost:3000` you should
see the home page with "Latest Recipes". To show the latest recipes properly,
complete the `01-get-ten-most-recent-recipes.sql` file.
After completing the file, make sure you correctly defined the sql query so that
the first recipe listed is the most recently updated recipe.
### `/recipes/:id`
If you click on one of the recipes in the list of recipes on the home page, it
will direct you to that recipe's Detail Page. Complete the following files that
correspond to this page and make sure to test a file right after you fill out
the file by refreshing the page:
- `02a-get-recipe-by-id.sql`
- `02b-get-ingredients-by-recipe-id.sql`
- `02c-get-instructions-by-recipe-id.sql`
Make sure to read the instructions well! In all the above sql queries, the
`$1` parameter will be the recipe id.
### `/recipes/new`
Click on `ADD A RECIPE` button on the Navigation Bar to direct you to the
New Recipe Form page. Fill out the `03a-insert-new-recipe.sql` file so you can
create a new recipe.
### `/recipes/:id/edit`
After creating a new recipe, you will be directed to the Recipe Edit page where
you can add instructions and ingredients to a recipe. Complete the following
files that correspond to this page and make sure to test a file right after
you fill out the file:
- `03b-get-units-of-measure.sql`
- `04-insert-new-ingredient.sql`
- `05-insert-new-instruction.sql`
- `06-delete-recipe.sql`
### `/recipes?term={searchTerm}`
Allow users to find recipes by a part of their name using the Search Bar in the
Navigation Bar. Complete `07-search-recipes.sql` for this feature.
[Recipe card]: images/sql-recipe-card.jpeg
[express.js]: https://www.expressjs.com
[pug]: https://pugjs.org
[node-postgres]: https://node-postgres.com
[Parameterized query]: https://node-postgres.com/features/queries#Parameterized%20query
[recipe box data model]: images/sql-recipe-box-data-model.png
________________________________________________________________________________
# WEEK-10 DAY-4
*Sequelize ORM* {ignore=true}
# ORM Learning Objectives
To ease the use of SQL, the object-relational mapping tool was invented. This
allows developers to focus on their application code and let a library generate
all of the SQL for them. Depending on which developer you ask, this is a miracle
or a travesty. Either way, those developers use them because writing all of the
SQL by hand is a chore that most software developers just don't want to do.
In this section, you will learn:
* How to install, configure, and use Sequelize, an ORM for JavaScript
* How to use database migrations to make your database grow with your
application in a source-control enabled way
* How to perform CRUD operations with Sequelize
* How to query using Sequelize
* How to perform data validations with Sequelize
* How to use transactions with Sequelize
________________________________________________________________________________
# Installing And Using Sequelize
Now that you have gained experience with SQL, it is time to learn how to
access data stored in a SQL database using a JavaScript program. You
will use a JavaScript library called Sequelize to do this. Sequelize is
an example of an _Object Relational Mapping_ (commonly abbreviated
_ORM_). An ORM allows a JavaScript programmer to fetch and store data in
a SQL database using JavaScript functions instead of writing SQL code.
When you finish this reading you will be able to:
* Describe what an Object Relational Mapping is and what it is used for.
* Install and configure the packages needed to use Sequelize.
* Use Sequelize to generate JavaScript code that fetches and stores data
in a SQL database.
* Use those auto-generated methods to fetch and store data in a SQL
database.
## What Is An ORM?
An _Object Relational Mapping_ is a library that allows you to access
data stored in a SQL database through object-oriented, non-SQL code
(such as JavaScript). You will write _object-oriented_ code that
accesses data stored in a _relational_ SQL database like Postgres. The
ORM is the _mapping_ that will "translate" your object-oriented code
into SQL code that the database can run. The ORM will automatically
generate SQL code for common tasks like fetching and storing data.
You will learn how to use the [Sequelize ORM][sequelize-documentation].
Sequelize is the most widely used JavaScript ORM library.
[sequelize-documentation]: https://sequelize.org/v5/
## How To Install Sequelize
After creating a new node project with `npm init` we are ready to install the
Sequelize library.
```sh
npm install<EMAIL_ADDRESS>npm install<EMAIL_ADDRESS>npm install<EMAIL_ADDRESS>```
We have installed not only the Sequelize library, but also a command
line tool called `sequelize-cli` that will help us auto-generate and
manage JavaScript files which will hold our Sequelize ORM code.
Last, we have also installed the pg library. This library allows
Sequelize to access a Postgres database. If you were using a different
database software (such as MySQL), you would need to install a different
library.
## How To Initialize Sequelize
We can run the command `npx sequelize init` to automatically setup the following directory structure for our project:
```
.
├── config
│ └── config.json
├── migrations
├── models
│ └── index.js
├── node_modules
├── package-lock.json
├── package.json
└── seeders
```
>Aside: the `npx` tool allows you to easily run scripts provided by
>packages like `sequelize-cli`. If you don't already have `npx`, you can
>install it with `npm install npx --global`. Without `npx` you would have
>to run the bash command: `./node_modules/.bin/sequelize init`. This
>directly runs the `sequelize` script provided by the installed
>`sequelize-cli` package.
Having run `npx sequelize init`, we must write our database login
information into `config/config.json`.
By default this file contains different sections we call "environments". In a
typical company you will have different database servers and configuration
depending on where you app is running. Development is usually where you do
your development work. In our case this is our local computer.
Since we are doing development, we can just modify the "development" section to look
like this:
```json
{
"development": {
"username": "catsdbuser",
"password": "catsdbpassword",
"database": "catsdb",
"host": "<IP_ADDRESS>",
"dialect": "postgres"
}
}...
```
Here we are supposing that we have already created a `catsdb` database
owned by the user `catsdbuser`, with password `catsdbpassword`. By
setting `host` to `<IP_ADDRESS>`, we are saying that the database will run
on the same machine as my JavaScript application. Last, we specify that
we are using a `postgres` database.
## Verifying That Sequelize Can Connect To The Database
At the top level of our project, we should create an `index.js` file.
From this file we will verify that Sequelize can connect to the SQL
database. To do this, we use the `authenticate` method of the sequelize object.
```javascript
// ./index.js
const { sequelize } = require("./models");
async function main() {
try {
await sequelize.authenticate();
} catch (e) {
console.log("Database connection failure.");
console.log(e);
return;
}
console.log("Database connection success!");
console.log("Sequelize is ready to use!");
// Close database connection when done with it.
await sequelize.close();
}
main();
// Prints:
//
// Executing (default): SELECT 1+1 AS result
// Database connection success!
// Sequelize is ready to use!
```
You may observe that the `authenticate` method returns a JavaScript
`Promise` object. We use `await` to wait for the database connection to
be established. If `authenticate` fails to connect, the `Promise` will
be rejected. Since we use `await`, an exception will be thrown.
Many Sequelize methods return `Promise`s. Using `async` and `await` lets
us use Sequelize methods as if they were synchronous. This helps reduce
code complexity significantly.
 This closes the connection to
the database. A Node.js JavaScript program will not terminate until all
open files and database connections are closed. Thus, to make sure the
Node.js program doesn't "hang" at the end, we close the database
connection.
## Our Preexisting Database Schema
We are assuming that we are working with a preexisting SQL database. Our
`catsdb` has a single table: `Cats`. Using the `psql` command-line
program, we can describe the pre-existing `Cats` table below.
```
catsdb=> \d "Cats"
Table "public.Cats"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('"Cats_id_seq"'::regclass)
firstName | character varying(255) | | |
specialSkill | character varying(255) | | |
age | integer | | |
createdAt | timestamp with time zone | | not null |
updatedAt | timestamp with time zone | | not null |
Indexes:
"Cats_pkey" PRIMARY KEY, btree (id)
```
Besides a primary key `id`, each `Cats` record has a `firstName`, a
`specialSkill`, and an `age`. Each record also keeps track of two
timestamps: the time when the cat was created (`createdAt`), and the
most recent time when a column of the cat has been updated
(`updatedAt`).
## Using Sequelize To Generate The Model File
We will configure Sequelize to access the `Cats` table via a JavaScript
class called `Cat`. To do this, we first use our trusty Sequelize CLI:
```bash
# Oops, forgot age:integer! (Don't worry we'll fix it later)
npx sequelize model:generate --name Cat --attributes "firstName:string,specialSkill:string"
```
This command generates two files: a _model_ file (`./models/cat.js`)
and a _migration_ file (`./migrations/20200203211508-Cat.js`). We will
ignore the migration file for now, and focus on the model file.
When using Sequelize's `model:generate` command, we specify two things.
First: we specify the _singular_ form of the `Cats` table name (`Cat`).
Second: we list the columns of the `Cats` table after the `--attributes`
flag: `firstName` and `specialSkill`. We tell Sequelize that these are
both `string` columns (Sequelize calls SQL `character varying(255)`
columns `string`s).
We do not need to list `id`, `createdAt`, or `updatedAt`. Sequelize will
always presume those exist. Notice that we have **forgotten** to list
`age:integer` -- we will fix that soon!
## Examining (And Modifying) A Sequelize Model File
Let us examine the generated `./models/cat.js` file:
```javascript
// ./models/cat.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cat = sequelize.define('Cat', {
firstName: DataTypes.STRING,
specialSkill: DataTypes.STRING
}, {});
Cat.associate = function(models) {
// associations can be defined here
};
return Cat;
};
```
This file exports a function that defines a `Cat` class. When you use
`Sequelize` to query the `Cats` table, each row retrieved will be
transformed by Sequelize into an instance of the `Cat` class. A
JavaScript class like `Cat` that corresponds to a SQL table is called a
_model_ class.
The `./models/cat.js` will not be loaded by us directly. Sequelize will
load this file and call the exported function to define the `Cat` class.
The exported function uses Sequelize's `define` method to auto-generate
a new class (called `Cat`).
> Note: You may notice we aren't using the JavaScript's `class` keyword
> to define the Cat class. With Sequelize, it is going to do all that for us
> with the `define` method. This is because Sequelize was around way before
> the `class` keyword was added to JavaScript. It is possible to use the class
> keyword with Sequelize, but it's [undocumented].
The first argument of `define` is the name of the class to define:
`Cat`. Notice how the second argument is an `Object` of `Cats` table
columns:
```javascript
{
firstName: DataTypes.STRING,
specialSkill: DataTypes.STRING
}
```
This object tells Sequelize about each of the columns of `Cats`. It maps
each column name (`firstName`, `specialSkill`) to the type of data
stored in the corresponding column of the `Cats` table. It is
unnecessary to list `id`, `createdAt`, `updatedAt`, since Sequelize will
already assume those exist.
We can correct our earlier mistake of forgetting `age`. We update the
definition as so:
```javascript
const Cat = sequelize.define('Cat', {
firstName: DataTypes.STRING,
specialSkill: DataTypes.STRING,
age: DataTypes.INTEGER,
}, {});
```
A complete list of Sequelize datatypes can be found in the
[documentation][sequelize-datatypes].
[sequelize-datatypes]: https://sequelize.org/v5/manual/data-types.html
## Using The `Cat` Model To Fetch And Update SQL Data
We are now ready to use our `Cat` model class. When Sequelize defines
the `Cat` class, it will generate instance and class methods needed to
interact with the `Cats` SQL table.
As we mentioned before we don't require our `cats.js` file directly.
Instead we require `./models` which loads the file `./models/index.js`.
Inside this file it reads through all our models and attaches them to an object
that it exports. So we can use destructuring to get a reference to our model
class `Cat` like so:
```javascript
const { sequelize, Cat } = require("./models");
```
Now let's update _our_ `index.js` file to fetch a `Cat` from the `Cats`
table:
```javascript
const { sequelize , Cat } = require("./models");
async function main() {
try {
await sequelize.authenticate();
} catch (e) {
console.log("Database connection failure.");
console.log(e);
return;
}
console.log("Database connection success!");
console.log("Sequelize is ready to use!");
const cat = await Cat.findByPk(1);
console.log(cat.toJSON());
await sequelize.close();
}
main();
// This code prints:
//
// Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
// {
// id: 1,
// firstName: 'Markov',
// specialSkill: 'sleeping',
// age: 5,
// createdAt: 2020-02-03T21:32:28.960Z,
// updatedAt: 2020-02-03T21:32:28.960Z
// }
```
We use the `Cat.findByPk` static class method to fetch a single cat: the one
with `id` equal to 1. This static method exists because our `Cat` model class
_extends_ `Sequelize.Model`.
"Pk" stands for _primary key_; the `id` field is the primary key for the
`Cats` table. `findByPk` returns a `Promise`, so we must `await` the
result. The result is an instance of the `Cat` model class.
The cleanest way to log a fetched database record is to first call the
`toJSON` method. `toJSON` converts a `Cat` object to a _Plain Old
JavaScript Object_ (POJO). `Cat` instances have many private variables
and methods that can be distracting when printed. When you call
`toJSON`, only the public data fields are copied into a JavaScript
`Object`. Printing this raw JavaScript `Object` is thus much cleaner.
> The author has a pet-peeve about the `.toJSON()` method of Sequelize,
> it does not return JSON. It instead returns a POJO. If you needed it to be
> JSON you would still need to call `JSON.stringify(cat.toJSON())`. Perhaps
> they should have called it `.toObject` or `.toPOJO` instead.
Note that Sequelize has logged the SQL query it ran to fetch Markov's
information. This logging information is often helpful when trying to
figure out what Sequelize is doing.
You'll also notice that Sequelize puts double quotes around the table and field
names. So if you are trying to look at your "Cats" table from the `psql`
command you will need to quote them there as well. This is because PostgreSQL
lowercases all identifiers like table and fields names before the query is run
if they aren't quoted.
## Reading And Changing Record Attributes
While `toJSON` is useful for logging a `Cat` object, it is not the
simplest way to access individual column values. To read the `id`,
`firstName`, etc of a `Cat`, you can directly access those attributes on
the `Cat` instance itself:
```javascript
async function main() {
// Sequelize authentication code from above...
const cat = await Cat.findByPk(1);
console.log(`${cat.firstName} has been assigned id #${cat.id}.`);
console.log(`They are ${cat.age} years old.`)
console.log(`Their special skill is ${cat.specialSkill}.`);
await sequelize.close();
}
main();
// This code prints:
//
// Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
// Markov has been assigned id #1.
// They are 5 years old.
// Their special skill is sleeping.
```
Accessing data directly through the `Cat` object is just like reading an
attribute on any other JavaScript class. You may likewise _change_
values in the database:
```javascript
async function main() {
// Sequelize authentication code from above...
// Fetch existing cat from database.
const cat = await Cat.findByPk(1);
// Change cat's attributes.
cat.firstName = "Curie";
cat.specialSkill = "jumping";
cat.age = 123;
// Save the new name to the database.
await cat.save();
await sequelize.close();
}
// Prints:
//
// Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
// Executing (default): UPDATE "Cats" SET "firstName"=$1,"specialSkill"=$2,"age"=$3,"updatedAt"=$4 WHERE "id" = $5
main();
```
Note that changing the `firstName` attribute value does not immediately
change the stored value in the SQL database. Changing the `firstName`
without calling `save` **has no effect** on the database. Only when we
call `cat.save()` (and `await` the promise to resolve) will the changes
to `firstName`, `specialSkill`, and `age` be saved to the SQL database.
All these values are updated simultaneously.
## Conclusion
Having completed this reading, you should be able to:
* Describe what an Object Relational Mapping is and what it is used for.
* Install the `sequelize`, `sequelize-cli`, `pg` packages.
* Configure Sequelize via the `config/config.json` file.
* Use Sequelize's `authenticate` method to verify that Sequelize can
connect to the database.
* Use the Sequelize CLI `model:generate` command to generate a model
file.
* Configure a model file to tell Sequelize about each database column.
* Use the `findByPk` class method to fetch data from a SQL table.
* Read data attributes from a model instance.
* Modify a model instance's attributes and save the changes back to the
SQL database using the `save` method.
[undocumented]: https://codewithhugo.com/using-es6-classes-for-sequelize-4-models/
________________________________________________________________________________
# Using Database Migrations
We've seen how to use an ORM like Sequelize to fetch and store data in a
SQL database using JavaScript classes and methods. Sequelize also lets
you write JavaScript code that creates, modifies, or drops SQL tables.
The JavaScript code that does this is called a _migration_. A migration
"moves" the database from an old schema to a new schema.
When you finish this reading you will be able to:
* Describe advantages to using migrations over raw SQL commands to
create and drop tables.
* Write migrations that create and drop tables.
* Undo incorrect migrations, fix them, and rerun them.
## Sequelize Migration Files
In the prior reading we assumed that a `Cats` table already existed in
our `catsdb` database. In this reading, we will presume that the `Cats`
table does not exist, and that we have to create the table ourselves.
This is the typical case when you aren't merely interacting with a
preexisting database. When you develop your own application, the
database will start out empty and with a blank schema.
We previously used the Sequelize CLI tool to autogenerate a `Cat` model
file like so:
```bash
# Oops, forgot age:integer!
npx sequelize model:generate --name Cat --attributes "firstName:string,specialSkill:string"
```
We noted that this creates _two_ files. We've already examined the model
file `./models/cat.js`. We will now look at the auto-generated
_migration_ file `./migrations/20200203211508-create-cat.js`.
```javascript
// ./migrations/20200203211508-create-cat.js
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Cats', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstName: {
type: Sequelize.STRING
},
specialSkill: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Cats');
}
};
```
The migration file exports two functions: `up` and `down`. The `up`
function tells Sequelize how to create a `Cats` table. The `down`
function tells Sequelize how to "undo" the `up` function. The `down`
function drops the `Cats` table.
We will examine these functions more closely, but let's first see how to
use a migration.
>Note: The timestamp `20200203211508` preceding `-create-cat.js`
>represents February 2, 2020. It gives the time and day that the
>migration was generated. (Your date should be when you generated your migration)
>By using the date and time as part of the filename, all migration files will
>have unique names. Also, alphabetical sorting will order the files from oldest
>to most recent migration.
## Running A Migration
To create the `Cats` table, we must run our migration code. Having
generated the `20200203211508-create-cat.js` migration file, we will use
the Sequelize CLI tool to run the migration. We may do this like so:
```bash
# Run the migration's `up` method.
npx sequelize db:migrate
```
By giving Sequelize the `db:migrate` subcommand, it will know that we
are asking it to run any new migrations. To run a migration, Sequelize
will call the `up` method defined in the migration file. The `up` method
will run the necessary `CREATE TABLE ...` SQL command for us. Sequelize
will record (in a special `catsdb` table called `SequelizeMeta`) that
the migration has been run. The next time we call `npx sequelize
db:migrate`, Sequelize will not try to "redo" this already performed
migration. It will do nothing the second time.
Having run the migration, we can verify that the `Cats` table looks like
it should (with the exception of the `age` column):
Note that we we are using the table name in quotes here in `psql`.
```
catsdb=> \d "Cats";
Table "public.Cats"
Column | Type | Collation | Nullable
--------------+--------------------------+-----------+----------
id | integer | | not null
firstName | character varying(255) | |
specialSkill | character varying(255) | |
createdAt | timestamp with time zone | | not null
updatedAt | timestamp with time zone | | not null
Indexes:
"Cats_pkey" PRIMARY KEY, btree (id)
```
## Rolling Back A Migration
We made a mistake when generating our `Cat` migration. We forgot to
include the `age` column.
One way to fix this is to generate a *second* migration that adds the
forgotten `age` column. If we have already pushed our migration code to
a remote git server, we should opt for this option.
If the migration has not yet been pushed, we can fix the migration
directly. We will "undo" (AKA _rollback_) the migration that created the
`Cats` table (dropping the table), fix the `up` method so that the `age`
column is included, and finally rerun the migration.
> Note: this is not the same as the SQL command ROLLBACK.
To undo the migration, we run:
```bash
npx sequelize db:migrate:undo
```
Sequelize will call the `down` method for us, and the `Cats` table is
dropped.
Why should you not use the `db:migrate:undo` way when the migration file
has already been pushed to a remote git server? The reason is this: you
can easily tell other developers to fix a broken migration by writing a
second fixup migration (for instance, that adds the `age` column). All
you need to do is check this new migration file into source control and
push it. When another developer pulls your new migration code, the next
time they run `npx sequelize db:migrate`, your fixup migration will be
run on their local machine.
When rolling back already-checked-in migrations, there is no way to
easily communicate to other developers that they should (1) rollback
your migration and (2) rerun the newly corrected version of this
migration. To avoid this communication problem, you should only rollback
commits if you haven't already pushed them to a remote git server.
## Editing A Migration File
Let's examine the `up` and `down` methods more closely. Let's start with
the `up` method:
```javascript
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Cats', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstName: {
type: Sequelize.STRING
},
specialSkill: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
// ...
};
```
The `up` method will be passed a QueryInterface
([documentation][query-interface-docs]) object. This object provides a
number of commands for modifying the SQL database schema. The
`createTable` method is amongst the most important.
[query-interface-docs]: https://sequelize.org/master/class/lib/query-interface.js~QueryInterface.html
We pass the table name (`'Cats'`) along with an object mapping column
names to column attributes. Every column must have a specified `type`.
This is similar to what we saw when we generated a model file. Note that
we **do not** take `id`, `createdAt`, or `updatedAt` for granted. We
need to include those columns. Luckily, everything has been
auto-generated for us!
We will talk about `allowNull` and `primaryKey` in a later reading.
These attributes ask Sequelize to add database constraints to a column.
Likewise we will ignore `autoIncrement` for the moment (this allows a
unique `id` to be auto-generated by the database for each saved row in
the `Cats` table).
We fix the `up` method like so:
```javascript
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Cats', {
// ...
firstName: {
type: Sequelize.STRING
},
specialSkill: {
type: Sequelize.STRING
},
// Here we add the `age` column.
age: {
type: Sequelize.INTEGER,
},
// ...
});
},
// ...
};
```
Adding the `age` column to the migration is a lot like how we added
`age` to our model file.
Having fixed our migration, we may now "rerun" it the same way we ran it
the first time:
```bash
npx sequelize db:migrate
```
We may now behold the fixed table:
```
catsdb=> \d "Cats"
Table "public.Cats"
Column | Type | Collation | Nullable
--------------+--------------------------+-----------+----------
id | integer | | not null
firstName | character varying(255) | |
specialSkill | character varying(255) | |
age | integer | |
createdAt | timestamp with time zone | | not null
updatedAt | timestamp with time zone | | not null
Indexes:
"Cats_pkey" PRIMARY KEY, btree (id)
```
## `up` And `down` are Asynchronous
A final note about `up` (and also `down`). Sequelize expects `up` to be
*asynchronous*. That is, Sequelize expects `up` to return a `Promise`
object. Sequelize will wait for the `Promise` to be resolved. When the
`Promise` is resolved, Sequelize will know the work of the `up` method
is complete.
The `createTable` method is also asynchronous (returns a `Promise`). The
promise resolves when `createTable` is done creating the table. This is
why `up` is written as:
```javascript
module.exports = {
up: (queryInterface, Sequelize) => {
// up returns Promise returned by `createTable`.
return queryInterface.createTable('Cats', {
// ...
});
},
// ...
};
```
Sequelize is able to autogenerate a migration to create a `Cats` table,
but many other migrations (for instance, to add an `age` column to our
`Cats` table) must be written by hand. When writing your own migrations,
you may prefer using `async`/`await`, which is clearer:
```javascript
module.exports = {
// Note the addition of the `async` keyword
up: async (queryInterface, Sequelize) => {
// await `createTable` to finish its work.
await queryInterface.createTable('Cats', {
// ...
});
// No need to return anything. An `async` method always returns a
// Promise that waits for all `await`ed work to be performed.
},
// ...
};
```
## Writing A `down` Method
A `down` method is written just like an `up` method. In the `down`
method we "undo" what has been performed by the `up` method. We call
`QueryInterface`'s `dropTable` method to drop the `Cats` table we
created in `up`:
```javascript
module.exports = {
// ...
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Cats');
}
};
// OR, async/await way:
module.exports = {
// ...
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('Cats');
}
};
```
**Imagine we had forgotten to drop the `Cats` table in the `down`
method.** That is: imagine the `down` method was somehow left empty. If
we rollback the migration nothing will be done by the empty `down`
method. Thus the incorrect `Cats` table we created will not have been
dropped. The wrong `Cats` table would still exist.
Imagine we next fix the migration's `up` method. We want to rerun the
migration now and create the corrected `Cats` table. But when try to do
this, Sequelize will hit an error! Rerunning the migration will try to
`CREATE TABLE "Cats"` again, but SQL will complain because a `Cats`
table already exists. It was created the first time we ran the
migration, but never dropped when we tried to rollback the migration!
Inevitably all programmers will sometimes make mistakes like this. In
these circumstances, you will probably have to open `psql` and write a
SQL `DROP TABLE` command to fix things. Having manually corrected
things, you can finally rerun the corrected migration.
You should **never** manually drop a table on a production database.
That is incredibly dangerous, and typically cannot be undone. Even if
database backups do not exist, recently inserted data will be lost
forever. This is yet another reason why you ought not rollback
migrations that have been pushed from your local development
environment!
## Advantages Of Migrations
Having seen how to _use_ Sequelize migrations, we can discuss their
benefits versus writing SQL commands like `CREATE TABLE ...` yourself.
The first advantage is that Sequelize migration code is written in
JavaScript, which you may find simpler to write/read than the
corresponding SQL code. Most programmers write more JavaScript than SQL,
so they are typically better at remembering how to do things in
JavaScript than in SQL.
A second advantage is that migration files store SQL schema change code
permanently. The migration files can be checked into git, so that you
don't ever forget how your database was configured.
A third (related) advantage comes when another developer wants to
collaborate on your JavaScript program. By cloning your git repository,
they get all the migration files, too. To setup their own copy of your
database, a collaborator can run the migration files on their own
computer, playing back the schema changes one-by-one. Because they apply
the same migrations as you, they end up with the same schema as you.
Last, by using migrations you are able to rollback database changes to
fix bugs. This can be helpful in a local development environment where
it is typical to make mistakes. Remember though: you should **never**
rollback migrations that have been run on a production server.
## Conclusion
Having completed this reading, you now are able to:
* Describe advantages to using migrations over raw SQL commands to
create, modify, and drop tables.
* Generate (and modify as needed)) migrations that create and drop
tables.
* Run migrations to change the database schema.
* Undo incorrect migrations, fix them, and rerun them.
________________________________________________________________________________
# CRUD Operations Using Sequelize
There are four general ways to interact with a database. To illustrate
these, recall our `Cats` table. We can:
1. Save a new cat to the database by _creating_ a new row in the
`Cats` table,
2. We can _read_ previously stored cat data by fetching a row (or
multiple rows) out of the `Cats` table,
3. We can _update_ some of the column values for a pre-existing cat by
modifying a row in the `Cats` table,
4. We can delete (_destroy_) the data for a cat by removing a row in
the `Cats` table.
These four actions are sometimes abbreviated as _CRUD_. After this
reading, you will be able to:
* Use Sequelize to create new records in a table,
* Use Sequelize to read/fetch existing records by primary key,
* Use Sequelize to update existing records with new attribute values,
* Use Sequelize to delete records from a table.
## Creating A New Record
To save a new cat's data as a row in the `Cats` table, we do a two step
process:
1. We call the static `build` method on the `Cat` class with the desired values.
2. We call the `save` method on the `cat` instance.
Let's see an example:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Constructs an instance of the JavaScript `Cat` class. **Does not
// save anything to the database yet**. Attributes are passed in as a
// POJO.
const cat = Cat.build({
firstName: "Markov",
specialSkill: "sleeping",
age: 5,
});
// This actually creates a new `Cats` record in the database. We must
// wait for this asynchronous operation to succeed.
await cat.save();
console.log(cat.toJSON());
await sequelize.close();
}
main();
```
Running the code:
```
Executing (default): INSERT INTO "Cats" ("id","firstName","specialSkill","age","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4,$5) RETURNING *;
{
id: 1,
firstName: 'Markov',
specialSkill: 'sleeping',
age: 5,
updatedAt: 2020-02-11T19:04:23.116Z,
createdAt: 2020-02-11T19:04:23.116Z
}
```
A new row has been inserted into the `Cats` table. We see that `id`,
`updatedAt`, and `createdAt` were each autogenerated for us.
## Reading A Record By Primary Key
Let's read an existing record from the database:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Fetch the cat with id #1.
const cat = await Cat.findByPk(1);
console.log(cat.toJSON());
await sequelize.close();
}
main();
```
Running this code prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
{
id: 1,
firstName: 'Markov',
specialSkill: 'sleeping',
age: 5,
createdAt: 2020-02-11T19:04:23.116Z,
updatedAt: 2020-02-11T19:04:23.116Z
}
```
Fetching a record by primary key is the most common form of read
operation from a database. In another reading we will learn other ways
to fetch data. For instance: we will learn how to fetch all cats named
"Markov" (there may be many).
## Updating A Record
Let's tweak our reading code to change (_update_) an attribute of Markov:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
const cat = await Cat.findByPk(1);
console.log("Old Markov: ");
console.log(cat.toJSON());
// The Cat object is modified, but the corresponding record in the
// database is *not* yet changed at all.
cat.specialSkill = "super deep sleeping";
// Only by calling `save` will the data be saved.
await cat.save();
console.log("New Markov: ");
console.log(cat.toJSON());
await sequelize.close();
}
main();
```
Running this code prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
Old Markov:
{
id: 1,
firstName: 'Markov',
specialSkill: 'sleeping',
age: 5,
createdAt: 2020-02-11T19:04:23.116Z,
updatedAt: 2020-02-11T19:04:23.116Z
}
Executing (default): UPDATE "Cats" SET "specialSkill"=$1,"updatedAt"=$2 WHERE "id" = $3
New Markov:
{
id: 1,
firstName: 'Markov',
specialSkill: 'super deep sleeping',
age: 5,
createdAt: 2020-02-11T19:04:23.116Z,
updatedAt: 2020-02-11T19:15:08.668Z
}
```
**Important note**: changing an attribute of a `Cat` object does not
immediately change any data in the `Cats` table. To change data in the
`Cats` table, you must also call `save`. If you forget to call `save`,
no data will be changed. `save` is asynchronous, so you must also
`await` for it to complete.
If you look carefully, you can see that the `updatedAt` attribute was
changed for us when we updated Markov!
## Destroying A Record
We can also destroy records and remove them from the database:
```javascript
const process = require("process");
const { sequelize , Cat } = require("./models");
async function main() {
const cat = await Cat.findByPk(1);
// Remove the Markov record.
await cat.destroy();
await sequelize.close();
}
main();
```
This code prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."id" = 1;
Executing (default): DELETE FROM "Cats" WHERE "id" = 1
```
## Class Methods For CRUD
When creating a record, you can avoid the two step process of (1)
creating a `Cat` instance and (2) calling the `save` instance method.
You can do a one step process of calling the `create` **class method**:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
const cat = await Cat.create({
firstName: "Curie",
specialSkill: "jumping",
age: 4,
});
console.log(cat.toJSON());
await sequelize.close();
}
main();
```
The `create` class method does both steps in one. It is just a
convenience. Similar to before, this code prints:
```
Executing (default): INSERT INTO "Cats" ("id","firstName","specialSkill","age","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4,$5) RETURNING *;
{
id: 3,
firstName: 'Curie',
specialSkill: 'jumping',
age: 4,
updatedAt: 2020-02-11T19:36:03.858Z,
createdAt: 2020-02-11T19:36:03.858Z
}
```
When destroying, we also did a two step process: (1) fetch the record,
(2) call the `destroy` instance method. Instead, we could just call the
`destroy` **class method** directly:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Destroy the Cat record with id #3.
await Cat.destroy({ where: { id: 3 } });
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): DELETE FROM "Cats" WHERE "id" = 3
```
An advantage to the class method form of destroying is that we avoid an
unnecessary fetch of `Cat.findByPk(3)`. Database queries can
sometimes be slow, though typically a few extra queries won't make a big
difference. Choosing between the instance and class methods of
destroying usually comes down to which you consider easier to
read/understand.
## Conclusion
As ever, the best resource for learning about Sequelize model methods is
the [documentation][sequelize-model-documentation]. The documentation
explains the `create`, `destroy`, `findByPk`, and `save` methods
in depth.
[sequelize-model-documentation]: https://sequelize.org/master/class/lib/model.js~Model.html
Having completed this reading, you now know how to:
* Use Sequelize to create new records in a table (using both instance
and class methods),
* Use Sequelize to read/fetch existing records by primary key,
* Use Sequelize to update existing records with new attribute values,
* Use Sequelize to delete records from a table (using both instance and
class methods).
________________________________________________________________________________
# Querying Using Sequelize
We have already seen how to find a single record by primary key:
`findByPk`. In this reading we will learn about more advanced ways to
query a table. We will learn how to:
* Fetch all `Cats` whose name is `"Markov"`,
* Fetch all `Cats` whose name is `"Markov"` **OR** `"Curie"`,
* Fetch all `Cats` whose age is **not** 5,
* Fetch all `Cats` whose name is `"Markov"` **AND** whose age is 5,
* Fetch all `Cats` whose age is **less than** 5,
We will also learn how to:
* Order `Cats` results by age (descending or ascending),
* Limit `Cats` results to a finite number.
## Basic Usage Of `findAll` To Retrieve Multiple Records
Let's consider a simple example where we want to retrieve all the `Cats`
in the database:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// `findAll` asks to retrieve _ALL_ THE CATS!! An array of `Cat`
// objects will be returned.
const cats = await Cat.findAll();
// Log the fetched cats.
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
Since this is an array we can't use that `.toJSON()` method we learned earlier,
so we can instead use `JSON.stringify` on the Array.
Pro tip: giving a 3rd argument to `JSON.stringify` will pretty-print the
result with the specified spacing. (We pass `null` as the 2nd argument
to skip it.) You can read more at the [`JSON.stringify`
docs][stringify-docs].
[stringify-docs]:
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify
Running this code prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat";
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
},
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
It isn't typical to want to fetch *every* record. We typically want to
get only those records that match some criterion. In SQL, we use a
`WHERE` clause to do this. With Sequelize, we issue a `WHERE` query like
so:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Fetch all cats named Markov.
const cats = await Cat.findAll({
where: {
firstName: "Markov",
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
Which prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."firstName" = 'Markov';
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
}
]
```
We've passed the `findAll` class method the `where` option. The `where`
option tells Sequelize to use a `WHERE` clause. The option value passed
is `{ firstName: "Markov" }`. This tells Sequelize to only return those
`Cats` where `firstName` is equal to `"Markov"`.
If we wanted to select those `Cats` named Markov **OR** Curie, we can
map `firstName` to an array of `["Markov", "Curie"]`. For example:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Fetch all cats named either Markov or Curie.
const cats = await Cat.findAll({
where: {
firstName: ["Markov", "Curie"],
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."firstName" IN ('Markov', 'Curie');
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
},
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
The difference is that we've passed `{ firstName: ["Markov", "Curie"
]}`. Sequelize will return all `Cats` whose `firstName` matches either
`"Markov"` or `"Curie"`.
## Using `findAll` To Find Objects Not Matching A Criterion
We can also find all the `Cats` whose names are **NOT** Markov, but we will
need to require in the `Op` object from Sequelize so we can use the "not equal" operator from it:
```javascript
const { Op } = require("sequelize");
const { sequelize, Cat } = require("./db/models");
async function main() {
const cats = await Cat.findAll({
where: {
firstName: {
// Op.ne means the "not equal" operator.
[Op.ne]: "Markov",
},
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
Prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."firstName" != 'Markov';
[
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
This is our first example of a Sequelize
[_operator_][sequelize-operator-docs]: `Op.ne`. `ne` stands for "not
equal." Instead of mapping `firstName` to a single value like `"Markov"`
or an array of values like `["Markov", "Curie"]`, we have mapped it to:
```javascript
{ [Op.ne]: "Markov" }
```
How does this work? `Op.ne` is a JavaScript _symbol_: `Op.ne ===
Symbol.for('ne')`. To simplify, let's just imagine that `Op.ne ===
"ne"`.
When we write `{ [Op.ne]: "Markov" }`, the `[]` brackets perform key
interpolation. So this is equal to `{ "ne": "Markov" }`. So overall, we
are effectively writing:
```javascript
db.Cat.findAll({
where: {
// Won't exactly work (you need to use `[Op.ne]` after all). Does
// illustrate the concept though.
firstName: { "ne": "Markov" },
},
})
```
This perhaps makes it clearer how Sequelize understands what we want.
Sequelize is being passed an *object* as the `firstName` value. The
object is specifying that we want to do a `!=` SQL operation by using
the `"ne"` ("not equal") key. The value to "not equal" is specified as
`"Markov"`.
## Combining Criteria with `Op.and`
We've seen one way to do an `OR` operation above (by mapping a column
name to an array of values). Let's see how to do an `AND` operation:
```javascript
const { Op } = require("sequelize");
const { sequelize , Cat } = require("./models");
async function main() {
// fetch cats with name != Markov AND age = 4.
const cats = await Cat.findAll({
where: {
firstName: {
[Op.ne]: "Markov",
},
age: 4,
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."firstName" != 'Markov' AND "Cat"."age" = 4;
[
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
Simply by listing more key/value pairs in the `where` object, we ask
Sequelize to "AND" together multiple criteria.
Another way to do the same thing is like so:
```javascript
const { Op } = require("sequelize");
const { sequelize , Cat } = require("./models");
async function main() {
const cats = await db.Cat.findAll({
where: {
[Op.and]: [
{ firstName: { [Op.ne]: "Markov" } },
{ age: 4 },
],
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
The use of the `Op.and` operator is somewhat similar to `Op.ne`. This
time we map `Op.and` to an *array* of criteria. Returned records must
match all the criteria.
## Combining Criteria with `Op.or`
We've already seen how to do an `OR` to match a *single column* against
*multiple values*. You can use `Op.or` for even greater flexibility:
```javascript
const { Op } = require("sequelize");
const { sequelize, Cat } = require("./models");
async function main() {
// fetch cats with name == Markov OR age = 4.
const cats = await Cat.findAll({
where: {
[Op.or]: [
{ firstName: "Markov" },
{ age: 4 },
],
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE ("Cat"."firstName" = 'Markov' OR "Cat"."age" = 4);
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
},
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
Our query is to find all cats whose names are "Markov" and whose age is
4. Therefore both cats are returned: Markov and Curie (whose age is 4).
## Querying With Comparisons
We can use operators like `Op.gt` (greater than) and `Op.lt` (less than)
to select by comparing values. We use these just like `Op.ne`:
```javascript
const { Op } = require("sequelize");
const { sequelize, Cat } = require("./models");
async function main() {
// Fetch all cats whose age is > 4.
const cats = await Cat.findAll({
where: {
age: { [Op.gt]: 4 },
},
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."age" > 4;
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
}
]
```
## Ordering Results
We've seen how to use a `where` query option to filter results with a
SQL `WHERE` clause. We can use the `order` query option to perform a SQL
`ORDER BY`:
```javascript
const { sequelize, Cat } db = require("./models");
async function main() {
const cats = await Cat.findAll({
order: [["age", "DESC"]],
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This prints:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" ORDER BY "Cat"."age" DESC;
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
},
{
"id": 5,
"firstName": "Curie",
"specialSkill": "jumping",
"age": 4,
"createdAt": "2020-02-11T23:03:25.398Z",
"updatedAt": "2020-02-11T23:03:25.398Z"
}
]
```
We've specified `{ order: [["age", "DESC"]] }`. Notice how we specify
the sort order with a doubly-nested array. If we wanted to sort
ascending we could more simply write: `{ order: ["age"] }`.
What if we wanted to sort by *two* columns? For instance, say we wanted
to `SORT BY age DESC, firstName`. We would write: `{ order: [["age",
"DESC"], "firstName"] }`. That would sort descending by `age`, and then
ascending by `firstName` for cats with the same age.
## Limiting Results and `findOne`
If we want only the oldest cat we can use the `limit` query option:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
const cats = await Cat.findAll({
order: [["age", "DESC"]],
limit: 1,
});
console.log(JSON.stringify(cats, null, 2));
await sequelize.close();
}
main();
```
This selects only one (the oldest) cat:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" ORDER BY "Cat"."age" DESC LIMIT 1;
[
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
}
]
```
Since we know that there will be only one result, it is pointless to
return an array. In cases when we want a maximum of one result, we can
use `findOne`:
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
const cat = await Cat.findOne({
order: [["age", "DESC"]],
});
console.log(JSON.stringify(cat, null, 2));
await sequelize.close();
}
main();
```
Which prints:
```
>> node index.js
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" ORDER BY "Cat"."age" DESC LIMIT 1;
{
"id": 4,
"firstName": "Markov",
"specialSkill": "sleeping",
"age": 5,
"createdAt": "2020-02-11T23:03:25.388Z",
"updatedAt": "2020-02-11T23:03:25.388Z"
}
```
This returned the `Cat` object directly, not wrapped in an array.
If there is no record matching the criteria passed to `findOne`, it will
return `null` (rather than an empty array):
```javascript
const { sequelize, Cat } = require("./models");
async function main() {
// Try to find a non-existant cat.
const cat = await Cat.findOne({
where: {
firstName: "Franklin Delano Catsevelt",
},
});
console.log(JSON.stringify(cat, null, 2));
await sequelize.close();
}
main();
```
No such cat exists:
```
Executing (default): SELECT "id", "firstName", "specialSkill", "age", "createdAt", "updatedAt" FROM "Cats" AS "Cat" WHERE "Cat"."firstName" = 'Franklin Delano Catsevelt' LIMIT 1;
null
```
## Conclusion
We've scratched the surface of the many query options supported by
Sequelize. You may find more information as necessary by reading the
[Sequelize querying documentation][querying-documentation]. You can in
particular review the [list of Sequelize query
operators][sequelize-operator-docs].
Now that you've completed this reading you should know how to:
* Use the `where` query option,
* Use the `Op.and` operator to match **all** of multiple criteria,
* Use the `Op.or` operator to match **any** of multiple criteria,
* Use the `Op.ne` to match rows where the value **does not equal** the
specified value,
* Use the `Op.gt`, `Op.lt` operators to **compare** values,
* Use the `order` query option to **order** results,
* Use the `limit` query option to **limit** the number of returned
results,
* Use `findOne` when only one result is expected or desired.
[querying-documentation]: https://sequelize.org/v5/manual/querying.html
[sequelize-operator-docs]: https://sequelize.org/v5/manual/querying.html#operators
________________________________________________________________________________
# Model Validations With Sequelize
It's important to make sure that data stored to a database is not
erroneous or incomplete. Imagine the following forms of "garbage data:"
* A `Cats` record with `firstName` set to `NULL`. All `Cats` ought to
have a name.
* A `Cats` record with `firstName` set to the empty string: `""`.
* A `Cats` record with an `age` less than `0`. `age` must always be
non-negative.
* Perhaps the `specialSkill` should come from a pre-defined limited list
of `["jumping", "sleeping", "purring"]`. A `Cats` record with a
`specialSkill` of `"pearl diving"` would thus be invalid.
Sequelize lets us write JavaScript code that will check that these data
requirements are satisfied before saving a record to the database. The
JavaScript code that does this is called a _validation_. A validation is
code that makes sure that data is valid.
In this reading you will learn how to:
1. Validate that an attribute is not set to `NULL`.
2. Validate that a string attribute is not set to the empty string `""`.
3. Validate that a string attribute is not too long (has too many
characters).
3. Validate that a numeric attribute meets minimum or maximum
thresholds.
4. Validate that an attribute is within a limited set of options.
## Validating That An Attribute Is Not `NULL`
We should not allow a `Cat` to be saved to the database if it lacks
1. a `firstName`,
1. an `age`, or
3. a `specialSkill`.
None of these should be set to `NULL`.
Before adding validations to check these requirements, let's review what
our `Cat` model code currently looks like:
```javascript
// ./models/cat.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cat = sequelize.define('Cat', {
firstName: DataTypes.STRING,
specialSkill: DataTypes.STRING,
age: DataTypes.INTEGER,
}, {});
Cat.associate = function(models) {
// associations can be defined here
};
return Cat;
};
```
We will modify our model definition to give more specific instructions
to Sequelize about the `firstName`, `specialSkill`, and `age`
attributes:
```javascript
// ./models/cat.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cat = sequelize.define('Cat', {
firstName: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "firstName must not be null",
},
},
},
specialSkill: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "specialSkill must not be null",
},
},
},
age: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "age must not be null",
},
},
},
}, {});
Cat.associate = function(models) {
// associations can be defined here
};
return Cat;
};
```
What has changed? We now map each attribute name (`firstName`,
`specialSkill`, `age`) to a POJO that tells Sequelize how to configure
that attribute. Here is the POJO for `firstName`:
```javascript
{
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "firstName must not be null",
},
},
}
```
The `type` attribute is of course vital: this used to be the only thing
we specified. We've added two new attributes. The first is `allowNull:
false`. This tells Sequelize not to let us set the `firstName` attribute
to `NULL`.
The second attribute is `validate`. We will spend a lot of time
examining this attribute in this reading. Validation logic for
`firstName` is configured inside the `validate` attribute. Our
`validate` configuration is:
```javascript
{
notNull: {
msg: "firstName must not be null",
},
}
```
This configuration tells Sequelize what error message to give if we try
to set the `firstName` attribute to `NULL`. It's odd that we have to set
both `allowNull: false` and `notNull: { msg: ... }`. This feels like
unnecessary duplication. Regardless, that's what Sequelize wants us to
do. On the other hand, we do get a chance to specify the error message
to print if the validation fails (`"firstName must not be null"`).
Let's see how the validation logic helps us avoid saving junk data to
our database:
```javascript
// index.js
const { sequelize, Cat } = require("./models");
async function main() {
const cat = Cat.build({
// Empty cat. All fields set to `null`.
});
try {
// Try to save cat to the database.
await cat.save();
console.log("Save success!");
console.log(JSON.stringify(cat, null, 2));
} catch (err) {
console.log("Save failed!");
// Print list of errors.
for (const validationError of err.errors) {
console.log("*", validationError.message);
}
}
await sequelize.close();
}
main()
```
Running this code prints:
```
Save failed!
* firstName must not be null
* specialSkill must not be null
* age must not be null
```
What happened? When we call the `save` method on a `Cat`, Sequelize will
check that all the specified validations are satisfied. In this case
none of them are! The `save` method will throw an exception, which we
handle using `try { ... } catch (err) { ... }`.
What kind of exception? The thrown error is a
[`ValidationError`][validation-error-docs]. This has an `errors`
attribute, which stores an array of
[`ValidationErrorItem`s][validation-error-item-docs]. We print out the
message for each item error.
Because there were validation failures, Sequelize **will not save** the
invalid `Cats` record to the database. Sequelize thus keeps us from
inserting junk data into the database.
If we want to save our `Cat` object, we would have to change its
attributes to meet the validations (i.e., set them to something other
than `NULL`) and call `save` a second time. For example:
```javascript
// index.js
const { sequelize, Cat } = require("./models");
async function main() {
const cat = Cat.build({
// Empty cat. All fields set to `null`.
});
try {
await cat.save();
} catch (err) {
// The save will not succeed!
console.log("We will fix and try again!");
}
// Fix the various validation problems.
cat.firstName = "Markov";
cat.specialSkill = "sleeping";
cat.age = 4;
try {
// Trying to save a second time!
await cat.save();
console.log("Success!");
} catch (err) {
// The save *should* succeed!
console.log(err);
}
await sequelize.close();
}
main()
```
[validation-error-docs]: https://sequelize.org/v5/class/lib/errors/validation-error.js~ValidationError.html
[validation-error-item-docs]: https://sequelize.org/v5/class/lib/errors/validation-error.js~ValidationErrorItem.html
## The `notEmpty` Validation
Even though we are not allowed to set `firstName` and `specialSkill` to
`NULL`, we could still set them to the empty string `""`:
```javascript
// index.js
const { sequelize, Cat } = require("./models");
async function main() {
const cat = Cat.build({
firstName: "",
specialSkill: "",
age: 5,
});
try {
// Try to save cat to the database.
await cat.save();
console.log("Save success!");
console.log(JSON.stringify(cat, null, 2));
} catch (err) {
console.log("Save failed!");
// Print list of errors.
for (const validationError of err.errors) {
console.log("*", validationError.message);
}
}
await sequelize.close();
}
main();
```
```
Executing (default): INSERT INTO "Cats" ("id","firstName","specialSkill","age","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4,$5) RETURNING *;
Save success!
{
"id": 8,
"firstName": "",
"specialSkill": "",
"age": 5,
"updatedAt": "2020-02-12T21:34:49.250Z",
"createdAt": "2020-02-12T21:34:49.250Z"
}
```
This is bogus: `Cats` records should have a non-empty `firstName` and
`specialSkill`. We will therefore add a second validation for both
`firstName` and `specialSkill`:
```javascript
// ./models/cat.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cat = sequelize.define('Cat', {
firstName: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "firstName must not be null",
},
notEmpty: {
msg: "firstName must not be empty",
},
},
},
specialSkill: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "specialSkill must not be null",
},
notEmpty: {
msg: "specialSkill must not be empty",
},
},
},
// ...
}, {});
Cat.associate = function(models) {
// associations can be defined here
};
return Cat;
};
```
When we run the same `index.js` that tries to save the `Cats` record
with the empty `firstName` and `specialSkill`, we now print:
```
Save failed!
* firstName must not be empty
* specialSkill must not be empty
```
Excellent! We've added the new validation by adding a `notEmpty` key to
the `validate` POJO. Just like with `notNull`, we specify a message to
print.
This is the typical story: we add new validations by adding new
key/value pairs to the `validate` POJO. Sequelize provides many
different kinds of validations for us, but we configure all of them in
the same general manner.
## Forbidding Long String Values
We don't want our cats to have names that are too long. We add a `len`
validation like so:
```javascript
// ./models/cat.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cat = sequelize.define('Cat', {
firstName: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "firstName must not be null",
},
notEmpty: {
msg: "firstName must not be empty",
},
len: {
args: [0, 8],
msg: "firstName must not be more than eight letters long",
},
},
},
// ...
}, {});
Cat.associate = function(models) {
// associations can be defined here
};
return Cat;
};
```
If we try to run:
```javascript
// index.js