Command Line Terminal

If you are going to be working in any kind of web development job, you're going to need to become familiar with the command line terminal. Many of the programs you will use don't have a graphical interface and are designed to be run on the terminal.

Fortunately you don't need to be an expert, and this tutorial will go through the basic commands you need to know.

How do I use the command line terminal?

How you access the terminal will depend on your system. If you are on Mac or Linux it'll be called "Terminal", on Windows you might choose to use PowerShell which is very similar. PowerShell comes bundled with Windows automatically, you can find it in your start menu.

Or you can download something like Git bash which comes included when you install Git and will give you a unix-style terminal even if you are using Windows.

I'll be using bash for this tutorial. Even if you are using PowerShell on Windows most of the commands will be the same or similar.

When you open your terminal it will look something like this:

Most terminals will show the directory directory you are in on the left. Right now I am in the ~ directory which is an alias for my "home" directory. On Windows it would be something like c:\Users\YOUR_NAME on Linux it might be something like /home/YOUR_NAME.

Let's create a new folder called example-folder.

mkdir example-folder

That will create example-folder in your home directory. Now let's "change directory" into that folder with:

cd example-folder

Protip: use the tab key to help autocomplete when working on the terminal. It's one of the most powerful shorthands you'll be able to use for efficiency (and avoiding spelling errors).

Our terminal directory is now in the folder we just created. Let's create a file!

touch hello.txt

(This one will be different on PowerShell, you'll need to type New-Item hello.txt).

If you navigate to that folder in your graphical interface you'll be able to see it has been created!

Now let's go back to the terminal and take a look at the contents of this folder.

ls

That command means "list" and lists the contents of the directory. You should see your hello.txt file show up.

That's enough of that file, we can delete it with:

rm hello.txt

Run ls again and should be gone.

Let's return to the parent directory.

cd ..

The two dots ".." is a shorthand for "parent directory" and a single dot "." is a shorthand for "this directory".

Finally let's remove that folder we created.

rmdir example-folder

And that's the absolute basics of navigating around on the command line. You're now at least equipped with the ability to navigate between folders which is one of the most common operations you will use.

What else do I need to know about the command line terminal?

Make sure you understand the difference between absolute and relative paths. This is not just for the command line, you'll need to understand the different when writing your code as well. You often need to import "modules" from other files and you need to describe to the editor where to find those files.

Get familiar with one of the command line text editing tools so that you can quickly make changes to text and code files without having to boot up VS Code. I prefer Nano for quick edits but there are lots of options including emacs, vi, and more.

(If your terminal opens files in vi and you can't figure out how to get out, type escape then :q. Quitting vi is one of those rights of initiation for anyone working in a terminal).

You'll want to know how to open up the help pages for any program you are using. Say you just installed git but have no idea how to use it. Depending on your platform it will either be:

man git

OR

git --help

You can replace git with the name of whatever program you are trying to learn about. Usually one of the other will get you what you want. The man command stands for "manual" and is intended to open up the manual.

What are the best free resources for learning the command line terminal?

The Odin Project command line basics

The Art of the Command Line beginner's tutorial

Unix/Linux Command Cheatsheet

Git

What is Git?

What is a Version Control System (VCS)?

Having a good understanding of Git is critically important for working in almost any software job. I'd go so far as to say that if you company doesn't use it (or at least an equivalent version control tool) you may want to consider another position.

It's that important to a healthy software lifecycle. So for that reason I want to make sure I explain it well, because despite its important, it can be very daunting to learn and understand for a beginner who isn't used to thinking this way.

Git is a version control system which is a fancy way of saying that it's a tool for taking snapshots of data at a certain point in time.

If you've ever worked on a file and made a copy of it called Resume.doc and Resume-backup.doc and Resume-FINAL.doc etc then you can imagine why a tool like Git might need to exist.

It works for any kind of file and is not specific to coding or software development, although that's where it's most commonly used.

Software developers and teams use it for two primary purposes:

1. To create a saved state at a certain point in time in case an issue comes up later and changes need to be reverted back to how they used to be, for one file or many files.

2. To allow multiple developers to work on files in a project at the same time, in the same directory and often even the same file. Git keeps track of who is making changes where, and helps you resolve and merge those changes together, ideally automatically without any input from the people making the changes at all.

For example, if you have a big Javascript file called my-code.js with 1000 lines, developer A could make edits to line 200, and developer B could make edits to line 300. Afterward they would perform a Git command called merge and Git would take care of combining the separate changes both devs made on their lines on their individual files into a single file with both changes.

There are a number of ways to use Git, the most common is through the command line terminal but there are many tools out there designed to make it easier to use. See this section for more info.

Let's take a quick look at how you can use Git

How do I use Git?

First you need to install it on your machine. Check out this link for instructions on how to install depending on what operating system you are on.

You will know you are successful when you are able to open a command line terminal and type:

git --version

You should see a response like git version 2.35.1 or whatever version is currently the newest when you install yours. If it says anything like command "git" not found then you need to double check your installation. Sometimes you need to close and re-open your terminal for newly installed programs to be visible.

Now I am going to create a new folder. You can create it anywhere that you like as long as you know how to navigate to it in your terminal. You can make it with the mkdir git-example command or you can just "Create New Folder" with your mouse and call it git-example.

After the folder is created open up your editor (I'll be using VS Code) and go File -> Open Folder and find that folder you just created.

You can see I have my VS Code terminal open there, it will automatically navigate you to the folder you have open. To open your terminal you can use ctrl + ~ or simply use the menu with View -> Terminal.

Just before we get started you'll want to run the following command:

git config --global init.defaultBranch main

If you are on a newer version of git the default branch will already be named main, but if you are on an older version it will be called master. This command just makes sure that your defaults match what we will be using for this tutorial.

The first command you will always need to use if you are creating a new "repository" (the name for a collection of folders and files tracked by the git program). The command is:

git init

You will receive a message that says Initialized empty Git repository in... with your folder path. That means git-example directory is now a git repository. Any files or folders created within that directory can be tracked as part of your "project".

Note that if you join an existing project/team, you will not need to initialize Git. When you clone a copy of their work it will already have been initialized for you, we only need to git init when we are creating a new project ourselves from scratch.

Now let's create a new file called sample.txt. To create a new file in VS Code simply right click on the left side under git-example and New File. Inside it I'm going to add some text, quite literally the phrase "Some example text".

Now I will introduce you to a couple of the first git commands you should learn, git add and git commit.

• git add: Will tell git to add new files to "track" in your project. Presuming that you want to keep track of every file in the directory (this is very common) then you can simply use the . character which in most terminals is a shorthand for "this directory". So git add . means "add all files in this directory to my git project.

• git commit: Will tell git that you want to save a snapshot of all tracked files at this current moment. Most commonly you will also include the -m flag which means "message". A commit message is a description of the changes you have made since the last time you committed.

(Worth noting that you can create a file called .gitignore in the root directory of your project and put the names of any files and directories you don't want to include in your repository, so even if you run the add . command those files will not get added. Useful if you have things like environment files that contain secrets and passwords you don't want to upload publicly with the rest of your code)

With that new knowledge in mind our commands will be as follows:

git add .git commit -m 'initial commit'

You might be able to come up with a better message than that, though it's a common one for the first commit on a project. The common standard for commit message is to write your message as if it follows the start of the sentence "When applied, this commit will...".

So an example would be "change the primary button colour to blue". So that is the format you should use rather than something like "I changed the primary button to blue". Consistent subject and tense makes commit messages in large projects much easier to read and understand.

(If you want to get really deep down the rabbit hole, you can encourage your team to adopt something like conventional commits which applies strict rules about how commit messages are written.)

So now that you have created your first commit, let's learn how to create another one, and navigate back and forth through time and change the state of our project.

Add another line to your sample.txt file. Something like "Some more text" on the next line below the first line. Doesn't matter what the text says, we just want to update the file.

Now run these commands again:

git add .git commit -m 'add a new line of text'

Technically the git add in this scenario does nothing since we didn't create any new files, but it's not a bad habit to get into as normally creating new files in projects is common, and you want to make sure you add them unless they are being explicitly excluded.

Now that you have two commits, and two "saved" states of your project, you can go back to one if you need to. Let's say that you just want to "see" how things were before, but not actually lose your newest changes.

Start with this command:

git log

If will show you a list of all your commits, with their messages, and a unique string character hash (purple arrow points to in image below) that you can use as an identifier. Note that yours will not be the same as mine, they are randomly generated.

To go back to that state simply type:

git checkout YOUR_COMMIT_HASH_HERE

So in my case it would be git checkout c849819c6f45ef72429abc36b1ee3891c3ede779 but that value will be different for you.

This brings us back to the first commit we did, notice that the content of your sample.txt doesn't have the second line you added. It's not gone, we are simply looking at a different state of the project in the timeline.

At any point you wish to return to the most up to date state just use:

git checkout main

In this context main is the name of the branch you are on. If you have been following this tutorial you will have set your default branch to main at the start. You can always double check what branch you are currently on by typing git status.

Branching is a critically important part of git. The idea of branching is that you create a separate place to track changes, outside of the main branch that acts as the central source of truth for your project. For example, whenever you release a new version of your website or app, that release is almost always done off main.

If you are working on a new feature on a separate branch, those changes won't be released when the main branch is released. Only when you merge the changes from your branch into main will those changes actually be included in the main product.

I'm not going to get into branching for this tutorial, but make sure that you are at least familiar with it before you apply for your first role. You can learn more about git branching here.

So at this stage, if you have learned the basics of add, commit, log and checkout (to switch between branches).

That covers most of the fundamentals you need to know to work with git locally as a solo developer.

But as you've probably heard, git is also used to track changes in a project between developers working on different machines. How does it do that? Well that's where additional tools like Github come into play.

What is the difference between Git and Github?

A common source of confusion for new developers is the difference between git and Github.

Git is a free open source program that runs on your computer and helps keep track of file changes within a project.

Github is a website owned and operated my Microsoft that hosts git repositories online so that you can have access to your files wherever you go and share them with other developers. Think of it like Dropbox with a bunch of extra features specifically designed around working with git repositories.

Github will always have a "copy" of your Git repository and whenever you like you can use git commands like push and pull to synchronize changes you've done on your computer with the ones hosted on Github.

If you are working by yourself it can simply act as a backup system, and help you track issues and to-do's within your project.

If you are working with other developers then it will act as the central location where all changes made by all team members are pushed to and synchronized.

Let's show an example of working with a repository on Github. I'm going to create one locally and show you how to move it up to Github from the command line so that other developers can download or even contribute code to it.

First I am going to make a new project from the command line:

mkdir example-git-projectcd example-git-projectgit init

Then I am going to copy the files I created for the tutorials we did on HTML, CSS and Javascript.

If you did not complete those tutorials, don't worry, you don't actually have to understand HTMl, CSS or Javascript for this. We're not even going to run that code, we are simply trying to save a copy of it remotely where other devs can view it.

You can copy the three files directly from the Basic Website Example section.

(Don't worry about what they do, that's not the focus of this tutorial, we are simply trying to show how to push those files to your remote repository.)

After adding those files, run these commands to add them and make your first commit for this project:

git add .git commit -m 'first commit'

When complete your example-git-project directory should look like this:

Now we are ready to move this project up to Github.

If you don't already have a Github account, create one now.

Once you have an account and are logged in, navigate to "Your repositories" and then look for the green "New" button to create a new repository.

Create a project following this basic template, give it a name and a description. The name does not have to match the same folder name you used when you created your local repository, but it helps if you want to avoid confusion between the two.

Make sure it is set to public if you want other people to be able to view and contribute to the code.

Once it is created you will get a screen that looks like the below. It will include all the commands that you need, the only difference will be that they will use your Github username rather than mine that you see in the screenshot:

All the commands you need are now visible on the screen for you. We would like to push the repository we created on our computer to this remote repository.

The first step is to tell git on our machine that this repository we created is the remote version of our repository. To do that we run the following command:

git remote add origin YOUR_REPOSITORY_URL

This command tells git to create a new remote with the name origin. The URL that you use will depend on how you have Github setup. You can use either an HTTPS or an SSH URL. SSH is normally preferred but you will likely want to go through the What is SSH section first to set it up if you are not familiar.

(The long and short of it is that HTTPS will require you to enter your username and password every time you interact with the Github repository. Setting up SSH means you don't need to do that because the auth key will exist on your machine and be provided automatically.)

Once you have added the remote repository you can confirm it is correct with this command:

git remote get-url origin

You can see the highlighted text in the above example that the origin is correct for the repository I just created.

With that complete you are ready to upload your code. To do that we use the git push command. Push says to "push the current state of my local repository to a remote one."

The first time you do this to a new repository you have to state what remote branch you want to push your current local branch to. In almost all cases this is simply a remote branch with the same name. The command looks like this:

git push -u origin main

Where -u stands for upstream. In this example we are on the main branch, but if you were working for another branch called cool-feature for example, the command would instead be git push -u origin cool-feature.

You only need to do this once, from now on just typing git push will default to pushing to the same upstream branch unless you change it.

Once this push command has been run you should see some upload status messages in your command line, and when you next refresh your Github repository URL it will look like this:

Seeing this indicates you have successfully made your first Github push. Congratulations!

You can now share your Github URL with other people (or even yourself on other machines) and they can instantly get a copy of this project.

Feel free to try it yourself. CLick the green "code" button and then copy the repository URL from there. Create a totally branch new directory anywhere on your machine and run this command:

git clone YOUR_GITHUB_URL

Where YOUR_GITHUB_URL is the URL of your repository. In my case it's git clone [email protected]:alexeagleson/example-git-project.git.

When the command finishes you will have an exact copy of your repo with all the files you added and committed in this new directory elsewhere on your machine. You can make changes and commit them to update the remote repo with the git add, git commit and git push commands same as always.

This applies to other developers on other machines as well!

There's certainly more to learn, but just having these basics down will position you very strongly for using git effectively with any large project at your company.

What else do I need to know about Git?

Important topics you should make sure to learn about include:

Branching

Working With Remote Repositories like Github for example

Merging

What are the best free resources for learning Git?

The Git Book

The Odin Project curriculum on Git basics

Learn Git Branching is a little interactive game that is great for teaching you more about the concept of branching

Oh my Git! an open-source game designed to help people learn git.

The Github documentation will teach you how to use git and Github at the same time.

APIs

What is an API?

API stands for application programming interface and (in context of web development) it has become basically a catch all term for any system that your code communicates with outside of the code.

The reason that we need an API is because we never want to gives users direct access to your data. Imagine you had a social network that displayed things like name, age, etc about your users.

If you let browsers request that raw data directly, they could also choose to display things like password, or credit card numbers, or any other sensitive data that your app might have stored for one reason or another.

Those are extreme examples but the fact is that even if something isn't critically sensitive, most of the time we want to protect our data and only share it with those people who should have access to it.

For that reason we create APIs. I will allow my web server direct access to all my data, and then the app that is running in the browser may "request" some of that data. My web server will decide if the app / user requesting it should have access to that data, and return it if so, or return an error if not.

A front-end developer needs to know how to read from an existing API. That might mean one developed by back-end developers on their team at their company, or even publicly available ones on the internet.

There are many public APIs out there you can start working with when building an app. Some popular ones include the open weather API, the PokeAPI, and the cocktail API.

These services allow you to query data and display it in your app. Regardless of what type you are working with you usually need to authenticate yourself to use them due to the fact that internet bandwidth is not free, so every time you query for data there is a cost associated with that being paid by whoever is hosting the API. (Most of them do offer free tiers for low traffic apps though.)

If you are a back-end developer you will be responsible for creating the APIs. This means you will write the code for them in a language like Javascript, or other common back-end languages like C#, Java, Go, Python, or Rust.

How do I create an API?

You can create an API very easily with just Javascript and NodeJS. Make sure you have read those sections and are familiar with those tools first.

Open up VS Code and start a new blank project. Create a single file called my-api.js with the following contents:

my-api.js

const http = require("http");const host = "localhost";const port = 8000;const requestHandler = function (req, res) {  res.setHeader("Content-Type", "application/json");  res.writeHead(200);  const weatherData = { toronto: { temp: 30, humidityPercent: 80 } };  const weatherDataString = JSON.stringify(weatherData);  res.end(weatherDataString);};const server = http.createServer(requestHandler);server.listen(port, host, () => {  console.log(`Listening at http://${host}:${port}`);});

The first line loads the built-in HTTP module in Node.js.

Next we define variables to set the host and post that our server will run on.

The requestHandler is a function which is designed to take two arguments, variables that represent the request and response of an HTTP message. The values in these variables are populated by the Node HTTP module whenever requests are made to your server.

Inside the function we call built in Node HTTP methods like setHeader and writeHead to begin crafting the response and HTTP status codes.

If you do not understand these concepts please take the time to read up on web servers.

Next we save some imaginary API data into the weatherData variable. In a real API this data was likely fetched from a database which itself was populated by data from some kind of hardware device like a digital thermometer. Web servers are not an appropriate place to store data since all data is lost when they are rebooted or shut down, hence why we need databases.

For our simple example though we are just creating some dummy data as an example. This data represents an imaginary weather server response from someone who has made a request to our server for the current weather in Toronto, Ontario, Canada.

The weatherData object is then converted into a string so that it can be sent over the network. weatherDataString is converted into JSON, a textual string representation based on Javascript objects (hence the name).

Since it's just text, it does not technically require Javascript to use, any language can output data in JSON format. You can convert a Javascript object to a JSON string with the JSON.stringify() function.

Finally the end method is what we call when we are done settings the headers and ready to return the data to the device which is making the request to our API.

Let's give it a try!

Save your my-api.js file and open the command line terminal in the same directory as your .js file and run this command:

node my-api.js

You will see the output Listening at http://localhost:8000

Open up your browser and go to [http://localhost:8000] to get a response that looks like:

Depending on your browser it may look slightly different since different ones format JSON responses in different ways, but either way, there you have it!

Remember that the primary purpose of most APIs is to send raw data so websites and web apps can be built using that data. Rather than actually displaying it raw like we do here your app would realistically query that URL using Javascript with something like the fetch method and then display it on the screen wrapped in some pretty HTML and CSS.

What are the best free resources for learning APIs?

If you simply want to learn about the concept of APIs, why they are needed and how they are designed here's a fantastic Stack Overflow response and discussion on the topic that covers all the fundamentals.

For more information of building your own API (at least from a Javascript perspective) check out the resources listed in What are the best free resources for learning Node.js?

Libraries and Package Management

What is a library?

What is a package?

In the context of web development, a library (also sometimes called a package, or even a framework in different contexts) is typically used to refer to "someone else's Javascript code" that you download from the internet and add to your code.

Libraries are a necessary and fundamental part of building websites and web applications. Without them it would require us to re-write the same basic code over and over again each time we start a new project.

To give a very simple example:

<html>  <head>    <script src="https://cdn.jsdelivr.net/npm/[email protected]/lodash.min.js"></script>  </head>  <body>    <p id="p-tag">hello world</p>    <script>      var pTag = document.getElementById("p-tag");      pTag.innerText = _.startCase(pTag.innerText);    </script>  </body></html>

In the above example we are loading the popular lodash Javascript library from a CDN inside the <script> tag.

That loads all the Javascript code from that library. Lodash (hence the name) stores its functions on a variable whose name is just the underscore character.

We then use Javascript to grab the <p> element by its ID and replace its lowercase "hello world" text with the return value of Lodash's startCase function. The startCase function transforms any string so the first letter of each world is capitalized.

There is no built-in Javascript function which does this (only all lowercase or uppercase) so either we write one ourselves, or simply add this library to do it for us.

Of course there are many other considerations you need to be aware of, this whole library for example has a lot more code in it than just the startCase function so it might slow your websites average load time down.

Everything in web development is a trade-off so you and your team always need to consider the pros and cons when making a decision.

What is a package manager?

What are NPM and Yarn?

NPM is something called a "package manager" and it's a tool that is automatically installed on your system when you install Node.js.

Make sure you don't confuse NPM the tool (which you run on your command like like npm install PACKAGE_NAME) and NPM the online repository that stores those packages which you can explore at https://www.npmjs.com/. They both have the same name, but can mean one or the other depending on context you are talking in.

Yarn is an alternative to NPM you can install on your system which works very similarly. For a beginner you will not notice any difference between Yarn and NPM, so I would only recommend you try it if you are curious, or if your project/company is using it.

You can create a branch new NPM project in an folder with this command:

npm init

After the project has been initialized you will see a package.json file which describes all the libraries and packages your project has access to. You can add a new one anytime from the command line like so:

npm install PACKAGE_NAME

Where PACKAGE_NAME is the name of the package listed on https://www.npmjs.com/.

You would learn what functions are available to use from the library in the example above) typically by reading the library's documentation.

That documentation may exist right on the NPM page, or it might be on the Github source code for the project, or most commonly at the project's official website.

Here's an example of the API documentation for the jQuery library.

Let's take a look at a practical example using the lodash package.

Create a new folder in the terminal, navigate to it and run the following commands:

npm initnpm install lodash

Remember you can replace lodash with any other library you find on the NPM registry. That is the source for Javascript packages, and after running the npm install command you will be able to use them in your code as follows if you create a fill called script.js:

script.js

const _ = require("lodash");console.log(_.startCase("hello world"));

The above will print the start-case'd "Hello World" to the console when you run node script.js.

Remember that libraries available on NPM are public and free to use, so don't hesitate to play and experiment!

Databases

What is a database?

In the context of web development, a database is simply the location where your website or web app will store its data. Basically a fancy hard drive that is accessible over the internet.

The main difference between and standard hard drive and a database is that a database assumes you will be accessing data constantly and will be optimized to provide ways to search for that data extremely quickly.

A database uses a concept called indexes which are exactly like you would expect if you're familiar with the index of a large book, a list of terms or keys that it creates to identify the exact location of information based on certain criteria (an index on text data might be alphabetical for example).

Databases used on the web are usually classified into one of two categories: relational and non-relational (also often called SQL and NoSQL).

(SQL stands for "structured query language" and is usually pronounced as "sequel", but some people prefer S-Q-L.)

Some examples of popular relational databases are MySQL, PostgreSQL, and Microsoft SQL Server. An example of a popular NoSQL database is MongoDB.

If you are just learning then deciding which one to use and learn makes little difference, either option will introduce you at least to the fundamentals of working with databases including indexes, schemas, creates, reads, updates, and deletes.

Typically the responsibility for working with databases will fall on the back-end developer. The front-end developer will have little to no direct exposure to the database.

The backend developer will be responsible for creating an API (see also What is an API?), which is basically code that reads information from the database and transforms it into a shape that the front-end client can use.

If your back-end is written in Javascript, then the developer would write Javascript code that uses an NPM package that gives them access to the database and write SQL queries that are sent to Javascript functions and then onward to the database.

Let's say that you want to build a page that shows current in-stock levels for a website that sells plants.

In your database you have a table which contains both the name of each plant your store carries and the number that are in stock.

(If you've used Microsoft Excel or Google Sheets before then you can think of a database as very similar, just significantly more powerful and able to store and process orders of magnitude more data.)

(Typically in a real relational database the items and stock levels would be in separate tables and marked with an ID number, a process called normalization, but we are keeping it simple for now)

A SQL query to get plant name and inventory information for plants that are currently in-stock might look something like:

SELECT (name, num_in_stock) FROM plants WHERE num_in_stock > 0;

Where plants is the name of the table and name and num_in_stock are the names of the columns that contain the data inventory data.

Once this data is returned the back-end developer writes more code to provide an API endpoint that the website can send requests to in order to get that plant inventory data and display it on the page.

An endpoint is simply an URL identifier designed to provide specific data, for example https://www.myplantstore.com/inventory.

It's important to understand that the website itself cannot query the database directly, because to do so would require you to include the login and password of your database on the website, which would then be accessible to everyone. It wouldn't long for someone (or some bot) to gain access and control of your database.

So one of the reasons we create and use an API is to provide a very specific interface via URL that only allows data to be requested from certain trusted sources, and is only programmed to return specific types of data.

Maybe we also store customer names and addresses in the database, but as long as we don't write code on our back-end to query that data and provide an interface for it to be accessed, then it will be safe as long as nobody (except those with permission) have direct access to the database.

Ultimately all you need to remember as a front-end developer is that a database is a place to permanently store data for your web app, and the back-end developer is responsible for providing you (the front-end dev) access to specific parts of it that you need in order to build your app.

How do I use a database?

This question can be interpreted in a few different ways. Someone working as a DBA (database administrator) would interpret this question as How do I install and setup a database? likely including follow up questions like what kind of data will I need to store?, should it be SQL or NoSQL?, how much data and traffic am I expecting? etc etc etc.

As a back-end developer you might may or may not need to include yourself in these discussions. Usually it depends on the size of the company. In small companies back-end developers often find themselves playing the role of the DBA as well as developing the API. At large companies that is less likely to be the case.

The way we are going to answer this question is specifically on the software development side since that is the focus of this tutorial. In most cases a database will already be set up for you, and your job will be to develop an API that can query it, maybe process the data a bit, and then return it to the user (usually front-end dev) in a shape that aligns with their needs to display it on a page to the user.

For our example we will be using a fantastic and user friendly SQL database which is totally free called SQLite. All the basic queries you would use for managing data in SQLite like SELECT, INSERT, CREATE TABLE etc are the same as you would use when working in all the industry standard SQL databases like MySQL, PostgreSQL etc.

SQLite has bindings for Node.js, which is a fancy way of saying your can just install it as an NPM package and get up and running in seconds.

We can create our SQLite database in one of two ways: either create it right in the code when the program loads (called doing it "in memory") or load up the database from a file on your computer.

Loading from a file mimics a real world use case a little closer, but requires some more configuration so we'll be doing it in memory in this example. If you want to learn how to load the database from a file here's a tutorial you can check out.

As we have learned already in the what is an API? section, an API typically involves a web server that acts as a middleman between a website/app and a database. So we are going to extend the example we showed in the API section to now include querying from a database.

We'll begin by navigating on our command line to the folder containing the my-api.js file from the How do I create an API? example and run the following command:

npm init

Go through the question prompts with all the defaults then run:

npm install sqlite3

(If you aren't sure what we are doing here you may need to revisit the What is a package manager? section)

Next, update the my-api.js as follows:

my-api.js

// Database setup// 1const sqlite3 = require("sqlite3").verbose();const db = new sqlite3.Database(":memory:");// 2db.serialize(() => {  // 3  db.run("CREATE TABLE weather (city TEXT, temp INTEGER)");  // 4  const stmt = db.prepare("INSERT INTO weather VALUES (?, ?)");  stmt.run("toronto", 30);  stmt.run("vancouver", 50);  stmt.finalize();});// Web server configconst http = require("http");const host = "localhost";const port = 8000;const requestHandler = function (req, res) {  res.setHeader("Content-Type", "application/json");  res.writeHead(200);  // 5  let requestedCity = "";  switch (req.url) {    case "/toronto":      requestedCity = "toronto";      break;    case "/vancouver":      requestedCity = "vancouver";      break;  }  // 6  db.get(    "SELECT * from weather WHERE city = ?",    // 7    [requestedCity],    (err, row) => {      // 8      const weatherDataString = JSON.stringify(row);      res.end(weatherDataString);    }  );};// Web server startconst server = http.createServer(requestHandler);server.listen(port, host, () => {  console.log(`Listening at http://${host}:${port}`);});

This is getting a bit more complex, so I'll added a few numbered comments I'll refer to below explaining what is going on here. I do not include any explanations for the web server code since it was covered in the APIs section already.

1. Load the sqlite3 package that we installed from NPM. What this literally means "look at the package.json file in the node_moudles/sqlite3 directory and see if there is a field called "main" which tells me the location of a Javascript file to start loading and running code from. The next line creates a new SQLite database based on the classes defined in that JS file, and :memory: tells it we will be creating the DB from memory and not from a file.
2. serialize is an SQLite function that means "run all the commands inside this function one at a time making sure each one finishes before the next one starts".
3. Standard SQL syntax for creating a table.
4. Prepare an INSERT statement to add data into our database. We prepare the statement first since the statement itself doesn't change, only the values we are inserting (represented by the ? character). Once this is finalized and run we will have two rows in our database, one for each run function called, with temps for Toronto and Vancouver.
5. We jump ahead to the point after our web server has been initialized. We introduce one new concept to our server itself and that's routing. I wanted the user to be able to define which city they want the weather for, rather than getting every single city every time, which is a waste of resources. req.url will have the route portion of the request, so if the URL is http://localhost:8000/toronto then req.url will be /toronto. We then save that city data into a variable called requestedCity.
6. Next we invoke the get function on our SQLite database and run a basic SELECT SQL query for the data. The ? in the statement is replaced with the value in our variable which presumably will be either toronto or vancouver (or fail if the user requests anything else). It's important that we use the ? syntax to insert the variables that came from the user. Remember that all user provided data must be considered potentially malicious, so using prepared statements and the ? substitution instead of directly concatenating the value onto the strings to help project against SQL injection attacks.
7. This array is where the variable values are taken from to replace the ? characters in the strings. There should be exactly as many values in this array as there are ? characters in your statement.
8. The query result is returned inside of this variable we've called row because it represents a row in your database weather table. If you're not familiar with this syntax here (the function as an argument that takes row and err as arguments) it's called a callback function and it's one of the more challenging topics to learn as a new Javascript dev, so don't worry if you don't get it right away. This row is returned as an object so we can JSON.stringify() it and return it as the payload for our HTTP request.

Let's give it a try! Boot up your web server with:

node my-api.js

Visit http://localhost:8000/toronto in your browser to GET the weather from Toronto that is saved in your database! Try changing the route to /vancouver and see what happens as well!

This example here provides you with a really solid introductory example of what a "day in the life" is like of a backend developer. You'll likely be writing SQL statements, creating and updating tables in databases, writing code to query tables in the database and returning that data to users by creating "endpoints" using routes that follow a specific set of rules you control for how data is accessed and who is allowed to access it through authentication and authorization.

What are the best free resources for learning databases?

SQLite Tutorial for Node.js to keep going further with the Node.js based exmaple of working with SQL beyond what we've done so far.

MongoDB Univeristy for those devs going the NoSQL route, MongoDB is one of the most popular databases in that space (particularly in the Node and Javascript world) and one you are likely to encounter at some point in your career.

Khan Academy's Introduction to SQL includes videos and tutorials on everything SQL

CS 186 Berkeley an amazing free course that covers pretty much everything you need to know about databases as a back-end developer

The Red Book the classic book on database theory available to read free online. Although it's extremely comprehensive, it's also fairly academic and probably more than the majority of back-end developers will need. Recommended when you have all the basics down and are ready to dive deeper into the fundamentals.