Learn Bash, Postgres and Xstream

You've got some practice with using SQL to select, update, and join database tables.

But perhaps you've found yourself with a sneaking suspicion that you could be more efficient with your PostgreSQL.

Does your query seem slow?

A lesson on how to profile queries will point you in the direction of a fix.

Are you performing multi-step operations one at a time that are susceptible to errors?

The "all or nothing" transaction is the cure.

These are just a couple of examples of the techniques, tips, and tricks that Tyler Clark has learned in his years of experience working on large scale production applications.

Other topics in this course include working with CSV files, different techniques for aggregating & filtering, and more!

Watch Tyler's course, and continue your journey toward SQL mastery.

Check out the [**Community Notes for this course.**](https://github.com/eggheadio-projects/advanced-sql-for-professional-developers)

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

postgres

Advanced SQL for Professional Developers

🚀 In this collection, I will be going over some command line tools I use on a daily basis that make my life a lot easier.

[First up is fzf](https://github.com/junegunn/fzf).

> fzf is a general-purpose command-line fuzzy finder.

Bash is a Unix shell and command language written by Brian Fox for the GNU Project as a free software replacement for the Bourne shell. Bash is a command processor that typically runs in a text window, where the user types commands that cause actions. Bash can also read and execute commands from a file, called a shell script.

bash

Upgrade Your Terminal and Make It a Joy to Work with

Extracting meaningful data from strings is a crucial skill when working with bash, and bash gives us powerful tools to do so: `head`, `tail`, `cut`, `sed`, and `awk`. But these tools can be hard to understand without spending time to learn them, so many developers fall into a rut of blindly copying and pasting commands. No more! With this mini-course, you'll understand the basic ins and outs of these commands in under 15 minutes. 


Manipulating strings in bash

SSH provides a secure way for your to [connect to and authenticate with GitHub's servers](https://help.github.com/en/github/authenticating-to-github/about-ssh). While HTTPS connections use usernames and passwords as credentials, SSH instead uses public and private key pairs. These can be generated on your system using a CLI like [`ssh-keygen`](https://help.github.com/en/github/authenticating-to-github/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent) and then associated with your GitHub account using the public key.

In some cases, you may need to connect to more than one GitHub account from the same system. For example, you may have [separate accounts for personal and professional projects](https://ryanharris.dev/managing-ssh-keys-for-github/), which can be difficult to manage manually. Using `ssh-agent` and a SSH configuration file, you can alleviate this pain point by automatically determining which key to use based on your GitHub repository's `git` URL.

Secure GitHub connections with SSH

“Setting up remote server authentication” will never be on your job description. It’s not one of those desirable responsibilities that convinces talented people to move across the country and pursue the job of their dreams.

But at that dream job, you might be asked to set up an SSH key. You’d better be ready.

In this course, we will learn the fundamentals of creating SSH keys and configuring them for connectivity to a remote server. We’ll figure out how to:

- tunnel SSH connections for easier local debugging
- set up configuration files to simplify the aspect of connecting to remote servers
- use security features like blocking incoming connections

Being prepared is key. Learn the skills now!

SSH for Remote Server Authentication

Bash makes automation easy. Just a few lines of code can take care of repetitive tasks for you and your teammates. 

In this course, we’ll dive deep into the automation potential of bash. You’ll learn to leverage common hooks that open up interesting automation possibilities, like git hooks, adding functions and aliases in your bash_profile, and running scheduled jobs in launchd.

For example, we’ll write a git hook that checks package.json for changes, and if there are changes, the hook runs npm install for you automatically. 

Along the way, you’ll also learn:

- more advanced bash commands like exec
- syntax such as redirecting stdout and stderr to the same destination
- timesaving tricks like brace expansions

Automation is all about saving you time — and at only 52 minutes, this course is well worth yours. Enjoy!

Advanced Bash Automation for Web Developers

Most applications rely on databases to store data. Understanding how to create a database and communicate with it unlocks a critical power in your web development career. 

In this course, Tyler presents all the building blocks of creating and interacting with a PostgreSQL database. From creating a brand new table, up through organizing and aggregating data across multiple tables, you will learn the fundamental skills of SQL — and you’ll be prepared to apply them right away.

After completing this course, you’ll be ready to tackle more challenging SQL topics like proper data modeling, normalizing data, and exploring different database types.


Check out the [**Community Notes for this course.**](https://github.com/eggheadio-projects/sql-fundamentals)

SQL Fundamentals



Humans were never meant to repeat joyless tasks in a precise manner, over and over and over. Computers are meant precisely for these types of tasks.

Bash is a ubiquitous technology in web development that gives you the immense power of automating repetitive tasks you find yourself performing throughout the day.

The topics we cover will give you confidence in creating build scripts that run on continuous integration servers or quickly testing and debugging an API with `curl`. We’ll go over:  - Interacting with the file system - Creating, moving, copying, and deleting files and folders - Finding files and text patterns in files - Creating and running scripts - Language syntax like variables and conditionals.

Automate Daily Development Tasks with Bash

You know the basics of Postgres, but most meaningful queries will require you to combine tables in novel ways to ask questions you couldn't ask with a single table alone. Joins and unions come up over and over again in interview questions -- because of how fundamental they are. This course will make you a master of your tables, and give you the power to answer questions you couldn't before. 

Understand Joins and Unions in Postgres

In this course we will show you how to wield the power behind the elusive window function. Which five movies were the highest grossing each year? Who are the highest performers in each department relative to their peers? These questions ask us to partition records into subgroups and answer questions about those groups -- that’s the power of a window function. 

Many programmers will reach for their favorite scripting language to solve problems like these, but window functions allow you to answer them with the speed and efficiency of the Postgres query planner.

Plus, with the power to further slice and dice this derivative information, there’s no limit to the questions you can answer with raw SQL.

If you’re interested in the Postgres client that is used in this course you can [download it here](https://macpostgresclient.com/?source=brett) to check it out and follow along.

Using Postgres Window Functions

It’s amazing how much you can accomplish with a basic knowledge of SQL. In this course we’ll learn how to store lots of information (many millions of things!) and answer nearly any question we might have about that information (on the order of milliseconds!) 

We’ll learn enough to make you say you “know SQL” -- table creation, inserts, selects, updates, deletes, aggregations, indexes, joins, and constraints. Along the way we’ll model real world problems so that you can see just how powerful SQL will make you!

If you’re interested in the Postgres client that is used in this course you can [download it here](https://macpostgresclient.com/?source=brett) to check it out and follow along.

Get Started With PostgreSQL

Lots of if and if else statements are hard to read and tough to maintain in any language, and bash is no exception. In this lesson, we’ll learn how to use `case`, bash’s construct for a switch statement. In bash, `case` actually does pattern matching, which gives some great flexibility. We’ll write a script that will uncompress a variety of different archive file  types (like .tar or .zip), based on their file extension. 

Use case for Complicated Conditional Statements in Bash

Let’s say you want to allow a user to pass a `-v` flag to turn on verbose logging in a script. Manually parsing out options passed to a script is difficult, but in this lesson, we’ll learn about `getopts` which makes it easy. We'll look at the limitations of using getopts (options must be in a format like `-a` or `-ab <value>`) as well as the importance of shifting processed options off of the argument array. 

Create a Bash Script that Accepts Named Options with getopts

In this lesson, we’ll apply our knowledge of `getopts` and create a script that opens a PR on Github, all without leaving your terminal. We’ll use `getopts` to take 3 options: username, password or token, and PR title. We’ll use any input after that as the body of the PR. We’ll validate the input and then use `curl` to POST it to Github. 

Create a Bash Script to Open a Pull Request on Github using getopts

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

Bash is great, but sometimes we need the power of a more robust programming language to accomplish our goals. In this lesson, we’ll look at how we can integrate node.js within bash to send data to and from a node.js script. First we’ll look at how to do quick one-liners at the shell using node's `-p` (print) option. Then we'll write a node.js script that accepts parameters from the shell to check the value of a query string. In node.js, we can use the `process` object to interact with command line arguments and stdio. 

Send and Receive Data from a Node.js Script in Bash using the Process Object

Pipes are one of bash's most powerful features, but sometimes plain bash doesn't give us the tools we need to perform more complicated transformations. In this lesson, we'll see how we can leverage node.js in conjunction with bash. We'll pipe a log file of line-delimited JSON (LDJSON) to a node script that filters it and then writes the result to stdout. We'll use node.js' Transform stream to accomplish this. 

Transform Piped Data from Bash using a Node.js Transform Stream

A Unix command used to search files for the occurrence of a string of characters that matches a specified pattern.

grep

When you have a bunch of text to sift through, standard Unix command line utilities like `grep`, `sed`, `awk`, `cut`, `sort`, and `uniq` can help extract the information you want. For example, with a git log for a project, you might want to group commits by day in order to see which days you're most productive, or find out how many commits you made last year. In this lesson, you'll learn how to combine these commands with the pipe operator to extract this data from git logs.

Analyze Git Logs with the Command Line

`launchd` is a robust scheduled job automation tool on macOS that allows you to schedule a task to be run at recurring times. It's macOS's take on Linux's `cron`. In this lesson, we’ll schedule a simple bash script that empties the Trash to run every Monday morning at 10am. 

First, we’ll create a plist file (a plist file is just XML) with our job configuration. In here, we’ll specify our bash script as the program to run. Then we move plist file to the `~/Library/LaunchAgents` folder. Then we’ll use launchctl to load our configuration file, which will make the job active. We'll manually activate our job to ensure it's working with `launchctl kickstart`. 

Schedule Timed Jobs on macOS with `launchd`

Learn how to use SSH config files to greatly simplify SSH connections to remote hosts, use hostname aliases, and set advanced directives such as Port and IdentityFile settings per host.

Simplify connections with SSH config files

AppleScript is a peculiar language with a COBOL-like desire to read like English, but it's the key to automating UIs on the Mac. We'll use it together with bash to make Terminal open a new window to run any shell command.

Run a Command-line App in a New Terminal Window using Bash and AppleScript

VS Code allows you to install a `code` command into your `PATH` so you can launch code a few different ways. You can open projects, create files in memory, or even pipe the results of other commands into `code`.

Topics

Instructors

Content Type