Welcome back to the backend master class. In the last lecture, we’ve learn how to design a database schema
with dbdiagram.io Today we will learn how to install Docker
desktop on local machine, then download and start a PostgreSQL container. We will also learn how to setup and use TablePlus to connect and interact with Postgres, and use it to run the SQL script that we’ve generated in the previous lecture to create our simple bank’s database schema. Alright, let’s start by installing docker. On a Mac, it’s super easy We just need to search for docker desktop Then open this docker website And click download. After the installer is downloaded, we open it, and drag Docker into the Applications folder. And that’s it! Docker desktop is successfully installed. Let’s click on this icon to run it. It will take a while for docker desktop to
start, You can see its status by clicking on this
icon. Right now it’s a yellow circle, which means docker desktop is still starting. When the circle turns green, we know that docker desktop is started and
ready to be used. Now let’s open the terminal and try it. We use docker ps command to list all running
containers. At the moment it’s an empty list Because we haven’t run any containers yet. Let’s try docker images to list all available
docker images. It’s also empty for now. Let’s learn how to pull the first image. In this course, we will use PostgreSQL as the database engine for our app. So let’s go to hub.docker.com to search
for its image. There are several results. But we’re gonna use the first one, because it is the official Postgres image. As you can see here, we can simply run “docker pull postgres”
to get this image. This will pull the image with the “latest”
tag, In this case, it is version 12. There are many other versions with different
tags as well. I often use alpine image, since its size is
very small. Thanks to the light weight alpine linux distribution. So let’s open the terminal, and run “docker pull postgres:12-alpine” The syntax to pull an image is “docker pull
<image_name>:<tag>” We use a colon to separate the image name
and its tag (or version). You can apply it to download any other images
of any version you want. Alright, the image is successfully downloaded. Now if we run “docker images” We can see the “postgres” image right
here, Its tag is 12-alpine, It’s also assigned a unique image ID. And look at the size of this image, it’s just around 150MB, pretty small. OK, so now we have the Postgres image, Let’s learn how to run it to start a Postgres database server container. We use the “docker run” command for this
purpose. Here’s the syntax: Start with docker run Then we use the --name flag to specify the
name of the container. The -e flag is used to set environment variable
for the container. In this case, we can set the password to connect
to Postgres. Then the -d flag is used to tell docker to run this container in background (or detach
mode) Finally the last argument is the name of the
image: postgres. If you’re new to docker, It’s important to distinguish 2 terms: image
and container. Basically, a container is 1 instance of the
application contained in the image, which is started by the docker run command. We can start multiple containers from 1 single
image. We can also customize the container by changing some of its environment variables, For example, With the POSTGRES_USER variable, We can set the username of the superuser to
login to Postgres. If this is not specified, the default username:
“postgres” will be used. Similarly, The POSTGRES_DB variable allows us to set the default database name which will be created when the container starts. Otherwise, the default database name will
be the same as POSTGRES_USER. Alright, now let’s copy this command And paste it to the terminal. Now let’s change the image name to postgres:12-alpine because we want to run this specific version
of postgres, For the password, I’m just gonna use “secret” to be simple. Then let’s set the superuser name to root with this POSTGRES_USER environment variable. Now the name of the container, I’m gonna use postgres12. Finally, one important argument we must add
to this command is the port mapping. What does that mean? Well, basically, a docker container is run
in a separate virtual network, which is different from the host network that
we’re on, So we cannot simply connect to the postgres
server running on port 5432 of the container network, unless we tell docker to create one kind of
“bridge” between our localhost’s network and the
container’s network. We do that by using -p flag, Then specify the port of the host network, followed by a colon, Then the corresponding port of the container. They don’t necessarily be the same, But I often use the same port to make it easier
to remember. Now when we press enter, Docker will start the Postgres container, and return its long unique ID. We can list all running containers with “docker
ps” command. As you can see here, This container ID is a short prefix of the
long one that docker returned in the previous command. Then the image name, postgres with tag 12-alpine If we run “docker images”, we can see it’s the same image that we’ve pulled before with the “docker
pull” command. There are several more information, Such as the created time of the container, Or the status of the container. And the port mapping, As we might expect, It’s mapping port 5432 on localhost To the same port in the container. And finally the name of the container, which is postgres12 as we set in the “docker
run” command. OK, now the Postgres server is ready, Let’s try to connect to it and access its
console. We can do that with the “docker exec”
command. It allows us to run 1 specific command inside
a running container. We use the -it flag to tell docker to run the command as an interactive TTY session. Then we specify the name of the container,
which is postgres12. And finally the command we want to run inside
postgres12, In this case, we would like to run psql command
to access the Postgres console. And we use the -U flag here to tell psql that we want to connect with the root user. And voila, we’re now inside the Postgres
console. One thing you might notice here is Postgres doesn’t ask for password, although we’ve set it when running the container. It’s because by default, the Postgres container sets up a trust authentication
locally, So password is not required when connecting
from localhost. OK, now we can try a simple query, such as “select now()” to get the current
time. It works! Let’s quit the console by \q enter. One more thing I want to show you here Is to display the logs of the container. We use the “docker logs” command followed by the name of the container. You can use the unique ID of the container
as well. But for me, I prefer the name because it’s
easier to remember. OK, here’s the log of the postgres12 container. With this, we can easily check what happens
inside the app’s container. Alright, So now you know how to use some basic docker
commands to interact with the Postgres container and access its console to run SQL queries. I’m gonna show you another easier way to
manage and play around with the database using Table
Plus. Table Plus is a GUI tool that can talk to many different kind of database
engines, It is very easy to use and will help us a lot in speeding up development. Let’s click this button to download
it. Then open the installer and drag TablePlus to the Applications folder. Now let’s open the app. There are no db connection yet, So let’s create a new one. There are many database engine options, But in our case, we use PostgreSQL. Now we enter the name of the connection, I’m gonna call it postgres12 The host is local host And the port is 5432 by default The username is “root”, and the password is “secret”, as we configured when running the postgres
container. The default database name is root, same as the username since we didn’t explicitly config it when
starting the container OK, let’s click test to test the connection. All green So now we can click Connect to connect to the database server. Everything is empty at the moment because we haven’t created the schema yet. But we can still run some queries by clicking on this SQL icon Let’s try select now(); And click Run current button, Or simply press command enter. The result will show up in the below section. Now let’s open the simple bank SQL file that we’ve generated in the previous lecture. Then select all queries in this file And press command enter to run them. All successful. Now when we press command R to refresh, 3 tables will show up on the left: accounts, entries and transfers. We can click on their names to see the data Or select the structure tab to see their schema
structure. There are many useful information, Such as the column name, data type, default value, foreign key, Is nullable or not. Looks like some foreign keys column are now
nullable, which is not really what we want, because every entry or transfer must link
to their accounts. So let’s go to dbdiagram.io to fix this. I’m gonna add not null constraint to the account_id column of entries table and the from_account_id and to_account_id
columns of transfers table Then export to PostgreSQL. Now let’s remove the old file, And change the name of this new file Then open it with TablePlus. Alright, now we can see the NOT NULL constraint
for the foreign keys I’m gonna select all these 3 tables Right click, and choose delete Choose cascade to make sure all reference
data will be deleted. Then click OK. Now you can see the tables are still there, But they are marked in red. If we press command S to save this state, The delete table commands will be executed And all tables will be gone. OK now let’s select all queries in this
new schema SQL file And run them. Refresh. 3 tables show up again. But this time, all columns are not nullable. That's exactly what we wanted. We can also see the comment for the amount
columns That we’ve written in the schema definition
script. Awesome! And that’s it for today’s lecture. I hope you find it interesting and useful. Thanks a lot for watching And see you in the next one!
