All right guys. 1
So we're going to get started learning about databases and how to actually persist or user data. 2
Now here's the problem. 3
Previously in the last challenge when we built our blog website, you would have noticed that every single 4
time that you've restarted your server 5
so for example at the moment, I'm using node app.js to run my app.js file and my server is 6
running on port 3000. 7
But if I use Control + C to stop running and then I rerun my app now, if I head back to my blog the post 8
that I added previously will now be removed as soon as I hit refresh. 9
And the reason behind this is because inside app.js, we're using an array to store all of that post 10
data. 11
And we don't have any way of persistently storing the data that the user generates 12
for example new blog posts or new comments or new users sign ups. 13
Now it's not just our website that's generating data. 14
If you take a look at internetlivestats.com, you can see just how much data is being generated as 15
we speak. 16
If you look at the total number of Google searches today not even you know of all time, you can see how 17
quickly that number moves and how many emails are sent or how many blog posts were written today. 18
It's an absolutely huge amount of data that's being generated all across the Internet and they all need 19
to be saved somewhere. 20
That's why we need to know about databases. 21
Once you realize the need for databases, the next step is actually choosing one. 22
And if you look on the Internet this is actually not an easy task because there are so many databases 23
out there. Even if you narrow it down to the databases that work well with Node.js, you still left 24
with a lot of choice. And very frequently I'll come across blog posts have a title that's something like this: 25
Cassandra vs MongoDB versus CouchDB vs... and it's just crazy the amount of choice that you have. 26
But there's a reason for that because depending on the type of data you're looking to store and the 27
structure of your data, you might favor one of these databases over another. But no matter what choice 28
you make 29
the main difference between databases is whether if they are sequel based or if they are no 30
SQL based. Now 31
SQL or sequel stands for Structured Query Language and it's a really old school piece of technology 32
that businesses have been using for many many years. Now 33
NoSQL 34
stands for Not Only Structured Query Language so they can actually be any format as long as it's 35
not SQL although most of them follow a very similar structure. 36
Those are the two big families of databases that you get to choose from. 37
And we're going to talk about the differences between each and I'm going to show you how to work with 38
both of these types of databases. 39
If you do a quick google, you will find that there are so many different types of databases that are 40
categorized as SQUL databases or noSQL. 41
But the top four most popular databases at least when we're working with Node.js, for the sequel family 42
that's MySQL and Postgres. And for noSQL 43
it's going to be MongoDB and redis. Now some of these databases you might have already heard of but 44
a lot of people are confused as to when to use which and what are the similarities and differences and 45
how do you choose anyways. 46
So let's talk about what are the differences between SQL and NOSQL. 47
The first difference is in their structure. So let's say that you're starting a new business and 48
you've decided to start selling stationery. Now because you're shipping a lot of your product to your 49
customers 50
you need to keep a record of their names and addresses. 51
So you create a database for that. 52
And if you used to excel and creating tables then you'll find that a sequel database will serve you 53
quite well. 54
A sequel database will group your data into tables. 55
So this is how our customer data would look if we chose a sequel database such as mySQL or 56
Postgres. But sometimes you might find that sequel databases can be extremely inflexible. 57
For example let's say that in your shop you have me as a customer and I'm one of those annoying customers 58
who don't really like having mail sent to my address. 59
So I say to you, 'You know what? 60
Why don't you deliver to my address 61
but for all other communications I want you to just email me? Because we are after all in the 21st 62
century right? 63
If you had a sequel table then you have a bit of a problem because that doesn't really fit with your 64
table. 65
You don't have a pre-specified email column to fit that data 66
and none of your existing data have an email entry. 67
And what if you had a customer who didn't want to give you any information at all? 68
They didn't want to give you their address or their email and they'd rather protect their privacy. 69
Well then you kind of have a gap in your table and it ends up looking very very irregular and that doesn't 70
sit very well with sequel databases. 71
So if you chose a sequel database, what it will try to do is it will try to square off the table. So you 72
can add a new column called email 73
but for all the places where there's missing information it will automatically insert a null. 74
And as we learned from previous programming lessons null can be very dangerous especially if you're 75
not expecting something to be null or not have any data. 76
For example if you decide to send out your marketing emails, a lot of those emails are going to be 77
going to an email address that's called null right? But if you had a NoSQL based database such as 78
MongoDB, then this wouldn't be a problem at all. 79
If you were working with Mongo the same data would be represented as JSON objects. 80
So you would have key value pairs such as first name, Last name, address 81
and if in one of those documents you had an extra key value pair such as email then it doesn't affect 82
any of the other data records. And none of these records or documents actually have to be the same shape 83
or follow the same structure. 84
If you're old enough to remember that ad with a PC Guy and Mac guy, it's kind of the same with SQL and 85
NoSQL. NoSQL databases tend to be flashier, newer with modern syntax and modern methods 86
but SQL database is old and reliable and it likes structure. 87
Everybody knows that guy who they work with in the office who really likes structure and for everybody 88
to follow rules and to specify a structure ahead of time and just stick to it. 89
But with NoSQL and especially MongoDB you're more flexible. You're able to adapt and maneuver around 90
the situation as and when things come up. 91
So it's really helpful for startups where your data structure is not predefined. 92
You know one day you could be selling toasters and tomorrow you could be pivoting to giving massages. 93
Then a NoSQL database can remain flexible and you're not bound to a particular structure. 94
But even though the NoSQL guy is flashy and he's new and he's hip, you know I'm sure you've 95
met people like this and I've met a lot of NoSQL 96
guys in my time. And what they're not good at are relationships and this is another big difference 97
between SQL 98
and NoSQL databases in a way that they implement relationships between your data. 99
Although previously we were comparing between SQL 100
and NoSQL databases, you also often hear people refer to them as relational and non-relational databases. 101
And this is the highlight that SQL databases of really good at establishing relationships between 102
your data. Coming back to our shop, 103
let's say that you wanted to keep a record of all of the orders that are being made. 104
So you want to know what's the name of the customer, who bought it, what's their address, 105
what did they buy, 106
what was the price of the product, 107
how many of it did they buy, 108
all of these things. 109
But it doesn't really make sense to cram it all into the same table. Especially if that same customer 110
comes back and buys a different product 111
then you have a lot of repeated data and it doesn't make any sense to cram everything into the same table 112
right? 113
Using a SQL database what you would do is you would group related pieces of data into individual 114
tables. 115
So you might have a customer table, a products table and an orders table. And in your database you can 116
specify a relationship between the tables. 117
So for example you could link up the customers table through the customer ID to the orders table. And 118
you could link the products table to the products ID in the orders table. 119
If we had some real data for example, we have a database of all of our customers and where they live. 120
We have a table of our products and what their prices are. 121
And we also have this Orders table which links up the data in the different tables. 122
Let's say we have an order with order ID 1, our very first order. We can have a column where we specify 123
the customer ID. 124
So in this case it's linked to the customer that has an ID of 2. 125
So that is of course me 126
right? 127
cUSTOMER 2 is Angela Yu. Now the next column saves the product ID. 128
So this links to the products table with the product that has an ID of 2. By using this format we can 129
join the tables together at a later point if need be. 130
So we can know for each order which customer bought it and which products were bought. 131
Now using a NoSQL database such as MongoDB 132
you have to rethink how you organize your data. 133
In this case we might have a document where we have an order ID. 134
We have a key value pair that's a customer with customer as the key and their details as the value 135
and also the products as its own embedded objects. 136
So now we have embedded objects in order to represent the entire order. 137
And this might lead to some data repetition. 138
So you could also organize your data in different documents 139
right? 140
So say you have three objects. One has details of the order, one has details of the customer and one has 141
details of the product. And you can also link them together 142
although a little bit more clumsily through using references. 143
So for example in the order document, you can see the customer references the document with a person 144
ID of person_01 and the product references a product with the ID of product_01. 145
So this is how you might implement your relationships in a NoSQL database such as MongoDB. But 146
it's a little bit clumsy and when you start searching this database that structured like this, it might 147
not be quite as efficient or fast as a SQL database. 148
So depending on the structure of your data, you will want to choose a SQL or a NoSQL database. 149
And you should know very often you will see on the Internet people saying MongoDB is the best database 150
out there ever. 151
And other people will say don't ever use MongoDB, it's the worst. Or you know 152
MySQL is the best thing since sliced bread. 153
Don't believe what people say. 154
Instead you have to know what your particular situation is to choose the right database. 155
If you need to store data such as orders, customer details, products inventory, things that have lots of 156
relationships between each other, then you might be better off choosing a SQL database such as 157
MySQL. 158
However if you have a website where you have something that's more of a one to many kind of relationship 159
like here for example say if we were making a database for Instagram, then we might have a user name, 160
a first name, last name, and then we might have a post array. 161
And so this is the array of objects and those objects each have an image URL description and the 162
date that they were created. 163
In this case where you have a single user generating lots of content creating a one user to many posts 164
relationship, then this is much easier to map out using something like MongoDB. The last difference 165
I want to talk about is scalability. Despite it being relatively weak on the relationship front 166
one of the reasons why you might use NoSQL over SQL is because of scalability. 167
This is a real strong point of NoSQL databases and this is a reason why you might choose a NoSQL 168
database over a SQL database. 169
If you've ever had an Excel spreadsheet that has lots and lots of rows, so say 10,000+ rows 170
then you might have experienced this. 171
It gets slower and slower 172
the more rows of data you add until a point where the file is so big that your computer can no longer 173
handle it and you might get a pop up saying Excel cannot complete this task with available resources. 174
Choose less data or close other applications. 175
It's saying that you need a more powerful computer. If you are running your SQL database on your laptop 176
for example 177
then you might just get a more powerful computer, say a desktop computer with higher specs 178
right? 179
And once that start slowing down and struggling, then you might just go for a mainframe and buy the biggest 180
computer that you can find. 181
But as we saw previously there are some companies that have so much data. 182
For example the number of websites that Google has to index and be able to serve up in searches is 183
in the bazillions and they have so much data that even the fastest computer will slow down and struggle. 184
Managing large amounts of data using SQL based databases 185
it's kind of like scaling a building vertically. As soon as you have more data 186
you just add floors onto your building until eventually you end up with the skyscraper of a building. 187
It might just become unstable and end up collapsing on you. 188
And it's also very costly to scale vertically like this. Now because the way that MongoDB organizes 189
its data into smaller chunks or smaller documents of data where essentially each record or each row 190
in the table is represented as a JSON object, 191
then in this case instead of buying larger and larger computers, it allows for a distributed system. 192
So your database can be distributed amongst lots and lots of different computers. 193
And this is equivalent to building low but sturdy buildings and you scale horizontally so you build 194
lots and lots of those. And all the data is distributed and spread out amongst all of them. 195
So if we were to compare the most popular SQL database which is MySQL against the most popular 196
NoSQL database which is MongoDB, then these are some of the pros and cons of each. SQL databases 197
have been around for ages. 198
That means that the technology is more mature. 199
But also if you come across anything that's SQL related on the internet, it all looks like it was created 200
in the 90s. 201
MongoDB is shiny and new but as we're finding out some of the things are not really ironed out. That 202
might mean more frequent changes in the technology as they find new problems and address them. 203
The next difference is that SQL databases run on a table structure as we've seen before whereas Mongo 204
DB works with a document structure. 205
So this way individual rows of data can be each represented as a document. 206
And this leads us onto the next point. With a SQL database 207
you should have a structure in mind before you create your database. 208
And this, in database lingo, is called a schema. 209
So these are the names of your columns and the names of your tables and how many columns you might need. 210
And SQL databases work really well 211
if you have a fixed schema, so if you already know ahead of time what your customer database will look 212
like, what your orders database will look like 213
and you know that they're not likely to change. 214
Whereas with something like MongoDB and other NoSQL databases, they're more flexible to changes. 215
So if you're a young startup and you haven't really figured out how you're going to organize your data 216
or what kind of data you're going to store, then it might be a better idea to choose this particular 217
type of database. 218
But as we saw earlier on, if you need to store data with a lot of relationships between the data then 219
a SQL database will work out to be much faster when you query through your database. 220
Whereas with something like MongoDB it can manage simple relationships but it's really not great when 221
those relationships start to get complex and when you have data in MongoDB that's linked up with complex 222
relationships 223
The speed and efficiency can quickly go down. The last point as we mentioned earlier on is that SQL 224
databases tend to scale vertically. So you tend to need more infrastructure, 225
it's more costly. 226
Whereas with MongoDB and other NoSQL databases they can scale horizontally and allows for a 227
more distributed structure which makes it much easier to scale. 228
So this is your introduction for working with databases and especially looking at the differences between 229
the two major families, SQL 230
and NoSQL databases. Now in the coming lessons we're going to explore in a more hands on way 231
the differences between SQL and NoSQL so that you can try it out and see for yourself how they 232
each work and experience some of the frustrations as well as the benefits of each. 233
So for all of that and more, I'll see on the next lesson.