Last updated
For Front end developers who like myself struggle with making the jump to fullstack.
**For Front end developers who like myself struggle with making the jump to fullstack.**
You can access and query the data using the findByPk, findOne, and findAll methods.
Terminology:
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.
RDBMS and Database Entities
Define what a relational database management system is
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.
Describe what relational data is
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.
Define what a database is
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.
Define what a database table is
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
Describe the purpose of a primary key
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.
Describe the purpose of a foreign key
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.
Describe how to properly name things in PostgreSQL
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)
Connect to an instance of PostgreSQL with the command line tool psql
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)
Identify whether a user is a normal user or a superuser by the prompt in the psql shell
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
Create a user for the relational database management system
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.
Create a database in the database management system
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.
Configure a database so that only the owner (and superusers) can connect to it
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.};
View a list of databases in an installation of PostgreSQL
To list all databases we can use the \l or \list command in psql.
Create tables in a database
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.
View a list of tables in a database
To list all database tables, use the \dt command.
Identify and describe the common data types used in PostgreSQL
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
Describe the purpose of the UNIQUE and NOT NULL constraints, and create columns in database tables that have them
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.
Create a primary key for a table
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:
Create foreign key constraints to relate tables
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:
SQL is not case sensitive for its keywords but is for its entity names
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.
SQL
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
— Selects all columns from the friends table
— Selects the first_name column from the friends table (remember whitespace is ignored) SELECT name FROM friends;
Sometimes we may need to specify what table we are selecting a column from, particulurly if we had joined multiple tables together.
— 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
How to use the WHERE clause on SELECT, UPDATE, and DELETE statements to narrow the scope of the command
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
How to use the JOIN keyword to join two (or more) tables together into a single virtual table
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:
— 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.
How to use the INSERT statement to insert data into a 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 are providing the table name, then multiple records to insert — The values are listed in the order that they are defined on the table
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.
— In this example, we want to use the default value for id since it is autoincremented, so we provide DEFAULT for this field
— Alternatively, we can leave it out completely, since the default value will be used if none is provided
How to use an UPDATE statement to update data in a table
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};
— Updates the pet with id of 4 to change their name and breed
How to use a DELETE statement to remove data from a table
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.
— Deletes from the pets table any record that either has a name Floofy, a name Doggo, or an id of 3.
How to use a seed file to populate data in a database
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}
SQL (continued)
How to perform relational database design
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.
How to use transactions to group multiple SQL commands into one succeed or fail operation
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.
How to apply indexes to tables to improve performance
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 what the EXPLAIN command is used for:
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.
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).
// 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:
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
selectAllAirports();
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.
Explain how to write prepared statements with placeholders for parameters of the form $1 , $2 , and so on
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.
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 }
ORM
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 sequelize@⁵.0.0 sequelize-cli@⁵.0.0 pg@⁸.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):
How to use database migrations to make your database grow with your application in a source-control enabled way
Migrations
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.
Models Validations and Associations
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
Updating Records
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
Deleting Records
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’ } });
How to query using Sequelize
findAll
WHERE clause
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
OR in the WHERE clause
Using an array for the value tells sequelize we want to match any of these values
{ where: { field: [value1, value2] } => WHERE field IN (value1, value2)
AND in the WHERE clause
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
Op.gt and Op.lt: greater than and less than operators
const cats = await Cat.findAll({ where: { // Find all cats where the age is greater than 4 age: { [Op.gt]: 4 }, } }, }); console.log(JSON.stringify(cats, null, 2));
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
We can include associated data by adding an include key to our options object
We can get nested associations by having include point to an object that specifies which model we have an association with, then chaining an association on with another include
How to perform data validations with Sequelize
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.
How to use transactions with Sequelize
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.
Sequelize provides utilities for generating migrations, models, and seed files. They are exposed through the sequelize-cli command.
You must create a database user, and update the config/config.json file to match your database settings to complete the initialization process.
student.js
scholarship.js
student.js
class.js
student.js
lesson.js
includeSimple include of one related model.
Include can take an array of models if you need to include more than one.
Include can also take an object with keys model and include.
This is in case you have nested associations.
In this case Owner doesn't have an association with PetType, but
Pet does, so we want to include PetType onto the Pet Model.
The confusingly named toJSON() method does not return a JSON string but instead returns a POJO for the instance.
You can access and query the data using the findByPk, findOne, and findAll methods. First, make sure you import the models in your JavaScript file. In this case, we are assuming your JavaScript file is in the root of your project and so is the models folder.
The models folder exports each of the models that you have created. We have these four in our data model, so we will destructure the models to access each table individually. The associations that you have defined in each of your models will allow you to access data of related tables when you query your database using the include option.
If you want to find all recipes, for the recipe list, you would use the findAll method. You need to await this, so make sure your function is async.
If you would like to include all the ingredients so you can create a shopping list for all the recipes, you would use include. This is possible because of the association you have defined in your Recipe and Ingredient models.
If you only want to find one where there is chicken in the ingredients list, you would use findOne and findByPk.
You have two options when you want to create a row in a table (where you are saving one record into the table). You can either .build the row and then .save it, or you can .create it. Either way it does the same thing. Here are some examples:
Let’s say we have a form that accepts the name of the recipe (for simplicity). When we get the results of the form, we can:
This just created our new recipe and added it to our Recipes table. You can do the same thing like this:
If you want to modify an item in your table, you can use update. Let's say we want to change the chicken noodle soup to chicken noodle soup with extra veggies, first we need to get the recipe, then we can update it.
To delete an item from your table, you will do the same kind of process. Find the recipe you want to delete and destroy it, like this:
NOTE: If you do not await these, you will receive a promise, so you will need to use .then and .catch to do more with the items you are accessing and modifying.
For the data types and validations in your models, here are the official docs. The sequelize docs are hard to look at, so these are the specific sections with just the lists: Sequelize Data Types: https://sequelize.org/v5/manual/data-types.html Validations: https://sequelize.org/v5/manual/models-definition.html#validations When you access the data in your queries, here are the operators available, again because the docs are hard to navigate, this is the specific section with the list of operators. Operators: https://sequelize.org/v5/manual/querying.html#operators The documentation for building, saving, creating, updating and destroying is linked here, it does a pretty good job of explaining in my opinion, it just has a title that we have not been using in this course. When they talk about an instance, they mean an item stored in your table. Create/Update/Destroy: https://sequelize.org/v5/manual/instances.html
Or Checkout my personal Resource Site:
(Under construction… may be broken at any time)
a/A-Student-Resources _Edit description_goofy-euclid-1cd736.netlify.app
By Bryan Guner on March 13, 2021.
Exported from Medium on August 31, 2021.
