Complete Intro to Databases
Table of Contents
IntroductionBrian Holt lists the prerequisites necessary to understand this course, shares the course resouces, and gives an overview of past professional experiences.
Installation NotesBrian 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.
TerminologyBrian 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.
NoSQLBrian 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.
MongoDBBrian 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.
MongoDB Logical OperatorsBrian 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.
ProjectionsBrian 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 MongoDBBrian 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 DocumentsBrian 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 MongoDBBrian 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 IndexesBrian 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.
AggregationBrian 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&ABrian 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 MongoDBBrian demonstrates how to create a bare bone Node.js application that searches for data within a MongoDB database.
Connect & Query with Mongoose & ExpressBrian 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 OpsBrian 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.
What are SQL DatabasesBrian 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.
PostgreSQLBrian 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 RecordBrian 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 WhereBrian 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 DeleteBrian 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 KeysBrian 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 JOINBrian 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 JOINSBrian 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 ByBrian 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 PostgreSQLBrian 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 PostgreSQLBrian 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 PostgreSQLBrian 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 InjectionBrian 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.
HasuraBrian 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 OpsBrian 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 DatabasesBrian explains that graph databases are used to represent data that has complex relationships with other data within a given database, and adds that a node is a graph database that represents an entity. Multiple nodes have relationships or edges between each other. Neo4j is the graph database used in this section.
Neo4jBrian introduces Neo4j, a graph database, and demonstrates how to create and connect to a Neo4j database using Docker.
Neo4j: Adding Nodes & RelationshipsBrian explains that Cypher is the query language used for Neo4j, demonstrates how to add data to the Neo4j database, how to create a node, and how to add relationships to a given node. Data about the movie Scott Pilgrim vs. the World is added to the Neo4j database.
Querying with Match & ConstraintBrian demonstrates how to query for relationships between nodes using the MATCH command. The MATCH command is used to find who acted in the movie added to the database in the previous segment. How to use the CONSTRAINT command to filter out movies with the same title is also mentioned here.
Neo4j BrowserBrian demonstrates how to get a visual representation of the various nodes and relationships of a Neo4j database in a browser, and how to add additional data to the database through a browser.
Complex Neo4j QueriesBrian demonstrates how to use the use shortest path feature in the Neo4j database that uses a path algorithm on a database. A path is a data structure that allows engineers to find existing relationships between two nodes in the database. The example Brian gives is the number of degrees of distance between actors and Kevin Bacon.
Recommendation Algorithm ExampleBrian demonstrates how to use another algorithm in an Neo4j database, the recommendation algorithm, which allows engineers to generate movie recommendations by finding which actors partnered the most with a given actor, and recommending their movies.
Indexes in Neo4jBrian explains that like a PostgreSQL or a MongoDB database, some queries in Neo4j could be costly to a given organization, and that the solution is to use indexes. Brian also demonstrates how to use indexes in a Neo4j database.
Node.js App with Neo4jBrian demonstrates how to create a Node.js application and adds a query to the server that fetches data from a Neo4j database.
Neo4j OpsBrian explains how a Neo4j database can run with no cost in production, as long as the team only uses one server.
Key-Value Store DatabasesBrian 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.
NamespacesBrian 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 CommandsBrian 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 OptionsBrian 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 TypesBrian 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 StreamsBrian 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 ConceptsBrian 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 RedisBrian 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 & RedisBrian 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 OpsBrian explains that, similarly to Neo4j, Redis has a primary and a secondary configuration. The preferred terms used are leader and follower.
Wrapping UpBrian 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.