Complete Intro to SQL & PostgreSQL

SQL Setup with Docker

Brian Holt

Brian Holt

SQLite Cloud
Complete Intro to SQL & PostgreSQL

Check out a free preview of the full Complete Intro to SQL & PostgreSQL course

The "SQL Setup with Docker" Lesson is part of the full, Complete Intro to SQL & PostgreSQL course featured in this preview video. Here's what you'd learn in this lesson:

Brian explains that Docker allows any computer to run a lightweight emulation of a pre-made environment. Two Docker containers that contain PostgresSQL 14 and prepopulated data are installed and will be used throughout the course.

Preview
Close

Transcript from the "SQL Setup with Docker" Lesson

[00:00:00]
>> So speaking of that, Docker, [LAUGH] this is not a Docker course. I left a link here for you to my Docker course, if you want to take a Docker course, which I suggest because I think containers are really important for developers. But that's not what this course is.

[00:00:16]
I'm gonna give you all the Docker commands, but I do need you to go download Docker desktop. Or whatever flavor of Docker, but if you don't have a preferred flavor of Docker you should use Docker desktop because that is by far the easiest one to use. So please go install Docker desktop, I left a link here for you.

[00:00:40]
It should detect whatever desktop you're on, and you can download it. They'll kind of lead you through all the various different steps that you're gonna have to do to get it up and running. And then we're going to interact a little bit. To actually quite a bit with the command line.

[00:00:55]
This is not a command line course, there is a command line course that I also teach that would be helpful but again not required for this. I will give you the command line commands that you are going to need. So in other words, you can basically just copy and paste all of my commands directly and that should just work for you.

[00:01:11]
If you're not familiar with Docker uses containers or containers, basically you can think of like a mini operating system that they kind of put together for you to use. We're gonna be using two containers and actually kind of three containers at one point, but these two are the primary ones.

[00:01:25]
The first one is just the vanilla Postgres container, and then the second is this kind of one that I built on top of it. So the second one actually depends on the first one, so Docker will handle that correctly, you don't have to do anything about that. We're gonna be using, specifically PostgreSQL 14 for this course, they actually do tend to change a lot between Postgres versions.

[00:01:48]
So even if you're taking this way later from when this class was filmed in Postgres, 15 or 16 is out, I will suggest that you still use PostgreSQL14. That way everything that all the commands and stuff like that will work as is you don't have to kind of do any translation.

[00:02:05]
And then later after the course then go learn what changed between 14 and 15 or 14 and 16. The second one here, you can click here. It's just a container that I put up on Docker hub called complete intro to SQL. It has, two different databases seated in it.

[00:02:22]
It has the Open Movie Database, which is a huge database with a bunch of movie stuff in it. And then there's another one for a website that you and I are gonna build, called RecipeGuru. Just know that I'm gonna have you download some of these containers they are quite large.

[00:02:36]
So if you're on like metered internet you might wanna find some of the non-metered. Each one of these containers is about a half gigabyte, so I'm gonna have you download about a gigabyte's worth of stuff. You can run these now, this is actually going to go pull these containers.

[00:02:50]
It's not gonna run them yet, but it'll it'll download them onto your computer. And then once you've done that, I want you to grab this command right here. And we're gonna just paste that into our command line here. So I'm just going to paste docker run, you can see I give it a post, this e means environment variable.

[00:03:20]
So I mean give it a password of lol because it's really easy to remember. Obviously don't make your database password lol in production. Not at all what I'm advocating. This is just something that's really easy for us to reference later. We're gonna call it pg as in postgres, just because that's really easy for us to reference later.

[00:03:40]
The-- rm means once this container is concluded remove all of the logs and stuff like that. Don't leave that stuff floating around. The- d is detached, which means run it in the background don't run in the foreground. The -p means expose this port. So we're gonna expose 5432 to our local computer.

[00:04:01]
Postgres is the name of the container and the 14 is the version. So I just explained it all to you so you're aware, but you actually don't need to care at all. You can just copy and paste it and say I don't care what Brian says. That's fine, you should get something like that.

[00:04:17]
That's the hash of the container that's running, now I should be able to say docker ps. And you can see here I have my container running. It's been up for seven seconds. Its name is Pg, and it is running on port 5432. So, going back I just wanna give you enough Docker information to be dangerous.

[00:04:42]
So now we have docker ps that'll show you everything that's running. If you want to stop it from running, you can just say docker kill pg, and that will stop it. So now if I say docker ps it's gone, and again I can run it again. That'll start it again that's all goodness.

[00:05:04]
The one thing that I wanted to say about running docker kill it will delete everything that you've done, so if you've been following along the course, and you're like halfway through it. And you'd run Docker kill, it's going to drop everything that you had. So just be aware of that.

[00:05:18]
So that's both a good thing if you're trying to reset and start over, but it's a bad thing if you didn't mean to drop everything. Docker is really good about just running quietly in the background. So if I just close my laptop and come back, the Docker container typically will still be running.

[00:05:33]
But if you restart your computer for example it probably will kill your containers. You could fix that by running volumes in the background, but I'm not gonna show you how to do that today so just be aware of that. The thing that you can always do the complete Recipeguru database that we're gonna build up over time is lives inside of this complete-intro-to-sql as well.

[00:05:52]
So you can just pull that one and start from there as well, yeah.
>> What does it mean to run the Docker container in the background versus foreground?
>> That's good question. I can just show you really quick. So if we do the docker run and I just take this -d out.

[00:06:12]
It's actually going to start Docker in the foreground. You can see all of the logs running immediately right in front of me, right? Now, I don't wanna do this, right, I just wanna run in the background and not think about the actual execution of it. But if this is useful to you and you wanna see all the logs, this is how you would do it.

[00:06:30]
What's hard about this, if I say Ctrl+C or if I close this window, it's going to stop the container, right? So you can see there I actually shut it down, it's not running it anymore. But if I do this, and I come back, and I say -d, it just runs it in the background and I can kind of just not think about it anymore.

[00:06:50]
The last thing I want you to do here is just copy and paste this one: docker exec. If you run this, you should just immediately get a of have been connected to postgres. So you should see this postgres prompt here. If you do \l, it should tell you all of the databases that exist.

[00:07:11]
In your database which we only have the three, postgres, template0, and template1, which we'll talk about here in a second. So what that command did this Docker exec this is basically saying hey run a bash command inside of the container. The- u means run it as the postgres user, -it is interactive.

[00:07:36]
Pg is the name of the container and pscql actually the name of the command that we're running inside of the container, which is the Postgres client, right? Psql is the client to run queries against the database. So I can type exit here. And the container is still running, right because we're not actually ending the container.

[00:07:57]
We're just exiting our session with the database. So anytime that you want to connect to it, you just run this docker exec -u postgres -it pg psql, and that'll put you inside of the container.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now