My Back-end Adventures - Setting up a local database (Postgres)
Tags: node, javascript, sql
Published: 2020-05-10 22:11:08

One of the things I really wanted to get started with was setting up a local database for my Express servers. While using an in-memory data object inside of Express is fine, I wanted something a little closer to a real project. 

So I decided to have a local instance of Postgres running. What better way than to host your database inside of a docker container? It eliminates all the complexity of installing Postgres on your system itself and it's easy to get up and running.

In this article, I'll run through how to set up a local postgres database inside a docker container and connect your Express back end to it using Sequelize. Let's go! 

**TL;DR: All the code can be found [here](https://github.com/Qarun-Qadir-Bissoondial/express-postgres-docker).**

## Setting up Docker

First thing you'll wanna do is to [install docker](https://docs.docker.com/get-docker/) on your system as well as [docker-compose](https://docs.docker.com/compose/install/). Docker is a runtime that allows us to run software inside of containers regardless of operating system. That means no frustrating installs and no OS-specific instructions. It works for all! :smile:

Once Docker and docker-compose is on your system and ready to go, you'll need to create a `docker-compose.yml` file. This is a config file for docker-compose that can spin up multiple containers and lets them communicate. Copy and paste the following code into the `docker-compose.yml`:

{% gist https://gist.github.com/Qarun-Qadir-Bissoondial/1fd700daad99df7a50692336e6106aeb %}

The config file does a couple things for us:

 - Creates 2 services (containers) for us called `postgres` and `pgadmin`
 - Sets the default username and password for us to access externally
 - Maps the corresponding ports. `5432:5432` on the postgres container means that port 5432 on the host machine is mapped to 5432 inside the docker container. The same logic applies for `5050:80` for pgadmin
 - Creates volumes for our containers. Even if our container is shut down, our data will persist. `postgres:/data/postgres` means the postgres folder in our local machine is mapped to the `/data/postgres` folder inside the docker container
 - Creates a common network called `postgres` so that our 2 containers can communicate with each other.

## Running the Docker containers and connecting PgAdmin to Postgres

Run `docker-compose up` in your terminal. Your computer will download the specified versions of the containers before activating them.

Your console output should look something like the following. I'm running an ubuntu system but it should pretty much be the same thing

{% gist https://gist.github.com/Qarun-Qadir-Bissoondial/99256e46d76f1b808db4460bbd879cef %}

Once this is up and running, go to `localhost:5050` in your browser and you should be greeting with the PgAdmin login screen. This is the administrative interface for Postgres. While this is not necessary to have, it's still pretty cool.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ewl59tazkt2sd9wtj0z2.png)

Log using the `PGADMIN_DEFAULT_EMAIL` and the `PGADMIN_DEFAULT_PASSWORD` from the `docker-compose.yml` file. In my example, these are `pgadmin4@pgadmin.com` and `admin` respectively.

Let's just recap what we did so far. We created 2 docker containers, one for Postgres and one for PgAdmin. We then launched those 2 containers and connected to PgAdmin through `localhost:5050` in the browser. Now we're going to connect PgAdmin to the Postgres container.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/q77w2c5eqsq981kgfmv2.png)

Create a new server by navigating to the right and right-clicking Servers > Create > Server.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/aafla0flu0o9l8h022sw.png)

Give your server a name. I'll call mine `express-postgres-docker`. Then, navigate to the Connection tab. Fill out the details so it looks like the following: 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dhk14axnmwk8m5l9660d.png)

Let's run through what's happening:

 - Host name/address is `postgres`. This field is asking us for the name or IP address of the machine that the Postgres database is located. Since we're using Docker, the container is considered a machine of its own with a name and address. The name is `postgres` as specified by our `docker-compose.yml` file. If we were connecting to a locally installed version of Postgres without Docker, it would have been `localhost` since the database would be located on your local machine.
 - Port is `5432`. This is the standard Postgres port. Since both Docker container are using the same network, they can see each other's port withouth having to use different IP addresses
 - Maintenance Database is `postgres`. This is standard and should not be altered.
 - Username is `postgres`. This was specified as `POSTGRES_USER` in the `docker-compose.yml` file. If you changed it, then you need to specify that change in this field.
 - Password is `password`. This was specified as `POSTGRES_PASSWORD` in the `docker-compose.yml` file. If you changed it, then you need to specify that change in this field.

Once you hit save, PgAdmin should be connected to your Postgres container. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/560rzm8qn45filgizjs7.png)

## Setting up your Express server

We'll need to install a few packages to get started. Our dependencies are going to be:

 - express
 - pg
 - pg-hstore
 - sequelize

Once installed, you'll need to create an `app.js` that looks like the following: 

{% gist https://gist.github.com/Qarun-Qadir-Bissoondial/cb31a23737844123581a8245aac2e911 %}



Run `node app.js` in your terminal and you should get the following output if your connection was successful:

```
Listening on port 5000
Executing (default): SELECT 1+1 AS result
Database connected!
```

## Conclusion

Congratulations! You just set up Postgres and PgAdmin in Docker containers and connected it to an Express Sequelize Project. Now you can use this to actually store data for your projects or use it as a platform to start learning more about Database and Database Caching. Happy Coding! :smile: