Docker Compose DB

Photo by Todd Cravens on Unsplash

The Problem…

Developer environments can be a tedious process, even more so at an agency. You spend 2 days getting Client A’s secret sauce of a config rolling, only to need Client B’s config later in the week. While you are typically at the liberty of their setup, a few things can still be in your control. e.g. Tooling.

Over the past couple of months, I’ve had clients needing versions of databases across the board. Typically, we don’t think of it. Install this version, switch to that, run for a while…. rinse, and repeat. But what happens when you need to adapt quickly? What happens when Mac updates are pushed and brew installs break? Big Sur anyone? **The issue, as much as we try to stay consistent in our setup, it’s almost impossible when we continue to patch and reconfigure. We’ve come a long way in terms of language versioning, but not much has been said in terms of databases.

It seems like something was made just for this… hmm… what was it.

*a wild dockerfile appears!

Credit: Kim Wilkes via http://www.pokemonbattlecreator.com/

Below is an exported README from a current docker setup for database versioning.

Feel free to clone and follow along in exploring this probably over-engineered setup.

Automation is good! More must be better!

Source Repo:

https://github.com/mthomps4/docker-compose-db

Why Docker DB

The same reason you use docker for anything, projects differ, and while version managers work well for programming languages, they are nightmarish for databases. Have you ever tried to manage 3 versions of MySQL on a mac? While you can brew link all day… they still share common MySQL setup files and config files via brew… and as you could imagine, MySQL@5.6 and MySQL8 do not play nice together.

Why can’t we just host these in the cloud somewhere?! Absolutely, this is not to discourage other alternatives. Slap that MySQL instance up there and drop some ENVs. At some point, however, you’ll want more direct access to logging, and those free services and hosts stop being free. And what if you need to modify some my.cnf files? While services have options, and there are definitely pro’s and con’s to both, having more direct control over what is going in our dev setup can be ideal when onboarding and debugging various applications.

insert docker

The Goal

The main focus here is to give each DB instance a home, MySQL 5.6 shouldn’t care about MySQL8, etc. Each instance is self contained to it’s container with a persisted volume and config files are set for each as needed. This also lends itself to expansion with other DB tooling, postgres, redis, and memcache for example. If we need to tweak a config, it’s as easy as stopping the running container, editing/creating a file and restarting.

Setup

Note: If you are familiar with docker and want a single instance of a DB feel free to take a quick peek at the docker-compose.yml and copy over what you need. This setup will enable you to set up and run, MySQL 5.6, 5.7, 8, and Postgres either as a suite or as individual services (recommended).

We are leveraging Docker/MySQL and Docker/Postgres docs here.

  • Create a from and set your DB ENVs accordingly.
  • run
  • run config to ensure your ENVs are pulling through
  • You may need to ensure has executable permissions by running chmod
  • Run

gives the folder permissions and runs each DB initializer file respectfully to scaffold out or folder.

Let’s take a look at what happened: You should now see a folder. The initializers have created a folder PER DB inside, each with a folder, folder, and for MySQL a file. If you take a peek at the db/mysql8/init you’ll notice the initializer also added a file. This file will then be used by docker in pair with to run extra commands on setup. In our case, we’ve taken the liberty to use our ENVs to ensure a superuser was created with all permissions and a respected DB. While the ENVs provided to Docker/MySQL already do this, I’ve kept this file as an example for any other seeds and SQL you may need to run.

Note: For MySQL 5.6, I’ve set an example setup of Root with NO password. If you need something different be sure to tweak the initializer, ENVs, and compose, respectfully.

Ways to run this thing

To pull, build, and link all the things with docker lets run from our project directory.

This will pull the DB images from DockerHub, create the default network drivers, and build the containers to run. Now we are ready to run all or some of our services.

You should see

Let’s see what’s going on with

You should see something similar to:

You’ll notice here — our other DBs have “exited” with a code of 0 — ie. They were never started. MySQL 8 is ready for connections on port: 3308

Side Note: Currently each DB port is hardcoded in the docker-compose file. If another port is needed, swap these out in the command: and ports: section, I’ll look to make a quick follow with ENVs for these as well.

Connecting via DB Viewer

This is no different than running a DB locally. Your config will still look similar to the below image with localhost (or 127.0.0.1), port, username, and password.

Config:

Connected View:

Next Steps

Do I really need to into another project for all of this?!

Nope!

Thanks to docker we can build some nice alias commands with the command.


More about:

Matt Thompson

Matt Thompson is a lead engineer at Echobind, mentor, and content creator. Matt spends most of his time finding ways to improve the process for others. When he’s not building software, you’ll find him enjoying time with family, unplugging with a book, and woodworking.