My Back-end Adventures - Setting up a local database (Postgres)

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.

Setting up Docker

First thing you'll wanna do is to install docker on your system as well as docker-compose. 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! 😄

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:

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

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.

Log using the PGADMIN_DEFAULT_EMAIL and the PGADMIN_DEFAULT_PASSWORD from the docker-compose.yml file. In my example, these are [e-mail] 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.

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

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:

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.

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:

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! 😄