• ​NodeJS We re going to use this to run JavaScript code on the server. I ve decided to use the latest version of Node, v6.3.0 at the time of writing, so that we ll have access to most of the new features introduced in ES6.
• ​Express As per their website, Express is a Fast, unopinionated, minimalist web framework for Node.js , that we re going to be building our Todo list application on.
• ​PostgreSQL This is a powerful open-source database that we re going to use. I ve attached an article I published on the setup below!

• However, if you face issues while installing PostgreSQL, or you don t want to dive into installing it, you can opt for a version of PostgreSQL hosted online. I recommend ElephantSQL. I found it s pretty easy to get started with. However, the free version will only give you a 20MB allowance.
• ​Sequelize In addition, we re going to use Sequelize, which is a database ORM that will interface with the Postgres database for us.

• RDBMS stands for Relational Database Management System
• A software application that you run that your programs can connect to so that they can store, modify, and retrieve data.
• An RDBMS can track many databases. We will use PostgreSQL, or postgres , primarily for our RDBMS and it will be able to create individual databases for each of our projects.

• In general, relational data is information that is connected to other pieces of information.
• When working with relational databases, we can connect two entries together utilizing foreign keys (explained below).
• In a pets database, we could be keeping track of dogs and cats as well as the toys that each of them own. That ownership of a cat to a toy is the relational aspect of relational data. Two pieces of information that can be connected together to show some sort of meaning.

• The actual location that data is stored.
• A database can be made up of many tables that each store specific kinds of information.
• We could have a pets database that stores information about many different types of animals. Each animal type could potentially be represented by a different table.

• Within a database, a table stores one specific kind of information.
• The records (entries) on these tables can be connected to records on other tables through the use of foreign keys
• In our pets database, we could have a dogs table, with individual records

• A primary key is used in the database as a unique identifier for the table.
• We often use an id field that simply increments with each entry. The incrementing ensures that each record has a unique identifier, even if their are other fields of the record that are repeated (two people with the same name would still need to have a unique identifier, for example).
• With a unique identifier, we can easily connect records within the table to records from other tables.

• A foreign key is used as the connector from this record to the primary key of another table s record.
• In our pets example, we can imagine two tables to demonstrate: a table to represent cats and a table to represent toys. Each of these tables has a primary key of id that is used as the unique identifier. In order to make a connection between a toy and a cat, we can add another field to the cat table called owner_id , indicating that it is a foreign key for the cat table. By setting a toy s owner_id to the same value as a particular cat s id , we can indicate that the cat is the owner of that toy.

• Names within postgres should generally consist of only lowercase letters, numbers, and underscores.
• Tables within a database are plural by convention, so a table for cats would typically be cats and office locations would be office_locations (all lowercase, underscores to replace spaces, plural)

• The psql command by default will try to connect to a database and username that matches your system s username
• We connect to a different database by providing an argument to the psql command
• psql pets
• To connect with a different username we can use the -U flag followed by the username we would like to use. To connect to the pets database as pets_user
• psql -U pets_user pets
• If there is a password for the user, we can tell psql that we would like a prompt for the password to show up by using the -W flag.
• psql -U pets_user -W pets (the order of our flags doesn t matter, as long as any arguments associated with them are together, such as pets_user directly following -U in this example)

• You can tell if you are logged in as a superuser or normal user by the prompt in the terminal.
• If the prompt shows =>, the user is a normal user
• If the prompt show =#, the user is a superuser

• Within psql, we can create a user with the CREATE USER {username} {WITH options} command.
• The most common options we ll want to use are WITH PASSWORD ‘mypassword’ to provide a password for the user we are creating, CREATEDB to allow the user to create new databases, or SUPERUSER to create a user with all elevated permissions.

• We can use the command CREATE DATABASE {database name} {options} inside psql to create a new database.
• A popular option we may utilize is WITH OWNER {owner name} to set another user as the owner of the database we are making.

• We can GRANT and REVOKE privileges from a database to users or categories of users.
• In order to remove connection privileges to a database from the public we can use REVOKE CONNECT ON DATABASE {db_name} FROM PUBLIC;, removing all public connection access.
• If we wanted to grant it back, or to a specific user, we could similarly do GRANT CONNECT ON DATABASE {db_name} FROM {specific user, PUBLIC, etc.};

• To list all databases we can use the \l or \list command in psql.

• The whitespace does not matter. Creating the SQL statements on multiple lines is easier to read, but just like JavaScript, they can be presented differently.
• One common issue is that SQL does not like trailing commas, so the last column cannot have a comma after its type in this example.

• To list all database tables, use the \dt command.

• There are many different data types that we can use in our tables, here are some common examples:
• SERIAL: autoincrementing, very useful for IDs
• VARCHAR(n): a string with a character limit of n
• TEXT: doesn t have character limit, but less performant
• BOOLEAN: true/false
• SMALLINT: signed two-byte integer (-32768 to 32767)
• INTEGER: signed four-byte integer (standard)
• BIGINT: signed eight-byte integer (very large numbers)
• NUMERIC: or DECIMAL, can store exact decimal values
• TIMESTAMP: date and time

• In addition to the data type, we can provide flags for constraints to place on our column data.
• The UNIQUE flag indicates that the data for the column must not be repeated.
• By default we can create entries in our tables that are missing data from columns. When creating a pet, maybe we don t provide an age because we don t know it, for example. If we want to require that the data be present in order to create a new record, we can indicate that column must be NOT NULL.
• In the example below, we are requiring our pets to have unique names and for them to be present (both UNIQUE and NOT NULL). We have no such constraints on the age column, allowing repetition of ages or their complete absence.

• When creating a table we can indicate the primary key by passing in the column name to parentheses like so:

• We could have also used the PRIMARY KEY flag on the column definition itself:

• In our table definition, we can use the line FOREIGN KEY (foreign_key_stored_in_this_table) REFERENCE {other table} ({other_tables_key_name}) to connect two tables.
• This is probably easier to see in an example:

• Exactly as the LO states, CREATE TABLE and create table are interpreted the same way. Using capitalization is a good convention in order to distinguish your keywords.
• The entity names that we use ARE case-sensitive, however. So a table named pets is unique from a table named Pets. In general, we prefer to use all lowercase for our entities to avoid any of this confusion.

1. 1.

   How to use the SELECT … FROM … statement to select data from a single table

• Supply the column names in the SELECT clause. If we want all columns, we can also use *
• Supply the table names in the FROM clause

• Sometimes we may need to specify what table we are selecting a column from, particulurly if we had joined multiple tables together.

• The WHERE clause allows us to select or apply actions to records that match specific criteria instead of to a whole table.
• We can use WHERE with a couple of different operators when making our comparison
• WHERE {column} = {value} provides an exact comparison
• WHERE {column} IN ({value1}, {value2}, {value3}, etc.) matches any provided value in the IN statement. We can make this more complex by having a subquery inside of the parentheses, having our column match any values within the returned results.
• WHERE {column} BETWEEN {value1} AND {value2} can check for matches between two values (numeric ranges)
• WHERE {column} LIKE {pattern} can check for matches to a string. This is most useful when we use the wildcard %, such as WHERE breed LIKE ‘%Shepherd’, which will match any breed that ends in Shepherd
• The NOT operator can also be used for negation in the checks.
• Mathematical operators can be used when performing calculations or comparisons within a query as well, such as

• When we want to get information from a related table or do querying based on related table values, we can join the connected table by comparing the foreign key to where it lines up on the other table:

• When a table is already created we can then insert records into it using the INSERT INTO keywords.
• We provide the name of the table that we would like to add records to, followed by the VALUES keyword and each record we are adding. Here s an example:

• We can also specify columns when we are inserting data. This makes it clear which fields we are providing data for and allows us to provide them out of order, skip null or default values, etc.

• The UPDATE keyword can be used to find records and change their values in our database.
• We generally follow the pattern of UPDATE {table} SET {column} = {new value} WHERE {match condition};.
• Without a condition to narrow our records down, we will update every record in the table, so this is an important thing to double check!
• We can update multiple fields as well by specifying each column in parentheses and their associated new values: UPDATE {table} SET ({column1}, {column2}) = ({value1}, {value2}) WHERE {match condition};

• Similar to selecting records, we can delete records from a table by specifying what table we are deleting from and what criteria we would like to match in order to delete.
• We follow the general structure DELETE FROM {table} WHERE {condition};
• The condition here is also very important! Without a condition, all records match and will be deleted.

• Seed files are a great way for us to create records that we want to start our database out with.
• Instead of having to individually add records to our tables or manually entering them in psql or postbird, we can create a file that has all of these records and then just pass this file to psql to run.
• Seed files are also great if we ever need to reset our database. We can clear out any records that we have by dropping all of our tables, then just run our seed files to get it into a predetermined starting point. This is great for our personal projects, testing environments, starting values for new tables we create, etc.
• There are two main ways we can use a seed file with psql, the < and the | operators. They perform the same function for us, just in slightly different orders, taking the content of a .sql file and executing in within the psql environment:
• psql -d {database} < {sql filepath}
• cat {sql filepath} | psql -d {database}

• Steps to Designing the Database:
• Define the entities. What data are are you storing, what are the fields for each entity?
• You can think of this in similar ways to OOP (object oriented programming).
• If you wanted to model this information using classes, what classes would you make? Those are generally going to be the tables that are created in your database.
• The attributes of your classes are generally going to be the fields/columns that we need for each table.
• Identify primary keys. Most of the time these will be ids that you can generate as a serial field, incrementing with each addition to the database.
• Establish table relationships. Connect related data together with foreign keys. Know how we store these keys in a one-to-one, one-to-many, or many-to-many relationship.
• With a one-to-one or one-to-many relationship, we are able to use a foreign key on the table to indicate the other specific record that it is connected to.
• With a many-to-many relationship, each record could be connected to multiple records, so we have to create a join table to connect these entities. A record on this join table connects a record from one table to a record from another table.

• We can define an explicit transaction using BEGIN and ending with either COMMIT or ROLLBACK.
• If any command inside the block fails, everything will be rolled back. We can also specify that we want to roll back at the end of the block instead of committing. We saw that this can be useful when analyzing operations that would manipulate our database.

• An index can help optimize queries that we have to run regularly. If we are constantly looking up records in a table by a particular field (such as username or phone number), we can add an index in order to speed up this process.
• An index maintains a sorted version of the field with a reference to the record that it points to in the table (via primary key). If we want to find a record based on a field that we have an index for, we can look through this index in a more efficient manner than having to scan through the entire table (generally O(log n) since the index is sorted, instead of O(n) for a sequential scan).
• To add an index to a field we can use the following syntax:

• To drop an index we can do the following:

• Making an index is not always the best approach. Indices allow for faster lookup, but slow down record insertion and the updating of associated fields, since we not only have to add the information to the table, but also manipulate the index.
• We generally wouldn t care about adding an index if:
• The tables are small
• We are updating the table frequently, especially the associated columns
• The column has many NULL values

• EXPLAIN gives us information about how a query will run (the query plan)
• It gives us an idea of how our database will search for data as well as a qualitative comparitor for how expensive that operation will be. Comparing the cost of two queries will tell us which one is more efficient (lower cost).
• We can also use the ANALYZE command with EXPLAIN, which will actually run the specified query. Doing so gives us more detailed information, such as the milliseconds it took our query to execute as well as specifics like the exact number of rows filtered and returned.

1. 1.

   Demonstrate how to install and use the node-postgres library and its Pool class to query a PostgreSQL-managed database

• We can add the node-postgres library to our application with npm install pg. From there we will typically use the Pool class associated with this library. That way we can run many SQL queries with one database connection (as opposed to Client, which closes the connection after a query).

• The query method on the Pool instance will allow us to execute a SQL query on our database. We can pass in a string that represents the query we want to run

• The return value of this asynchronous function is an object with a rows key that points to an array of objects, each object representing a record with field names as keys.

• The prepared statement (SQL string that we wrote) can also be made more dynamic by allowing for parameters to be passed in.
• The Pool instance s query function allows us to pass a second argument, an array of parameters to be used in the query string. The location of the parameter substitutions are designated with $1, $2, etc., to signify the first, second, etc., arguments.

1. 1.

   How to install, configure, and use Sequelize, an ORM for JavaScript

• To start a new project we use our standard npm initialize statement
• npm init -y
• Add in the packages we will need (sequelize, sequelize-cli, and pg)
• npm install [email protected]⁵.0.0 [email protected]⁵.0.0 [email protected]⁸.0.0
• Initialize sequelize in our project
• npx sequelize-cli init
• Create a database user with credentials we will use for the project
• psql
• CREATE USER example_user WITH PASSWORD ‘badpassword’
• Here we can also create databases since we are already in postgres

• If we don t create these databases now, we could also create them after we make our changes to our config file. If we take this approach, we need to make sure our user that we created has the CREATEDB option when we make them, since sequelize will attempt to make the databases with this user. This other approach would look like:
• In psql: CREATE USER example_user WITH PASSWORD ‘badpassword’ CREATEDB
• In terminal: npx sequelize-cli db:create
• Double check that our configuration file matches our username, password, database, dialect, and seederStorage (these will be filled out for you in an assessment scenario):

1. 1.

   How to use database migrations to make your database grow with your application in a source-control enabled way

• In order to make new database tables and sequelize models that reflect them, we want to generate a migration file and model file using model:generate

• Here we are creating a migration file and a model file for a Cat. We are specifying that we want this table to have fields for firstName and specialSkill. Sequelize will automatically make fields for an id, createdAt, and updatedAt, as well, so we do not need to specify these.
• Once our migration file is created, we can go in and edit any details that we need to. Most often we will want to add in database constraints such as allowNull: false, adding a uniqueness constraint with unique: true, adding in character limits to fields such as type: Sequelize.STRING(100), or specifying a foreign key with references to another table references: { model: ‘Categories’ }.
• After we make any necessary changes to our migration file, we need to perform the migration, which will run the SQL commands to actually create the table.

• This command runs any migration files that have not been previously run, in the order that they were created (this is why the timestamp in the file name is important)
• If we realize that we made a mistake after migrating, we can undo our previous migration, or all of our migrations. After undoing them, we can make any changes necessary to our migration files (They won t be deleted from the undo, so we don t need to generate anything! Just make the necessary changes to the files that already exist and save the files.). Running the migrations again will make the tables with the updates reflected.

• In addition to the migration files, our model:generate command also created a model file for us. This file is what allows sequelize to transform the results of its SQL queries into useful JavaScript objects for us.
• The model is where we can specify a validation that we want to perform before trying to run a SQL query. If the validation fails, we can respond with a message instead of running the query, which can be an expensive operation that we know won t work.

• Another key part of the model file is setting up our associations. We can use the belongsTo, hasMany, and belongsToMany methods to set up model-level associations. Doing so is what creates the helpful functionality like addOwner that we saw in the pets example, a function that automatically generates the SQL necessary to create a petOwner record and supplies the appropriate petId and ownerId.
• In a one-to-many association, we need to have a belongsTo association on the many side, and a hasMany association on the one side:
• Instruction.belongsTo(models.Recipe, { foreignKey: ‘recipeId’ });
• Recipe.hasMany(models.Instruction, { foreignKey: ‘recipeId’ });
• In a many-to-many association, we need to have a belongsToMany on each side of the association. We generally specify a columnMapping object to show the association more clearly:

• Seed Files
• Seed files can be used to populate our database with starter data.
• npx sequelize-cli seed:generate — name add-cats
• up indicates what to create when we seed our database, down indicates what to delete if we want to unseed the database.
• For our up, we use the queryInterface.bulkInsert() method, which takes in the name of the table to seed and an array of objects representing the records we want to create:

• For our down, we use the queryInterface.bulkDelete() method, which takes in the name of the table and an object representing our WHERE clause. Unseeding will delete all records from the specified table that match the WHERE clause.

• Running npx sequelize-cli db:seed:all will run all of our seeder files.
• npx sequelize-cli db:seed:undo:all will undo all of our seeding.
• If we omit the :all we can run specific seed files
• Inserting with Build and Create
• In addition to seed files, which we generally use for starter data, we can create new records in our database by using build and save, or the combined create
• Use the .build method of the Cat model to create a new Cat instance in index.js

• When we have a reference to an instance of a model (i.e. after we have queried for it or created it), we can update values by simply reassigning those fields and using the save method

• When we have a reference to an instance of a model, we can delete that record by using destroy
• const cat = await Cat.findByPk(1); // Remove the Markov record. await cat.destroy();
• We can also call destroy on the model itself. By passing in an object that specifies a where clause, we can destroy all records that match that query
• await Cat.destroy({ where: { specialSkill: ‘jumping’ } });

• Passing an object to findAll can add on clauses to our query
• The where key takes an object as a value to indicate what we are filtering by
• { where: { field: value } } => WHERE field = value

• Using an array for the value tells sequelize we want to match any of these values

• Providing additional key/value pairs to the where object indicates all filters must match
• { where: { field1: value1, field2: value2 } } => WHERE field1 = value1 AND field2 = value2

• By requiring Op from the sequelize library we can provide more advanced comparison operators
• const { Op } = require(“sequelize”);
• Op.ne: Not equal operator

• Just like the where clause, we can pass an order key to specify we want our results ordered
• The key order points to an array with the fields that we want to order by
• By default, the order is ascending, just like standard SQL. If we want to specify descending, we can instead use a nested array with the field name as the first element and DESC as the second element. (We could also specify ASC as a second element in a nested array, but it is unnecessary as it is default)
• const cats = await Cat.findAll({ // Order by age descending, then by firstName ascending if cats have the same age order: [[“age”, “DESC”], “firstName”], }); console.log(JSON.stringify(cats, null, 2));

• Limiting results
• We can provide a limit key in order to limit our results to a specified number

• If we only want one record to be returned we can use findOne instead of findAll
• If multiple records would have matched our findOne query, it will return the first record
• Unlike findAll, findOne will return the object directly instead of an array. If no records matched the query it will return null.

• Querying with Associations

• See the database migrations section above.
• In general, we add in a validate key to each field that we want validations for. This key points to an object that specifies all of the validations we want to make on that field, such as notEmpty, notNull, len, isIn, etc.

• We can create a transaction block in order to make sure either all operations are performed or none of them are
• We use the .transaction method in order to create our block. The method takes in a callback with an argument to track our transaction id (typically just a simple tx variable).
• All of our sequelize operations can be passed a transaction key on their options argument which points to our transaction id. This indicates that this operation is part of the transaction block and should only be executed in the database when the whole block executes without error.