Databases Overview

[00:00:00]
>> And you did it, you hooked up a WebSocket to your very own domain name. Now, Jem sack.lol is realtime and live. We can all enjoy it for the power of the WebSocket and a little bit of engine X tuning. And if you got stuck somewhere along the way, there were a lot of steps and that it really easy to go a bit of a.

[00:00:20]
The link to the WebSocket example is in the slides it's in GitHub repo, it's all fully fleshed out and tested, it does work. So if you're stuck, just go in there and make a copy or find out where you missed a line there. So, let's move on and let's talk about databases.

[00:00:40]
Why do databases exist? Why don't we just save everything to a file? Database seem like a lot of work for what reason? Why can't we just save everything to a file?
>> They're unorganized.
>> Unorganized, yeah.
>> Inefficient to analyze that data access the data across multiple files.

[00:01:07]
>> Yeah, yeah, it's inefficient, you can't really access the data across multiple files, what else?
>> The structuring of it allows you to go directly to a relevant thing, while if it were just a normal file, you might have to read the entire thing.
>> I like that, yeah, databases give you structure for the file which can be unstructured.

[00:01:33]
Yeah, these are all good answers, we use databases because one a file doesn't scale. How are you gonna have multiple servers writing to one file? Because the file can only exist on one hard disk at a time, so we can save everything to a file today. But if we have two servers, it's not gonna work anymore it breaks down.

[00:01:53]
Build one is about structure, how do you save things to a file? What if my opinion about the structure of saving data to a file is different from your opinion? There's no enforcement there, so the kind of exalting useless. I recall this out earlier when we talked about the modern computing stack, but everything you gonna do.

[00:02:18]
This is my favorite quote from my CS professor is he said, Jem, and he said this right before I graduated, he's like, Jem 90% of the stuff you're gonna be doing in your career is reading and writing if databases, that's it. Anyway, it's funny but you think about what you do in your day-to-day, what do you actually do?

[00:02:34]
At the core of what you're doing, you're reading from some API, probably writing from an API. Those APIs you're reading and writing from maybe another system or service for maybe another database. And that's why in the modern computing stack, the database is always there cuz we're never just reading writing to the hard disk because that doesn't scale very well.

[00:02:51]
In the realm of databases, there's two different kinds, there's relational databases. So relational database is our MySQL, SQL Lite, Postgres, SQL Server, etcetera. There's even a browser database called index.DB. A lot of people don't know that, there's a database built into your browser that you can use, it's kind of a pain but databases are generally kind of a pain, that's why we have libraries on top of them.

[00:03:15]
So relational databases are very structured, they're very strict, they have strong opinions about how and where you write and retrieve data? And this is good, the other kind of database is Non-relational databases. So non-relational databases also have a structure, but it's a lot looser. And these are commonly known as NoSQL databases, which is I don't know, you're still using some sort of query language no matter what you're doing.

[00:03:50]
But the benefits of relational versus non-relational, relational is kind of it's type systems. You can write your JavaScript code in just pure plain JavaScript or you can write with TypeScript. There's pros and cons to both if you use typing's it's gonna be a lot stricter it's gonna throw up a lot more errors and it won't let you do all the things you wanna do and sometimes that's a good thing.

[00:04:13]
That's the same thing about relational databases, they're very strict and you have to do things a certain way. Whereas, non-relational databases is much more free, you can just dump things into them and query them back out and there's pros and cons of both. No, SQL tends to be a little bit faster because there's not a whole structure it has to run through every time you make a query but again, you don't have the structure, so that's a protocol.

[00:04:36]
Today we're gonna be using a relational database, because that's probably the one that trips up people the most when they see the syntax of SQL. And relational databases is made of three different parts, really. They're made up of tables, so there's always tables of data, in this case, it would be called the users table.

[00:05:03]
Those tables have fields. Fields are also known as columns, so think tabular data, it's a column. Those columns have rows or they're also called records here. And you're like, that's great, that's a table, there's some rows, there's some columns, I've seen a table before, Jem, how does this all make sense?

[00:05:24]
Well, when we have multiple tables of data, we have to get them to relate to each other somehow, hence the relational database part. In this case, user ID is known as your primary ID or primary key, every single table has a primary key. The primary key is usually an incremented number or some random hash it doesn't really matter, but it's unique to that row in that table and we can query against it very quickly.

[00:05:55]
Now, if you wanna have a primary key in another table, that's known as the, anybody? Anybody takes SQL here?
>> Secondary key.
>> Close, secondary key, you think that, foreign key. So a primary key in a different database or in different tables, is just called the foreign key and again, we can query off that again.

[00:06:14]
If we see it in our food, our food table, how it relates to the user table is that the food has some sort of ID that says my favorite food is ramen, which it is the type of ramen. I'm a fan of Tonkatsu. It's an katsu, spicy not generally, but that's an entirely different table.

[00:06:37]
It's a table of everybody's favorite foods, but it doesn't tell you anything about who's in it, is it mine? Is it Kyle's? Whose is it? We can use the user ID as the foreign key and we jump back to the user table and that will tell us the name, where we're at the location, the email.

[00:06:57]
So hopefully you're seeing the power of relational databases, where you have a bunch of table of kind of disjointed data but you make it all tie together so that when we write a query and we can write a complex query. We can pull a lot of information together using SQL, which is structured query language.

[00:07:19]
So if I wanna select just all the information about myself from the users table, I would say select star, star is just the wildcard for everything from users tables, where the name equals Jem. Probably the easiest example of SQL will write. Usually, things get a lot more complicated because we want information from multiple tables of data, but we wanna tie them all together.

[00:07:45]
So we use something called a JOIN. There's different types of JOINS, I will not go into JOINS it's a whole thing, there's people here laughing because you've probably dealt with SQL JOINS at some point and it's a pain in the butt unless you do it every single day.

[00:07:58]
But here's an example of JOIN where I was talking about Robin. I want to find all the difference users and the type of food from the food table where the food name is ramen. So we have to LEFT JOIN the two tables together, the food and the user's data.

[00:08:17]
And then once we get the output, we get the data from two different tables, we put it together, and it looks like just one big column of data. So you take this concept of relational database and blow it up even more. You see, you can make some really, really complex queries but at the core, the data you're saving, and reading and writing is actually pretty simple, the tables are very structured.

[00:08:39]
And there is, of course, a, entire course on Frontend Masters on databases. If you wanna go really, rally deep, but I bring up SQL and relational databases, because a lot of Frontend engineers get sucked on SQL because they're like, what does this mean? I don't understand what's going on, and really, if you understand table structures, you get a better sense of how to actually query data.