Databases, SQL, and Sequelize (Week 10) - Learning Objectives

Assessment Structure

Portfolio Quality, RDBMS and Database Entitities (W10D1) - Learning Objectives

Portfolio Quality (Not directly assessed)

  1. Recall the items recruiters are most interested in
  1. Explain aspects of a good looking Web application
  1. Identify App Academy’s expectations of your projects for after you graduate
  1. Practice good code hygiene when making projects live

RDBMS and Database Entities

  1. Define what a relational database management system is
  1. Describe what relational data is
  1. Define what a database is
  1. Define what a database table is
  1. Describe the purpose of a primary key
  1. Describe the purpose of a foreign key
  1. Describe how to properly name things in PostgreSQL
  1. Install and configure PostgreSQL 12, its client tools, and a GUI client for it named Postbird
  1. Connect to an instance of PostgreSQL with the command line tool psql
  1. Identify whether a user is a normal user or a superuser by the prompt in the psql shell
  1. Create a user for the relational database management system
  1. Create a database in the database management system
  1. Configure a database so that only the owner (and superusers) can connect to it
  1. View a list of databases in an installation of PostgreSQL
  1. Create tables in a database
CREATE TABLE {table name} (
  {columnA} {typeA},
  {columnB} {typeB},
  etc...
);
  1. View a list of tables in a database
  1. Identify and describe the common data types used in PostgreSQL
  1. Describe the purpose of the UNIQUE and NOT NULL constraints, and create columns in database tables that have them
CREATE TABLE pets (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) UNIQUE NOT NULL,
  age SMALLINT
);
  1. Create a primary key for a table
CREATE TABLE people (
  id SERIAL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  PRIMARY KEY (id)
);
CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);
  1. Create foreign key constraints to relate tables
CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE pets (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  age SMALLINT,
  person_id INTEGER,
  FOREIGN KEY (person_id) REFERENCES people (id)
);
  1. Explain that SQL is not case sensitive for its keywords but is for its entity names

SQL (W10D2) - Learning Objectives

SQL

  1. How to use the SELECT ... FROM ... statement to select data from a single table
-- Selects all columns from the friends table
SELECT
  * 
FROM
  friends;

-- Selects the first_name column from the friends table (remember whitespace is ignored)
SELECT name
FROM friends;
-- Notice here we are indicating that we want the "name" field from the "friends" table as well as the "name" field from the "puppies" table. We indicate the table name by table.column
-- We are also aliasing these fields with the AS keyword so that our returned results have friend_name and puppy_name as field headers
SELECT
  friends.name AS friend_name , puppies.name AS puppy_name
FROM
  friends
JOIN 
  puppies ON friends.puppy_id = puppies.id
  1. How to use the WHERE clause on SELECT, UPDATE, and DELETE statements to narrow the scope of the command
  1. How to use the JOIN keyword to join two (or more) tables together into a single virtual table
-- Here we are joining the puppies table on to the friends table. We are specifying that the comparison we should make is the foreign key puppy_id on the friends table should line up with the primary key id on the puppies table.
SELECT
  *
FROM
  friends
JOIN 
  puppies ON friends.puppy_id = puppies.id
  1. How to use the INSERT statement to insert data into a table
-- We are providing the table name, then multiple records to insert
-- The values are listed in the order that they are defined on the table
INSERT INTO table_name
VALUES
  (column1_value, colum2_value, column3_value),
  (column1_value, colum2_value, column3_value),
  (column1_value, colum2_value, column3_value);
-- In this example, we want to use the default value for id since it is autoincremented, so we provide DEFAULT for this field
INSERT INTO friends (id, first_name, last_name)
VALUES
  (DEFAULT, 'Amy', 'Pond');

-- Alternatively, we can leave it out completely, since the default value will be used if none is provided
INSERT INTO friends (first_name, last_name)
VALUES
  ('Rose', 'Tyler'),
  ('Martha', 'Jones'),
  ('Donna', 'Noble'),
  ('River', 'Song');
  1. How to use an UPDATE statement to update data in a table
-- Updates the pet with id of 4 to change their name and breed
UPDATE
  pets
SET
  (name, breed) = ('Floofy', 'Fluffy Dog Breed') WHERE id = 4;
  1. How to use a DELETE statement to remove data from a table
-- Deletes from the pets table any record that either has a name Floofy, a name Doggo, or an id of 3.
DELETE FROM
  pets
WHERE
  name IN ('Floofy', 'Doggo') OR id = 3;
  1. How to use a seed file to populate data in a database

SQL (continued) (W10D3) - Learning Objectives

SQL (continued)

  1. How to perform relational database design
  1. How to use transactions to group multiple SQL commands into one succeed or fail operation
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;
BEGIN;
  EXPLAIN ANALYZE
  UPDATE cities
  SET city = 'New York City'
  WHERE city = 'New York';
ROLLBACK;
  1. How to apply indexes to tables to improve performance
CREATE INDEX index_name ON table_name (column_name);
DROP INDEX index_name
  1. Explain what and why someone would use EXPLAIN
  1. Demonstrate how to install and use the node-postgres library and its Pool class to query a PostgreSQL-managed database
const { Pool } = require('pg');

// If we need to specify a username, password, or database, we can do so when we create a Pool instance, otherwise the default values for logging in to psql are used:
const pool = new Pool({ username: '<<username>>', password: '<<password>>', database: '<<database>>'})
const allAirportsSql = `
  SELECT id, city_id, faa_id, name
  FROM airports;
`;

async function selectAllAirports() {
  const results = await pool.query(allAirportsSql);
  console.log(results.rows);
  pool.end(); // invoking end() will close our connection to the database
}

selectAllAirports();
  1. Explain how to write prepared statements with placeholders for parameters of the form “$1”, “$2”, and so on
const airportsByNameSql = `
  SELECT name, faa_id
  FROM airports
  WHERE UPPER(name) LIKE UPPER($1)
`;

async function selectAirportsByName(name) {
    const results = await pool.query(airportsByNameSql, [ `%${name}%` ]);
    console.log(results.rows);
    pool.end(); // invoking end() will close our connection to the database
}

// Get the airport name from the command line and store it
// in the variable "name". Pass that value to the
// selectAirportsByName function.
const name = process.argv[2];
// console.log(name);
selectAirportsByName(name);

ORM (W10D4) - Learning Objectives

ORM

  1. How to install, configure, and use Sequelize, an ORM for JavaScript
{
  "development": {
    "username": "sequelize_recipe_box_app",
    "password": "HfKfK79k",
    "database": "recipe_box_development",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "seederStorage": "sequelize"
  },
  "test": {
    "username": "sequelize_recipe_box_app",
    "password": "HfKfK79k",
    "database": "recipe_box_test",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "seederStorage": "sequelize"
  },
  "production": {
    "username": "sequelize_recipe_box_app",
    "password": "HfKfK79k",
    "database": "recipe_box_production",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "seederStorage": "sequelize"
  }
}
  1. How to use database migrations to make your database grow with your application in a source-control enabled way
  1. How to perform CRUD operations with Sequelize
  1. How to query using Sequelize
  1. How to perform data validations with Sequelize
specification: {
  type: DataTypes.TEXT,
  validate: {
    notEmpty: {
      msg: 'The specification cannot be empty'
    },
    len: {
      args: [10, 100]
      msg: 'The specifcation must be between 10 and 100 characters'
    }
  }
}
  1. How to use transactions with Sequelize
async function main() {
  try {
    // Do all database access within the transaction.
    await sequelize.transaction(async (tx) => {
      // Fetch Markov and Curie's accounts.
      const markovAccount = await BankAccount.findByPk(
        1, { transaction: tx },
      );
      const curieAccount = await BankAccount.findByPk(
        2, { transaction: tx }
      );

      // No one can mess with Markov or Curie's accounts until the
      // transaction completes! The account data has been locked!

      // Increment Curie's balance by $5,000.
      curieAccount.balance += 5000;
      await curieAccount.save({ transaction: tx });

      // Decrement Markov's balance by $5,000.
      markovAccount.balance -= 5000;
      await markovAccount.save({ transaction: tx });
    });
  } catch (err) {
    // Report if anything goes wrong.
    console.log("Error!");

    for (const e of err.errors) {
      console.log(
        `${e.instance.clientName}: ${e.message}`
      );
    }
  }

  await sequelize.close();
}

main();