DEV Community

Bearded JavaScripter
Bearded JavaScripter

Posted on • Edited on

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:

version: '3.5'
services:
postgres:
image: postgres:12.2-alpine
environment:
POSTGRES_USER: ${POSTGRES_USER:-postgres}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-password}
POSTGRES_DB: postgres
PGDATA: /data/postgres
volumes:
- postgres:/data/postgres
ports:
- "5432:5432"
networks:
- postgres
restart: unless-stopped
pgadmin:
image: dpage/pgadmin4:4.21
environment:
PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
volumes:
- pgadmin:/root/.pgadmin
ports:
- "5050:80"
networks:
- postgres
depends_on:
- postgres
restart: unless-stopped
networks:
postgres:
driver: bridge
volumes:
postgres:
pgadmin:

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

Creating network "express-postgres-docker_postgres" with driver "bridge"
Creating volume "express-postgres-docker_postgres" with default driver
Creating volume "express-postgres-docker_pgadmin" with default driver
Creating express-postgres-docker_postgres_1 ... done
Creating express-postgres-docker_pgadmin_1 ... done
Attaching to express-postgres-docker_postgres_1, express-postgres-docker_pgadmin_1
postgres_1 | The files belonging to this database system will be owned by user "postgres".
postgres_1 | This user must also own the server process.
postgres_1 |
postgres_1 | The database cluster will be initialized with locale "en_US.utf8".
postgres_1 | The default database encoding has accordingly been set to "UTF8".
postgres_1 | The default text search configuration will be set to "english".
postgres_1 |
postgres_1 | Data page checksums are disabled.
postgres_1 |
postgres_1 | fixing permissions on existing directory /data/postgres ... ok
postgres_1 | creating subdirectories ... ok
postgres_1 | selecting dynamic shared memory implementation ... posix
postgres_1 | selecting default max_connections ... 100
postgres_1 | selecting default shared_buffers ... 128MB
postgres_1 | selecting default time zone ... UTC
postgres_1 | creating configuration files ... ok
postgres_1 | running bootstrap script ... ok
postgres_1 | performing post-bootstrap initialization ... sh: locale: not found
postgres_1 | 2020-05-10 21:27:54.167 UTC [30] WARNING: no usable system locales were found
postgres_1 | ok
postgres_1 | syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
postgres_1 | You can change this by editing pg_hba.conf or using the option -A, or
postgres_1 | --auth-local and --auth-host, the next time you run initdb.
postgres_1 | ok
postgres_1 |
postgres_1 |
postgres_1 | Success. You can now start the database server using:
postgres_1 |
postgres_1 | pg_ctl -D /data/postgres -l logfile start
postgres_1 |
postgres_1 | waiting for server to start....2020-05-10 21:27:54.941 UTC [35] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
postgres_1 | 2020-05-10 21:27:54.943 UTC [35] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1 | 2020-05-10 21:27:54.959 UTC [36] LOG: database system was shut down at 2020-05-10 21:27:54 UTC
postgres_1 | 2020-05-10 21:27:54.963 UTC [35] LOG: database system is ready to accept connections
postgres_1 | done
postgres_1 | server started
postgres_1 |
postgres_1 | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
postgres_1 |
postgres_1 | waiting for server to shut down...2020-05-10 21:27:55.035 UTC [35] LOG: received fast shutdown request
postgres_1 | .2020-05-10 21:27:55.036 UTC [35] LOG: aborting any active transactions
postgres_1 | 2020-05-10 21:27:55.037 UTC [35] LOG: background worker "logical replication launcher" (PID 42) exited with exit code 1
postgres_1 | 2020-05-10 21:27:55.038 UTC [37] LOG: shutting down
postgres_1 | 2020-05-10 21:27:55.051 UTC [35] LOG: database system is shut down
postgres_1 | done
postgres_1 | server stopped
postgres_1 |
postgres_1 | PostgreSQL init process complete; ready for start up.
postgres_1 |
postgres_1 | 2020-05-10 21:27:55.143 UTC [1] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
postgres_1 | 2020-05-10 21:27:55.145 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
postgres_1 | 2020-05-10 21:27:55.145 UTC [1] LOG: listening on IPv6 address "::", port 5432
postgres_1 | 2020-05-10 21:27:55.149 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1 | 2020-05-10 21:27:55.162 UTC [44] LOG: database system was shut down at 2020-05-10 21:27:55 UTC
postgres_1 | 2020-05-10 21:27:55.165 UTC [1] LOG: database system is ready to accept connections
pgadmin_1 | NOTE: Configuring authentication for SERVER mode.
pgadmin_1 |
pgadmin_1 | [2020-05-10 21:28:01 +0000] [1] [INFO] Starting gunicorn 19.9.0
pgadmin_1 | [2020-05-10 21:28:01 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
pgadmin_1 | [2020-05-10 21:28:01 +0000] [1] [INFO] Using worker: threads
pgadmin_1 | [2020-05-10 21:28:01 +0000] [87] [INFO] Booting worker with pid: 87
view raw console-output hosted with ❤ by GitHub

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

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

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

Alt Text

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

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

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:

const express = require('express');
const { Sequelize } = require('sequelize');
/*
new Sequelize takes in database name (postgres), username (postgres) and password (password).
It also takes in the host and the dialect of SQL (in this case, Postgres).
If you want another database name, you'll need to go inside PgAdmin and create a new database.
And then put that database name here
const sequelize_connection = new Sequelize('new_database_name', 'postgres', 'password', {
host: 'localhost',
dialect: 'postgres'
});
*/
const sequelize_connection = new Sequelize('postgres', 'postgres', 'password', {
host: 'localhost',
dialect: 'postgres'
});
const app = express();
const port = process.env.PORT || 5000;
sequelize_connection.authenticate()
.then(() => { console.log('Database connected!') })
.catch(error => { console.error(error); });
app.listen(port, () => { console.log(`Listening on port ${port}`); });
view raw app.js hosted with ❤ by GitHub

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!
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)