 Resources from Tyler Clark in 2021

PostgreSQL is a powerful, open source object-relational database system.

postgres

Data integrity is critical to scaling and working with large datasets. One additional way to keep this integrity is to define custom types like enums. In this lesson we’ll create and assign data to a custom enum type. 


For more info: 

- https://www.postgresql.org/docs/9.2/sql-createtype.html
- https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88856/c14ordb.htm#433046
- https://www.c-sharpcorner.com/article/user-defined-table-types-and-table-valued-parameters/

Create a Custom SQL Enum Type 

Using the query planner within SQL is the equivalent of using the debugger tools in browsers. By using `explain` with options for formatting and actually executing with `analyze`, we can see how our query performs. 

Maybe we need to implement our query differently. Which includes possibly adding an index, using a sub-query instead of another table, or dozens of other options.

In the end, we need to first know how to profile our query and we do that with `explain`. 

Video notes: 
https://explain.depesz.com

Profiling Queries with SQL Explain Analyze

Working with large datasets is hard. What's even more difficult is trying to read through queries that have many subqueries and joins. In this lesson we will create common table expressions (CTEs for short), which act kind-of like temp tables (but not really) that can help break out complex queries into more readable blocks. 


Common Table Expressions in SQL

The where clause does a great job of filtering rows, however when we want to filter out data that is grouped and aggregated, we need to work with the `having` clause. This can be difficult to understand and work with at first, so in this lesson, we will discuss the main differences. 


Filter Grouped and Aggregated Data with SQL Having

Queries can grow in size and become unreadable pretty quickly. Not to mention error-prone if we have to write out the same value multiple times. Just like in almost any other programming language, variables and blocks can be created within SQL. This gives users the ability to write variables at the top of a block and then be referenced through the following transaction.  


Create Variables and Blocks with Do / Declare in SQL

Similar to if / else statements in many programming languages, SQL has the case then statement. Using this statement allows a query to be written dynamically depending on the condition it is checking against.

Conditional Returns with SQL case / then

Transactions are a fundamental concept within all database systems. When a series of actions need to be done at once or not at all, transactions are how we accomplish this. In this lesson, we'll review how actions are performed, saved, and rollbacked within transactions as well. Some keywords used will be: `start transaction`, `begin`, `commit`, `savepoint`, `rollback`

Perform Multiple Steps in One with SQL Transactions

Nulls can be a huge burden if not handled correctly. Conditional logic is tricky and joining tables with null columns requires cognitive recognition. Similar to many popular programming languages, we can conditionally change values within SQL if the returning value is null. 


Create and Safely Handle Nulls in SQL

Most programming languages have some sort of pattern matching and/or regular expressions. SQL is no different! In this lesson we are going to work with `like`, `ilike`, `similar to`, and POSIX to filter out our data. We'll talk about the characters that can be used within our patterns, such as `%`, `_`, `|`, `*`, `+`, `?`, and `()`

Pattern Matching and Regular Expressions in SQL

Imagine instead of filtering all returned rows of data with the WHERE clause (which affects the other selected out columns) you only filtered the data for one aggregate function in the select. The inline filter clause can target only one column, giving you the ability to pull out filtered and unfiltered columns with one select statement. 


Aggregate Inline filters in SQL

Most relational databases today come with built-in support for JSON. You'll find a great range of processing functions and operators needed to handle all CRUD operations. In this lesson, we work with Postgres and walk through creating a table with a JSON column type, how to insert JSON, how to select it out in different formats, and how to use the built-in Postgres JSON functions.  

Work with JSON Data in SQL

JavaScript® (often shortened to JS) is a lightweight, interpreted, object-oriented language with first-class functions, most known as the scripting language for Web pages, but used in many non-browser environments as well such as node.js or Apache CouchDB. It is a prototype-based, multi-paradigm scripting language that is dynamic, and supports object-oriented, imperative, and functional programming styles.

javascript

Within five minutes, this lesson will show you how you can go from a blank template to a completely integrated Gatsby + Auth0 website. We'll use a Gatsby recipe to install our auth0-spa-js npm package, create new pages, and add all the Auth0 boilerplate needed to initialize the Auth0 client within the website. 

We'll then review all of the code added by the gatsby recipe and finish up by configuring the app with our Auth0 account. 



******************************

Link to gatsby recipe: https://gist.github.com/twclark0/0e11c962743f4e62ccd33a92d8b52d23

Authenticating Gatsby Sites with Auth0

Thanks for watching the course! In this video I am going to walk you through some info that is good to be aware of before watching the course. 

Introduction to Advanced SQL for Professional Developers

React is one of the web’s most popular frameworks for building JavaScript applications. 

If you know what you’re doing, React can drastically simplify how you build, use, and maintain code.

Whether you’re a React newbie or you’re ready for advanced techniques, you can level-up with egghead. Explore our in-depth courses, lessons, and community resources to build more powerful applications and crack open your career.

react

Within this 7 minute video we will add authentication to the default next.js template with Auth0. This includes installing the nextjs-auth0 npm package, configuring your Auth0 account, creating API endpoints, adding login / logout buttons, and pulling user data from an authenticated user session (by using the native getServerSideProps function)! 

Authenticating a Next.js site with Auth0

Authentication and authorization doesn't need to be hard. In this lesson, we're going to use the Auth0 React SDK to add authentication to protect a route. Which means that a user has to be logged in in order to access any content on that page. We'll finish the lesson by sending an access token to an Express API in order to retrieve some data. 

Protecting Routes and Calling Secure APIs in React with Auth0

With Auth0's React SDK, integreting an identity provider has never been easier. With a couple changes to your React app, creating an account within Auth0, we can have a login experience ready to go! This lesson will walk through all the steps nesscary such as adding the SDK through npm, setting up your Auth0 account, adding a login/logout button, and using the Auth0 provided hook to access values. 

Adding Login to React with Auth0

JSON Web Tokens are an open, industry standard RFC 7519 method for representing claims securely between two parties.

In simple terms, authorization is the process of verifying what users have access to (after authenticating). This video assumes that you already have authentication set up and are ready to add authorization. 

Authentication is the process of verifying if a user is who they say they are. After a user successfully authenticates, access JWTs are issued back to the application. 

In our case of authorization, we are using the JWT (JSON web token) in our NodeJS / ExpressJS API calls, to verify the user has the correct access to the desired resource. 

Authorization in NodeJS with ExpressJS and JWT

Role-Based Access Control (RBAC) is the idea of grouping permissions together by a role. Users are assigned a role through the Auth0 dashboard the corresponding permissions are placed on the user's access token (JWT) after authenticating. That access token can be extracted by an Auth0 SDK (this video uses JavaScipt/ ReactJS) and sent to a custom server. 

Server Side logic (this video is based on NodeJS and ExpressJS), can then check for specific permissions and then allow or reject certain resources. 

-------------

Starting point- https://github.com/twclark0/roles-scopes-node-auth0/tree/roles-beginning

Ending point- https://github.com/twclark0/roles-scopes-node-auth0/tree/roles-final

Role Based Auth in NodeJS with ExpressJS and Auth0

Universal Login is Auth0's implementation of the login flow, which is the key feature of an Authorization Server. Each time a user needs to prove their identity, your applications redirect to Universal Login and Auth0 will do what is needed to guarantee the user's identity.

This video will walk you through how to customize and brand the Universal Login experience. This includes setting a custom domain, styling the user emails, and add your company logo to the form. 

Customize the Auth0 Universal Login

Most applications require multiple side effects. So in this lesson, we’ll modify our TakeLatest to accept more than one saga, as well as add another saga helper called TakeEvery.  

Run Sagas Concurrently with Saga Helpers

In this lesson we’ll fetch the star wars api and display the response to our UI. Saga effects and generators allow us to synchronously run asynchronous tasks.  

Topics

Instructors

Content Type