Install & use Docker + Postgres + TablePlus to create DB schema
Tags: database, docker, beginners, tutorial
Published: 2020-06-13 09:39:14

Welcome back to the backend master class!

In the [last lecture](https://dev.to/techschoolguru/design-db-schema-and-generate-sql-code-with-dbdiagram-io-4ko5), we’ve learn how to design a database schema with [dbdiagram.io](https://dbdiagram.io/home)

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.

{% youtube Q9ipbLeqmQo %}

Here's the link to the [full series playlist on Youtube](https://bit.ly/backendmaster)

## 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](https://www.docker.com/products/docker-desktop) 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.

![install-docker](https://dev-to-uploads.s3.amazonaws.com/i/ipyvs8qlw7anrlu16uqb.png)

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.

![docker-starting](https://dev-to-uploads.s3.amazonaws.com/i/6qd3iph0lay1bhbi6xf9.png)

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.

![docker-empty](https://dev-to-uploads.s3.amazonaws.com/i/wc3d09e49uyi37y1ld7q.png)

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](https://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.

![hub-postgres](https://dev-to-uploads.s3.amazonaws.com/i/z4u57k9rj99ta1a8jslx.png)

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:

```bash
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.

![docker-pull](https://dev-to-uploads.s3.amazonaws.com/i/bp7j7f06ir8dnjb7stxk.png)

Alright, the image is successfully downloaded. Now if we run `docker images`, we can see the `postgres` image right here.

![docker-images](https://dev-to-uploads.s3.amazonaws.com/i/cct3x6m7jczaaolud1p4.png)

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:

![docker-run](https://dev-to-uploads.s3.amazonaws.com/i/tfpjvaxdml6l9fdb7x4z.png)

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

![image-vs-container](https://dev-to-uploads.s3.amazonaws.com/i/sb14quvgjg66exmqdl6b.png)

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:

```bash
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.

![port-mapping](https://dev-to-uploads.s3.amazonaws.com/i/uxhtcrl2god7owff6ep2.png)

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.

```bash
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.

![docker-ps](https://dev-to-uploads.s3.amazonaws.com/i/34cznxqj75yu33frg8pa.png)

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

![docker-exec](https://dev-to-uploads.s3.amazonaws.com/i/s75pmiqs608yve7v1bu3.png)

We can do that with the `docker exec` command. It allows us to run 1 specific command inside a running container.

```bash
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.

![postgres-console](https://dev-to-uploads.s3.amazonaws.com/i/hr7b3s86uv1971eeujrn.png)

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.

![docker-logs](https://dev-to-uploads.s3.amazonaws.com/i/owtjr9cp3mcbsyksyb0x.png)

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.

```bash
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](https://dev-to-uploads.s3.amazonaws.com/i/agrwvz0x9j7v5sei6smk.png)

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](https://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!

![select-pg](https://dev-to-uploads.s3.amazonaws.com/i/z4okto4rdxv8laxj4m54.png)

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.

![test-connection](https://dev-to-uploads.s3.amazonaws.com/i/4x801ehgjm685jdcfzst.png)

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();` 

![tp-run-select-now](https://dev-to-uploads.s3.amazonaws.com/i/h8ovyc2bjmr126324pwd.png)

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.

![create-schema](https://dev-to-uploads.s3.amazonaws.com/i/y47l9zfuh1aadynvswvw.png)

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.

![table-structure](https://dev-to-uploads.s3.amazonaws.com/i/lfeilp98ft1ytmngf258.png)

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](https://dbdiagram.io/home) 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.

```sql
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.

![delete-table](https://dev-to-uploads.s3.amazonaws.com/i/540m5wqff2jxayh15vbc.png)

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`.

![table-in-red](https://dev-to-uploads.s3.amazonaws.com/i/gk0elc9v7cvk3hi7ww3a.png)

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.

![not-null](https://dev-to-uploads.s3.amazonaws.com/i/vbnlp5y60ji70ffirl9s.png)

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](http://bit.ly/techschoolguru) and [follow us on Twitter](https://twitter.com/techschoolguru) 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](https://bit.ly/voodoojobs). Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.