Docker Compose DB
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!
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.
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.
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).
- Create a
.env.exampleand set your DB ENVs accordingly.
docker-composeconfig to ensure your ENVs are pulling through
- You may need to ensure
setup.shhas executable permissions by running chmod
./setup.sh gives the folder
./initializers permissions and runs each DB initializer file respectfully to scaffold out or
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
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 0 postgres exited with code 0 MySQL57 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.
Do I really need to
cd into another project for all of this?!
Thanks to docker we can build some nice alias commands with the
Base Command:docker-compose -f <path_to_file> <command> <service_name>
# .bash_aliases alias 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'