Create new PostgreSQL database for local development using Docker

Depending on the type of project, you might need a PostgreSQL database for your app's local development. There are a couple of options available from running the Postgres.app Mac app or through homebrew. But one of the easiest ones is to use Docker.

Docker command for starting PostgreSQL instance

Assuming you've got Docker Desktop installed, enter the following one-liner into the terminal:

1
2
3
4
5
6
7
$ docker run --name app-postgres \
             --env POSTGRES_USER=app_dev \
             --env POSTGRES_PASSWORD=e1bc9e7f864d \
             --publish 127.0.0.1:5432:5432 \
             --detach \
             --restart unless-stopped \
             postgres:13

Here's what the switches do:

  • we'll name the container app-postgres
  • ask PostgreSQL to name both the superuser account and the default database as app_dev
  • give the user account app_dev a password
  • we publish the container's 5432 port to the host machine, but only to the localhost interface 127.0.0.1 and not the whole network
  • we let the container run in the background with --detach
  • normally Docker containers won't last over machine restarts, but we specifically ask for it with --restart unless-stopped
  • we also specifically bolt down database version with postgres:13 to avoid major version release changing from under us

You've got app_dev database

In the PostgreSQL instance, you now have

  • a database named app_dev
  • a user called app_dev with superuser rights with password set to the one you provided
  • you can connect to it at localhost:5432

Run command in psql

You can hit the psql console and inspect the database contents:

1
$ docker exec -it app-postgres psql -U app_dev

Here's what the switches do:

  • use -it so docker will simulate a terminal, and we can send commands to psql
  • you don't need to enter the password since you're running the command inside the container, and PostgreSQL is set up liberally

Load schemas and database dumps

Now you're ready to load a database schema:

1
docker exec -i app-postgres psql -U app_dev < schema/schema.sql

This time we strip the -t argument since we're piping input data from a file.

Or, you can insert a whole database dump created with pg_dump:

1
bzcat dump_202010291148.bz2 | docker exec -i app-postgres psql -U app_dev
Node doesn't wait for your database call to finish?

Node doesn't wait for your database call to finish?

Learn how asynchronous calls work and make your app run as you intended. Get short email course on asynchronicity and two chapters from Finish Your Node App.