Building a Backend – Database (Part 1 of 3)

In this series I am going to be covering how to create a backend from start to finish. I’m going to cover the process in several different sections. For our database we will be using PostgreSQL. I’ll go over all of the steps in detail, including what tools I’m using.

The architecture used in this series is built to be scalable, yet hopefully very simple for beginners. I’ll try my best to explain why I used certain technologies as we go along.

Getting a PostgreSQL Server Running

For this series we are going to use PostgreSQL for our database. PostgreSQL is an open source relational database that’s been around since 1996.

Setting up Docker

We will be using Docker to set up an instance of PostgreSQL. Docker is great because we can run our PostgreSQL server on any platform (macOS, Windows or Linux) and PostgreSQL provides an official Docker image that we can simply download and run to set our environment up.

To set up Docker itself simply download and install Docker Desktop if you’re on macOS or Windows. If you’re on Linux, visit the installation page and find the steps on how to install Docker Engine based on your distribution.

Setting up PostgreSQL

To set up PostgreSQL using Docker we will first need to pull the official image by running the following command:

$ docker pull postgres

This command will download the postgres image.

Now that we have the image, let’s create our own image using the postgres image as our base image. In other words, we will customize our PostgreSQL instance, like setting our own database user and database name.

Create a file named Dockerfile with the following contents:

FROM postgres 
ENV POSTGRES_USER user
ENV POSTGRES_HOST_AUTH_METHOD=trust
ENV POSTGRES_DB database

On the first line, From designates that we are going to use postgres as our base image. On the second line, we are declaring the username for our user. On the third line, we set POSTGRES_HOST_AUTH_METHOD to trust since I am not going to be using a database password in this tutorial. Finally, the last line sets our database name to “database”.

Running PostgreSQL

Now that we have a Dockerfile describing an image with a PostgreSQL database that’s configured to our liking, we have to build the image.

To build the docker image navigate to where our Dockerfile is located and run the following command:

$ docker build -t database .

We will now have a docker image from that was built from our Dockerfile. The -t flag is used to set the name of our image, in this case we named it “database”.

Now that we have our docker image, we can run it!

To run our docker image, run the following command:

$ docker run --name database -d -p 5432:5432 database

This command runs our docker image and maps port 5432 of our docker image to the host machine. PostgreSQL uses port 5432 by default and without this port mapping we won’t be able to connect to our database running inside of our docker container.

Connecting to Our Database

We should now be able to connect to our database! To do so simply use the sql client of your choice. I’m using Postico. Another alternative is DBeaver. If you try to connect to a PostgresSQL database to port 5432 on localhost with a username of “user” and database name “database” you should be able to see a database with no tables. This is what I see in my Postico client:

Leveraging Docker to Customize Our Database Schema

With a tool like Postico or DBeaver you can easily create tables and columns. Let’s say that you have your database schema all figured out but you now want to have to so that when you create your docker image from your Dockerfile, the database schema is customized to your fitting the first time you run the image. How do you do this you might ask? I will show you how in the steps below.

Create a CreateDB.sql File

The first step is to create a sql file to initialize our database schema. If you use a tool like Postico you can get the DDL for each creating each table. We are going to use sql create queries to create our tables. One tricky thing to note is if you have columns dependent on a foreign key, then you will need to create the tables you’re dependent on first.

For our database, we are going to have a “hello_world” table with a “message” column that is of type text. The sql query to create this table is the following:

CREATE TABLE hello_world (
    id SERIAL PRIMARY KEY,
    message text
);

That’s the only table and column I am going to have in this example so I created a file called CreateDB.sql in the same directory as our Dockerfile that we created before. In the Dockerfile I added the a line so now it looks like the following:

FROM postgres 
ENV POSTGRES_USER macrowealth
ENV POSTGRES_HOST_AUTH_METHOD=trust
ENV POSTGRES_DB macrowealth
ADD CreateDB.sql /docker-entrypoint-initdb.d/

We must stop and remove our old docker container based of the image that we created with our old Dockerfile. To find the name of our container run the following command:

$ docker ps

That command should return the name of our container in the last column.

To stop our old currently running container, remove it and create it again using our updated Dockerfile run the following commands in the directory our Dockerfile is located:

$ docker stop database
$ docker rm database
$ docker build -t database .
$ docker run --name database -d -p 5432:5432 database

Now if you try to connect to our database again, you should be able to see that it is initialized with the hello_world table!

Conclusion

In this tutorial, you learned how to create and initialize a schema for a PostgreSQL database running within a docker container. I’m going to put the Dockerfile along with the CreateDB.sql file in this GitHub repo. Stay tuned for the next part of this series where we will create our API project that will house our endpoints!