What are SQL Databases

[00:00:00]
>> Let's get into SQL. Now the first thing I wanna say is that I still sometimes out of habit still called the SQL. You can tell that I have a lot of habits around databases and I just cannot break free of them. Like I've been doing this for a decade.

[00:00:16]
The correct way to say this is SQL, and My SQL, but it's SQL server and you writes SQL for SQL server. I still call it SQL, because I was around a bunch of people that called it SQL. So I apologize, I really mean SQL. But when I say SQL, usually SQL is associated with like the Microsoft stack.

[00:00:38]
Unlike Microsoft SQL server, so when I say SQL, if you see hear me say SQL, I will try and stop myself. But obviously it was not successful, records versus documents. Anyway, just like no SQL is kind of a dumb marketing term. So as SQL databases, when I say SQL databases what I really mean is really relational databases.

[00:01:03]
And you also see that sometimes abbreviated as RD BMS, which I think is relational database management system. I think is what that stands for, but again, that means an SQL based database are a relational database. It's also important to remember that not all relational databases use SQL. And not all non relational databases don't use SQL.

[00:01:29]
But again, I'm trying to use the most common terminology here to hopefully demystify some of the stuff. So when you see SQL database, you can kind of loosely associate or even strongly associate that with relational database in your head. Hopefully that's as clear as mud because marketing is done.

[00:01:46]
I don't know why these people still try and confuse us more, but let's talk about relational databases. For the rest of time, I'm gonna try and use relational databases that's actually probably the closest thing to what I mean. So what is a relational database? The best thing to think about with relational databases is Excel, right?

[00:02:02]
Excel or numbers or some sort of spreadsheet. That is like the easiest picture, it's like a table of data. Notice, that the name of a collection in a relational database is called a table. That that is no coincidence. Yeah, so it's good to think of this as rows and columns.

[00:02:22]
In fact, that's those are terms that I'll use as rows and columns. One of the things with a relational database is it has a defined and very structured schema. Whereas with if you remember in Mongo, we were writing to our collection and we just added the owner field on the fly.

[00:02:39]
That's very easy to do in MongoDB. And it's not very easy to do in SQL, because SQL is like, you need to tell me about all of these fields before you can give them to me. If you try and write something that doesn't exist, it just won't let you.

[00:02:51]
So what we would have to do you actually have to issue an entire statement called an alter table statement. Which you don't wanna do alter tables are very expensive. As in like we used to do alter tables. When I worked at Reddit, it would literally take down like Reddit for us to do that.

[00:03:07]
So we were very cautious to not alter our tables very much. So a big thing of up front is defining your schema. Here's the secret power of relational databases is that they're very good at describing relations, right? Whereas with MongoDB you don't wanna ever have like this collection refer to this collection over here.

[00:03:26]
That's not a good idea. With MongoDB one of the things you don't wanna do is you don't want to have one collection refer to another collection in general, right? That's a very expensive thing for you to say. Join these two collections based on this one field, right? In general with MongoDB when you're architecting this type of data.

[00:03:47]
You want all the like data to live together and MongoDB provides you with a lot of tools to be able to do that. Now, I was actually talking to my friend who is the director of MongoDB and he's like, you can do it it's possible. And certain In situations, it makes sense to do that with MongoDB.

[00:04:03]
But I still hold to the rule that if you're doing joins, you're doing MongoDB incorrectly. With SQL, that's like a core component of how SQL actually works is that you wanna have data that relates to each other. So you're gonna have multiple different tables, and those tables are going to refer to each other.

[00:04:22]
The example that we're gonna do today is we're basically gonna create a message board. And with that message board we're gonna have a table of boards that are going to refer to a table of contents. So table of comments, it's hard to say that way, a table of comments, right?

[00:04:41]
And there's gonna be two separate tables that are going to refer to each other based on IDs. And we're gonna do joins and that's a big power of what SQL databases can do. So that's why it's called relational databases is because it's very oriented to the idea of data relating to each other.

[00:05:01]
So what is SQL? Why do I keep referring to this dumb acronym? I hate acronyms. Why do we have this one? SQL stands for structured query language, which I had to look up. It was one of the things I still I never knew, I just always knew what SQL was.

[00:05:17]
And it's a powerful query language. Whereas, in MongoDB, we were using JSON and JavaScript to write queries, to query MongoDB. SQL is what you're gonna use instead of JavaScript to query this database. So it's a very kind of structured query language that's useful to say out loud. It's a language that is structured, that you use to query databases.

[00:05:45]
So if you use this kind of grammar, you'll be telling the database, how and when, to do these kind of queries and we'll get into that. Whereas there's not a ton of document based databases out there. Like if you're gonna talk about a document based database, you're gonna be talking about MongoDB most of the time, that is definitely not the case for SQL.

[00:06:04]
With SQL, there's a lot of different types of databases. I chose Postgres because I think that's kind of the accepted I will say accepted. It is one of the most used databases for JavaScript developers. And it has a lot of features that I think JavaScript developers will find interesting.

[00:06:25]
And it's just well used today. Beyond that it's just my favorite, right? Like it's the one that I would choose today, if I was gonna go write a new application. Now the elephant in the room that is pinot intended, but also kind of funny. The elephant in the room would be the mascot for Postgres is an elephant.

[00:06:46]
That's why that's a dumb joke. The other would be my SQL, my SQL is the other relational open source database that gets a ton of usage. And I actually, my first job and my second job, I wrote a lot of things my SQL queries. It's great, it's scales enormously, Facebook is famously based heavily on MySQL.

[00:07:11]
So it does well. MySQL is also backed by Oracle. So obviously they know a thing or two about databases so it's well supported. It's very good choice today as well. Google and Netflix are on two other very big utilizers of MySQL. Some people don't want to be tied to Oracle.

[00:07:33]
And so another group of the old core committers of MySQL formed a new database called Maria DB. Maria DB is meant to be a drop in replacement for MySQL, so they are largely compatible. It's just more open source, it's not tied to a big company. It's another very well used database as well.

[00:07:57]
So those are kind of like the ones that people mostly think about. The other one to kind of just mention is SQL Lite. This one's a little bit different, it's meant to be very small. It actually doesn't even have any net code or anything like that. It's generally something you can drop into like an IOT device.

[00:08:15]
MyMac is currently running SQLite. Everyone's Mac is running SQLite it cheap in a lot of things it's very small, it's very performant. It's been to kind of thing generally not used in web does scenarios. I would not recommend it for web development scenarios, but it is a very simple one to get.

[00:08:35]
Because keep that in mind. And then the other one are like the big corporate players like SQL Server, Oracle, IBM DB2. These are kind of like the big corporate databases. They're very expensive. Like typically you're gonna sign like a very large corporate contract, use one of these. I'm not gonna tell you about them because I never used them.

[00:08:59]
And I never have used them despite the fact that like I said, my father literally worked on DB2. But they're out there as well. I'm sure some of your companies are using them. There's, I mean, they're obviously good products.