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
andpgadmin
- 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 for5050: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 |
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 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.
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 ispostgres
as specified by ourdocker-compose.yml
file. If we were connecting to a locally installed version of Postgres without Docker, it would have beenlocalhost
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 asPOSTGRES_USER
in thedocker-compose.yml
file. If you changed it, then you need to specify that change in this field. - Password is
password
. This was specified asPOSTGRES_PASSWORD
in thedocker-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:
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}`); }); |
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! 😄
Top comments (0)