Install & use Docker + Postgres + TablePlus to create DB schema

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 the POSTGRES_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 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.

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 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 (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 (or 127.0.0.1), 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 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.