Docker makes for Easy Databases

As our various JSON API implementations expect to be able to save and retrieve data beyond the lifetime of a single request, it makes sense that sooner or later, we're going to need a database.

Depending on your preference, you may wish to use MySQL, or Postgres for your database. Sometimes one is easier than the other, sometimes it makes no difference.

We're going to use Docker for our database. You do not need to. It just makes life easier, in my opinion.

Whichever database you want to use, a docker-compose.yml file is going to make working with Docker the easiest it can be.

Whether using MySQL or Postgres, create a docker-compose.yml in the root of your project's directory.

MySQL

Here's what we'll use for MySQL:

version: '3'

services:

    db_dev:
        image: mysql:5.7.21
#        volumes:
#          - "./volumes/mysql_dev:/var/lib/mysql"
        ports:
          - "3306:3306"
        environment:
          - MYSQL_ROOT_PASSWORD=password
          - MYSQL_DATABASE=your_db_name_here
          - MYSQL_USER=dbuser
          - MYSQL_PASSWORD=dbpassword

I have a Beginner friendly Docker tutorial right here on the site. There's nothing we are going to do here that requires anything but the most commonly used Docker commands.

Simply put this configuration will bring up a MySQL instance, running version 5.7.21 (the latest in the 5.x branch at the time of recording).

This MySQL database will be available on port 3306.

You won't need to create a database in this container. Instead the container comes up with an already available database - called in this case: your_db_name_here.

We have defined a user and password of dbuser / dbpassword.

The root password needs to be set. These are all mandatory environment variables for bringing a MySQL docker container online. Change any of the values as you see fit.

Postgres

Here's what we'll use for Postgres:

version: '3'

services:

  postgres:
    image: postgres:10.2
#    volumes:
#      - "./volumes/postgres:/var/lib/postgresql/data"
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=dbuser
      - POSTGRES_PASSWORD=dbpass
      - POSTGRES_DB=your_db_name_here

Again, very similar to MySQL. A Postgres container will come online with a preset database - your_db_name_here, with the user and password of dbuser / dbpass. Change as needed.

This container will be available on port 5432.

Volumes

Docker volumes are an interesting subject. I strongly recommend you watch the relevant videos in the Beginner friendly Docker tutorial if at all curious.

I have left both volumes commented out in the respective docker-compose.yml file definitions.

You do not need volumes to complete this series. However, if you'd like the data to stick around then be aware these are configured as bind mounts.

Ports

In both MySQL and Postgres definitions we have exposed the default ports.

For MySQL this is port 3306. And for Postgres, this is 5432.

There is a potential gotcha here.

If you are running multiple instances of the MySQL or Postgres container concurrently, or you have an existing MySQL / Postgres server running locally, only one of these may take the port.

This means you may need to change the definition slightly, to use a different port:

version: '3'

services:

  postgres:
    image: postgres:10.2
    ports:
      - "5433:5432"
    environment:
      - #etc

Here we've remapped the Postgres port to 5433. This still maps internally on the container to 5432. This means we can now have multiple Postgres containers up and running without risk of port conflict.

I typically just increment the number by one on a new project.

Of course, be sure to update your project to use the alternate port as appropriate.

Going Online and Offline

In order to bring this database up and online, we need to run a command:

docker-compose down && \
    docker-compose build --pull --no-cache && \
    docker-compose \
        -f docker-compose.yml \
    up -d --remove-orphans

Again, you don't need to know much about this command in order to use it. Please watch this course if you would like to understand it further.

To save from having to remember all that, I would advise creating a Makefile:

touch Makefile

And add the following contents:

dev:
    @docker-compose down && \
        docker-compose build --pull --no-cache && \
        docker-compose \
            -f docker-compose.yml \
        up -d --remove-orphans

Now you can simply run make dev to bring up your MySQL server.

To shut it down, docker-compose down.

Don't want to remember, or type all that? Add it to your Makefile:

dev:
    @docker-compose down && \
        docker-compose build --pull --no-cache && \
        docker-compose \
            -f docker-compose.yml \
        up -d --remove-orphans

down:
    @docker-compose down

Now you can run make dev to go up, and make down when you're done. Change the names of these commands to whatever you like.

Boom, crash course in Docker: done.