SQL Overview & Creating a Database

[00:00:00]
>> Now that we've gotten over all the Docker stuff, let's go ahead and chat about SQL. So Mark, you had a question?
>> Yeah, since Postgres is such a stable, long running software, what kind of things would change between versions? I'm assuming SQL is not gonna change the language, but what types of features would they be changing?

[00:00:30]
>> Cool, so yeah, like what's changing from version to version on Postgres? Super valid question. So the first thing to know about SQL and Postgres are kind of different things. SQL is a standardized language that exists across not just Postgres, but it also applies to SQL Server, Oracle Database, IBM DB2, MySQL, MariaDB.

[00:00:54]
There's a bunch of databases that all use SQL and there's one single standard language. You can kind of think of it like JavaScript, right? JavaScript is a language but there's different runtimes that run JavaScript. There's Node.js, there's the browser, there's Denno, there's now Bun, there's Rhino, there's JerryScript, right?

[00:01:14]
They're all using JavaScript, which is the name of written language, but something different is executing them. It is the same thing with SQL and Postgres. The interesting thing about SQL, and Postgres and all these different permutations is they all kinda have their own flavor. Despite the fact that SQL is a standardized language, they all take their own different approaches to it.

[00:01:37]
And they all add their own features to it. So a great example of this is we're gonna talk about down in quite a few lessons. There's one here called JSONB. This is not a part of the SQL spec. This is a postgres-only thing. So that's what changes between versions is they're adding all these cool new features that are specific to Postgres, and they're making refinements, and they're trying new things.

[00:02:03]
Once we get to JSONB, you're gonna see why wasn't I teaching this the entire time, kinda spoiler alerts right there. But that was changing, as they're adding new cool features and they're still innovating at a pretty rapid pace. And particularly Postgres, I feel like is leading the path, which is why I chose to teach this one over MySQL mostly.

[00:02:24]
So now's a good as time as any to address the SQL, S-Q-L elephant in the room, what is the correct pronunciation of S-Q-L? Well, it depends on who you ask. I did a decent amount of research on this cuz I was positive that someone's gonna ask me. The correct pronunciation of S-Q-L according to the body that defines S-Q-L is S-Q-L, they pronounce it S-Q-L.

[00:02:57]
However, the people that make SQL Server, aka Microsoft, calls it SQL Server, right? One of the most well known people that works on defining what S-Q-L is and writes all of these books about it, she calls it SQL. So in another words, S-Q-L is probably, by the most definition of that, the defining body calls it S-Q-L is probably the most correct, but SQL is equally valid.

[00:03:25]
I call it S-Q-L because that's just what I've always called it. There are other people that will always call it SQL, but I think generally my rule of thumb is if I'm talking about MySQL or Postgres or one of those, I generally say S-Q-L. And if I'm talking specifically about SQL Server and Microsoft Stack, I will call it SQL because that's what everyone in that community calls it.

[00:03:52]
Basically, Microsoft and Oracle have codified SQL. So if you're talking about Oracle as well, they call it SQL in their docs. Basically, everyone else that I'm aware of calls it S-Q-L, in terms of it's PostgreSQL, MySQL, MariaDB calls it S-Q-L. So they're both correct, someone will get really opinionated about it and you can just tell them that you don't care, because it does not matter, that we're both talking about the same thing.

[00:04:23]
There's a really interesting history about it, but suffice to say that SQL was invented in the 70s. It was derivative from a language called square. They tried to cause actually literally sequel, S-E-Q-U-E-L, that was some acronym, that was the name of some fighter jet or something like that.

[00:04:40]
So they couldn't call it that, so they ended up calling it structured query language, SQL, and we ended up with that. A fun fact is that my dad, [LAUGH] he actually used to work on SQL databases. He worked on DB2, which is the IBM flavor of it. They still sell DB2 these days.

[00:04:56]
And then, he ended up being a technical salesman for IBM for years and years. This is actually stuff I ended up growing up around, kind of unintentionally, it's really funny to my dad that he did all this stuff all these years and now I'm teaching it to people.

[00:05:08]
So fun fact, hi dad, he might be watching, I don't know. Let's get into structured query language, SQL. And again, you can call it whatever you want, I will never correct you because I profoundly do not care how [LAUGH] you pronounce it. And it's one of those hills that people choose to die on for no apparent reason.

[00:05:27]
So, we're gonna get started on tables that inserts. So if you're not already, please get to running inside of your container. You should see something here like Postgres, like that. If not, you can go back to the previous lesson, it will show you how to do that. And let's start running some commands.

[00:05:52]
So again, we are interacting with the Postgres database with a client called PSQL, those are different things, right? One is a database, one is a client that is sending queries and getting information back. So I can be hosting one database and everybody else could be querying it with their own PSQL, right?

[00:06:12]
So you have one database but you could have many clients to it. This client has a bunch of built-in kind of helpful commands, and if you forget what they are, you can hit \?. And this will just pop up a really helpful sort of help information that I'll show you a bunch of stuff.

[00:06:33]
I use it all the time cuz I can never remember all of them. But there's a bunch of stuff here. You can run commands out here. So if I do \!, that will allow me to run a bash command and the underlying bash, sometimes that can be helpful.

[00:06:53]
You can cut and files and things like that. We're not gonna be using it today, but there's a bunch of stuff you can do with it. And anything that you see prefixed with a backslash, that's going to be a feature specifically of PSQL and not necessarily of Postgres to database.

[00:07:06]
The one that I find myself using a lot is \l, this will show me all of the databases that exist. And I'll use \d quite a bit. We haven't created any tables yet, so it will not find any, but \d will show you all of the tables and sequences and constraints and stuff like that.

[00:07:31]
So that's a good place to get started from there. So we have a bunch of default databases, you can see here I have Postgres, I have template 0, and I have template 1. So the first thing to say is what is a database? Are we connected to a database?

[00:07:48]
It's a bit of a conflated term. In this particular context, when I say we're connected to the Postgres database, it means I have my Postgres server running, and there are multiple databases inside of this. You can think of a database in this context, as a folder, all right?

[00:08:06]
It's a folder that contains many tables, right? Which you can kind of think of like spreadsheets, right? So Postgres here or template0, template1 would be the name of my folder that I'm in. In a moment, we're going to create our own database or create our own folder, but a database contains many other tables and things like that, okay?

[00:08:27]
As you can see here, your database comes with three of them pre-created. So the Postgres1 exists solely as the purposes, it's the default database that you connect to if you don't give it anything that as you're trying to connect to a specific database. It just exists for that purpose, it's the default one.

[00:08:44]
You can technically delete it, there's no reason that you can't delete it. You also don't have, that's up to you. So template1 and template0 exist for the fact if you are creating and deleting a bunch of database, if you wanna have a templates to start from, you would use template1, you'd make a bunch of modifications to template1.

[00:09:04]
And that'll affect the default database that you create. If that gets all out of whack, what you'll do is you'll then clone a new template1 from template0, you should never modify template0. And this is basically all stuff that you can totally forget because I never used them. [LAUGH] This is why they're there.

[00:09:25]
This is why they exist. But I never use the templates, I always just create everything from scratch because I don't find myself creating and deleting a lot of databases, right? I generally just create my database and then just go from there, right? So, but I want to know why they're there.

[00:09:42]
Okay, that being said, we're going to run this command, right? So I'm gonna say CREATE DATABASE recipeguru. So a couple of things to note here. Now I can say \l. And you can see here we have our recipeguru Database. So we've created our own folder to work from.

[00:10:10]
The first part of this workshop, we're going to be creating a recipe website, okay? Hence the name recipeguru. Now, you might have seen there that I said, in all caps here, CREATE DATABASE like that. Why did I do it that way, why is it all caps? Well, the reason why I do that is so that you can see the commands that are coming from SQL are always gonna be all caps when I do them for you.

[00:10:39]
And everything that we do that's optional user controlled information, that's gonna be in lowercase here, like recipeguru. That's a very common pattern in SQL, but there are also people that insist that you should never do caps, and it's really done and people are wasting their time and Pinky strength by holding ships down for so long.

[00:11:01]
And again, it doesn't matter. I like it, whenever I'm writing code, I tended to do everything in all caps. So it's really easy for me to glance and say, okay, CREATE DATABASE, that's a command, recipeguru, that's the name of the database, and it's really easy to parse that visually.

[00:11:15]
But there's no requirement that you have to do it that way. I will find myself when I'm just writing queries, just as like write queries, I'll probably not do caps just cuz I'm lazy, right? So that is totally up to you. And it's one of the things like SQL versus sequel, it really just doesn't matter.

[00:11:29]
So it's never worth getting into an argument with anyone over it. So I'm just gonna throw out here one more note, that if you kill your container at this point and you restart the container, you will have to recreate recipeguru again. Because it's going to give you another blank slate, right?

[00:11:44]
That's just another word of caution that I'm putting out there. I do provide here this complete SQL file. You can actually download this, this will actually be the complete recipeguru. So this will give you actually the first half of the workshop already done, if you need that. Or you can use the container from Docker Hub, which is the, This one here, the complete intro to SQL.

[00:12:11]
And that will also give you the complete recipeguru database. So a database is a collection of similar tables. For all of our recipe apps, we'll be storing them in one database. People ask me frequently when do I use multiple databases, when do I just shut everything into one database?

[00:12:30]
That is a very you call to make. In general, I'm tend to shove a lot of things into the same database because I don't really see the use and trying to make them work across databases. In particular, if anything would ever have to be like related to something else, like I have my ingredients, I have my recipes as those obviously have to be connected to each other, right?

[00:12:53]
It does have to be in the same database, no matter what. But let's say I have like, user session information, and I have recipes and ingredients. In theory, those could live in different databases because I would never have to collate those two together, right? So it's an highly opinionated thing, right?

[00:13:12]
It's kinda like asking the question, should I have two services or should I have one monolithic service, right? Should I have microservices or macroservices? That depends on what you're doing. For your purposes, getting started, just throw everything in a database, and in general, you'd be fine. The question you can kinda ask yourself is, would I ever have to scale these things independently?

[00:13:35]
Which is where it can be useful if we split across databases. So you can see here, I created the recipeguru database, but I'm still connected to the Postgres database. So I wanna switch my connections. So I'm gonna hit \c recipe, and then this actually does have tab completion.

[00:13:53]
So I can hit \c recipeguru. And now you can see here, I'm connected to the recipeguru database, which again, has nothing in it. If you put \d, you will not find any relations yet. But we're gonna go on to the next lesson, we'll actually create some tables.