Complete Intro to Databases

Brian Holt

Brian Holt

SQLite Cloud
7 hours, 4 minutes CC
Complete Intro to Databases

Course Description

Databases can seem like big, insurmountable obstacles when learning to become a full-stack developer. In this course, you'll learn the basics of using four of the most popular open-source types of databases: document-based database MongoDB, relational database PostgreSQL, graph database Neo4j, and key-value store Redis. And how and when you'd use each database!

This course and others like it are available as part of our Frontend Masters video subscription.

Preview
Close

Course Details

Published: December 8, 2020

Learning Paths

Learn Straight from the Experts Who Shape the Modern Web

Your Path to Senior Developer and Beyond
  • 200+ In-depth courses
  • 18 Learning Paths
  • Industry Leading Experts
  • Live Interactive Workshops
Get Unlimited Access Now

Table of Contents

Introduction

Section Duration: 25 minutes
  • Introduction
    Brian Holt lists the prerequisites necessary to understand this course, shares the course resouces, and gives an overview of past professional experiences.
  • Installation Notes
    Brian introduces the four types of databases that the course covers, MongoDB, PostgreSQL, Neo4j, and Redis, and adds that Docker is used in this course to manage the various databases.
  • Terminology
    Brian defines a database as a place to store data, explains that a schema is used, explores the various types of databases, and discusses when it is best practice to use them. The ACID acronym and database transactions are also explored in this segment, particularly around their usefulness relating to queries.

NoSQL

Section Duration: 1 hour, 52 minutes
  • NoSQL
    Brian explains that "NoSQL" is a buzzword to describe a database that does not use SQL, even though some NoSQL databases are able to handle SQL queries. NoSQL databases do not require schemas.
  • MongoDB
    Brian introduces MongoDB, a NoSQL database, demonstrates how to create a MongoDB database using Docker, and defines collections as a group of documents or a group of objects. It is best practice to have one collection to represent objects with the same characteristic. A collection would be a row within a database. Row, record, and document are terms that can be used interchangeably.
  • Querying MongoDB
    Brian demonstrates how to insert an array of JavaScript objects into a MongoDB database, and shares a few queries that can be used when working with MongoDB. Query operators and how to efficiently use them are also discussed in this segment.
  • MongoDB Logical Operators
    Brian uses logical operators to add conditions to the data queried, and limits the amount of data queried by using a projection. A projection select only the queried data, and not the entire document.
  • Projections
    Brian explains that projections are used when engineers query a database but want to limit which fields are returned, and demonstrates how to write projections in a MongoDB database.
  • Updating MongoDB
    Brian demonstrates how to update an object within MongoDB by first querying the object that needs to be modified, and then modifying that object. The concept of upsert is also discussed in this segment, which is to find an object in a database, and updating it, or if that object does not exist creating it.
  • Deleting Documents
    Brian demonstrates how to delete multiple objects from a MongoDB database, and how to find and delete a specific query object based on a given characteristic within the database.
  • Indexes in MongoDB
    Brian explains that indexes are data structures that a database maintains to allow users to find information quickly by creating a shortcut to specifc data, and runs index queries on the MongoDB database.
  • Text Search Indexes
    Brian demonstrates how to use text search indexes in MongoDB. A text search is similar to a search one would conduct on a search engine, that would focus and match the key words of a given search.
  • Aggregation
    Brian explains that an aggregation is a feature of MongoDB database, and demonstrates how to write one. An aggregation reduces data until smaller sets of data. A Pipeline is an aggregation that is easier to maintain and that provides filters that transform documents within a collection.
  • Aggregation Q&A
    Brian answers questions about buckets within pipeline aggregations, the various stages within the aggregation pipeline, and the creation time of a given object in a MongoDB database.
  • Write a Node.js App with MongoDB
    Brian demonstrates how to create a bare bone Node.js application that searches for data within a MongoDB database.
  • Connect & Query with MongoDB & Express
    Brian demonstrates how to use Mongoose and Express to connect to the MongoDB database, and how to query the database using the Node.js application started in the previous segment.
  • MongoDB Ops
    Brian demonstrates how to run a MongoDB cluster after giving an overview of what primaries, secondaries, and replica set servers are. Sharding is also discussed in this segment.

SQL

Section Duration: 1 hour, 59 minutes
  • What are SQL Databases
    Brian defines SQL databases as relational databases, frequently abbreviated as RDBMS, or relational database management system. SQL stands for standard query language. A relational database is a set of tables that contains columns and rows, and that has a defined schema.
  • PostgreSQL
    Brian demonstrates how to create and run a PostgreSQL database inside of a Docker container. The database created is a sample PostgreSQL message board database containing various boards, messages, users, and comments.
  • Creating a Database, Table, and Record
    Brian demonstrates how to create a table, add it to the message database created in the previous segment, and how to insert a new record into the database.
  • Select, Limit, and Where
    Brian adds data in bulk to the PostgreSQL database created earlier, and demonstrates how to select data, use the limit keyword to limit the amount of data queried, and delete data from the message board database.
  • Count, Sort, Update, and Delete
    Brian demonstrates some basic operations that can be made on the data in a PostgreSQL database, including using the WHERE command to filter data, the COUNT command to check how many users are in the database, how to update data, and how to delete data in a PostgreSQL database.
  • Foreign Keys
    Brian explains that foreign keys are a reference to one table located within another table, and walks through the various tables that are within the example message boards database and that contain foreign keys.
  • Introducing JOIN
    Brian explains that one of the advantages of working with an SQL database is the option of using JOIN, and demonstrates how JOIN can be useful by joining two tables, the comments and users example tables.
  • SQL JOINS
    Brian explores and explains the use of the three most used SQL joins: INNER JOIN, RIGHT JOIN, and LEFT JOIN. The other less popular SQL joins are also briefly discussed in this segment.
  • Group By
    Brian explains that GROUP BY is a command that allows engineers to determine how to group query results by chosen values, and is used in the example of the board messaging database to query the top 10 most commented on message boards. How to use GROUP BY with the SELECT, INNER JOIN and COUNT commands is also discussed in this segment.
  • JSON in PostgreSQL
    Brian explains that PostgreSQL has a JSON datatype, demonstrates how to query JSON data stored in the message boards database, and how to write multi-leveled data query when trying to access data within a JSON file that is included in a PostgreSQL database.
  • Indexes in PostgreSQL
    Brian demonstrates how to use indexes in PostgreSQL to speed up the process of looking for specific data. When using indexes PostgreSQL decides which scan to use to complete the query and create a shortcut or a tree. PostgreSQL can select which scan an index uses, namely a bitmap heap scan node or an index scan.
  • Node.js App with PostgreSQL
    Brian starts to build a similar Node.js application to the one build in the previous section, but uses PostgreSQL instead of MongoDB, demonstrates how add a PostgreSQL query into the server code so that only a specific portion of the database is targeted and accessible.
  • SQL Injection
    Brian explains that SQL injection happens when users can add input that is interpreted by the database as SQL, and can therefore modify the data within a given database. Parameterized queries block SQL injections by not allowing any SQL queries into a user input field.
  • Hasura
    Brian explains that Hasura transforms a database into a GraphQL endpoint, and adds that GraphQL can be combined with Apollo or other interfaces that will link GraphQL to the frontend of a given application.
  • PostgreSQL Ops
    Brian reviews the most common terms of Postgres Ops used by database administrators, and recommends additional resources where students can gain more knowledge on the subject.

Graph

Section Duration: 1 hour, 16 minutes

Key-Value Store

Section Duration: 1 hour, 15 minutes
  • Key-Value Store Databases
    Brian explains that key-value store databases are rarely used as the only database in a company, and are generally used for data that is less important like cache data. This section focuses on Redis, a key-value store database. but in this segment Brian introduces other key-value store databases that could also be used. How to set and get data from a Redis store is also discussed in this segment.
  • Namespaces
    Brian explains how namespaces are used to avoid name collision within a key-value store database, and demonstrates how to use namespaces in a Redis database.
  • Redis Mathematical Commands
    Brian demonstrates how Redis can execute some mathematical commands by increasing, decreasing, adding or subtracting key values that are integers, and shares a few example commands.
  • Redis Command Options
    Brian continues sharing commands that are useful to engineers when using Redis. Writing conditional queries and TTLs are discussed. TTLs or Time to Live set elements of a Redis database to expire. For instance, using TTL with cache determines when the cache will be deleted. The concept of a thundering herd, and how to avoid it, are also discussed in the segment.
  • Redis Data Types
    Brian demonstrates how to use the list data type on Redis, and creates a list of notifications that is linked to a given user. Notifications are an example of data that can be stored using Redis, because the loss of notifications would not be a drastic loss of data within a given application. Hash and set datatypes in Redis are also discussed in this segment.
  • HyperLogLog and Streams
    Brian defines HyperLogLog as a command used to check if an element does not exist in the database, and explains that streams are useful when adding an important amount of data to a source, and needing to subscribe to updates of that data.
  • More Redis Concepts
    Brian explains that Redis has the ability to evaluate Lua, a scripting language, adds that when engineers need Redis to accept entire programs they use Lua, and demonstrates how to use the Lua language in a Redis database. The least recently used data, or LRU, and how Redis has the capacity to delete it from a database is also discussed in this segment.
  • Node.js App with Redis
    Brian builds a page counter Node.js application that uses a Redis database, and uses the promisify function to be able to use promises within the server instead of callbacks. Redis only understands callbacks.
  • Combining PostgreSQL & Redis
    Brian demonstrates how to combine PostgresSQL and Redis by adding caching to a PostgresSQL query that takes a long time to complete. A higher order function cache sends a cached response while the PostgresSQL query is running.
  • Redis Ops
    Brian explains that, similarly to Neo4j, Redis has a primary and a secondary configuration. The preferred terms used are leader and follower.

Wrapping Up

Section Duration: 13 minutes
  • Wrapping Up
    Brian walks through the process of choosing a database for a given application or project, evaluates the tradeoffs of each choice, mentions other courses available on Frontend Masters that tackle backend technology, and thanks the audience. Questions about data migration are also answered in this segment.
I just completed "Complete Intro to Databases" by Brian Holt on Frontend Masters! Great refresher on things I knew, and the new concepts and perspective of how things should be is just invaluable! Good one 🙂
Abu Talha

Abu Talha

rogdex24

The part about Neo4j was entirely new for me. A graph database, it's feels so cool. Examples of searching within JSON objects in Postgres are also good.
Lula Leus

Lula Leus

LulaLeus

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now