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.
Here's the link to the full series playlist on Youtube
Install docker desktop
Alright, let’s start by installing docker. On a Mac, it’s super easy! We just need to 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 open the app! 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.
If we run docker images
to list all available docker images. It’s also empty for now. So let’s learn how to pull the first image.
Pull Postgres image
In this course, we will use PostgreSQL as the database engine for our app. So let’s go to Docker Hub 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 :
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.
Run Postgres container
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
.
Docker image vs container
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.
Docker environment variable
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 run this command in the terminal:
docker run --name postgres12 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:12-alpine
- Here I use the image name
postgres:12-alpine
because we want to run this specific version of Postgres. - For the password, I just use
secret
to be simple. - Then I set the superuser name to
root
with thePOSTGRES_USER
environment variable. - I use
postgres12
as the name of the container.
Finally, one important argument we must add to this command is the port mapping
.
Docker 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 the -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.
docker run --name postgres12 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:12-alpine
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.
- The image name is
postgres
with tag12-alpine
. If we rundocker images
, we can see it’s the same image that we’ve pulled before with thedocker 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
onlocalhost
to the same port in the container. - And finally the name of the container, which is
postgres12
as we set in thedocker run
command.
Access Postgres console
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.
docker exec -it postgres12 psql -U root
- 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 runpsql
command to access the Postgres console. And we use the-U
flag here to tellpsql
that we want to connect with theroot
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 (inside the container).
We can try a simple query, such as select now()
to get the current time. And quit console by \q
enter.
View container logs
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.
docker logs postgres12
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.
Install Table Plus
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, such as PostgreSQL, MySQL, Mongo, Redis, etc. It's very easy to use and will help us a lot in speeding up development.
OK, let’s go to tableplus.com to download it. Then open the installer and drag Table Plus to the Applications folder.
Create a new connection
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
localhost
(or127.0.0.1
), and the port is5432
by default - The username is
root
, and the password issecret
, 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 the SQL
icon. Let’s try select now();
Then click Run current
button, or simply press Command Enter
. The result will show up in the below section.
Create Simple Bank schema
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.
Fix schema definition
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.
Table accounts as A {
id bigserial [pk]
owner varchar [not null]
balance bigint [not null]
currency varchar [not null]
created_at timestamptz [not null, default: `now()`]
Indexes {
owner
}
}
Table entries {
id bigserial [pk]
account_id bigint [ref: > A.id, not null]
amount bigint [not null, note: 'can be negative or positive']
created_at timestamptz [not null, default: `now()`]
Indexes {
account_id
}
}
Table transfers {
id bigserial [pk]
from_account_id bigint [ref: > A.id, not null]
to_account_id bigint [ref: > A.id, not null]
amount bigint [not null, note: 'must be positive']
created_at timestamptz [not null, default: `now()`]
Indexes {
from_account_id
to_account_id
(from_account_id, to_account_id)
}
}
Now let’s remove the old file, and change the name of this new file. Then open it with Table Plus.
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 simple_bank.sql
file. Run them and 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 reading and see you in the next one!
If you like the article, please subscribe to our Youtube channel and follow us on Twitter for more tutorials in the future.
If you want to join me on my current amazing team at Voodoo, check out our job openings here. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.
Top comments (7)
Hello,
I wanted to say thanks for this tutorial! Also, I am having problems connecting tableplus to postgres, it says password authentication failed for user "root". Any help or suggestions would be appreciated.
Thanks,
Nate
if you are on windows, press windows key plus r and type services.msc.
press enter and scroll down to postgres and press stop.
Everything will work fine now
Hey, I am facing same issue are you able to solve it ?
Hey
When i am trying to create new postgres container with different password, it doesn't allow me to connect even with tableplus, 'it says can not connect to server connection time out' attaching below the SS of the issues, please check and help.
Thanks in Advance
If you are facing issues with connecting TablePlus on windows machine, then problem is in the port mapping. If you already have postgres installed on your windows, all you have to do when running the postgres container is map the container to a new port.
e.g,
In this case, you are mapping port
3356
to5432
.In your TablePlus config, input port to be
3356
. This tells TablePlus which postgres instance to connect.Hi,
I think there's one typo under the topic 'Access Postgres console'. The simple query executed on Postgres might be "select now();", not "select now()"
hi, can i get discount on your course? i have a plan to buy it, but its like im missing it