Docker Compose DB
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
.env
from.env.example
and set your DB ENVs accordingly. - run
source .env
- run
docker-compose config
to ensure your ENVs are pulling through - You may need to ensure
setup.sh
has executable permissions by runningchmod +x ./setup.sh
- Run
./setup.sh
./setup.sh
gives the folder ./initializers
permissions and runs each DB initializer file respectfully to scaffold out or db
folder.
Let’s take a look at what happened: You should now see a db
folder. The initializers have created a folder PER DB inside, each with a data
folder, init
folder, and for MySQL a my.conf
file. If you take a peek at the db/mysql8/init you’ll notice the initializer also added a 01.sql
file. This file will then be used by docker in pair with docker-entrypoint-initdb.d
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.
docker-compose up --no-start
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.
docker-compose start mysql8
You should see Starting mysql8 ... done
.
Let’s see what’s going on with
docker-compose logs -f
You should see something similar to:
MySQL8 | 2021-01-08T17:37:28.188065Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22' socket: '/var/run/mysqld/mysqld.sock' port: 3308 MySQL Community Server - GPL.MySQL56 exited with code 0postgres exited with code 0MySQL57 exited with code 0
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 cd
into another project for all of this?!
Nope!
Thanks to docker we can build some nice alias commands with the -f
command.
Base Command:docker-compose -f <path_to_file> <command> <service_name>
# .bash_aliasesalias db:mysql8:start='docker-compose -f ~/Databases/docker-compose.yml start mysql8'alias db:mysql8:stop='docker-compose -f ~/Databases/docker-compose.yml stop mysql8'